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.ToLower() != "normal") { 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].XfId == 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()); } }
internal int CloneStyle(ExcelStyles style, int styleID, bool isNamedStyle, bool allwaysAdd) { ExcelXfs xfs; lock (style) { if (isNamedStyle) { xfs = style.CellStyleXfs[styleID]; } else { xfs = style.CellXfs[styleID]; } ExcelXfs newXfs = xfs.Copy(this); //Numberformat if (xfs.NumberFormatId > 0) { string format = ""; foreach (var fmt in style.NumberFormats) { if (fmt.NumFmtId == xfs.NumberFormatId) { format = fmt.Format; break; } } int ix = NumberFormats.FindIndexByID(format); if (ix < 0) { ExcelNumberFormatXml item = new ExcelNumberFormatXml(NameSpaceManager) { Format = format, NumFmtId = NumberFormats.NextId++ }; NumberFormats.Add(format, item); ix = item.NumFmtId; } newXfs.NumberFormatId = ix; } //Font if (xfs.FontId > -1) { int ix = Fonts.FindIndexByID(xfs.Font.Id); if (ix < 0) { ExcelFontXml item = style.Fonts[xfs.FontId].Copy(); ix = Fonts.Add(xfs.Font.Id, item); } newXfs.FontId = ix; } //Border if (xfs.BorderId > -1) { int ix = Borders.FindIndexByID(xfs.Border.Id); if (ix < 0) { ExcelBorderXml item = style.Borders[xfs.BorderId].Copy(); ix = Borders.Add(xfs.Border.Id, item); } newXfs.BorderId = ix; } //Fill if (xfs.FillId > -1) { int ix = Fills.FindIndexByID(xfs.Fill.Id); if (ix < 0) { var item = style.Fills[xfs.FillId].Copy(); ix = Fills.Add(xfs.Fill.Id, item); } newXfs.FillId = ix; } //Named style reference if (xfs.XfId > 0) { var id = style.CellStyleXfs[xfs.XfId].Id; var newId = CellStyleXfs.FindIndexByID(id); //if (newId < 0) //{ // newXfs.XfId = CloneStyle(style, xfs.XfId, true); //} //else //{ newXfs.XfId = newId; //} } int index; if (isNamedStyle) { index = CellStyleXfs.Add(newXfs.Id, newXfs); } else { if (allwaysAdd) { index = CellXfs.Add(newXfs.Id, newXfs); } else { index = CellXfs.FindIndexByID(newXfs.Id); if (index < 0) { index = CellXfs.Add(newXfs.Id, newXfs); } } } return(index); } }
internal int CloneStyle(ExcelStyles style, int styleID, bool isNamedStyle, bool allwaysAddCellXfs) { ExcelXfs xfs; lock (style) { if (isNamedStyle) { xfs = style.CellStyleXfs[styleID]; } else { xfs = style.CellXfs[styleID]; } ExcelXfs newXfs = xfs.Copy(this); //Numberformat if (xfs.NumberFormatId > 0) { //rake36: Two problems here... //rake36: 1. the first time through when format stays equal to String.Empty, it adds a string.empty to the list of Number Formats //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 //rake36: for the numberformatid string format = string.Empty; foreach (var fmt in style.NumberFormats) { if (fmt.NumFmtId == xfs.NumberFormatId) { format = fmt.Format; break; } } //rake36: Don't add another format if it's blank if (!String.IsNullOrEmpty(format)) { int ix = NumberFormats.FindIndexByID(format); if (ix < 0) { var item = new ExcelNumberFormatXml(NameSpaceManager) { Format = format, NumFmtId = NumberFormats.NextId++ }; NumberFormats.Add(format, item); //rake36: Use the just added format id newXfs.NumberFormatId = item.NumFmtId; } else { //rake36: Use the format id defined by the index... not the index itself newXfs.NumberFormatId = NumberFormats[ix].NumFmtId; } } } //Font if (xfs.FontId > -1) { int ix = Fonts.FindIndexByID(xfs.Font.Id); if (ix < 0) { ExcelFontXml item = style.Fonts[xfs.FontId].Copy(); ix = Fonts.Add(xfs.Font.Id, item); } newXfs.FontId = ix; } //Border if (xfs.BorderId > -1) { int ix = Borders.FindIndexByID(xfs.Border.Id); if (ix < 0) { ExcelBorderXml item = style.Borders[xfs.BorderId].Copy(); ix = Borders.Add(xfs.Border.Id, item); } newXfs.BorderId = ix; } //Fill if (xfs.FillId > -1) { int ix = Fills.FindIndexByID(xfs.Fill.Id); if (ix < 0) { var item = style.Fills[xfs.FillId].Copy(); ix = Fills.Add(xfs.Fill.Id, item); } newXfs.FillId = ix; } //Named style reference if (xfs.XfId > 0) { var id = style.CellStyleXfs[xfs.XfId].Id; var newId = CellStyleXfs.FindIndexByID(id); if (newId >= 0) { newXfs.XfId = newId; } else if (style._wb != _wb && allwaysAddCellXfs == false) //Not the same workbook, copy the namedstyle to the workbook or match the id { var nsFind = style.NamedStyles.ToDictionary(d => (d.StyleXfId)); if (nsFind.ContainsKey(xfs.XfId)) { var st = nsFind[xfs.XfId]; if (NamedStyles.ExistsKey(st.Name)) { newXfs.XfId = NamedStyles.FindIndexByID(st.Name); } else { var ns = CreateNamedStyle(st.Name, st.Style); newXfs.XfId = NamedStyles.Count - 1; } } } } int index; if (isNamedStyle && allwaysAddCellXfs == false) { index = CellStyleXfs.Add(newXfs.Id, newXfs); } else { if (allwaysAddCellXfs) { index = CellXfs.Add(newXfs.Id, newXfs); } else { index = CellXfs.FindIndexByID(newXfs.Id); if (index < 0) { index = CellXfs.Add(newXfs.Id, newXfs); } } } return(index); } }
/// <summary> /// Loads the style XML to memory /// </summary> private void LoadFromDocument() { //NumberFormats ExcelNumberFormatXml.AddBuildIn(NameSpaceManager, NumberFormats); XmlNode numNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager); if (numNode != null) { foreach (XmlNode n in numNode) { ExcelNumberFormatXml nf = new ExcelNumberFormatXml(_nameSpaceManager, n); NumberFormats.Add(nf.Id, nf); if (nf.NumFmtId >= NumberFormats.NextId) { NumberFormats.NextId = nf.NumFmtId + 1; } } } //Fonts XmlNode fontNode = _styleXml.SelectSingleNode(FontsPath, _nameSpaceManager); foreach (XmlNode n in fontNode) { ExcelFontXml f = new ExcelFontXml(_nameSpaceManager, n); Fonts.Add(f.Id, f); } //Fills XmlNode fillNode = _styleXml.SelectSingleNode(FillsPath, _nameSpaceManager); foreach (XmlNode n in fillNode) { ExcelFillXml f; if (n.FirstChild != null && n.FirstChild.LocalName == "gradientFill") { f = new ExcelGradientFillXml(_nameSpaceManager, n); } else { f = new ExcelFillXml(_nameSpaceManager, n); } Fills.Add(f.Id, f); } //Borders XmlNode borderNode = _styleXml.SelectSingleNode(BordersPath, _nameSpaceManager); foreach (XmlNode n in borderNode) { ExcelBorderXml b = new ExcelBorderXml(_nameSpaceManager, n); Borders.Add(b.Id, b); } //cellStyleXfs XmlNode styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager); if (styleXfsNode != null) { foreach (XmlNode n in styleXfsNode) { ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this); CellStyleXfs.Add(item.Id, item); } } XmlNode styleNode = _styleXml.SelectSingleNode(CellXfsPath, _nameSpaceManager); for (int i = 0; i < styleNode.ChildNodes.Count; i++) { XmlNode n = styleNode.ChildNodes[i]; ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this); CellXfs.Add(item.Id, item); } //cellStyle XmlNode namedStyleNode = _styleXml.SelectSingleNode(CellStylesPath, _nameSpaceManager); if (namedStyleNode != null) { foreach (XmlNode n in namedStyleNode) { ExcelNamedStyleXml item = new ExcelNamedStyleXml(_nameSpaceManager, n, this); NamedStyles.Add(item.Name, item); } } //dxfsPath XmlNode dxfsNode = _styleXml.SelectSingleNode(dxfsPath, _nameSpaceManager); if (dxfsNode != null) { foreach (XmlNode x in dxfsNode) { ExcelDxfStyleConditionalFormatting item = new ExcelDxfStyleConditionalFormatting(_nameSpaceManager, x, this); Dxfs.Add(item.Id, item); } } }
public bool IsFormattedAsDate() { ExcelNumberFormatXml numberFormat = _styles.NumberFormats.FirstOrDefault(x => x.NumFmtId == Index); return(numberFormat?.FormatTranslator.DataType == ExcelNumberFormatXml.eFormatType.DateTime); }