EPPlus/ExcelStyles.cs

/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 *
 * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 * Copyright (C) 2011  Jan K�llman
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.

 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Lesser General Public License for more details.
 *
 * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 *
 * All code and executables are provided "as is" with no warranty either express or implied.
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 * Code change notes:
 *
 * Author							Change						Date
 * ******************************************************************************
 * Jan K�llman		    Initial Release		        2009-10-01
 * Jan K�llman		    License changed GPL-->LGPL 2011-12-27
 *******************************************************************************/

using System;
using System.Linq;
using System.Xml;
using OfficeOpenXml.ConditionalFormatting;
using OfficeOpenXml.Style;
using OfficeOpenXml.Style.Dxf;
using OfficeOpenXml.Style.XmlAccess;

namespace OfficeOpenXml;

/// <summary>
/// Containts all shared cell styles for a workbook
/// </summary>
public sealed class ExcelStyles : XmlHelper {
  private const string _numberFormatsPath = "d:styleSheet/d:numFmts";
  private const string _fontsPath = "d:styleSheet/d:fonts";
  private const string _fillsPath = "d:styleSheet/d:fills";
  private const string _bordersPath = "d:styleSheet/d:borders";
  private const string _cellStyleXfsPath = "d:styleSheet/d:cellStyleXfs";
  private const string _cellXfsPath = "d:styleSheet/d:cellXfs";
  private const string _cellStylesPath = "d:styleSheet/d:cellStyles";
  private const string _dxfsPath = "d:styleSheet/d:dxfs";

  //internal Dictionary<int, ExcelXfs> Styles = new Dictionary<int, ExcelXfs>();
  private readonly XmlDocument _styleXml;
  private readonly ExcelWorkbook _wb;
  private readonly XmlNamespaceManager _nameSpaceManager;
  internal int _nextDfxNumFmtID = 164;

  internal ExcelStyles(XmlNamespaceManager nameSpaceManager, XmlDocument xml, ExcelWorkbook wb)
      : base(nameSpaceManager, xml) {
    _styleXml = xml;
    _wb = wb;
    _nameSpaceManager = nameSpaceManager;
    SchemaNodeOrder = new[] {
      "numFmts",
      "fonts",
      "fills",
      "borders",
      "cellStyleXfs",
      "cellXfs",
      "cellStyles",
      "dxfs",
    };
    LoadFromDocument();
  }

  /// <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(_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);
      }
    }
  }

  internal ExcelStyle GetStyleObject(int id, int positionId, string address) {
    if (id < 0) {
      id = 0;
    }
    return new(this, positionId, address, id);
  }

  internal int GetStyleId(ExcelWorksheet ws, int row, int col) {
    int v = 0;
    if (ws._styles.Exists(row, col, ref v)) {
      return v;
    }
    if (ws._styles.Exists(
        row,
        0,
        ref v)) //First Row
    {
      return v;
    } // then column
    if (ws._styles.Exists(0, col, ref v)) {
      return v;
    }
    int r = 0,
        c = col;
    if (ws._values.PrevCell(ref r, ref c)) {
      var column = ws._values.GetValue(0, c) as ExcelColumn;
      if (column != null
          && column.ColumnMax
              >= col) //Fixes issue 15174
      {
        return ws._styles.GetValue(0, c);
      }
      return 0;
    }
    return 0;
  }

  public ExcelStyleCollection<ExcelNumberFormatXml> NumberFormats = new();
  public ExcelStyleCollection<ExcelFontXml> Fonts = new();
  public ExcelStyleCollection<ExcelFillXml> Fills = new();
  public ExcelStyleCollection<ExcelBorderXml> Borders = new();
  public ExcelStyleCollection<ExcelXfs> CellStyleXfs = new();
  public ExcelStyleCollection<ExcelXfs> CellXfs = new();
  public ExcelStyleCollection<ExcelNamedStyleXml> NamedStyles = new();
  public ExcelStyleCollection<ExcelDxfStyleConditionalFormatting> Dxfs = new();

  internal string Id => "";

  public ExcelNamedStyleXml CreateNamedStyle(string name) {
    return CreateNamedStyle(name, null);
  }

  public ExcelNamedStyleXml CreateNamedStyle(string name, ExcelStyle template) {
    if (_wb.Styles.NamedStyles.ExistsKey(name)) {
      throw new(string.Format("Key {0} already exists in collection", name));
    }

    ExcelNamedStyleXml style;
    style = new(NameSpaceManager, this);
    int xfIdCopy,
        positionId;
    ExcelStyles styles;
    if (template == null) {
      //                style.Style = new ExcelStyle(this, NamedStylePropertyChange, -1, name, 0);
      xfIdCopy = 0;
      positionId = -1;
      styles = this;
    } else {
      if (template.PositionID < 0 && template.Styles == this) {
        xfIdCopy = template.Index;
        positionId = template.PositionID;
        styles = this;
        //style.Style = new ExcelStyle(this, NamedStylePropertyChange, Template.PositionID, name, Template.Index);
        //style.StyleXfId = Template.Index;
      } else {
        xfIdCopy = template.XfId;
        positionId = -1;
        styles = template.Styles;
      }
    }
    //Clone namedstyle
    int styleXfId = CloneStyle(styles, xfIdCopy, true);
    //Close cells style
    CellStyleXfs[styleXfId].XfId = CellStyleXfs.Count - 1;
    int xfid = CloneStyle(styles, xfIdCopy, false, true); //Always add a new style (We create a new named style here)
    CellXfs[xfid].XfId = styleXfId;
    style.Style = new(this, positionId, name, styleXfId);
    style.StyleXfId = styleXfId;

    style.Name = name;
    int ix = _wb.Styles.NamedStyles.Add(style.Name, style);
    style.Style.SetIndex(ix);
    //style.Style.XfId = ix;
    return style;
  }

  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", StringComparison.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].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());
    }
  }

  private void AddNamedStyle(
      int id,
      XmlNode styleXfsNode,
      XmlNode cellXfsNode,
      ExcelNamedStyleXml style) {
    var styleXfs = CellStyleXfs[style.StyleXfId];
    styleXfsNode.AppendChild(
        styleXfs.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage._schemaMain), true));
    styleXfs.newID = id;
    styleXfs.XfId = style.StyleXfId;

    var ix = CellXfs.FindIndexById(styleXfs.Id);
    if (ix < 0) {
      cellXfsNode.AppendChild(
          styleXfs.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage._schemaMain)));
    } else {
      if (id < 0) {
        CellXfs[ix].XfId = id;
      }
      cellXfsNode.AppendChild(
          CellXfs[ix].CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage._schemaMain)));
      CellXfs[ix].useCnt = 0;
      CellXfs[ix].newID = id;
    }

    if (style.XfId >= 0) {
      style.XfId = CellXfs[style.XfId].newID;
    } else {
      style.XfId = 0;
    }
  }

  private void RemoveUnusedStyles() {
    CellXfs[0].useCnt = 1; //First item is allways used.
    foreach (ExcelWorksheet sheet in _wb.Worksheets) {
      var cse = new CellsStoreEnumerator<int>(sheet._styles);
      while (cse.Next()) {
        var v = cse.Value;
        if (v >= 0) {
          CellXfs[v].useCnt++;
        }
      }
    }
    foreach (ExcelNamedStyleXml ns in NamedStyles) {
      CellStyleXfs[ns.StyleXfId].useCnt++;
    }

    foreach (ExcelXfs xf in CellXfs) {
      if (xf.useCnt > 0) {
        if (xf.FontId >= 0) {
          Fonts[xf.FontId].useCnt++;
        }
        if (xf.FillId >= 0) {
          Fills[xf.FillId].useCnt++;
        }
        if (xf.BorderId >= 0) {
          Borders[xf.BorderId].useCnt++;
        }
      }
    }
    foreach (ExcelXfs xf in CellStyleXfs) {
      if (xf.useCnt > 0) {
        if (xf.FontId >= 0) {
          Fonts[xf.FontId].useCnt++;
        }
        if (xf.FillId >= 0) {
          Fills[xf.FillId].useCnt++;
        }
        if (xf.BorderId >= 0) {
          Borders[xf.BorderId].useCnt++;
        }
      }
    }
  }

  internal int GetStyleIdFromName(string name) {
    int i = NamedStyles.FindIndexById(name);
    if (i >= 0) {
      int id = NamedStyles[i].XfId;
      if (id < 0) {
        int styleXfId = NamedStyles[i].StyleXfId;
        ExcelXfs newStyle = CellStyleXfs[styleXfId].Copy();
        newStyle.XfId = styleXfId;
        id = CellXfs.FindIndexById(newStyle.Id);
        if (id < 0) {
          id = CellXfs.Add(newStyle.Id, newStyle);
        }
        NamedStyles[i].XfId = id;
      }
      return id;
    }
    return 0;
    //throw(new Exception("Named style does not exist"));
  }

  private string GetXmlNode(XmlNode node) {
    if (node == null) {
      return "";
    }
    if (node.Value != null) {
      return node.Value;
    }
    return "";
  }

  internal int CloneStyle(ExcelStyles style, int styleId) {
    return CloneStyle(style, styleId, false, false);
  }

  internal int CloneStyle(ExcelStyles style, int styleId, bool isNamedStyle) {
    return CloneStyle(style, styleId, isNamedStyle, false);
  }

  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) {
        //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
            && allwaysAdd
                == 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) {
        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;
    }
  }
}