EPPlus/Style/XmlAccess/ExcelNumberFormatXml.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-16
 *******************************************************************************/

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;

namespace OfficeOpenXml.Style.XmlAccess;

/// <summary>
/// Xml access class for number formats
/// </summary>
public sealed class ExcelNumberFormatXml : StyleXmlHelper {
  internal ExcelNumberFormatXml(XmlNamespaceManager nameSpaceManager)
      : base(nameSpaceManager) {}

  internal ExcelNumberFormatXml(XmlNamespaceManager nameSpaceManager, bool buildIn)
      : base(nameSpaceManager) {
    BuildIn = buildIn;
  }

  internal ExcelNumberFormatXml(XmlNamespaceManager nsm, XmlNode topNode)
      : base(nsm, topNode) {
    _numFmtId = GetXmlNodeInt("@numFmtId");
    _format = GetXmlNodeString("@formatCode");
  }

  public bool BuildIn { get; private set; }

  private int _numFmtId;

  //        const string idPath = "@numFmtId";
  /// <summary>
  /// Id for number format
  ///
  /// Build in ID's
  ///
  /// 0   General
  /// 1   0
  /// 2   0.00
  /// 3   #,##0
  /// 4   #,##0.00
  /// 9   0%
  /// 10  0.00%
  /// 11  0.00E+00
  /// 12  # ?/?
  /// 13  # ??/??
  /// 14  mm-dd-yy
  /// 15  d-mmm-yy
  /// 16  d-mmm
  /// 17  mmm-yy
  /// 18  h:mm AM/PM
  /// 19  h:mm:ss AM/PM
  /// 20  h:mm
  /// 21  h:mm:ss
  /// 22  m/d/yy h:mm
  /// 37  #,##0 ;(#,##0)
  /// 38  #,##0 ;[Red](#,##0)
  /// 39  #,##0.00;(#,##0.00)
  /// 40  #,##0.00;[Red](#,##0.00)
  /// 45  mm:ss
  /// 46  [h]:mm:ss
  /// 47  mmss.0
  /// 48  ##0.0E+0
  /// 49  @
  /// </summary>
  public int NumFmtId {
    get => _numFmtId;
    set => _numFmtId = value;
  }

  internal override string Id => _format;

  private string _format = string.Empty;

  public string Format {
    get => _format;
    set {
      _numFmtId = ExcelNumberFormat.GetFromBuildIdFromFormat(value);
      _format = value;
    }
  }

  internal string GetNewId(int numFmtId, string format) {
    if (numFmtId < 0) {
      numFmtId = ExcelNumberFormat.GetFromBuildIdFromFormat(format);
    }
    return numFmtId.ToString();
  }

  internal static void AddBuildIn(
      XmlNamespaceManager nameSpaceManager,
      ExcelStyleCollection<ExcelNumberFormatXml> numberFormats) {
    numberFormats.Add(
        "General",
        new(nameSpaceManager, true) {
          NumFmtId = 0,
          Format = "General",
        });
    numberFormats.Add(
        "0",
        new(nameSpaceManager, true) {
          NumFmtId = 1,
          Format = "0",
        });
    numberFormats.Add(
        "0.00",
        new(nameSpaceManager, true) {
          NumFmtId = 2,
          Format = "0.00",
        });
    numberFormats.Add(
        "#,##0",
        new(nameSpaceManager, true) {
          NumFmtId = 3,
          Format = "#,##0",
        });
    numberFormats.Add(
        "#,##0.00",
        new(nameSpaceManager, true) {
          NumFmtId = 4,
          Format = "#,##0.00",
        });
    numberFormats.Add(
        "0%",
        new(nameSpaceManager, true) {
          NumFmtId = 9,
          Format = "0%",
        });
    numberFormats.Add(
        "0.00%",
        new(nameSpaceManager, true) {
          NumFmtId = 10,
          Format = "0.00%",
        });
    numberFormats.Add(
        "0.00E+00",
        new(nameSpaceManager, true) {
          NumFmtId = 11,
          Format = "0.00E+00",
        });
    numberFormats.Add(
        "# ?/?",
        new(nameSpaceManager, true) {
          NumFmtId = 12,
          Format = "# ?/?",
        });
    numberFormats.Add(
        "# ??/??",
        new(nameSpaceManager, true) {
          NumFmtId = 13,
          Format = "# ??/??",
        });
    numberFormats.Add(
        "mm-dd-yy",
        new(nameSpaceManager, true) {
          NumFmtId = 14,
          Format = "mm-dd-yy",
        });
    numberFormats.Add(
        "d-mmm-yy",
        new(nameSpaceManager, true) {
          NumFmtId = 15,
          Format = "d-mmm-yy",
        });
    numberFormats.Add(
        "d-mmm",
        new(nameSpaceManager, true) {
          NumFmtId = 16,
          Format = "d-mmm",
        });
    numberFormats.Add(
        "mmm-yy",
        new(nameSpaceManager, true) {
          NumFmtId = 17,
          Format = "mmm-yy",
        });
    numberFormats.Add(
        "h:mm AM/PM",
        new(nameSpaceManager, true) {
          NumFmtId = 18,
          Format = "h:mm AM/PM",
        });
    numberFormats.Add(
        "h:mm:ss AM/PM",
        new(nameSpaceManager, true) {
          NumFmtId = 19,
          Format = "h:mm:ss AM/PM",
        });
    numberFormats.Add(
        "h:mm",
        new(nameSpaceManager, true) {
          NumFmtId = 20,
          Format = "h:mm",
        });
    numberFormats.Add(
        "h:mm:ss",
        new(nameSpaceManager, true) {
          NumFmtId = 21,
          Format = "h:mm:ss",
        });
    numberFormats.Add(
        "m/d/yy h:mm",
        new(nameSpaceManager, true) {
          NumFmtId = 22,
          Format = "m/d/yy h:mm",
        });
    numberFormats.Add(
        "#,##0 ;(#,##0)",
        new(nameSpaceManager, true) {
          NumFmtId = 37,
          Format = "#,##0 ;(#,##0)",
        });
    numberFormats.Add(
        "#,##0 ;[Red](#,##0)",
        new(nameSpaceManager, true) {
          NumFmtId = 38,
          Format = "#,##0 ;[Red](#,##0)",
        });
    numberFormats.Add(
        "#,##0.00;(#,##0.00)",
        new(nameSpaceManager, true) {
          NumFmtId = 39,
          Format = "#,##0.00;(#,##0.00)",
        });
    numberFormats.Add(
        "#,##0.00;[Red](#,##0.00)",
        new(nameSpaceManager, true) {
          NumFmtId = 40,
          Format = "#,##0.00;[Red](#,#)",
        });
    numberFormats.Add(
        "mm:ss",
        new(nameSpaceManager, true) {
          NumFmtId = 45,
          Format = "mm:ss",
        });
    numberFormats.Add(
        "[h]:mm:ss",
        new(nameSpaceManager, true) {
          NumFmtId = 46,
          Format = "[h]:mm:ss",
        });
    numberFormats.Add(
        "mmss.0",
        new(nameSpaceManager, true) {
          NumFmtId = 47,
          Format = "mmss.0",
        });
    numberFormats.Add(
        "##0.0",
        new(nameSpaceManager, true) {
          NumFmtId = 48,
          Format = "##0.0",
        });
    numberFormats.Add(
        "@",
        new(nameSpaceManager, true) {
          NumFmtId = 49,
          Format = "@",
        });

    numberFormats.NextId = 164; //Start for custom formats.
  }

  internal override XmlNode CreateXmlNode(XmlNode topNode) {
    TopNode = topNode;
    SetXmlNodeString("@numFmtId", NumFmtId.ToString());
    SetXmlNodeString("@formatCode", Format);
    return TopNode;
  }

  internal enum eFormatType {
    Unknown = 0,
    Number = 1,
    DateTime = 2,
  }

  private ExcelFormatTranslator _translator;

  internal ExcelFormatTranslator FormatTranslator {
    get {
      if (_translator == null) {
        _translator = new(Format, NumFmtId);
      }
      return _translator;
    }
  }

  internal class ExcelFormatTranslator {
    internal ExcelFormatTranslator(string format, int numFmtId) {
      if (numFmtId == 14) {
        NetFormat = NetFormatForWidth = "d";
        NetTextFormat = NetTextFormatForWidth = "";
        DataType = eFormatType.DateTime;
      } else if (format.Equals("general", StringComparison.InvariantCultureIgnoreCase)) {
        NetFormat = NetFormatForWidth = "0.#####";
        NetTextFormat = NetTextFormatForWidth = "";
        DataType = eFormatType.Number;
      } else {
        ToNetFormat(format, false);
        ToNetFormat(format, true);
      }
    }

    internal string NetTextFormat { get; private set; }

    internal string NetFormat { get; private set; }

    private CultureInfo _ci;

    internal CultureInfo Culture {
      get {
        if (_ci == null) {
          return CultureInfo.CurrentCulture;
        }
        return _ci;
      }
      private set => _ci = value;
    }

    internal eFormatType DataType { get; private set; }

    internal string NetTextFormatForWidth { get; private set; }

    internal string NetFormatForWidth { get; private set; }

    //internal string FractionFormatInteger { get; private set; }
    internal string FractionFormat { get; private set; }

    //internal string FractionFormat2 { get; private set; }

    private void ToNetFormat(string excelFormat, bool forColWidth) {
      DataType = eFormatType.Unknown;
      int secCount = 0;
      bool isText = false;
      bool isBracket = false;
      string bracketText = "";
      bool prevBslsh = false;
      bool useMinute = false;
      bool prevUnderScore = false;
      bool ignoreNext = false;
      int fractionPos = -1;
      string specialDateFormat = "";
      bool containsAmPm = excelFormat.Contains("AM/PM");
      List<int> lstDec = new List<int>();
      StringBuilder sb = new StringBuilder();
      Culture = null;
      var format = "";
      var text = "";
      char clc;

      if (containsAmPm) {
        excelFormat = Regex.Replace(excelFormat, "AM/PM", "");
        DataType = eFormatType.DateTime;
      }

      for (int pos = 0; pos < excelFormat.Length; pos++) {
        char c = excelFormat[pos];
        if (c == '"') {
          isText = !isText;
        } else {
          if (ignoreNext) {
            ignoreNext = false;
            continue;
          }
          if (isText && !isBracket) {
            sb.Append(c);
          } else if (isBracket) {
            if (c == ']') {
              isBracket = false;
              if (bracketText[0]
                  == '$') //Local Info
              {
                string[] li = Regex.Split(bracketText, "-");
                if (li[0].Length > 1) {
                  sb.Append("\"" + li[0].Substring(1, li[0].Length - 1) + "\""); //Currency symbol
                }
                if (li.Length > 1) {
                  if (li[1].Equals("f800", StringComparison.InvariantCultureIgnoreCase)) {
                    specialDateFormat = "D";
                  } else if (li[1].Equals("f400", StringComparison.InvariantCultureIgnoreCase)) {
                    specialDateFormat = "T";
                  } else {
                    var num = int.Parse(li[1], NumberStyles.HexNumber);
                    try {
                      Culture = CultureInfo.GetCultureInfo(num & 0xFFFF);
                    } catch {
                      Culture = null;
                    }
                  }
                }
              } else if (bracketText[0] == 't') {
                sb.Append("hh"); //TODO:This will not be correct for dates over 24H.
              } else if (bracketText[0] == 'h') {
                specialDateFormat = "hh"; //TODO:This will not be correct for dates over 24H.
              }
            } else {
              bracketText += c;
            }
          } else if (prevUnderScore) {
            if (forColWidth) {
              sb.AppendFormat("\"{0}\"", c);
            }
            prevUnderScore = false;
          } else {
            if (c
                == ';') //We use first part (for positive only at this stage)
            {
              secCount++;
              if (DataType == eFormatType.DateTime || secCount == 3) {
                //Add qoutes
                if (DataType == eFormatType.DateTime) {
                  SetDecimal(lstDec, sb); //Remove?
                }
                lstDec = new();
                format = sb.ToString();
                sb = new();
              } else {
                sb.Append(c);
              }
            } else {
              clc = c.ToString().ToLower(CultureInfo.InvariantCulture)[0]; //Lowercase character
              //Set the datetype
              if (DataType == eFormatType.Unknown) {
                if (c == '0' || c == '#' || c == '.') {
                  DataType = eFormatType.Number;
                } else if (clc == 'y'
                    || clc == 'm'
                    || clc == 'd'
                    || clc == 'h'
                    || clc == 'm'
                    || clc == 's') {
                  DataType = eFormatType.DateTime;
                }
              }

              if (prevBslsh) {
                if (c == '.' || c == ',') {
                  sb.Append('\\');
                }
                sb.Append(c);
                prevBslsh = false;
              } else if (c == '[') {
                bracketText = "";
                isBracket = true;
              } else if (c == '\\') {
                prevBslsh = true;
              } else if (c == '0'
                  || c == '#'
                  || c == '.'
                  || c == ','
                  || c == '%'
                  || clc == 'd'
                  || clc == 's') {
                sb.Append(c);
                if (c == '.') {
                  lstDec.Add(sb.Length - 1);
                }
              } else if (clc == 'h') {
                if (containsAmPm) {
                  sb.Append('h');
                  ;
                } else {
                  sb.Append('H');
                }
                useMinute = true;
              } else if (clc == 'm') {
                if (useMinute) {
                  sb.Append('m');
                } else {
                  sb.Append('M');
                }
              } else if (c
                  == '_') //Skip next but use for alignment
              {
                prevUnderScore = true;
              } else if (c == '?') {
                sb.Append(' ');
              } else if (c == '/') {
                if (DataType == eFormatType.Number) {
                  fractionPos = sb.Length;
                  int startPos = pos - 1;
                  while (startPos >= 0
                          && (excelFormat[startPos] == '?'
                                  || excelFormat[startPos] == '#'
                                  || excelFormat[startPos] == '0')) {
                    startPos--;
                  }

                  if (startPos
                      > 0) //RemovePart
                  {
                    sb.Remove(sb.Length - (pos - startPos - 1), (pos - startPos - 1));
                  }

                  int endPos = pos + 1;
                  while (endPos < excelFormat.Length
                          && (excelFormat[endPos] == '?'
                                  || excelFormat[endPos] == '#'
                                  || (excelFormat[endPos] >= '0' && excelFormat[endPos] <= '9'))) {
                    endPos++;
                  }
                  pos = endPos;
                  if (FractionFormat != "") {
                    FractionFormat = excelFormat.Substring(startPos + 1, endPos - startPos - 1);
                  }
                  sb.Append('?'); //Will be replaced later on by the fraction
                } else {
                  sb.Append('/');
                }
              } else if (c == '*') {
                //repeat char--> ignore
                ignoreNext = true;
              } else if (c == '@') {
                sb.Append("{0}");
              } else {
                sb.Append(c);
              }
            }
          }
        }
      }

      //Add qoutes
      if (DataType == eFormatType.DateTime) {
        SetDecimal(lstDec, sb); //Remove?
      }

      // AM/PM format
      if (containsAmPm) {
        format += "tt";
      }

      if (format == "") {
        format = sb.ToString();
      } else {
        text = sb.ToString();
      }
      if (specialDateFormat != "") {
        format = specialDateFormat;
      }

      if (forColWidth) {
        NetFormatForWidth = format;
        NetTextFormatForWidth = text;
      } else {
        NetFormat = format;
        NetTextFormat = text;
      }
      if (Culture == null) {
        Culture = CultureInfo.CurrentCulture;
      }
    }

    private static void SetDecimal(List<int> lstDec, StringBuilder sb) {
      if (lstDec.Count > 1) {
        for (int i = lstDec.Count - 1; i >= 0; i--) {
          sb.Insert(lstDec[i] + 1, '\'');
          sb.Insert(lstDec[i], '\'');
        }
      }
    }

    internal string FormatFraction(double d) {
      int numerator,
          denomerator;

      int intPart = (int)d;

      string[] fmt = FractionFormat.Split('/');

      if (!int.TryParse(fmt[1], out var fixedDenominator)) {
        fixedDenominator = 0;
      }

      if (d == 0 || double.IsNaN(d)) {
        if (fmt[0].Trim() == "" && fmt[1].Trim() == "") {
          return new(' ', FractionFormat.Length);
        }
        return 0.ToString(fmt[0]) + "/" + 1.ToString(fmt[0]);
      }

      int maxDigits = fmt[1].Length;
      string sign = d < 0 ? "-" : "";
      if (fixedDenominator == 0) {
        List<double> numerators = new List<double> { 1, 0 };
        List<double> denominators = new List<double> { 0, 1 };

        if (maxDigits < 1 && maxDigits > 12) {
          throw (new ArgumentException("Number of digits out of range (1-12)"));
        }

        int maxNum = 0;
        for (int i = 0; i < maxDigits; i++) {
          maxNum += 9 * (int)(Math.Pow(10, i));
        }

        double divRes = 1 / (Math.Abs(d) - intPart);
        double result,
            prevResult = double.NaN;
        int listPos = 2,
            index = 1;
        while (true) {
          index++;
          double intDivRes = Math.Floor(divRes);
          numerators.Add((intDivRes * numerators[index - 1] + numerators[index - 2]));
          if (numerators[index] > maxNum) {
            break;
          }

          denominators.Add((intDivRes * denominators[index - 1] + denominators[index - 2]));

          result = numerators[index] / denominators[index];
          if (denominators[index] > maxNum) {
            break;
          }
          listPos = index;

          if (result == prevResult) {
            break;
          }

          if (result == d) {
            break;
          }

          prevResult = result;

          divRes = 1 / (divRes - intDivRes); //Rest
        }

        numerator = (int)numerators[listPos];
        denomerator = (int)denominators[listPos];
      } else {
        numerator = (int)Math.Round((d - intPart) / (1D / fixedDenominator), 0);
        denomerator = fixedDenominator;
      }
      if (numerator == denomerator || numerator == 0) {
        if (numerator == denomerator) {
          intPart++;
        }
        return sign + intPart.ToString(NetFormat).Replace("?", new(' ', FractionFormat.Length));
      }
      if (intPart == 0) {
        return sign + FmtInt(numerator, fmt[0]) + "/" + FmtInt(denomerator, fmt[1]);
      }
      return sign
          + intPart
              .ToString(NetFormat)
              .Replace("?", FmtInt(numerator, fmt[0]) + "/" + FmtInt(denomerator, fmt[1]));
    }

    private string FmtInt(double value, string format) {
      string v = value.ToString("#");
      string pad = "";
      if (v.Length < format.Length) {
        for (int i = format.Length - v.Length - 1; i >= 0; i--) {
          if (format[i] == '?') {
            pad += " ";
          } else if (format[i] == ' ') {
            pad += "0";
          }
        }
      }
      return pad + v;
    }
  }
}