Context Navigation

source: stable/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/ExcelStyles.cs @ 18000

Visit:

Last change on this file since 18000 was 12074, checked in by sraggl, 10 years ago
#2341: Added EPPlus-4.0.3 to ExtLibs
File size: 40.5 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 Initial Release 2009-10-01
30	* Jan Källman License changed GPL-->LGPL 2011-12-27
31	*******************************************************************************/
32	using System;
33	using System.Xml;
34	using System.Linq;
35	using System.Collections.Generic;
36	using OfficeOpenXml.FormulaParsing.Excel.Functions.Logical;
37	using draw=System.Drawing;
38	using OfficeOpenXml.Style;
39	using OfficeOpenXml.Style.XmlAccess;
40	using OfficeOpenXml.Style.Dxf;
41	using OfficeOpenXml.ConditionalFormatting;
42	namespace OfficeOpenXml
43	{
44	/// <summary>
45	/// Containts all shared cell styles for a workbook
46	/// </summary>
47	public sealed class ExcelStyles : XmlHelper
48	{
49	const string NumberFormatsPath = "d:styleSheet/d:numFmts";
50	const string FontsPath = "d:styleSheet/d:fonts";
51	const string FillsPath = "d:styleSheet/d:fills";
52	const string BordersPath = "d:styleSheet/d:borders";
53	const string CellStyleXfsPath = "d:styleSheet/d:cellStyleXfs";
54	const string CellXfsPath = "d:styleSheet/d:cellXfs";
55	const string CellStylesPath = "d:styleSheet/d:cellStyles";
56	const string dxfsPath = "d:styleSheet/d:dxfs";
57
58	//internal Dictionary<int, ExcelXfs> Styles = new Dictionary<int, ExcelXfs>();
59	XmlDocument _styleXml;
60	ExcelWorkbook _wb;
61	XmlNamespaceManager _nameSpaceManager;
62	internal int _nextDfxNumFmtID = 164;
63	internal ExcelStyles(XmlNamespaceManager NameSpaceManager, XmlDocument xml, ExcelWorkbook wb) :
64	base(NameSpaceManager, xml)
65	{
66	_styleXml=xml;
67	_wb = wb;
68	_nameSpaceManager = NameSpaceManager;
69	SchemaNodeOrder = new string[] { "numFmts", "fonts", "fills", "borders", "cellStyleXfs", "cellXfs", "cellStyles", "dxfs" };
70	LoadFromDocument();
71	}
72	/// <summary>
73	/// Loads the style XML to memory
74	/// </summary>
75	private void LoadFromDocument()
76	{
77	//NumberFormats
78	ExcelNumberFormatXml.AddBuildIn(NameSpaceManager, NumberFormats);
79	XmlNode numNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager);
80	if (numNode != null)
81	{
82	foreach (XmlNode n in numNode)
83	{
84	ExcelNumberFormatXml nf = new ExcelNumberFormatXml(_nameSpaceManager, n);
85	NumberFormats.Add(nf.Id, nf);
86	if (nf.NumFmtId >= NumberFormats.NextId) NumberFormats.NextId=nf.NumFmtId+1;
87	}
88	}
89
90	//Fonts
91	XmlNode fontNode = _styleXml.SelectSingleNode(FontsPath, _nameSpaceManager);
92	foreach (XmlNode n in fontNode)
93	{
94	ExcelFontXml f = new ExcelFontXml(_nameSpaceManager, n);
95	Fonts.Add(f.Id, f);
96	}
97
98	//Fills
99	XmlNode fillNode = _styleXml.SelectSingleNode(FillsPath, _nameSpaceManager);
100	foreach (XmlNode n in fillNode)
101	{
102	ExcelFillXml f;
103	if (n.FirstChild != null && n.FirstChild.LocalName == "gradientFill")
104	{
105	f = new ExcelGradientFillXml(_nameSpaceManager, n);
106	}
107	else
108	{
109	f = new ExcelFillXml(_nameSpaceManager, n);
110	}
111	Fills.Add(f.Id, f);
112	}
113
114	//Borders
115	XmlNode borderNode = _styleXml.SelectSingleNode(BordersPath, _nameSpaceManager);
116	foreach (XmlNode n in borderNode)
117	{
118	ExcelBorderXml b = new ExcelBorderXml(_nameSpaceManager, n);
119	Borders.Add(b.Id, b);
120	}
121
122	//cellStyleXfs
123	XmlNode styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager);
124	if (styleXfsNode != null)
125	{
126	foreach (XmlNode n in styleXfsNode)
127	{
128	ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this);
129	CellStyleXfs.Add(item.Id, item);
130	}
131	}
132
133	XmlNode styleNode = _styleXml.SelectSingleNode(CellXfsPath, _nameSpaceManager);
134	for (int i = 0; i < styleNode.ChildNodes.Count; i++)
135	{
136	XmlNode n = styleNode.ChildNodes[i];
137	ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this);
138	CellXfs.Add(item.Id, item);
139	}
140
141	//cellStyle
142	XmlNode namedStyleNode = _styleXml.SelectSingleNode(CellStylesPath, _nameSpaceManager);
143	if (namedStyleNode != null)
144	{
145	foreach (XmlNode n in namedStyleNode)
146	{
147	ExcelNamedStyleXml item = new ExcelNamedStyleXml(_nameSpaceManager, n, this);
148	NamedStyles.Add(item.Name, item);
149	}
150	}
151
152	//dxfsPath
153	XmlNode dxfsNode = _styleXml.SelectSingleNode(dxfsPath, _nameSpaceManager);
154	if (dxfsNode != null)
155	{
156	foreach (XmlNode x in dxfsNode)
157	{
158	ExcelDxfStyleConditionalFormatting item = new ExcelDxfStyleConditionalFormatting(_nameSpaceManager, x, this);
159	Dxfs.Add(item.Id, item);
160	}
161	}
162	}
163	internal ExcelStyle GetStyleObject(int Id,int PositionID, string Address)
164	{
165	if (Id < 0) Id = 0;
166	return new ExcelStyle(this, PropertyChange, PositionID, Address, Id);
167	}
168	/// <summary>
169	/// Handels changes of properties on the style objects
170	/// </summary>
171	/// <param name="sender"></param>
172	/// <param name="e"></param>
173	/// <returns></returns>
174	internal int PropertyChange(StyleBase sender, Style.StyleChangeEventArgs e)
175	{
176	var address = new ExcelAddressBase(e.Address);
177	var ws = _wb.Worksheets[e.PositionID];
178	Dictionary<int, int> styleCashe = new Dictionary<int, int>();
179	//Set single address
180	lock (ws._styles)
181	{
182	SetStyleAddress(sender, e, address, ws, ref styleCashe);
183	if (address.Addresses != null)
184	{
185	//Handle multiaddresses
186	foreach (var innerAddress in address.Addresses)
187	{
188	SetStyleAddress(sender, e, innerAddress, ws, ref styleCashe);
189	}
190	}
191	}
192	return 0;
193	}
194
195	private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary<int, int> styleCashe)
196	{
197	if (address.Start.Column == 0 \|\| address.Start.Row == 0)
198	{
199	throw (new Exception("error address"));
200	}
201	//Columns
202	else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
203	{
204	ExcelColumn column;
205	int col = address.Start.Column, row = 0;
206	//Get the startcolumn
207	//ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
208	if (!ws._values.Exists(0, address.Start.Column))
209	{
210	column = ws.Column(address.Start.Column);
211	}
212	else
213	{
214	column = ws._values.GetValue(0, address.Start.Column) as ExcelColumn;
215	}
216
217
218	//var index = ws._columns.IndexOf(colID);
219	while (column.ColumnMin <= address.End.Column)
220	{
221	if (column.ColumnMax > address.End.Column)
222	{
223	var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
224	column.ColumnMax = address.End.Column;
225	}
226	var s = ws._styles.GetValue(0, column.ColumnMin);
227	if (styleCashe.ContainsKey(s))
228	{
229	//column.StyleID = styleCashe[s];
230	ws._styles.SetValue(0, column.ColumnMin, styleCashe[s]);
231	ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
232	}
233	else
234	{
235	ExcelXfs st = CellXfs[s];
236	int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
237	styleCashe.Add(s, newId);
238	//column.StyleID = newId;
239	ws.SetStyle(0, column.ColumnMin, newId);
240	}
241
242	//index++;
243
244	if (!ws._values.NextCell(ref row, ref col) \|\| row > 0)
245	{
246	column._columnMax = address.End.Column;
247	break;
248	}
249	else
250	{
251	column = (ws._values.GetValue(0, col) as ExcelColumn);
252	}
253	}
254
255	if (column._columnMax < address.End.Column)
256	{
257	var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
258	newCol._columnMax = address.End.Column;
259
260	var s = ws._styles.GetValue(0, column.ColumnMin);
261	if (styleCashe.ContainsKey(s))
262	{
263	//newCol.StyleID = styleCashe[s];
264	//ws._styles.SetValue(0, column.ColumnMin, styleCashe[s]);
265	ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
266	}
267	else
268	{
269	ExcelXfs st = CellXfs[s];
270	int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
271	styleCashe.Add(s, newId);
272	//newCol.StyleID = newId;
273	ws.SetStyle(0, column.ColumnMin, newId);
274	}
275
276	column._columnMax = address.End.Column;
277	}
278
279	//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
280	var cse = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
281	while (cse.Next())
282	{
283	if (cse.Column >= address.Start.Column &&
284	cse.Column <= address.End.Column)
285	{
286	if (styleCashe.ContainsKey(cse.Value))
287	{
288	ws.SetStyle(cse.Row, cse.Column, styleCashe[cse.Value]);
289	}
290	else
291	{
292	ExcelXfs st = CellXfs[cse.Value];
293	int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
294	styleCashe.Add(cse.Value, newId);
295	//cse.Value = newId;
296	ws.SetStyle(cse.Row, cse.Column, newId);
297	}
298	}
299	}
300	}
301	//Rows
302	else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
303	{
304	for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
305	{
306	//ExcelRow row = ws.Row(rowNum);
307	var s = ws._styles.GetValue(rowNum, 0);
308	if (s == 0)
309	{
310	//iteratte all columns and set the row to the style of the last column
311	var cse = new CellsStoreEnumerator<int>(ws._styles, 0, 1, 0, ExcelPackage.MaxColumns);
312	while (cse.Next())
313	{
314	s = cse.Value;
315	var c = ws._values.GetValue(cse.Row, cse.Column) as ExcelColumn;
316	if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
317	{
318	for (int col = c.ColumnMin; col < c.ColumnMax; col++)
319	{
320	if (!ws._styles.Exists(rowNum, col))
321	{
322	ws._styles.SetValue(rowNum, col, s);
323	}
324	}
325	}
326	}
327	ws.SetStyle(rowNum, 0, s);
328	cse.Dispose();
329	}
330	if (styleCashe.ContainsKey(s))
331	{
332	ws.SetStyle(rowNum, 0, styleCashe[s]);
333	//row.StyleID = styleCashe[s];
334	}
335	else
336	{
337	ExcelXfs st = CellXfs[s];
338	int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
339	styleCashe.Add(s, newId);
340	ws._styles.SetValue(rowNum, 0, newId);
341	ws.SetStyle(rowNum, 0, newId);
342	}
343	}
344
345	//Get Start Cell
346	//ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
347	//int index = ws._cells.IndexOf(rowID);
348
349	//index = ~index;
350	var cse2 = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
351	//while (index < ws._cells.Count)
352	while (cse2.Next())
353	{
354	//var cell = ws._cells[index] as ExcelCell;
355	//if(cell.Row > address.End.Row)
356	//{
357	// break;
358	//}
359	var s = cse2.Value;
360	if (styleCashe.ContainsKey(s))
361	{
362	ws.SetStyle(cse2.Row, cse2.Column, styleCashe[s]);
363	}
364	else
365	{
366	ExcelXfs st = CellXfs[s];
367	int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
368	styleCashe.Add(s, newId);
369	cse2.Value = newId;
370	ws.SetStyle(cse2.Row, cse2.Column, newId);
371	}
372	}
373	}
374	else //Cellrange
375	{
376	//var cse = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
377	//while(cse.Next())
378	for (int col = address.Start.Column; col <= address.End.Column; col++)
379	{
380	for (int row = address.Start.Row; row <= address.End.Row; row++)
381	{
382	//ExcelCell cell = ws.Cell(row, col);
383	//int s = ws._styles.GetValue(row, col);
384	var s = GetStyleId(ws, row, col);
385	if (styleCashe.ContainsKey(s))
386	{
387	ws.SetStyle(row, col, styleCashe[s]);
388	}
389	else
390	{
391	ExcelXfs st = CellXfs[s];
392	int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
393	styleCashe.Add(s, newId);
394	ws.SetStyle(row, col, newId);
395	}
396	}
397	}
398	}
399	}
400
401	internal int GetStyleId(ExcelWorksheet ws, int row, int col)
402	{
403	int v=0;
404	if (ws._styles.Exists(row, col, ref v))
405	{
406	return v;
407	}
408	else
409	{
410	if (ws._styles.Exists(row, 0, ref v)) //First Row
411	{
412	return v;
413	}
414	else // then column
415	{
416	if (ws._styles.Exists(0, col, ref v))
417	{
418	return v;
419	}
420	else
421	{
422	int r=0,c=col;
423	if(ws._values.PrevCell(ref r,ref c))
424	{
425	var column=ws._values.GetValue(0,c) as ExcelColumn;
426	if (column != null && column.ColumnMax >= col) //Fixes issue 15174
427	{
428	return ws._styles.GetValue(0, c);
429	}
430	else
431	{
432	return 0;
433	}
434	}
435	else
436	{
437	return 0;
438	}
439	}
440
441	}
442	}
443
444	}
445	/// <summary>
446	/// Handles property changes on Named styles.
447	/// </summary>
448	/// <param name="sender"></param>
449	/// <param name="e"></param>
450	/// <returns></returns>
451	internal int NamedStylePropertyChange(StyleBase sender, Style.StyleChangeEventArgs e)
452	{
453
454	int index = NamedStyles.FindIndexByID(e.Address);
455	if (index >= 0)
456	{
457	int newId = CellStyleXfs[NamedStyles[index].StyleXfId].GetNewID(CellStyleXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
458	int prevIx=NamedStyles[index].StyleXfId;
459	NamedStyles[index].StyleXfId = newId;
460	NamedStyles[index].Style.Index = newId;
461
462	NamedStyles[index].XfId = int.MinValue;
463	foreach (var style in CellXfs)
464	{
465	if (style.XfId == prevIx)
466	{
467	style.XfId = newId;
468	}
469	}
470	}
471	return 0;
472	}
473	public ExcelStyleCollection<ExcelNumberFormatXml> NumberFormats = new ExcelStyleCollection<ExcelNumberFormatXml>();
474	public ExcelStyleCollection<ExcelFontXml> Fonts = new ExcelStyleCollection<ExcelFontXml>();
475	public ExcelStyleCollection<ExcelFillXml> Fills = new ExcelStyleCollection<ExcelFillXml>();
476	public ExcelStyleCollection<ExcelBorderXml> Borders = new ExcelStyleCollection<ExcelBorderXml>();
477	public ExcelStyleCollection<ExcelXfs> CellStyleXfs = new ExcelStyleCollection<ExcelXfs>();
478	public ExcelStyleCollection<ExcelXfs> CellXfs = new ExcelStyleCollection<ExcelXfs>();
479	public ExcelStyleCollection<ExcelNamedStyleXml> NamedStyles = new ExcelStyleCollection<ExcelNamedStyleXml>();
480	public ExcelStyleCollection<ExcelDxfStyleConditionalFormatting> Dxfs = new ExcelStyleCollection<ExcelDxfStyleConditionalFormatting>();
481
482	internal string Id
483	{
484	get { return ""; }
485	}
486
487	public ExcelNamedStyleXml CreateNamedStyle(string name)
488	{
489	return CreateNamedStyle(name, null);
490	}
491	public ExcelNamedStyleXml CreateNamedStyle(string name, ExcelStyle Template)
492	{
493	if (_wb.Styles.NamedStyles.ExistsKey(name))
494	{
495	throw new Exception(string.Format("Key {0} already exists in collection", name));
496	}
497
498	ExcelNamedStyleXml style;
499	style = new ExcelNamedStyleXml(NameSpaceManager, this);
500	int xfIdCopy, positionID;
501	ExcelStyles styles;
502	if (Template == null)
503	{
504	// style.Style = new ExcelStyle(this, NamedStylePropertyChange, -1, name, 0);
505	xfIdCopy = 0;
506	positionID = -1;
507	styles = this;
508	}
509	else
510	{
511	if (Template.PositionID < 0 && Template.Styles==this)
512	{
513	xfIdCopy = Template.Index;
514	positionID=Template.PositionID;
515	styles = this;
516	//style.Style = new ExcelStyle(this, NamedStylePropertyChange, Template.PositionID, name, Template.Index);
517	//style.StyleXfId = Template.Index;
518	}
519	else
520	{
521	xfIdCopy = Template.XfId;
522	positionID = -1;
523	styles = Template.Styles;
524	}
525	}
526	//Clone namedstyle
527	int styleXfId = CloneStyle(styles, xfIdCopy, true);
528	//Close cells style
529	CellStyleXfs[styleXfId].XfId = CellStyleXfs.Count-1;
530	int xfid = CloneStyle(styles, xfIdCopy, false, true); //Always add a new style (We create a new named style here)
531	CellXfs[xfid].XfId = styleXfId;
532	style.Style = new ExcelStyle(this, NamedStylePropertyChange, positionID, name, styleXfId);
533	style.StyleXfId = styleXfId;
534
535	style.Name = name;
536	int ix =_wb.Styles.NamedStyles.Add(style.Name, style);
537	style.Style.SetIndex(ix);
538	//style.Style.XfId = ix;
539	return style;
540	}
541	public void UpdateXml()
542	{
543	RemoveUnusedStyles();
544
545	//NumberFormat
546	XmlNode nfNode=_styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager);
547	if (nfNode == null)
548	{
549	CreateNode(NumberFormatsPath, true);
550	nfNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager);
551	}
552	else
553	{
554	nfNode.RemoveAll();
555	}
556
557	int count = 0;
558	int normalIx = NamedStyles.FindIndexByID("Normal");
559	if (NamedStyles.Count > 0 && normalIx>=0 && NamedStyles[normalIx].Style.Numberformat.NumFmtID >= 164)
560	{
561	ExcelNumberFormatXml nf = NumberFormats[NumberFormats.FindIndexByID(NamedStyles[normalIx].Style.Numberformat.Id)];
562	nfNode.AppendChild(nf.CreateXmlNode(_styleXml.CreateElement("numFmt", ExcelPackage.schemaMain)));
563	nf.newID = count++;
564	}
565	foreach (ExcelNumberFormatXml nf in NumberFormats)
566	{
567	if(!nf.BuildIn /&& nf.newID<0/) //Buildin formats are not updated.
568	{
569	nfNode.AppendChild(nf.CreateXmlNode(_styleXml.CreateElement("numFmt", ExcelPackage.schemaMain)));
570	nf.newID = count;
571	count++;
572	}
573	}
574	(nfNode as XmlElement).SetAttribute("count", count.ToString());
575
576	//Font
577	count=0;
578	XmlNode fntNode = _styleXml.SelectSingleNode(FontsPath, _nameSpaceManager);
579	fntNode.RemoveAll();
580
581	//Normal should be first in the collection
582	if (NamedStyles.Count > 0 && normalIx >= 0 && NamedStyles[normalIx].Style.Font.Index > 0)
583	{
584	ExcelFontXml fnt = Fonts[NamedStyles[normalIx].Style.Font.Index];
585	fntNode.AppendChild(fnt.CreateXmlNode(_styleXml.CreateElement("font", ExcelPackage.schemaMain)));
586	fnt.newID = count++;
587	}
588
589	foreach (ExcelFontXml fnt in Fonts)
590	{
591	if (fnt.useCnt > 0/* && fnt.newID<0*/)
592	{
593	fntNode.AppendChild(fnt.CreateXmlNode(_styleXml.CreateElement("font", ExcelPackage.schemaMain)));
594	fnt.newID = count;
595	count++;
596	}
597	}
598	(fntNode as XmlElement).SetAttribute("count", count.ToString());
599
600
601	//Fills
602	count = 0;
603	XmlNode fillsNode = _styleXml.SelectSingleNode(FillsPath, _nameSpaceManager);
604	fillsNode.RemoveAll();
605	Fills[0].useCnt = 1; //Must exist (none);
606	Fills[1].useCnt = 1; //Must exist (gray125);
607	foreach (ExcelFillXml fill in Fills)
608	{
609	if (fill.useCnt > 0)
610	{
611	fillsNode.AppendChild(fill.CreateXmlNode(_styleXml.CreateElement("fill", ExcelPackage.schemaMain)));
612	fill.newID = count;
613	count++;
614	}
615	}
616
617	(fillsNode as XmlElement).SetAttribute("count", count.ToString());
618
619	//Borders
620	count = 0;
621	XmlNode bordersNode = _styleXml.SelectSingleNode(BordersPath, _nameSpaceManager);
622	bordersNode.RemoveAll();
623	Borders[0].useCnt = 1; //Must exist blank;
624	foreach (ExcelBorderXml border in Borders)
625	{
626	if (border.useCnt > 0)
627	{
628	bordersNode.AppendChild(border.CreateXmlNode(_styleXml.CreateElement("border", ExcelPackage.schemaMain)));
629	border.newID = count;
630	count++;
631	}
632	}
633	(bordersNode as XmlElement).SetAttribute("count", count.ToString());
634
635	XmlNode styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager);
636	if (styleXfsNode == null && NamedStyles.Count > 0)
637	{
638	CreateNode(CellStyleXfsPath);
639	styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager);
640	}
641	if (NamedStyles.Count > 0)
642	{
643	styleXfsNode.RemoveAll();
644	}
645	//NamedStyles
646	count = normalIx > -1 ? 1 : 0; //If we have a normal style, we make sure it's added first.
647
648	XmlNode cellStyleNode = _styleXml.SelectSingleNode(CellStylesPath, _nameSpaceManager);
649	if(cellStyleNode!=null)
650	{
651	cellStyleNode.RemoveAll();
652	}
653	XmlNode cellXfsNode = _styleXml.SelectSingleNode(CellXfsPath, _nameSpaceManager);
654	cellXfsNode.RemoveAll();
655
656	if (NamedStyles.Count > 0 && normalIx >= 0)
657	{
658	NamedStyles[normalIx].newID = 0;
659	AddNamedStyle(0, styleXfsNode, cellXfsNode, NamedStyles[normalIx]);
660	}
661	foreach (ExcelNamedStyleXml style in NamedStyles)
662	{
663	if (!style.Name.Equals("normal", StringComparison.InvariantCultureIgnoreCase))
664	{
665	AddNamedStyle(count++, styleXfsNode, cellXfsNode, style);
666	}
667	else
668	{
669	style.newID = 0;
670	}
671	cellStyleNode.AppendChild(style.CreateXmlNode(_styleXml.CreateElement("cellStyle", ExcelPackage.schemaMain)));
672	}
673	if (cellStyleNode!=null) (cellStyleNode as XmlElement).SetAttribute("count", count.ToString());
674	if (styleXfsNode != null) (styleXfsNode as XmlElement).SetAttribute("count", count.ToString());
675
676	//CellStyle
677	int xfix = 0;
678	foreach (ExcelXfs xf in CellXfs)
679	{
680	if (xf.useCnt > 0 && !(normalIx >= 0 && NamedStyles[normalIx].XfId == xfix))
681	{
682	cellXfsNode.AppendChild(xf.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain)));
683	xf.newID = count;
684	count++;
685	}
686	xfix++;
687	}
688	(cellXfsNode as XmlElement).SetAttribute("count", count.ToString());
689
690	//Set dxf styling for conditional Formatting
691	XmlNode dxfsNode = _styleXml.SelectSingleNode(dxfsPath, _nameSpaceManager);
692	foreach (var ws in _wb.Worksheets)
693	{
694	if (ws is ExcelChartsheet) continue;
695	foreach (var cf in ws.ConditionalFormatting)
696	{
697	if (cf.Style.HasValue)
698	{
699	int ix = Dxfs.FindIndexByID(cf.Style.Id);
700	if (ix < 0)
701	{
702	((ExcelConditionalFormattingRule)cf).DxfId = Dxfs.Count;
703	Dxfs.Add(cf.Style.Id, cf.Style);
704	var elem = ((XmlDocument)TopNode).CreateElement("d", "dxf", ExcelPackage.schemaMain);
705	cf.Style.CreateNodes(new XmlHelperInstance(NameSpaceManager, elem), "");
706	dxfsNode.AppendChild(elem);
707	}
708	else
709	{
710	((ExcelConditionalFormattingRule)cf).DxfId = ix;
711	}
712	}
713	}
714	}
715	if (dxfsNode != null) (dxfsNode as XmlElement).SetAttribute("count", Dxfs.Count.ToString());
716	}
717
718	private void AddNamedStyle(int id, XmlNode styleXfsNode,XmlNode cellXfsNode, ExcelNamedStyleXml style)
719	{
720	var styleXfs = CellStyleXfs[style.StyleXfId];
721	styleXfsNode.AppendChild(styleXfs.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain), true));
722	styleXfs.newID = id;
723	styleXfs.XfId = style.StyleXfId;
724
725	var ix = CellXfs.FindIndexByID(styleXfs.Id);
726	if (ix < 0)
727	{
728	cellXfsNode.AppendChild(styleXfs.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain)));
729	}
730	else
731	{
732	if(id<0) CellXfs[ix].XfId = id;
733	cellXfsNode.AppendChild(CellXfs[ix].CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain)));
734	CellXfs[ix].useCnt = 0;
735	CellXfs[ix].newID = id;
736	}
737
738	if (style.XfId >= 0)
739	style.XfId = CellXfs[style.XfId].newID;
740	else
741	style.XfId = 0;
742	}
743
744	private void RemoveUnusedStyles()
745	{
746	CellXfs[0].useCnt = 1; //First item is allways used.
747	foreach (ExcelWorksheet sheet in _wb.Worksheets)
748	{
749	var cse = new CellsStoreEnumerator<int>(sheet._styles);
750	while(cse.Next())
751	{
752	var v = cse.Value;
753	if (v >= 0)
754	{
755	CellXfs[v].useCnt++;
756	}
757	}
758	}
759	foreach (ExcelNamedStyleXml ns in NamedStyles)
760	{
761	CellStyleXfs[ns.StyleXfId].useCnt++;
762	}
763
764	foreach (ExcelXfs xf in CellXfs)
765	{
766	if (xf.useCnt > 0)
767	{
768	if (xf.FontId >= 0) Fonts[xf.FontId].useCnt++;
769	if (xf.FillId >= 0) Fills[xf.FillId].useCnt++;
770	if (xf.BorderId >= 0) Borders[xf.BorderId].useCnt++;
771	}
772	}
773	foreach (ExcelXfs xf in CellStyleXfs)
774	{
775	if (xf.useCnt > 0)
776	{
777	if (xf.FontId >= 0) Fonts[xf.FontId].useCnt++;
778	if (xf.FillId >= 0) Fills[xf.FillId].useCnt++;
779	if (xf.BorderId >= 0) Borders[xf.BorderId].useCnt++;
780	}
781	}
782	}
783	internal int GetStyleIdFromName(string Name)
784	{
785	int i = NamedStyles.FindIndexByID(Name);
786	if (i >= 0)
787	{
788	int id = NamedStyles[i].XfId;
789	if (id < 0)
790	{
791	int styleXfId=NamedStyles[i].StyleXfId;
792	ExcelXfs newStyle = CellStyleXfs[styleXfId].Copy();
793	newStyle.XfId = styleXfId;
794	id = CellXfs.FindIndexByID(newStyle.Id);
795	if (id < 0)
796	{
797	id = CellXfs.Add(newStyle.Id, newStyle);
798	}
799	NamedStyles[i].XfId=id;
800	}
801	return id;
802	}
803	else
804	{
805	return 0;
806	//throw(new Exception("Named style does not exist"));
807	}
808	}
809	#region XmlHelpFunctions
810	private int GetXmlNodeInt(XmlNode node)
811	{
812	int i;
813	if (int.TryParse(GetXmlNode(node), out i))
814	{
815	return i;
816	}
817	else
818	{
819	return 0;
820	}
821	}
822	private string GetXmlNode(XmlNode node)
823	{
824	if (node == null)
825	{
826	return "";
827	}
828	if (node.Value != null)
829	{
830	return node.Value;
831	}
832	else
833	{
834	return "";
835	}
836	}
837
838	#endregion
839	internal int CloneStyle(ExcelStyles style, int styleID)
840	{
841	return CloneStyle(style, styleID, false, false);
842	}
843	internal int CloneStyle(ExcelStyles style, int styleID, bool isNamedStyle)
844	{
845	return CloneStyle(style, styleID, isNamedStyle, false);
846	}
847	internal int CloneStyle(ExcelStyles style, int styleID, bool isNamedStyle, bool allwaysAdd)
848	{
849	ExcelXfs xfs;
850	lock (style)
851	{
852	if (isNamedStyle)
853	{
854	xfs = style.CellStyleXfs[styleID];
855	}
856	else
857	{
858	xfs = style.CellXfs[styleID];
859	}
860	ExcelXfs newXfs = xfs.Copy(this);
861	//Numberformat
862	if (xfs.NumberFormatId > 0)
863	{
864	//rake36: Two problems here...
865	//rake36: 1. the first time through when format stays equal to String.Empty, it adds a string.empty to the list of Number Formats
866	//rake36: 2. when adding a second sheet, if the numberformatid == 164, it finds the 164 added by previous sheets but was using the array index
867	//rake36: for the numberformatid
868
869	string format = string.Empty;
870	foreach (var fmt in style.NumberFormats)
871	{
872	if (fmt.NumFmtId == xfs.NumberFormatId)
873	{
874	format = fmt.Format;
875	break;
876	}
877	}
878	//rake36: Don't add another format if it's blank
879	if (!String.IsNullOrEmpty(format))
880	{
881	int ix = NumberFormats.FindIndexByID(format);
882	if (ix < 0)
883	{
884	var item = new ExcelNumberFormatXml(NameSpaceManager) { Format = format, NumFmtId = NumberFormats.NextId++ };
885	NumberFormats.Add(format, item);
886	//rake36: Use the just added format id
887	newXfs.NumberFormatId = item.NumFmtId;
888	}
889	else
890	{
891	//rake36: Use the format id defined by the index... not the index itself
892	newXfs.NumberFormatId = NumberFormats[ix].NumFmtId;
893	}
894	}
895	}
896
897	//Font
898	if (xfs.FontId > -1)
899	{
900	int ix = Fonts.FindIndexByID(xfs.Font.Id);
901	if (ix < 0)
902	{
903	ExcelFontXml item = style.Fonts[xfs.FontId].Copy();
904	ix = Fonts.Add(xfs.Font.Id, item);
905	}
906	newXfs.FontId = ix;
907	}
908
909	//Border
910	if (xfs.BorderId > -1)
911	{
912	int ix = Borders.FindIndexByID(xfs.Border.Id);
913	if (ix < 0)
914	{
915	ExcelBorderXml item = style.Borders[xfs.BorderId].Copy();
916	ix = Borders.Add(xfs.Border.Id, item);
917	}
918	newXfs.BorderId = ix;
919	}
920
921	//Fill
922	if (xfs.FillId > -1)
923	{
924	int ix = Fills.FindIndexByID(xfs.Fill.Id);
925	if (ix < 0)
926	{
927	var item = style.Fills[xfs.FillId].Copy();
928	ix = Fills.Add(xfs.Fill.Id, item);
929	}
930	newXfs.FillId = ix;
931	}
932
933	//Named style reference
934	if (xfs.XfId > 0)
935	{
936	var id = style.CellStyleXfs[xfs.XfId].Id;
937	var newId = CellStyleXfs.FindIndexByID(id);
938	if (newId >= 0)
939	{
940	newXfs.XfId = newId;
941	}
942	else if(style._wb!=_wb && allwaysAdd==false) //Not the same workbook, copy the namedstyle to the workbook or match the id
943	{
944	var nsFind = style.NamedStyles.ToDictionary(d => (d.StyleXfId));
945	if (nsFind.ContainsKey(xfs.XfId))
946	{
947	var st = nsFind[xfs.XfId];
948	if (NamedStyles.ExistsKey(st.Name))
949	{
950	newXfs.XfId = NamedStyles.FindIndexByID(st.Name);
951	}
952	else
953	{
954	var ns = CreateNamedStyle(st.Name, st.Style);
955	newXfs.XfId = NamedStyles.Count - 1;
956	}
957	}
958	}
959	}
960
961	int index;
962	if (isNamedStyle)
963	{
964	index = CellStyleXfs.Add(newXfs.Id, newXfs);
965	}
966	else
967	{
968	if (allwaysAdd)
969	{
970	index = CellXfs.Add(newXfs.Id, newXfs);
971	}
972	else
973	{
974	index = CellXfs.FindIndexByID(newXfs.Id);
975	if (index < 0)
976	{
977	index = CellXfs.Add(newXfs.Id, newXfs);
978	}
979	}
980	}
981	return index;
982	}
983	}
984	}
985	}

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

Download in other formats:

Update cookies preferences