Context Navigation

source: trunk/sources/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/ExcelStyles.cs @ 14815

Visit:

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

Rev	Line
[12074]	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