Context Navigation

source: branches/Async/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/3.1.3/EPPlus-3.1.3/Table/PivotTable/ExcelPivotTableField.cs @ 13042

Visit:

Last change on this file since 13042 was 9580, checked in by sforsten, 12 years ago

added SymbolicDataAnalysisExpressionExcelFormatter
changed modifiers in SymbolicExpressionTreeChart of methods SaveImageAsBitmap and SaveImageAsEmf to public
added menu item ExportSymbolicSolutionToExcelMenuItem to export a symbolic solution to an excel file
added EPPlus-3.1.3 to ExtLibs

File size: 40.8 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());
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]) + 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());
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