Context Navigation

source: brances/2913_MatlabScriptProblemInstanceProvider/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/Table/PivotTable/ExcelPivotTableField.cs @ 15887

Visit:

Last change on this file since 15887 was 12074, checked in by sraggl, 10 years ago
#2341: Added EPPlus-4.0.3 to ExtLibs
File size: 40.9 KB

Line
1	/*******************************************************************************
2	* You may amend and distribute as you like, but don't remove this header!
3	*
4	* EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
5	* See http://www.codeplex.com/EPPlus for details.
6	*
7	* Copyright (C) 2011 Jan Källman
8	*
9	* This library is free software; you can redistribute it and/or
10	* modify it under the terms of the GNU Lesser General Public
11	* License as published by the Free Software Foundation; either
12	* version 2.1 of the License, or (at your option) any later version.
13
14	* This library is distributed in the hope that it will be useful,
15	* but WITHOUT ANY WARRANTY; without even the implied warranty of
16	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
17	* See the GNU Lesser General Public License for more details.
18	*
19	* The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
20	* If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
21	*
22	* All code and executables are provided "as is" with no warranty either express or implied.
23	* The author accepts no liability for any damage or loss of business that this product may cause.
24	*
25	* Code change notes:
26	*
27	* Author Change Date
28	* ******************************************************************************
29	* Jan Källman Added 21-MAR-2011
30	* Jan Källman License changed GPL-->LGPL 2011-12-16
31	*******************************************************************************/
32	using System;
33	using System.Collections.Generic;
34	using System.Text;
35	using System.Xml;
36	using System.Globalization;
37
38	namespace OfficeOpenXml.Table.PivotTable
39	{
40
41	/// <summary>
42	/// Defines the axis for a PivotTable
43	/// </summary>
44	public enum ePivotFieldAxis
45	{
46	/// <summary>
47	/// None
48	/// </summary>
49	None=-1,
50	/// <summary>
51	/// Column axis
52	/// </summary>
53	Column,
54	/// <summary>
55	/// Page axis (Include Count Filter)
56	///
57	/// </summary>
58	Page,
59	/// <summary>
60	/// Row axis
61	/// </summary>
62	Row,
63	/// <summary>
64	/// Values axis
65	/// </summary>
66	Values
67	}
68	/// <summary>
69	/// Build-in table row functions
70	/// </summary>
71	public enum DataFieldFunctions
72	{
73	Average,
74	Count,
75	CountNums,
76	Max,
77	Min,
78	Product,
79	None,
80	StdDev,
81	StdDevP,
82	Sum,
83	Var,
84	VarP
85	}
86	/// <summary>
87	/// Defines the data formats for a field in the PivotTable
88	/// </summary>
89	public enum eShowDataAs
90	{
91	/// <summary>
92	/// Indicates the field is shown as the "difference from" a value.
93	/// </summary>
94	Difference,
95	/// <summary>
96	/// Indicates the field is shown as the "index.
97	/// </summary>
98	Index,
99	/// <summary>
100	/// Indicates that the field is shown as its normal datatype.
101	/// </summary>
102	Normal,
103	/// <summary>
104	/// /Indicates the field is show as the "percentage of" a value
105	/// </summary>
106	Percent,
107	/// <summary>
108	/// Indicates the field is shown as the "percentage difference from" a value.
109	/// </summary>
110	PercentDiff,
111	/// <summary>
112	/// Indicates the field is shown as the percentage of column.
113	/// </summary>
114	PercentOfCol,
115	/// <summary>
116	/// Indicates the field is shown as the percentage of row
117	/// </summary>
118	PercentOfRow,
119	/// <summary>
120	/// Indicates the field is shown as percentage of total.
121	/// </summary>
122	PercentOfTotal,
123	/// <summary>
124	/// Indicates the field is shown as running total in the table.
125	/// </summary>
126	RunTotal,
127	}
128	/// <summary>
129	/// Built-in subtotal functions
130	/// </summary>
131	[Flags]
132	public enum eSubTotalFunctions
133	{
134	None=1,
135	Count=2,
136	CountA=4,
137	Avg=8,
138	Default=16,
139	Min=32,
140	Max=64,
141	Product=128,
142	StdDev=256,
143	StdDevP=512,
144	Sum=1024,
145	Var=2048,
146	VarP=4096
147	}
148	/// <summary>
149	/// Data grouping
150	/// </summary>
151	[Flags]
152	public enum eDateGroupBy
153	{
154	Years = 1,
155	Quarters = 2,
156	Months = 4,
157	Days = 8,
158	Hours = 16,
159	Minutes = 32,
160	Seconds = 64
161	}
162	/// <summary>
163	/// Sorting
164	/// </summary>
165	public enum eSortType
166	{
167	None,
168	Ascending,
169	Descending
170	}
171	/// <summary>
172	/// A pivot table field.
173	/// </summary>
174	public class ExcelPivotTableField : XmlHelper
175	{
176	internal ExcelPivotTable _table;
177	internal ExcelPivotTableField(XmlNamespaceManager ns, XmlNode topNode,ExcelPivotTable table, int index, int baseIndex) :
178	base(ns, topNode)
179	{
180	Index = index;
181	BaseIndex = baseIndex;
182	_table = table;
183	}
184	public int Index
185	{
186	get;
187	set;
188	}
189	internal int BaseIndex
190	{
191	get;
192	set;
193	}
194	/// <summary>
195	/// Name of the field
196	/// </summary>
197	public string Name
198	{
199	get
200	{
201	string v = GetXmlNodeString("@name");
202	if (v == "")
203	{
204	return _cacheFieldHelper.GetXmlNodeString("@name");
205	}
206	else
207	{
208	return GetXmlNodeString("@name");
209	}
210	}
211	set
212	{
213	SetXmlNodeString("@name", value);
214	}
215	}
216	/// <summary>
217	/// Compact mode
218	/// </summary>
219	public bool Compact
220	{
221	get
222	{
223	return GetXmlNodeBool("@compact");
224	}
225	set
226	{
227	SetXmlNodeBool("@compact",value);
228	}
229	}
230	/// <summary>
231	/// A boolean that indicates whether the items in this field should be shown in Outline form
232	/// </summary>
233	public bool Outline
234	{
235	get
236	{
237	return GetXmlNodeBool("@outline");
238	}
239	set
240	{
241	SetXmlNodeBool("@outline",value);
242	}
243	}
244	/// <summary>
245	/// The custom text that is displayed for the subtotals label
246	/// </summary>
247	public bool SubtotalTop
248	{
249	get
250	{
251	return GetXmlNodeBool("@subtotalTop");
252	}
253	set
254	{
255	SetXmlNodeBool("@subtotalTop",value);
256	}
257	}
258	/// <summary>
259	/// A boolean that indicates whether to show all items for this field
260	/// </summary>
261	public bool ShowAll
262	{
263	get
264	{
265	return GetXmlNodeBool("@showAll");
266	}
267	set
268	{
269	SetXmlNodeBool("@showAll",value);
270	}
271	}
272	/// <summary>
273	/// The type of sort that is applied to this field
274	/// </summary>
275	public eSortType Sort
276	{
277	get
278	{
279	string v = GetXmlNodeString("@sortType");
280	return v == "" ? eSortType.None : (eSortType)Enum.Parse(typeof(eSortType), v, true);
281	}
282	set
283	{
284	if (value == eSortType.None)
285	{
286	DeleteNode("@sortType");
287	}
288	else
289	{
290	SetXmlNodeString("@sortType", value.ToString().ToLower(CultureInfo.InvariantCulture));
291	}
292	}
293	}
294	/// <summary>
295	/// A boolean that indicates whether manual filter is in inclusive mode
296	/// </summary>
297	public bool IncludeNewItemsInFilter
298	{
299	get
300	{
301	return GetXmlNodeBool("@includeNewItemsInFilter");
302	}
303	set
304	{
305	SetXmlNodeBool("@includeNewItemsInFilter",value);
306	}
307	}
308	/// <summary>
309	/// Enumeration of the different subtotal operations that can be applied to page, row or column fields
310	/// </summary>
311	public eSubTotalFunctions SubTotalFunctions
312	{
313	get
314	{
315	eSubTotalFunctions ret = 0;
316	XmlNodeList nl = TopNode.SelectNodes("d:items/d:item/@t", NameSpaceManager);
317	if (nl.Count == 0) return eSubTotalFunctions.None;
318	foreach (XmlAttribute item in nl)
319	{
320	try
321	{
322	ret \|= (eSubTotalFunctions)Enum.Parse(typeof(eSubTotalFunctions), item.Value, true);
323	}
324	catch (ArgumentException ex)
325	{
326	throw new ArgumentException("Unable to parse value of " + item.Value + " to a valid pivot table subtotal function", ex);
327	}
328	}
329	return ret;
330	}
331	set
332	{
333	if ((value & eSubTotalFunctions.None) == eSubTotalFunctions.None && (value != eSubTotalFunctions.None))
334	{
335	throw (new ArgumentException("Value None can not be combined with other values."));
336	}
337	if ((value & eSubTotalFunctions.Default) == eSubTotalFunctions.Default && (value != eSubTotalFunctions.Default))
338	{
339	throw (new ArgumentException("Value Default can not be combined with other values."));
340	}
341
342
343	// remove old attribute
344	XmlNodeList nl = TopNode.SelectNodes("d:items/d:item/@t", NameSpaceManager);
345	if (nl.Count > 0)
346	{
347	foreach (XmlAttribute item in nl)
348	{
349	DeleteNode("@" + item.Value + "Subtotal");
350	item.OwnerElement.ParentNode.RemoveChild(item.OwnerElement);
351	}
352	}
353
354
355	if (value==eSubTotalFunctions.None)
356	{
357	// for no subtotals, set defaultSubtotal to off
358	SetXmlNodeBool("@defaultSubtotal", false);
359	TopNode.InnerXml = "";
360	}
361	else
362	{
363	string innerXml = "";
364	int count = 0;
365	foreach (eSubTotalFunctions e in Enum.GetValues(typeof(eSubTotalFunctions)))
366	{
367	if ((value & e) == e)
368	{
369	var newTotalType = e.ToString();
370	var totalType = char.ToLower(newTotalType[0], CultureInfo.InvariantCulture) + newTotalType.Substring(1);
371	// add new attribute
372	SetXmlNodeBool("@" + totalType + "Subtotal", true);
373	innerXml += "<item t=\"" + totalType + "\" />";
374	count++;
375	}
376	}
377	TopNode.InnerXml = string.Format("<items count=\"{0}\">{1}</items>", count, innerXml);
378	}
379	}
380	}
381	/// <summary>
382	/// Type of axis
383	/// </summary>
384	public ePivotFieldAxis Axis
385	{
386	get
387	{
388	switch(GetXmlNodeString("@axis"))
389	{
390	case "axisRow":
391	return ePivotFieldAxis.Row;
392	case "axisCol":
393	return ePivotFieldAxis.Column;
394	case "axisPage":
395	return ePivotFieldAxis.Page;
396	case "axisValues":
397	return ePivotFieldAxis.Values;
398	default:
399	return ePivotFieldAxis.None;
400	}
401	}
402	internal set
403	{
404	switch (value)
405	{
406	case ePivotFieldAxis.Row:
407	SetXmlNodeString("@axis","axisRow");
408	break;
409	case ePivotFieldAxis.Column:
410	SetXmlNodeString("@axis","axisCol");
411	break;
412	case ePivotFieldAxis.Values:
413	SetXmlNodeString("@axis", "axisValues");
414	break;
415	case ePivotFieldAxis.Page:
416	SetXmlNodeString("@axis", "axisPage");
417	break;
418	default:
419	DeleteNode("@axis");
420	break;
421	}
422	}
423	}
424	/// <summary>
425	/// If the field is a row field
426	/// </summary>
427	public bool IsRowField
428	{
429	get
430	{
431	return (TopNode.SelectSingleNode(string.Format("../../d:rowFields/d:field[@x={0}]", Index), NameSpaceManager) != null);
432	}
433	internal set
434	{
435	if (value)
436	{
437	var rowsNode = TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
438	if (rowsNode == null)
439	{
440	_table.CreateNode("d:rowFields");
441	}
442	rowsNode = TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
443
444	AppendField(rowsNode, Index, "field", "x");
445	if (BaseIndex == Index)
446	{
447	TopNode.InnerXml = "<items count=\"1\"><item t=\"default\" /></items>";
448	}
449	else
450	{
451	TopNode.InnerXml = "<items count=\"0\"></items>";
452	}
453	}
454	else
455	{
456	XmlElement node = TopNode.SelectSingleNode(string.Format("../../d:rowFields/d:field[@x={0}]", Index), NameSpaceManager) as XmlElement;
457	if (node != null)
458	{
459	node.ParentNode.RemoveChild(node);
460	}
461	}
462	}
463	}
464	/// <summary>
465	/// If the field is a column field
466	/// </summary>
467	public bool IsColumnField
468	{
469	get
470	{
471	return (TopNode.SelectSingleNode(string.Format("../../d:colFields/d:field[@x={0}]", Index), NameSpaceManager) != null);
472	}
473	internal set
474	{
475	if (value)
476	{
477	var columnsNode = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
478	if (columnsNode == null)
479	{
480	_table.CreateNode("d:colFields");
481	}
482	columnsNode = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
483
484	AppendField(columnsNode, Index, "field", "x");
485	if (BaseIndex == Index)
486	{
487	TopNode.InnerXml = "<items count=\"1\"><item t=\"default\" /></items>";
488	}
489	else
490	{
491	TopNode.InnerXml = "<items count=\"0\"></items>";
492	}
493	}
494	else
495	{
496	XmlElement node = TopNode.SelectSingleNode(string.Format("../../d:colFields/d:field[@x={0}]", Index), NameSpaceManager) as XmlElement;
497	if (node != null)
498	{
499	node.ParentNode.RemoveChild(node);
500	}
501	}
502	}
503	}
504	/// <summary>
505	/// If the field is a datafield
506	/// </summary>
507	public bool IsDataField
508	{
509	get
510	{
511	return GetXmlNodeBool("@dataField", false);
512	}
513	}
514	/// <summary>
515	/// If the field is a page field.
516	/// </summary>
517	public bool IsPageField
518	{
519	get
520	{
521	return (Axis==ePivotFieldAxis.Page);
522	}
523	internal set
524	{
525	if (value)
526	{
527	var dataFieldsNode = TopNode.SelectSingleNode("../../d:pageFields", NameSpaceManager);
528	if (dataFieldsNode == null)
529	{
530	_table.CreateNode("d:pageFields");
531	dataFieldsNode = TopNode.SelectSingleNode("../../d:pageFields", NameSpaceManager);
532	}
533
534	TopNode.InnerXml = "<items count=\"1\"><item t=\"default\" /></items>";
535
536	XmlElement node = AppendField(dataFieldsNode, Index, "pageField", "fld");
537	_pageFieldSettings = new ExcelPivotTablePageFieldSettings(NameSpaceManager, node, this, Index);
538	}
539	else
540	{
541	_pageFieldSettings = null;
542	XmlElement node = TopNode.SelectSingleNode(string.Format("../../d:pageFields/d:pageField[@fld={0}]", Index), NameSpaceManager) as XmlElement;
543	if (node != null)
544	{
545	node.ParentNode.RemoveChild(node);
546	}
547	}
548	}
549	}
550	//public ExcelPivotGrouping DateGrouping
551	//{
552
553	//}
554	internal ExcelPivotTablePageFieldSettings _pageFieldSettings = null;
555	public ExcelPivotTablePageFieldSettings PageFieldSettings
556	{
557	get
558	{
559	return _pageFieldSettings;
560	}
561	}
562	internal eDateGroupBy DateGrouping
563	{
564	get;
565	set;
566	}
567	ExcelPivotTableFieldGroup _grouping=null;
568	/// <summary>
569	/// Grouping settings.
570	/// Null if the field has no grouping otherwise ExcelPivotTableFieldNumericGroup or ExcelPivotTableFieldNumericGroup.
571	/// </summary>
572	public ExcelPivotTableFieldGroup Grouping
573	{
574	get
575	{
576	return _grouping;
577	}
578	}
579	#region Private & internal Methods
580	internal XmlElement AppendField(XmlNode rowsNode, int index, string fieldNodeText, string indexAttrText)
581	{
582	XmlElement prevField = null, newElement;
583	foreach (XmlElement field in rowsNode.ChildNodes)
584	{
585	string x = field.GetAttribute(indexAttrText);
586	int fieldIndex;
587	if(int.TryParse(x, out fieldIndex))
588	{
589	if (fieldIndex == index) //Row already exists
590	{
591	return field;
592	}
593	//else if (fieldIndex > index)
594	//{
595	// newElement = rowsNode.OwnerDocument.CreateElement(fieldNodeText, ExcelPackage.schemaMain);
596	// newElement.SetAttribute(indexAttrText, index.ToString());
597	// rowsNode.InsertAfter(newElement, field);
598	//}
599	}
600	prevField=field;
601	}
602	newElement = rowsNode.OwnerDocument.CreateElement(fieldNodeText, ExcelPackage.schemaMain);
603	newElement.SetAttribute(indexAttrText, index.ToString());
604	rowsNode.InsertAfter(newElement, prevField);
605
606	return newElement;
607	}
608	internal XmlHelperInstance _cacheFieldHelper = null;
609	internal void SetCacheFieldNode(XmlNode cacheField)
610	{
611	_cacheFieldHelper = new XmlHelperInstance(NameSpaceManager, cacheField);
612	var groupNode = cacheField.SelectSingleNode("d:fieldGroup", NameSpaceManager);
613	if (groupNode!=null)
614	{
615	var groupBy = groupNode.SelectSingleNode("d:rangePr/@groupBy", NameSpaceManager);
616	if (groupBy==null)
617	{
618	_grouping = new ExcelPivotTableFieldNumericGroup(NameSpaceManager, cacheField);
619	}
620	else
621	{
622	DateGrouping=(eDateGroupBy)Enum.Parse(typeof(eDateGroupBy), groupBy.Value, true);
623	_grouping = new ExcelPivotTableFieldDateGroup(NameSpaceManager, groupNode);
624	}
625	}
626	}
627	#endregion
628	#region Grouping
629	internal ExcelPivotTableFieldDateGroup SetDateGroup(eDateGroupBy GroupBy, DateTime StartDate, DateTime EndDate, int interval)
630	{
631	ExcelPivotTableFieldDateGroup group;
632	group = new ExcelPivotTableFieldDateGroup(NameSpaceManager, _cacheFieldHelper.TopNode);
633	_cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsDate", true);
634	_cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsNonDate", false);
635	_cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsSemiMixedTypes", false);
636
637	group.TopNode.InnerXml += string.Format("<fieldGroup base=\"{0}\"><rangePr groupBy=\"{1}\" /><groupItems /></fieldGroup>", BaseIndex, GroupBy.ToString().ToLower(CultureInfo.InvariantCulture));
638
639	if (StartDate.Year < 1900)
640	{
641	_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@startDate", "1900-01-01T00:00:00");
642	}
643	else
644	{
645	_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@startDate", StartDate.ToString("s", CultureInfo.InvariantCulture));
646	_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@autoStart", "0");
647	}
648
649	if (EndDate==DateTime.MaxValue)
650	{
651	_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@endDate", "9999-12-31T00:00:00");
652	}
653	else
654	{
655	_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@endDate", EndDate.ToString("s", CultureInfo.InvariantCulture));
656	_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@autoEnd", "0");
657	}
658
659	int items = AddDateGroupItems(group, GroupBy, StartDate, EndDate, interval);
660	AddFieldItems(items);
661
662	_grouping = group;
663	return group;
664	}
665	internal ExcelPivotTableFieldNumericGroup SetNumericGroup(double start, double end, double interval)
666	{
667	ExcelPivotTableFieldNumericGroup group;
668	group = new ExcelPivotTableFieldNumericGroup(NameSpaceManager, _cacheFieldHelper.TopNode);
669	_cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsNumber", true);
670	_cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsInteger", true);
671	_cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsSemiMixedTypes", false);
672	_cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsString", false);
673
674	group.TopNode.InnerXml += string.Format("<fieldGroup base=\"{0}\"><rangePr autoStart=\"0\" autoEnd=\"0\" startNum=\"{1}\" endNum=\"{2}\" groupInterval=\"{3}\"/><groupItems /></fieldGroup>", BaseIndex, start.ToString(CultureInfo.InvariantCulture), end.ToString(CultureInfo.InvariantCulture), interval.ToString(CultureInfo.InvariantCulture));
675	int items = AddNumericGroupItems(group, start, end, interval);
676	AddFieldItems(items);
677
678	_grouping = group;
679	return group;
680	}
681
682	private int AddNumericGroupItems(ExcelPivotTableFieldNumericGroup group, double start, double end, double interval)
683	{
684	if (interval < 0)
685	{
686	throw (new Exception("The interval must be a positiv"));
687	}
688	if (start > end)
689	{
690	throw(new Exception("Then End number must be larger than the Start number"));
691	}
692
693	XmlElement groupItems = group.TopNode.SelectSingleNode("d:fieldGroup/d:groupItems", group.NameSpaceManager) as XmlElement;
694	int items = 2;
695	//First date
696	double index=start;
697	double nextIndex=start+interval;
698	AddGroupItem(groupItems, "<" + start.ToString(CultureInfo.InvariantCulture));
699
700	while (index < end)
701	{
702	AddGroupItem(groupItems, string.Format("{0}-{1}", index.ToString(CultureInfo.InvariantCulture), nextIndex.ToString(CultureInfo.InvariantCulture)));
703	index=nextIndex;
704	nextIndex+=interval;
705	items++;
706	}
707	AddGroupItem(groupItems, ">" + nextIndex.ToString(CultureInfo.InvariantCulture));
708	return items;
709	}
710
711	private void AddFieldItems(int items)
712	{
713	XmlElement prevNode = null;
714	XmlElement itemsNode = TopNode.SelectSingleNode("d:items", NameSpaceManager) as XmlElement;
715	for (int x = 0; x < items; x++)
716	{
717	var itemNode = itemsNode.OwnerDocument.CreateElement("item", ExcelPackage.schemaMain);
718	itemNode.SetAttribute("x", x.ToString());
719	if (prevNode == null)
720	{
721	itemsNode.PrependChild(itemNode);
722	}
723	else
724	{
725	itemsNode.InsertAfter(itemNode, prevNode);
726	}
727	prevNode = itemNode;
728	}
729	itemsNode.SetAttribute("count", (items + 1).ToString());
730	}
731
732	private int AddDateGroupItems(ExcelPivotTableFieldGroup group, eDateGroupBy GroupBy, DateTime StartDate, DateTime EndDate, int interval)
733	{
734	XmlElement groupItems = group.TopNode.SelectSingleNode("d:fieldGroup/d:groupItems", group.NameSpaceManager) as XmlElement;
735	int items = 2;
736	//First date
737	AddGroupItem(groupItems, "<" + StartDate.ToString("s", CultureInfo.InvariantCulture).Substring(0, 10));
738
739	switch (GroupBy)
740	{
741	case eDateGroupBy.Seconds:
742	case eDateGroupBy.Minutes:
743	AddTimeSerie(60, groupItems);
744	items += 60;
745	break;
746	case eDateGroupBy.Hours:
747	AddTimeSerie(24, groupItems);
748	items += 24;
749	break;
750	case eDateGroupBy.Days:
751	if (interval == 1)
752	{
753	DateTime dt = new DateTime(2008, 1, 1); //pick a year with 366 days
754	while (dt.Year == 2008)
755	{
756	AddGroupItem(groupItems, dt.ToString("dd-MMM"));
757	dt = dt.AddDays(1);
758	}
759	items += 366;
760	}
761	else
762	{
763	DateTime dt = StartDate;
764	items = 0;
765	while (dt < EndDate)
766	{
767	AddGroupItem(groupItems, dt.ToString("dd-MMM"));
768	dt = dt.AddDays(interval);
769	items++;
770	}
771	}
772	break;
773	case eDateGroupBy.Months:
774	AddGroupItem(groupItems, "jan");
775	AddGroupItem(groupItems, "feb");
776	AddGroupItem(groupItems, "mar");
777	AddGroupItem(groupItems, "apr");
778	AddGroupItem(groupItems, "may");
779	AddGroupItem(groupItems, "jun");
780	AddGroupItem(groupItems, "jul");
781	AddGroupItem(groupItems, "aug");
782	AddGroupItem(groupItems, "sep");
783	AddGroupItem(groupItems, "oct");
784	AddGroupItem(groupItems, "nov");
785	AddGroupItem(groupItems, "dec");
786	items += 12;
787	break;
788	case eDateGroupBy.Quarters:
789	AddGroupItem(groupItems, "Qtr1");
790	AddGroupItem(groupItems, "Qtr2");
791	AddGroupItem(groupItems, "Qtr3");
792	AddGroupItem(groupItems, "Qtr4");
793	items += 4;
794	break;
795	case eDateGroupBy.Years:
796	if(StartDate.Year>=1900 && EndDate!=DateTime.MaxValue)
797	{
798	for (int year = StartDate.Year; year <= EndDate.Year; year++)
799	{
800	AddGroupItem(groupItems, year.ToString());
801	}
802	items += EndDate.Year - StartDate.Year+1;
803	}
804	break;
805	default:
806	throw (new Exception("unsupported grouping"));
807	}
808
809	//Lastdate
810	AddGroupItem(groupItems, ">" + EndDate.ToString("s", CultureInfo.InvariantCulture).Substring(0, 10));
811	return items;
812	}
813
814	private void AddTimeSerie(int count, XmlElement groupItems)
815	{
816	for (int i = 0; i < count; i++)
817	{
818	AddGroupItem(groupItems, string.Format("{0:00}", i));
819	}
820	}
821
822	private void AddGroupItem(XmlElement groupItems, string value)
823	{
824	var s = groupItems.OwnerDocument.CreateElement("s", ExcelPackage.schemaMain);
825	s.SetAttribute("v", value);
826	groupItems.AppendChild(s);
827	}
828	#endregion
829	internal ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem> _items=null;
830	/// <summary>
831	/// Pivottable field Items. Used for grouping.
832	/// </summary>
833	public ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem> Items
834	{
835	get
836	{
837	if (_items == null)
838	{
839	_items = new ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem>(_table);
840	foreach (XmlNode node in TopNode.SelectNodes("d:items//d:item", NameSpaceManager))
841	{
842	var item = new ExcelPivotTableFieldItem(NameSpaceManager, node,this);
843	if (item.T == "")
844	{
845	_items.AddInternal(item);
846	}
847	}
848	//if (_grouping is ExcelPivotTableFieldDateGroup)
849	//{
850	// ExcelPivotTableFieldDateGroup dtgrp = ((ExcelPivotTableFieldDateGroup)_grouping);
851
852	// ExcelPivotTableFieldItem minItem=null, maxItem=null;
853	// foreach (var item in _items)
854	// {
855	// if (item.X == 0)
856	// {
857	// minItem = item;
858	// }
859	// else if (maxItem == null \|\| maxItem.X < item.X)
860	// {
861	// maxItem = item;
862	// }
863	// }
864	// if (dtgrp.AutoStart)
865	// {
866	// _items._list.Remove(minItem);
867	// }
868	// if (dtgrp.AutoEnd)
869	// {
870	// _items._list.Remove(maxItem);
871	// }
872
873	//}
874	}
875	return _items;
876	}
877	}
878	/// <summary>
879	/// Add numberic grouping to the field
880	/// </summary>
881	/// <param name="Start">Start value</param>
882	/// <param name="End">End value</param>
883	/// <param name="Interval">Interval</param>
884	public void AddNumericGrouping(double Start, double End, double Interval)
885	{
886	ValidateGrouping();
887	SetNumericGroup(Start, End, Interval);
888	}
889	/// <summary>
890	/// Add a date grouping on this field.
891	/// </summary>
892	/// <param name="groupBy">Group by</param>
893	public void AddDateGrouping(eDateGroupBy groupBy)
894	{
895	AddDateGrouping(groupBy, DateTime.MinValue, DateTime.MaxValue,1);
896	}
897	/// <summary>
898	/// Add a date grouping on this field.
899	/// </summary>
900	/// <param name="groupBy">Group by</param>
901	/// <param name="startDate">Fixed start date. Use DateTime.MinValue for auto</param>
902	/// <param name="endDate">Fixed end date. Use DateTime.MaxValue for auto</param>
903	public void AddDateGrouping(eDateGroupBy groupBy, DateTime startDate, DateTime endDate)
904	{
905	AddDateGrouping(groupBy, startDate, endDate, 1);
906	}
907	/// <summary>
908	/// Add a date grouping on this field.
909	/// </summary>
910	/// <param name="days">Number of days when grouping on days</param>
911	/// <param name="startDate">Fixed start date. Use DateTime.MinValue for auto</param>
912	/// <param name="endDate">Fixed end date. Use DateTime.MaxValue for auto</param>
913	public void AddDateGrouping(int days, DateTime startDate, DateTime endDate)
914	{
915	AddDateGrouping(eDateGroupBy.Days, startDate, endDate, days);
916	}
917	private void AddDateGrouping(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, int groupInterval)
918	{
919	if (groupInterval < 1 \|\| groupInterval >= Int16.MaxValue)
920	{
921	throw (new ArgumentOutOfRangeException("Group interval is out of range"));
922	}
923	if (groupInterval > 1 && groupBy != eDateGroupBy.Days)
924	{
925	throw (new ArgumentException("Group interval is can only be used when groupBy is Days"));
926	}
927	ValidateGrouping();
928
929	bool firstField = true;
930	List<ExcelPivotTableField> fields=new List<ExcelPivotTableField>();
931	//Seconds
932	if ((groupBy & eDateGroupBy.Seconds) == eDateGroupBy.Seconds)
933	{
934	fields.Add(AddField(eDateGroupBy.Seconds, startDate, endDate, ref firstField));
935	}
936	//Minutes
937	if ((groupBy & eDateGroupBy.Minutes) == eDateGroupBy.Minutes)
938	{
939	fields.Add(AddField(eDateGroupBy.Minutes, startDate, endDate, ref firstField));
940	}
941	//Hours
942	if ((groupBy & eDateGroupBy.Hours) == eDateGroupBy.Hours)
943	{
944	fields.Add(AddField(eDateGroupBy.Hours, startDate, endDate, ref firstField));
945	}
946	//Days
947	if ((groupBy & eDateGroupBy.Days) == eDateGroupBy.Days)
948	{
949	fields.Add(AddField(eDateGroupBy.Days, startDate, endDate, ref firstField, groupInterval));
950	}
951	//Month
952	if ((groupBy & eDateGroupBy.Months) == eDateGroupBy.Months)
953	{
954	fields.Add(AddField(eDateGroupBy.Months, startDate, endDate, ref firstField));
955	}
956	//Quarters
957	if ((groupBy & eDateGroupBy.Quarters) == eDateGroupBy.Quarters)
958	{
959	fields.Add(AddField(eDateGroupBy.Quarters, startDate, endDate, ref firstField));
960	}
961	//Years
962	if ((groupBy & eDateGroupBy.Years) == eDateGroupBy.Years)
963	{
964	fields.Add(AddField(eDateGroupBy.Years, startDate, endDate, ref firstField));
965	}
966
967	if (fields.Count > 1) _cacheFieldHelper.SetXmlNodeString("d:fieldGroup/@par", (_table.Fields.Count - 1).ToString());
968	if (groupInterval != 1)
969	{
970	_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@groupInterval", groupInterval.ToString());
971	}
972	else
973	{
974	_cacheFieldHelper.DeleteNode("d:fieldGroup/d:rangePr/@groupInterval");
975	}
976	_items = null;
977	}
978
979	private void ValidateGrouping()
980	{
981	if (!(IsColumnField \|\| IsRowField))
982	{
983	throw (new Exception("Field must be a row or column field"));
984	}
985	foreach (var field in _table.Fields)
986	{
987	if (field.Grouping != null)
988	{
989	throw (new Exception("Grouping already exists"));
990	}
991	}
992	}
993	private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref bool firstField)
994	{
995	return AddField(groupBy, startDate, endDate, ref firstField,1);
996	}
997	private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref bool firstField, int interval)
998	{
999	if (firstField == false)
1000	{
1001	//Pivot field
1002	var topNode = _table.PivotTableXml.SelectSingleNode("//d:pivotFields", _table.NameSpaceManager);
1003	var fieldNode = _table.PivotTableXml.CreateElement("pivotField", ExcelPackage.schemaMain);
1004	fieldNode.SetAttribute("compact", "0");
1005	fieldNode.SetAttribute("outline", "0");
1006	fieldNode.SetAttribute("showAll", "0");
1007	fieldNode.SetAttribute("defaultSubtotal", "0");
1008	topNode.AppendChild(fieldNode);
1009
1010	var field = new ExcelPivotTableField(_table.NameSpaceManager, fieldNode, _table, _table.Fields.Count, Index);
1011	field.DateGrouping = groupBy;
1012
1013	XmlNode rowColFields;
1014	if (IsRowField)
1015	{
1016	rowColFields=TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
1017	}
1018	else
1019	{
1020	rowColFields = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
1021	}
1022
1023	int fieldIndex, index = 0;
1024	foreach (XmlElement rowfield in rowColFields.ChildNodes)
1025	{
1026	if (int.TryParse(rowfield.GetAttribute("x"), out fieldIndex))
1027	{
1028	if (_table.Fields[fieldIndex].BaseIndex == BaseIndex)
1029	{
1030	var newElement = rowColFields.OwnerDocument.CreateElement("field", ExcelPackage.schemaMain);
1031	newElement.SetAttribute("x", field.Index.ToString());
1032	rowColFields.InsertBefore(newElement, rowfield);
1033	break;
1034	}
1035	}
1036	index++;
1037	}
1038
1039	if (IsRowField)
1040	{
1041	_table.RowFields.Insert(field, index);
1042	}
1043	else
1044	{
1045	_table.ColumnFields.Insert(field, index);
1046	}
1047
1048	_table.Fields.AddInternal(field);
1049
1050	AddCacheField(field, startDate, endDate, interval);
1051	return field;
1052	}
1053	else
1054	{
1055	firstField = false;
1056	DateGrouping = groupBy;
1057	Compact = false;
1058	SetDateGroup(groupBy, startDate, endDate, interval);
1059	return this;
1060	}
1061	}
1062	private void AddCacheField(ExcelPivotTableField field, DateTime startDate, DateTime endDate, int interval)
1063	{
1064	//Add Cache definition field.
1065	var cacheTopNode = _table.CacheDefinition.CacheDefinitionXml.SelectSingleNode("//d:cacheFields", _table.NameSpaceManager);
1066	var cacheFieldNode = _table.CacheDefinition.CacheDefinitionXml.CreateElement("cacheField", ExcelPackage.schemaMain);
1067
1068	cacheFieldNode.SetAttribute("name", field.DateGrouping.ToString());
1069	cacheFieldNode.SetAttribute("databaseField", "0");
1070	cacheTopNode.AppendChild(cacheFieldNode);
1071	field.SetCacheFieldNode(cacheFieldNode);
1072
1073	field.SetDateGroup(field.DateGrouping, startDate, endDate, interval);
1074	}
1075	}
1076	}

Note: See TracBrowser for help on using the repository browser.

Download in other formats:

Update cookies preferences