/*******************************************************************************
 * 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.Collections.Generic;
using System.Collections.Immutable;
using System.Linq;
using System.Xml;

namespace AppsheetEpplus;

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

  protected override ImmutableArray<string> SchemaNodeOrder { get; } = [
    "numFmts",
    "fonts",
    "fills",
    "borders",
    "cellStyleXfs",
    "cellXfs",
    "cellStyles",
    "dxfs",
  ];

  internal ExcelStyles(XmlNamespaceManager nameSpaceManager, XmlDocument xml, ExcelWorkbook wb)
      : base(nameSpaceManager, xml) {
    _styleXml = xml;
    _wb = wb;
    _nameSpaceManager = nameSpaceManager;
    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, PropertyChange, positionId, address, id);
  }

  /// <summary>
  /// Handels changes of properties on the style objects
  /// </summary>
  /// <param name="sender"></param>
  /// <param name="e"></param>
  /// <returns></returns>
  internal int PropertyChange(StyleBase sender, StyleChangeEventArgs e) {
    var address = new ExcelAddressBase(e.Address);
    var ws = _wb.Worksheets[e.PositionID];
    Dictionary<int, int> styleCashe = new Dictionary<int, int>();
    //Set single address
    SetStyleAddress(sender, e, address, ws, ref styleCashe);
    if (address.Addresses != null) {
      //Handle multiaddresses
      foreach (var innerAddress in address.Addresses) {
        SetStyleAddress(sender, e, innerAddress, ws, ref styleCashe);
      }
    }
    return 0;
  }

  private void SetStyleAddress(
      StyleBase sender,
      StyleChangeEventArgs e,
      ExcelAddressBase address,
      ExcelWorksheet ws,
      ref Dictionary<int, int> styleCashe) {
    if (address.Start.Column == 0 || address.Start.Row == 0) {
      throw (new("error address"));
    }
    //Columns
    if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows) {
      ExcelColumn column;
      int col = address.Start.Column,
          row = 0;
      //Get the startcolumn
      if (!ws._values.Exists(0, address.Start.Column)) {
        column = ws.Column(address.Start.Column);
      } else {
        column = (ExcelColumn)ws._values.GetValue(0, address.Start.Column);
      }

      while (column.ColumnMin <= address.End.Column) {
        if (column.ColumnMax > address.End.Column) {
          var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
          column.ColumnMax = address.End.Column;
        }
        var s = ws._styles.GetValue(0, column.ColumnMin);
        if (styleCashe.ContainsKey(s)) {
          ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
        } else {
          ExcelXfs st = CellXfs[s];
          int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
          styleCashe.Add(s, newId);
          ws.SetStyle(0, column.ColumnMin, newId);
        }

        //index++;

        if (!ws._values.NextCell(ref row, ref col) || row > 0) {
          column._columnMax = address.End.Column;
          break;
        }
        column = (ws._values.GetValue(0, col) as ExcelColumn);
      }

      if (column._columnMax < address.End.Column) {
        var newCol = ws.Column(column._columnMax + 1);
        newCol._columnMax = address.End.Column;

        var s = ws._styles.GetValue(0, column.ColumnMin);
        if (styleCashe.ContainsKey(s)) {
          ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
        } else {
          ExcelXfs st = CellXfs[s];
          int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
          styleCashe.Add(s, newId);
          ws.SetStyle(0, column.ColumnMin, newId);
        }

        column._columnMax = address.End.Column;
      }

      //Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
      var cse = new CellsStoreEnumerator<int>(
          ws._styles,
          1,
          address._fromCol,
          address._toRow,
          address._toCol);
      while (cse.Next()) {
        if (cse.Column >= address.Start.Column && cse.Column <= address.End.Column) {
          if (styleCashe.ContainsKey(cse.Value)) {
            ws.SetStyle(cse.Row, cse.Column, styleCashe[cse.Value]);
          } else {
            ExcelXfs st = CellXfs[cse.Value];
            int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
            styleCashe.Add(cse.Value, newId);
            cse.Value = newId;
            //ws.SetStyle(cse.Row, cse.Column, newId);
          }
        }
      }

      //Update cells with styled columns
      cse = new(ws._styles, 1, 0, address._toRow, 0);
      while (cse.Next()) {
        for (int c = address._fromRow; c <= address._toCol; c++) {
          if (!ws._styles.Exists(cse.Row, c)) {
            if (styleCashe.ContainsKey(cse.Value)) {
              ws.SetStyle(cse.Row, c, styleCashe[cse.Value]);
            } else {
              ExcelXfs st = CellXfs[cse.Value];
              int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
              styleCashe.Add(cse.Value, newId);
              ws.SetStyle(cse.Row, c, newId);
            }
          }
        }
      }
    }
    //Rows
    else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns) {
      for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++) {
        var s = ws._styles.GetValue(rowNum, 0);
        if (s == 0) {
          //iterate all columns and set the row to the style of the last column
          var cse = new CellsStoreEnumerator<int>(ws._styles, 0, 1, 0, ExcelPackage.MaxColumns);
          while (cse.Next()) {
            s = cse.Value;
            var c = ws._values.GetValue(cse.Row, cse.Column) as ExcelColumn;
            if (c != null && c.ColumnMax < ExcelPackage.MaxColumns) {
              for (int col = c.ColumnMin; col < c.ColumnMax; col++) {
                if (!ws._styles.Exists(rowNum, col)) {
                  ws._styles.SetValue(rowNum, col, s);
                }
              }
            }
          }
          ws.SetStyle(rowNum, 0, s);
        }
        if (styleCashe.ContainsKey(s)) {
          ws.SetStyle(rowNum, 0, styleCashe[s]);
        } else {
          ExcelXfs st = CellXfs[s];
          int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
          styleCashe.Add(s, newId);
          ws._styles.SetValue(rowNum, 0, newId);
          ws.SetStyle(rowNum, 0, newId);
        }
      }

      //Update individual cells
      var cse2 = new CellsStoreEnumerator<int>(
          ws._styles,
          address._fromRow,
          address._fromCol,
          address._toRow,
          address._toCol);
      while (cse2.Next()) {
        var s = cse2.Value;
        if (styleCashe.ContainsKey(s)) {
          ws.SetStyle(cse2.Row, cse2.Column, styleCashe[s]);
        } else {
          ExcelXfs st = CellXfs[s];
          int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
          styleCashe.Add(s, newId);
          cse2.Value = newId;
        }
      }

      //Update cells with styled rows
      cse2 = new(ws._styles, 0, 1, 0, address._toCol);
      while (cse2.Next()) {
        for (int r = address._fromRow; r <= address._toRow; r++) {
          if (!ws._styles.Exists(r, cse2.Column)) {
            var s = cse2.Value;
            if (styleCashe.ContainsKey(s)) {
              ws.SetStyle(r, cse2.Column, styleCashe[s]);
            } else {
              ExcelXfs st = CellXfs[s];
              int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
              styleCashe.Add(s, newId);
              ws.SetStyle(r, cse2.Column, newId);
            }
          }
        }
      }
    } else //Cellrange
    {
      for (int col = address.Start.Column; col <= address.End.Column; col++) {
        for (int row = address.Start.Row; row <= address.End.Row; row++) {
          var s = GetStyleId(ws, row, col);
          if (styleCashe.ContainsKey(s)) {
            ws.SetStyle(row, col, styleCashe[s]);
          } else {
            ExcelXfs st = CellXfs[s];
            int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
            styleCashe.Add(s, newId);
            ws.SetStyle(row, col, newId);
          }
        }
      }
    }
  }

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

  /// <summary>
  /// Handles property changes on Named styles.
  /// </summary>
  /// <param name="sender"></param>
  /// <param name="e"></param>
  /// <returns></returns>
  internal int NamedStylePropertyChange(StyleBase sender, StyleChangeEventArgs e) {
    int index = NamedStyles.FindIndexById(e.Address);
    if (index >= 0) {
      int newId = CellStyleXfs[NamedStyles[index].StyleXfId]
          .GetNewId(CellStyleXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
      int prevIx = NamedStyles[index].StyleXfId;
      NamedStyles[index].StyleXfId = newId;
      NamedStyles[index].Style.Index = newId;

      NamedStyles[index].XfId = int.MinValue;
      foreach (var style in CellXfs) {
        if (style.XfId == prevIx) {
          style.XfId = newId;
        }
      }
    }
    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, NamedStylePropertyChange, 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) {
    var xfs = isNamedStyle ? style.CellStyleXfs[styleId] : 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;
  }
}