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;
}
}
}
}
}
