Google Git Google Git
Sign in
appsheet-third-party/epplus/ef1ff0a941cc6ffec71dccddb428f8459acacbc3/./EPPlus/Style/XmlAccess/ExcelNumberFormatXml.cs
blob: 410a2ddeb1370f651db92ca2999b0f1beaac6b06 [file]
/*******************************************************************************
* 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.Text;
using System.Xml;
using System.Globalization;
using System.Text.RegularExpressions;
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; }
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
{
return _numFmtId;
}
set
{
_numFmtId = value;
}
}
internal override string Id
{
get
{
return _format;
}
}
const string fmtPath = "@formatCode";
string _format = string.Empty;
public string Format
{
get
{
return _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 ExcelNumberFormatXml(NameSpaceManager,true){NumFmtId=0,Format="General"});
NumberFormats.Add("0", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 1, Format = "0" });
NumberFormats.Add("0.00", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 2, Format = "0.00" });
NumberFormats.Add("#,##0", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 3, Format = "#,##0" });
NumberFormats.Add("#,##0.00", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 4, Format = "#,##0.00" });
NumberFormats.Add("0%", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 9, Format = "0%" });
NumberFormats.Add("0.00%", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 10, Format = "0.00%" });
NumberFormats.Add("0.00E+00", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 11, Format = "0.00E+00" });
NumberFormats.Add("# ?/?", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 12, Format = "# ?/?" });
NumberFormats.Add("# ??/??", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 13, Format = "# ??/??" });
NumberFormats.Add("mm-dd-yy", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 14, Format = "mm-dd-yy" });
NumberFormats.Add("d-mmm-yy", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 15, Format = "d-mmm-yy" });
NumberFormats.Add("d-mmm", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 16, Format = "d-mmm" });
NumberFormats.Add("mmm-yy", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 17, Format = "mmm-yy" });
NumberFormats.Add("h:mm AM/PM", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 18, Format = "h:mm AM/PM" });
NumberFormats.Add("h:mm:ss AM/PM", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 19, Format = "h:mm:ss AM/PM" });
NumberFormats.Add("h:mm", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 20, Format = "h:mm" });
NumberFormats.Add("h:mm:ss", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 21, Format = "h:mm:ss" });
NumberFormats.Add("m/d/yy h:mm", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 22, Format = "m/d/yy h:mm" });
NumberFormats.Add("#,##0 ;(#,##0)", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 37, Format = "#,##0 ;(#,##0)" });
NumberFormats.Add("#,##0 ;[Red](#,##0)", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 38, Format = "#,##0 ;[Red](#,##0)" });
NumberFormats.Add("#,##0.00;(#,##0.00)", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 39, Format = "#,##0.00;(#,##0.00)" });
NumberFormats.Add("#,##0.00;[Red](#,##0.00)", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 40, Format = "#,##0.00;[Red](#,#)" });
NumberFormats.Add("mm:ss", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 45, Format = "mm:ss" });
NumberFormats.Add("[h]:mm:ss", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 46, Format = "[h]:mm:ss" });
NumberFormats.Add("mmss.0", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 47, Format = "mmss.0" });
NumberFormats.Add("##0.0", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 48, Format = "##0.0" });
NumberFormats.Add("@", new ExcelNumberFormatXml(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,
}
ExcelFormatTranslator _translator = null;
internal ExcelFormatTranslator FormatTranslator
{
get
{
if (_translator == null)
{
_translator = new ExcelFormatTranslator(Format, NumFmtId);
}
return _translator;
}
}
#region Excel --> .Net Format
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; }
CultureInfo _ci = null;
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;
}
else 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 List<int>();
format = sb.ToString();
sb = new StringBuilder();
}
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('/');
int fixedDenominator;
if (!int.TryParse(fmt[1], out fixedDenominator))
{
fixedDenominator = 0;
}
if (d == 0 || double.IsNaN(d))
{
if (fmt[0].Trim() == "" && fmt[1].Trim() == "")
{
return new string(' ', FractionFormat.Length);
}
else
{
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((double)10, (double)i));
}
double divRes = 1 / ((double)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 string(' ', FractionFormat.Length));
}
else if (intPart == 0)
{
return sign + FmtInt(numerator, fmt[0]) + "/" + FmtInt(denomerator, fmt[1]);
}
else
{
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;
}
}
#endregion
}
}