public void UpdateXml() { RemoveUnusedStyles(); //NumberFormat XmlNode nfNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager); if (nfNode == null) { CreateNode(NumberFormatsPath, true); nfNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager); } else { nfNode.RemoveAll(); } int count = 0; int normalIx = NamedStyles.FindIndexByID("Normal"); if (NamedStyles.Count > 0 && normalIx >= 0 && NamedStyles[normalIx].Style.Numberformat.NumFmtID >= 164) { ExcelNumberFormatXml nf = NumberFormats[NumberFormats.FindIndexByID(NamedStyles[normalIx].Style.Numberformat.Id)]; nfNode.AppendChild(nf.CreateXmlNode(_styleXml.CreateElement("numFmt", ExcelPackage.schemaMain))); nf.newID = count++; } foreach (ExcelNumberFormatXml nf in NumberFormats) { if (!nf.BuildIn /*&& nf.newID<0*/) //Buildin formats are not updated. { nfNode.AppendChild(nf.CreateXmlNode(_styleXml.CreateElement("numFmt", ExcelPackage.schemaMain))); nf.newID = count; count++; } } (nfNode as XmlElement).SetAttribute("count", count.ToString()); //Font count = 0; XmlNode fntNode = _styleXml.SelectSingleNode(FontsPath, _nameSpaceManager); fntNode.RemoveAll(); //Normal should be first in the collection if (NamedStyles.Count > 0 && normalIx >= 0 && NamedStyles[normalIx].Style.Font.Index > 0) { ExcelFontXml fnt = Fonts[NamedStyles[normalIx].Style.Font.Index]; fntNode.AppendChild(fnt.CreateXmlNode(_styleXml.CreateElement("font", ExcelPackage.schemaMain))); fnt.newID = count++; } foreach (ExcelFontXml fnt in Fonts) { if (fnt.useCnt > 0 /* && fnt.newID<0*/) { fntNode.AppendChild(fnt.CreateXmlNode(_styleXml.CreateElement("font", ExcelPackage.schemaMain))); fnt.newID = count; count++; } } (fntNode as XmlElement).SetAttribute("count", count.ToString()); //Fills count = 0; XmlNode fillsNode = _styleXml.SelectSingleNode(FillsPath, _nameSpaceManager); fillsNode.RemoveAll(); Fills[0].useCnt = 1; //Must exist (none); Fills[1].useCnt = 1; //Must exist (gray125); foreach (ExcelFillXml fill in Fills) { if (fill.useCnt > 0) { fillsNode.AppendChild(fill.CreateXmlNode(_styleXml.CreateElement("fill", ExcelPackage.schemaMain))); fill.newID = count; count++; } } (fillsNode as XmlElement).SetAttribute("count", count.ToString()); //Borders count = 0; XmlNode bordersNode = _styleXml.SelectSingleNode(BordersPath, _nameSpaceManager); bordersNode.RemoveAll(); Borders[0].useCnt = 1; //Must exist blank; foreach (ExcelBorderXml border in Borders) { if (border.useCnt > 0) { bordersNode.AppendChild(border.CreateXmlNode(_styleXml.CreateElement("border", ExcelPackage.schemaMain))); border.newID = count; count++; } } (bordersNode as XmlElement).SetAttribute("count", count.ToString()); XmlNode styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager); if (styleXfsNode == null && NamedStyles.Count > 0) { CreateNode(CellStyleXfsPath); styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager); } if (NamedStyles.Count > 0) { styleXfsNode.RemoveAll(); } //NamedStyles count = normalIx > -1 ? 1 : 0; //If we have a normal style, we make sure it's added first. XmlNode cellStyleNode = _styleXml.SelectSingleNode(CellStylesPath, _nameSpaceManager); if (cellStyleNode != null) { cellStyleNode.RemoveAll(); } XmlNode cellXfsNode = _styleXml.SelectSingleNode(CellXfsPath, _nameSpaceManager); cellXfsNode.RemoveAll(); if (NamedStyles.Count > 0 && normalIx >= 0) { NamedStyles[normalIx].newID = 0; AddNamedStyle(0, styleXfsNode, cellXfsNode, NamedStyles[normalIx]); } foreach (ExcelNamedStyleXml style in NamedStyles) { if (!style.Name.Equals("normal", StringComparisonEx.InvariantCultureIgnoreCase)) { AddNamedStyle(count++, styleXfsNode, cellXfsNode, style); } else { style.newID = 0; } cellStyleNode.AppendChild(style.CreateXmlNode(_styleXml.CreateElement("cellStyle", ExcelPackage.schemaMain))); } if (cellStyleNode != null) { (cellStyleNode as XmlElement).SetAttribute("count", count.ToString()); } if (styleXfsNode != null) { (styleXfsNode as XmlElement).SetAttribute("count", count.ToString()); } //CellStyle int xfix = 0; foreach (ExcelXfs xf in CellXfs) { if (xf.useCnt > 0 && !(normalIx >= 0 && NamedStyles[normalIx].StyleXfId == xfix)) { cellXfsNode.AppendChild(xf.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain))); xf.newID = count; count++; } xfix++; } (cellXfsNode as XmlElement).SetAttribute("count", count.ToString()); //Set dxf styling for conditional Formatting XmlNode dxfsNode = _styleXml.SelectSingleNode(dxfsPath, _nameSpaceManager); foreach (var ws in _wb.Worksheets) { if (ws is ExcelChartsheet) { continue; } foreach (var cf in ws.ConditionalFormatting) { if (cf.Style.HasValue) { int ix = Dxfs.FindIndexByID(cf.Style.Id); if (ix < 0) { ((ExcelConditionalFormattingRule)cf).DxfId = Dxfs.Count; Dxfs.Add(cf.Style.Id, cf.Style); var elem = ((XmlDocument)TopNode).CreateElement("d", "dxf", ExcelPackage.schemaMain); cf.Style.CreateNodes(new XmlHelperInstance(NameSpaceManager, elem), ""); dxfsNode.AppendChild(elem); } else { ((ExcelConditionalFormattingRule)cf).DxfId = ix; } } } } if (dxfsNode != null) { (dxfsNode as XmlElement).SetAttribute("count", Dxfs.Count.ToString()); } }
public void UpdateXml() { RemoveUnusedStyles(); //NumberFormat XmlNode nfNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager); if (nfNode == null) { CreateNode(NumberFormatsPath, true); nfNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager); } else { nfNode.RemoveAll(); } int count = 0; //Normal should be first in the collection if (NamedStyles[0].Style.Numberformat.NumFmtID >= 164) { ExcelNumberFormatXml nf = NumberFormats[NumberFormats.FindIndexByID(NamedStyles[0].Style.Numberformat.Id)]; nfNode.AppendChild(nf.CreateXmlNode(_styleXml.CreateElement("numFmt", ExcelPackage.schemaMain))); nf.newID = count++; } foreach (ExcelNumberFormatXml nf in NumberFormats) { if (!nf.BuildIn && nf.newID < 0) //Buildin formats are not updated. { nfNode.AppendChild(nf.CreateXmlNode(_styleXml.CreateElement("numFmt", ExcelPackage.schemaMain))); nf.newID = count; count++; } } (nfNode as XmlElement).SetAttribute("count", count.ToString()); //Font count = 0; XmlNode fntNode = _styleXml.SelectSingleNode(FontsPath, _nameSpaceManager); fntNode.RemoveAll(); //Normal should be first in the collection if (NamedStyles[0].Style.Font.Index > 0) { ExcelFontXml fnt = Fonts[NamedStyles[0].Style.Font.Index]; fntNode.AppendChild(fnt.CreateXmlNode(_styleXml.CreateElement("font", ExcelPackage.schemaMain))); fnt.newID = count++; } foreach (ExcelFontXml fnt in Fonts) { if (fnt.useCnt > 0 && fnt.newID < 0) { fntNode.AppendChild(fnt.CreateXmlNode(_styleXml.CreateElement("font", ExcelPackage.schemaMain))); fnt.newID = count; count++; } } (fntNode as XmlElement).SetAttribute("count", count.ToString()); //Fills count = 0; XmlNode fillsNode = _styleXml.SelectSingleNode(FillsPath, _nameSpaceManager); fillsNode.RemoveAll(); Fills[0].useCnt = 1; //Must exist (none); Fills[1].useCnt = 1; //Must exist (gray125); foreach (ExcelFillXml fill in Fills) { if (fill.useCnt > 0) { fillsNode.AppendChild(fill.CreateXmlNode(_styleXml.CreateElement("fill", ExcelPackage.schemaMain))); fill.newID = count; count++; } } (fillsNode as XmlElement).SetAttribute("count", count.ToString()); //Borders count = 0; XmlNode bordersNode = _styleXml.SelectSingleNode(BordersPath, _nameSpaceManager); bordersNode.RemoveAll(); Borders[0].useCnt = 1; //Must exist blank; foreach (ExcelBorderXml border in Borders) { if (border.useCnt > 0) { bordersNode.AppendChild(border.CreateXmlNode(_styleXml.CreateElement("border", ExcelPackage.schemaMain))); border.newID = count; count++; } } (bordersNode as XmlElement).SetAttribute("count", count.ToString()); XmlNode styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager); styleXfsNode.RemoveAll(); count = 0; //NamedStyles count = 0; XmlNode cellStyleNode = _styleXml.SelectSingleNode(CellStylesPath, _nameSpaceManager); cellStyleNode.RemoveAll(); XmlNode cellXfsNode = _styleXml.SelectSingleNode(CellXfsPath, _nameSpaceManager); cellXfsNode.RemoveAll(); foreach (ExcelNamedStyleXml style in NamedStyles) { var styleXfs = CellStyleXfs[style.StyleXfId]; styleXfsNode.AppendChild(styleXfs.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain), true)); styleXfs.newID = count; styleXfs.XfId = style.StyleXfId; cellStyleNode.AppendChild(style.CreateXmlNode(_styleXml.CreateElement("cellStyle", ExcelPackage.schemaMain))); style.newID = count; var ix = CellXfs.FindIndexByID(styleXfs.Id); if (ix < 0) { cellXfsNode.AppendChild(styleXfs.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain))); } else { cellXfsNode.AppendChild(CellXfs[ix].CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain))); CellXfs[ix].useCnt = 0; CellXfs[ix].newID = count; } if (style.XfId >= 0) { style.XfId = CellXfs[style.XfId].newID; } else { style.XfId = 0; } count++; } (cellStyleNode as XmlElement).SetAttribute("count", count.ToString()); (styleXfsNode as XmlElement).SetAttribute("count", count.ToString()); //CellStyle foreach (ExcelXfs xf in CellXfs) { if (xf.useCnt > 0) { cellXfsNode.AppendChild(xf.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain))); xf.newID = count; count++; } } (cellXfsNode as XmlElement).SetAttribute("count", count.ToString()); //Set dxf styling for conditional Formatting XmlNode dxfsNode = _styleXml.SelectSingleNode(dxfsPath, _nameSpaceManager); foreach (var ws in _wb.Worksheets) { foreach (var cf in ws.ConditionalFormatting) { if (cf.Style.HasValue) { int ix = Dxfs.FindIndexByID(cf.Style.Id); if (ix < 0) { ((ExcelConditionalFormattingRule)cf).DxfId = Dxfs.Count; Dxfs.Add(cf.Style.Id, cf.Style); var elem = ((XmlDocument)TopNode).CreateElement("d", "dxf", ExcelPackage.schemaMain); cf.Style.CreateNodes(new XmlHelperInstance(NameSpaceManager, elem), ""); dxfsNode.AppendChild(elem); } else { ((ExcelConditionalFormattingRule)cf).DxfId = ix; } } } } }