| 1 | /*******************************************************************************
|
|---|
| 2 | * You may amend and distribute as you like, but don't remove this header!
|
|---|
| 3 | *
|
|---|
| 4 | * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
|
|---|
| 5 | * See http://www.codeplex.com/EPPlus for details.
|
|---|
| 6 | *
|
|---|
| 7 | * Copyright (C) 2011 Jan Källman
|
|---|
| 8 | *
|
|---|
| 9 | * This library is free software; you can redistribute it and/or
|
|---|
| 10 | * modify it under the terms of the GNU Lesser General Public
|
|---|
| 11 | * License as published by the Free Software Foundation; either
|
|---|
| 12 | * version 2.1 of the License, or (at your option) any later version.
|
|---|
| 13 |
|
|---|
| 14 | * This library is distributed in the hope that it will be useful,
|
|---|
| 15 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|---|
| 16 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
|
|---|
| 17 | * See the GNU Lesser General Public License for more details.
|
|---|
| 18 | *
|
|---|
| 19 | * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
|
|---|
| 20 | * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
|
|---|
| 21 | *
|
|---|
| 22 | * All code and executables are provided "as is" with no warranty either express or implied.
|
|---|
| 23 | * The author accepts no liability for any damage or loss of business that this product may cause.
|
|---|
| 24 | *
|
|---|
| 25 | * Code change notes:
|
|---|
| 26 | *
|
|---|
| 27 | * Author Change Date
|
|---|
| 28 | * ******************************************************************************
|
|---|
| 29 | * Jan Källman Initial Release 2009-10-01
|
|---|
| 30 | * Jan Källman License changed GPL-->LGPL 2011-12-16
|
|---|
| 31 | *******************************************************************************/
|
|---|
| 32 | using System;
|
|---|
| 33 | using System.Collections.Generic;
|
|---|
| 34 | using System.Text;
|
|---|
| 35 | using System.Xml;
|
|---|
| 36 | using System.Globalization;
|
|---|
| 37 | using System.Text.RegularExpressions;
|
|---|
| 38 | namespace OfficeOpenXml.Style.XmlAccess
|
|---|
| 39 | {
|
|---|
| 40 | /// <summary>
|
|---|
| 41 | /// Xml access class for number formats
|
|---|
| 42 | /// </summary>
|
|---|
| 43 | public sealed class ExcelNumberFormatXml : StyleXmlHelper
|
|---|
| 44 | {
|
|---|
| 45 | internal ExcelNumberFormatXml(XmlNamespaceManager nameSpaceManager) : base(nameSpaceManager)
|
|---|
| 46 | {
|
|---|
| 47 |
|
|---|
| 48 | }
|
|---|
| 49 | internal ExcelNumberFormatXml(XmlNamespaceManager nameSpaceManager, bool buildIn): base(nameSpaceManager)
|
|---|
| 50 | {
|
|---|
| 51 | BuildIn = buildIn;
|
|---|
| 52 | }
|
|---|
| 53 | internal ExcelNumberFormatXml(XmlNamespaceManager nsm, XmlNode topNode) :
|
|---|
| 54 | base(nsm, topNode)
|
|---|
| 55 | {
|
|---|
| 56 | _numFmtId = GetXmlNodeInt("@numFmtId");
|
|---|
| 57 | _format = GetXmlNodeString("@formatCode");
|
|---|
| 58 | }
|
|---|
| 59 | public bool BuildIn { get; private set; }
|
|---|
| 60 | int _numFmtId;
|
|---|
| 61 | // const string idPath = "@numFmtId";
|
|---|
| 62 | /// <summary>
|
|---|
| 63 | /// Id for number format
|
|---|
| 64 | ///
|
|---|
| 65 | /// Build in ID's
|
|---|
| 66 | ///
|
|---|
| 67 | /// 0 General
|
|---|
| 68 | /// 1 0
|
|---|
| 69 | /// 2 0.00
|
|---|
| 70 | /// 3 #,##0
|
|---|
| 71 | /// 4 #,##0.00
|
|---|
| 72 | /// 9 0%
|
|---|
| 73 | /// 10 0.00%
|
|---|
| 74 | /// 11 0.00E+00
|
|---|
| 75 | /// 12 # ?/?
|
|---|
| 76 | /// 13 # ??/??
|
|---|
| 77 | /// 14 mm-dd-yy
|
|---|
| 78 | /// 15 d-mmm-yy
|
|---|
| 79 | /// 16 d-mmm
|
|---|
| 80 | /// 17 mmm-yy
|
|---|
| 81 | /// 18 h:mm AM/PM
|
|---|
| 82 | /// 19 h:mm:ss AM/PM
|
|---|
| 83 | /// 20 h:mm
|
|---|
| 84 | /// 21 h:mm:ss
|
|---|
| 85 | /// 22 m/d/yy h:mm
|
|---|
| 86 | /// 37 #,##0 ;(#,##0)
|
|---|
| 87 | /// 38 #,##0 ;[Red](#,##0)
|
|---|
| 88 | /// 39 #,##0.00;(#,##0.00)
|
|---|
| 89 | /// 40 #,##0.00;[Red](#,##0.00)
|
|---|
| 90 | /// 45 mm:ss
|
|---|
| 91 | /// 46 [h]:mm:ss
|
|---|
| 92 | /// 47 mmss.0
|
|---|
| 93 | /// 48 ##0.0E+0
|
|---|
| 94 | /// 49 @
|
|---|
| 95 | /// </summary>
|
|---|
| 96 | public int NumFmtId
|
|---|
| 97 | {
|
|---|
| 98 | get
|
|---|
| 99 | {
|
|---|
| 100 | return _numFmtId;
|
|---|
| 101 | }
|
|---|
| 102 | set
|
|---|
| 103 | {
|
|---|
| 104 | _numFmtId = value;
|
|---|
| 105 | }
|
|---|
| 106 | }
|
|---|
| 107 | internal override string Id
|
|---|
| 108 | {
|
|---|
| 109 | get
|
|---|
| 110 | {
|
|---|
| 111 | return _format;
|
|---|
| 112 | }
|
|---|
| 113 | }
|
|---|
| 114 | const string fmtPath = "@formatCode";
|
|---|
| 115 | string _format = string.Empty;
|
|---|
| 116 | public string Format
|
|---|
| 117 | {
|
|---|
| 118 | get
|
|---|
| 119 | {
|
|---|
| 120 | return _format;
|
|---|
| 121 | }
|
|---|
| 122 | set
|
|---|
| 123 | {
|
|---|
| 124 | _numFmtId = ExcelNumberFormat.GetFromBuildIdFromFormat(value);
|
|---|
| 125 | _format = value;
|
|---|
| 126 | }
|
|---|
| 127 | }
|
|---|
| 128 | internal string GetNewID(int NumFmtId, string Format)
|
|---|
| 129 | {
|
|---|
| 130 |
|
|---|
| 131 | if (NumFmtId < 0)
|
|---|
| 132 | {
|
|---|
| 133 | NumFmtId = ExcelNumberFormat.GetFromBuildIdFromFormat(Format);
|
|---|
| 134 | }
|
|---|
| 135 | return NumFmtId.ToString();
|
|---|
| 136 | }
|
|---|
| 137 |
|
|---|
| 138 | internal static void AddBuildIn(XmlNamespaceManager NameSpaceManager, ExcelStyleCollection<ExcelNumberFormatXml> NumberFormats)
|
|---|
| 139 | {
|
|---|
| 140 | NumberFormats.Add("General",new ExcelNumberFormatXml(NameSpaceManager,true){NumFmtId=0,Format="General"});
|
|---|
| 141 | NumberFormats.Add("0", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 1, Format = "0" });
|
|---|
| 142 | NumberFormats.Add("0.00", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 2, Format = "0.00" });
|
|---|
| 143 | NumberFormats.Add("#,##0", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 3, Format = "#,##0" });
|
|---|
| 144 | NumberFormats.Add("#,##0.00", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 4, Format = "#,##0.00" });
|
|---|
| 145 | NumberFormats.Add("0%", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 9, Format = "0%" });
|
|---|
| 146 | NumberFormats.Add("0.00%", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 10, Format = "0.00%" });
|
|---|
| 147 | NumberFormats.Add("0.00E+00", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 11, Format = "0.00E+00" });
|
|---|
| 148 | NumberFormats.Add("# ?/?", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 12, Format = "# ?/?" });
|
|---|
| 149 | NumberFormats.Add("# ??/??", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 13, Format = "# ??/??" });
|
|---|
| 150 | NumberFormats.Add("mm-dd-yy", new ExcelNumberFormatXml(NameSpaceManager,true) { NumFmtId = 14, Format = "mm-dd-yy" });
|
|---|
| 151 | NumberFormats.Add("d-mmm-yy", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 15, Format = "d-mmm-yy" });
|
|---|
| 152 | NumberFormats.Add("d-mmm", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 16, Format = "d-mmm" });
|
|---|
| 153 | NumberFormats.Add("mmm-yy", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 17, Format = "mmm-yy" });
|
|---|
| 154 | NumberFormats.Add("h:mm AM/PM", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 18, Format = "h:mm AM/PM" });
|
|---|
| 155 | NumberFormats.Add("h:mm:ss AM/PM", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 19, Format = "h:mm:ss AM/PM" });
|
|---|
| 156 | NumberFormats.Add("h:mm", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 20, Format = "h:mm" });
|
|---|
| 157 | NumberFormats.Add("h:mm:dd", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 21, Format = "h:mm:dd" });
|
|---|
| 158 | NumberFormats.Add("m/d/yy h:mm", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 22, Format = "m/d/yy h:mm" });
|
|---|
| 159 | NumberFormats.Add("#,##0 ;(#,##0)", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 37, Format = "#,##0 ;(#,##0)" });
|
|---|
| 160 | NumberFormats.Add("#,##0 ;[Red](#,##0)", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 38, Format = "#,##0 ;[Red](#,##0)" });
|
|---|
| 161 | NumberFormats.Add("#,##0.00;(#,##0.00)", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 39, Format = "#,##0.00;(#,##0.00)" });
|
|---|
| 162 | NumberFormats.Add("#,##0.00;[Red](#,#)", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 40, Format = "#,##0.00;[Red](#,#)" });
|
|---|
| 163 | NumberFormats.Add("mm:ss", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 45, Format = "mm:ss" });
|
|---|
| 164 | NumberFormats.Add("[h]:mm:ss", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 46, Format = "[h]:mm:ss" });
|
|---|
| 165 | NumberFormats.Add("mmss.0", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 47, Format = "mmss.0" });
|
|---|
| 166 | NumberFormats.Add("##0.0", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 48, Format = "##0.0" });
|
|---|
| 167 | NumberFormats.Add("@", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 49, Format = "@" });
|
|---|
| 168 |
|
|---|
| 169 | NumberFormats.NextId = 164; //Start for custom formats.
|
|---|
| 170 | }
|
|---|
| 171 |
|
|---|
| 172 | internal override XmlNode CreateXmlNode(XmlNode topNode)
|
|---|
| 173 | {
|
|---|
| 174 | TopNode = topNode;
|
|---|
| 175 | SetXmlNodeString("@numFmtId", NumFmtId.ToString());
|
|---|
| 176 | SetXmlNodeString("@formatCode", Format);
|
|---|
| 177 | return TopNode;
|
|---|
| 178 | }
|
|---|
| 179 |
|
|---|
| 180 | internal enum eFormatType
|
|---|
| 181 | {
|
|---|
| 182 | Unknown = 0,
|
|---|
| 183 | Number = 1,
|
|---|
| 184 | DateTime = 2,
|
|---|
| 185 | }
|
|---|
| 186 | ExcelFormatTranslator _translator = null;
|
|---|
| 187 | internal ExcelFormatTranslator FormatTranslator
|
|---|
| 188 | {
|
|---|
| 189 | get
|
|---|
| 190 | {
|
|---|
| 191 | if (_translator == null)
|
|---|
| 192 | {
|
|---|
| 193 | _translator = new ExcelFormatTranslator(Format, NumFmtId);
|
|---|
| 194 | }
|
|---|
| 195 | return _translator;
|
|---|
| 196 | }
|
|---|
| 197 | }
|
|---|
| 198 | #region Excel --> .Net Format
|
|---|
| 199 | internal class ExcelFormatTranslator
|
|---|
| 200 | {
|
|---|
| 201 | internal ExcelFormatTranslator(string format, int numFmtID)
|
|---|
| 202 | {
|
|---|
| 203 | if (numFmtID == 14)
|
|---|
| 204 | {
|
|---|
| 205 | NetFormat = NetFormatForWidth = "d";
|
|---|
| 206 | NetTextFormat = NetTextFormatForWidth = "";
|
|---|
| 207 | DataType = eFormatType.DateTime;
|
|---|
| 208 | }
|
|---|
| 209 | else if (format.ToLower() == "general")
|
|---|
| 210 | {
|
|---|
| 211 | NetFormat = NetFormatForWidth = "0.#####";
|
|---|
| 212 | NetTextFormat = NetTextFormatForWidth = "";
|
|---|
| 213 | DataType = eFormatType.Number;
|
|---|
| 214 | }
|
|---|
| 215 | else
|
|---|
| 216 | {
|
|---|
| 217 | ToNetFormat(format, false);
|
|---|
| 218 | ToNetFormat(format, true);
|
|---|
| 219 | }
|
|---|
| 220 | }
|
|---|
| 221 | internal string NetTextFormat { get; private set; }
|
|---|
| 222 | internal string NetFormat { get; private set; }
|
|---|
| 223 | CultureInfo _ci = null;
|
|---|
| 224 | internal CultureInfo Culture
|
|---|
| 225 | {
|
|---|
| 226 | get
|
|---|
| 227 | {
|
|---|
| 228 | if (_ci == null)
|
|---|
| 229 | {
|
|---|
| 230 | return CultureInfo.CurrentCulture;
|
|---|
| 231 | }
|
|---|
| 232 | return _ci;
|
|---|
| 233 | }
|
|---|
| 234 | private set
|
|---|
| 235 | {
|
|---|
| 236 | _ci = value;
|
|---|
| 237 | }
|
|---|
| 238 | }
|
|---|
| 239 | internal eFormatType DataType { get; private set; }
|
|---|
| 240 | internal string NetTextFormatForWidth { get; private set; }
|
|---|
| 241 | internal string NetFormatForWidth { get; private set; }
|
|---|
| 242 |
|
|---|
| 243 | //internal string FractionFormatInteger { get; private set; }
|
|---|
| 244 | internal string FractionFormat { get; private set; }
|
|---|
| 245 | //internal string FractionFormat2 { get; private set; }
|
|---|
| 246 |
|
|---|
| 247 | private void ToNetFormat(string ExcelFormat, bool forColWidth)
|
|---|
| 248 | {
|
|---|
| 249 | DataType = eFormatType.Unknown;
|
|---|
| 250 | int secCount = 0;
|
|---|
| 251 | bool isText = false;
|
|---|
| 252 | bool isBracket = false;
|
|---|
| 253 | string bracketText = "";
|
|---|
| 254 | bool prevBslsh = false;
|
|---|
| 255 | bool useMinute = false;
|
|---|
| 256 | bool prevUnderScore = false;
|
|---|
| 257 | bool ignoreNext = false;
|
|---|
| 258 | int fractionPos = -1;
|
|---|
| 259 | string specialDateFormat = "";
|
|---|
| 260 | bool containsAmPm = ExcelFormat.Contains("AM/PM");
|
|---|
| 261 |
|
|---|
| 262 | StringBuilder sb = new StringBuilder();
|
|---|
| 263 | Culture = null;
|
|---|
| 264 | var format = "";
|
|---|
| 265 | var text = "";
|
|---|
| 266 | char clc;
|
|---|
| 267 |
|
|---|
| 268 | if (containsAmPm)
|
|---|
| 269 | {
|
|---|
| 270 | ExcelFormat = Regex.Replace(ExcelFormat, "AM/PM", "");
|
|---|
| 271 | DataType = eFormatType.DateTime;
|
|---|
| 272 | }
|
|---|
| 273 |
|
|---|
| 274 | for (int pos = 0; pos < ExcelFormat.Length; pos++)
|
|---|
| 275 | {
|
|---|
| 276 | char c = ExcelFormat[pos];
|
|---|
| 277 | if (c == '"')
|
|---|
| 278 | {
|
|---|
| 279 | isText = !isText;
|
|---|
| 280 | }
|
|---|
| 281 | else
|
|---|
| 282 | {
|
|---|
| 283 | if (ignoreNext)
|
|---|
| 284 | {
|
|---|
| 285 | ignoreNext = false;
|
|---|
| 286 | continue;
|
|---|
| 287 | }
|
|---|
| 288 | else if (isText && !isBracket)
|
|---|
| 289 | {
|
|---|
| 290 | sb.Append(c);
|
|---|
| 291 | }
|
|---|
| 292 | else if (isBracket)
|
|---|
| 293 | {
|
|---|
| 294 | if (c == ']')
|
|---|
| 295 | {
|
|---|
| 296 | isBracket = false;
|
|---|
| 297 | if (bracketText[0] == '$') //Local Info
|
|---|
| 298 | {
|
|---|
| 299 | string[] li = Regex.Split(bracketText, "-");
|
|---|
| 300 | if (li[0].Length > 1)
|
|---|
| 301 | {
|
|---|
| 302 | sb.Append("\"" + li[0].Substring(1, li[0].Length - 1) + "\""); //Currency symbol
|
|---|
| 303 | }
|
|---|
| 304 | if (li.Length > 1)
|
|---|
| 305 | {
|
|---|
| 306 | if (li[1].ToLower() == "f800")
|
|---|
| 307 | {
|
|---|
| 308 | specialDateFormat = "D";
|
|---|
| 309 | }
|
|---|
| 310 | else if (li[1].ToLower() == "f400")
|
|---|
| 311 | {
|
|---|
| 312 | specialDateFormat = "T";
|
|---|
| 313 | }
|
|---|
| 314 | else
|
|---|
| 315 | {
|
|---|
| 316 | var num = int.Parse(li[1], NumberStyles.HexNumber);
|
|---|
| 317 | try
|
|---|
| 318 | {
|
|---|
| 319 | Culture = CultureInfo.GetCultureInfo(num & 0xFFFF);
|
|---|
| 320 | }
|
|---|
| 321 | catch
|
|---|
| 322 | {
|
|---|
| 323 | Culture = null;
|
|---|
| 324 | }
|
|---|
| 325 | }
|
|---|
| 326 | }
|
|---|
| 327 | }
|
|---|
| 328 | }
|
|---|
| 329 | else
|
|---|
| 330 | {
|
|---|
| 331 | bracketText += c;
|
|---|
| 332 | }
|
|---|
| 333 | }
|
|---|
| 334 | else if (prevUnderScore)
|
|---|
| 335 | {
|
|---|
| 336 | if (forColWidth)
|
|---|
| 337 | {
|
|---|
| 338 | sb.AppendFormat("\"{0}\"", c);
|
|---|
| 339 | }
|
|---|
| 340 | prevUnderScore = false;
|
|---|
| 341 | }
|
|---|
| 342 | else
|
|---|
| 343 | {
|
|---|
| 344 | if (c == ';') //We use first part (for positive only at this stage)
|
|---|
| 345 | {
|
|---|
| 346 | secCount++;
|
|---|
| 347 | if (DataType == eFormatType.DateTime || secCount == 3)
|
|---|
| 348 | {
|
|---|
| 349 | format = sb.ToString();
|
|---|
| 350 | sb = new StringBuilder();
|
|---|
| 351 | }
|
|---|
| 352 | else
|
|---|
| 353 | {
|
|---|
| 354 | sb.Append(c);
|
|---|
| 355 | }
|
|---|
| 356 | }
|
|---|
| 357 | else
|
|---|
| 358 | {
|
|---|
| 359 | clc = c.ToString().ToLower()[0]; //Lowercase character
|
|---|
| 360 | //Set the datetype
|
|---|
| 361 | if (DataType == eFormatType.Unknown)
|
|---|
| 362 | {
|
|---|
| 363 | if (c == '0' || c == '#' || c == '.')
|
|---|
| 364 | {
|
|---|
| 365 | DataType = eFormatType.Number;
|
|---|
| 366 | }
|
|---|
| 367 | else if (clc == 'y' || clc == 'm' || clc == 'd' || clc == 'h' || clc == 'm' || clc == 's')
|
|---|
| 368 | {
|
|---|
| 369 | DataType = eFormatType.DateTime;
|
|---|
| 370 | }
|
|---|
| 371 | }
|
|---|
| 372 |
|
|---|
| 373 | if (prevBslsh)
|
|---|
| 374 | {
|
|---|
| 375 | sb.Append(c);
|
|---|
| 376 | prevBslsh = false;
|
|---|
| 377 | }
|
|---|
| 378 | else if (c == '[')
|
|---|
| 379 | {
|
|---|
| 380 | bracketText = "";
|
|---|
| 381 | isBracket = true;
|
|---|
| 382 | }
|
|---|
| 383 | else if (c == '\\')
|
|---|
| 384 | {
|
|---|
| 385 | prevBslsh = true;
|
|---|
| 386 | }
|
|---|
| 387 | else if (c == '0' ||
|
|---|
| 388 | c == '#' ||
|
|---|
| 389 | c == '.' ||
|
|---|
| 390 | c == ',' ||
|
|---|
| 391 | c == '%' ||
|
|---|
| 392 | clc == 'd' ||
|
|---|
| 393 | clc == 's')
|
|---|
| 394 | {
|
|---|
| 395 | sb.Append(c);
|
|---|
| 396 | }
|
|---|
| 397 | else if (clc == 'h')
|
|---|
| 398 | {
|
|---|
| 399 | if (containsAmPm)
|
|---|
| 400 | {
|
|---|
| 401 | sb.Append('h'); ;
|
|---|
| 402 | }
|
|---|
| 403 | else
|
|---|
| 404 | {
|
|---|
| 405 | sb.Append('H');
|
|---|
| 406 | }
|
|---|
| 407 | useMinute = true;
|
|---|
| 408 | }
|
|---|
| 409 | else if (clc == 'm')
|
|---|
| 410 | {
|
|---|
| 411 | if (useMinute)
|
|---|
| 412 | {
|
|---|
| 413 | sb.Append('m');
|
|---|
| 414 | }
|
|---|
| 415 | else
|
|---|
| 416 | {
|
|---|
| 417 | sb.Append('M');
|
|---|
| 418 | }
|
|---|
| 419 | }
|
|---|
| 420 | else if (c == '_') //Skip next but use for alignment
|
|---|
| 421 | {
|
|---|
| 422 | prevUnderScore = true;
|
|---|
| 423 | }
|
|---|
| 424 | else if (c == '?')
|
|---|
| 425 | {
|
|---|
| 426 | sb.Append(' ');
|
|---|
| 427 | }
|
|---|
| 428 | else if (c == '/')
|
|---|
| 429 | {
|
|---|
| 430 | if (DataType == eFormatType.Number)
|
|---|
| 431 | {
|
|---|
| 432 | fractionPos = sb.Length;
|
|---|
| 433 | int startPos = pos - 1;
|
|---|
| 434 | while (startPos >= 0 &&
|
|---|
| 435 | (ExcelFormat[startPos] == '?' ||
|
|---|
| 436 | ExcelFormat[startPos] == '#' ||
|
|---|
| 437 | ExcelFormat[startPos] == '0'))
|
|---|
| 438 | {
|
|---|
| 439 | startPos--;
|
|---|
| 440 | }
|
|---|
| 441 |
|
|---|
| 442 | if (startPos > 0) //RemovePart
|
|---|
| 443 | sb.Remove(sb.Length-(pos-startPos-1),(pos-startPos-1)) ;
|
|---|
| 444 |
|
|---|
| 445 | int endPos = pos + 1;
|
|---|
| 446 | while (endPos < ExcelFormat.Length &&
|
|---|
| 447 | (ExcelFormat[endPos] == '?' ||
|
|---|
| 448 | ExcelFormat[endPos] == '#' ||
|
|---|
| 449 | (ExcelFormat[endPos] >= '0' && ExcelFormat[endPos]<= '9')))
|
|---|
| 450 | {
|
|---|
| 451 | endPos++;
|
|---|
| 452 | }
|
|---|
| 453 | pos = endPos;
|
|---|
| 454 | if (FractionFormat != "")
|
|---|
| 455 | {
|
|---|
| 456 | FractionFormat = ExcelFormat.Substring(startPos+1, endPos - startPos-1);
|
|---|
| 457 | }
|
|---|
| 458 | sb.Append('?'); //Will be replaced later on by the fraction
|
|---|
| 459 | }
|
|---|
| 460 | else
|
|---|
| 461 | {
|
|---|
| 462 | sb.Append('/');
|
|---|
| 463 | }
|
|---|
| 464 | }
|
|---|
| 465 | else if (c == '*')
|
|---|
| 466 | {
|
|---|
| 467 | //repeat char--> ignore
|
|---|
| 468 | ignoreNext = true;
|
|---|
| 469 | }
|
|---|
| 470 | else if (c == '@')
|
|---|
| 471 | {
|
|---|
| 472 | sb.Append("{0}");
|
|---|
| 473 | }
|
|---|
| 474 | else
|
|---|
| 475 | {
|
|---|
| 476 | sb.Append(c);
|
|---|
| 477 | }
|
|---|
| 478 | }
|
|---|
| 479 | }
|
|---|
| 480 | }
|
|---|
| 481 | }
|
|---|
| 482 |
|
|---|
| 483 | // AM/PM format
|
|---|
| 484 | if (containsAmPm)
|
|---|
| 485 | {
|
|---|
| 486 | format += "tt";
|
|---|
| 487 | }
|
|---|
| 488 |
|
|---|
| 489 |
|
|---|
| 490 | if (format == "")
|
|---|
| 491 | format = sb.ToString();
|
|---|
| 492 | else
|
|---|
| 493 | text = sb.ToString();
|
|---|
| 494 | if (specialDateFormat != "")
|
|---|
| 495 | {
|
|---|
| 496 | format = specialDateFormat;
|
|---|
| 497 | }
|
|---|
| 498 |
|
|---|
| 499 | if (forColWidth)
|
|---|
| 500 | {
|
|---|
| 501 | NetFormatForWidth = format;
|
|---|
| 502 | NetTextFormatForWidth = text;
|
|---|
| 503 | }
|
|---|
| 504 | else
|
|---|
| 505 | {
|
|---|
| 506 | NetFormat = format;
|
|---|
| 507 | NetTextFormat = text;
|
|---|
| 508 | }
|
|---|
| 509 | if (Culture == null)
|
|---|
| 510 | {
|
|---|
| 511 | Culture = CultureInfo.CurrentCulture;
|
|---|
| 512 | }
|
|---|
| 513 | }
|
|---|
| 514 | internal string FormatFraction(double d)
|
|---|
| 515 | {
|
|---|
| 516 | int numerator, denomerator;
|
|---|
| 517 |
|
|---|
| 518 | int intPart = (int)d;
|
|---|
| 519 |
|
|---|
| 520 | string[] fmt = FractionFormat.Split('/');
|
|---|
| 521 |
|
|---|
| 522 | int fixedDenominator;
|
|---|
| 523 | if (!int.TryParse(fmt[1], out fixedDenominator))
|
|---|
| 524 | {
|
|---|
| 525 | fixedDenominator = 0;
|
|---|
| 526 | }
|
|---|
| 527 |
|
|---|
| 528 | if (d == 0 || double.IsNaN(d))
|
|---|
| 529 | {
|
|---|
| 530 | if (fmt[0].Trim() == "" && fmt[1].Trim() == "")
|
|---|
| 531 | {
|
|---|
| 532 | return new string(' ', FractionFormat.Length);
|
|---|
| 533 | }
|
|---|
| 534 | else
|
|---|
| 535 | {
|
|---|
| 536 | return 0.ToString(fmt[0]) + "/" + 1.ToString(fmt[0]);
|
|---|
| 537 | }
|
|---|
| 538 | }
|
|---|
| 539 |
|
|---|
| 540 | int maxDigits = fmt[1].Length;
|
|---|
| 541 | string sign = d < 0 ? "-" : "";
|
|---|
| 542 | if (fixedDenominator == 0)
|
|---|
| 543 | {
|
|---|
| 544 | List<double> numerators = new List<double>() { 1, 0 };
|
|---|
| 545 | List<double> denominators = new List<double>() { 0, 1 };
|
|---|
| 546 |
|
|---|
| 547 | if (maxDigits < 1 && maxDigits > 12)
|
|---|
| 548 | {
|
|---|
| 549 | throw (new ArgumentException("Number of digits out of range (1-12)"));
|
|---|
| 550 | }
|
|---|
| 551 |
|
|---|
| 552 | int maxNum = 0;
|
|---|
| 553 | for (int i = 0; i < maxDigits; i++)
|
|---|
| 554 | {
|
|---|
| 555 | maxNum += 9 * (int)(Math.Pow((double)10, (double)i));
|
|---|
| 556 | }
|
|---|
| 557 |
|
|---|
| 558 | double divRes = 1 / ((double)Math.Abs(d) - intPart);
|
|---|
| 559 | double result, prevResult = double.NaN;
|
|---|
| 560 | int listPos = 2, index = 1;
|
|---|
| 561 | while (true)
|
|---|
| 562 | {
|
|---|
| 563 | index++;
|
|---|
| 564 | double intDivRes = Math.Floor(divRes);
|
|---|
| 565 | numerators.Add((intDivRes * numerators[index - 1] + numerators[index - 2]));
|
|---|
| 566 | if (numerators[index] > maxNum)
|
|---|
| 567 | {
|
|---|
| 568 | break;
|
|---|
| 569 | }
|
|---|
| 570 |
|
|---|
| 571 | denominators.Add((intDivRes * denominators[index - 1] + denominators[index - 2]));
|
|---|
| 572 |
|
|---|
| 573 | result = numerators[index] / denominators[index];
|
|---|
| 574 | if (denominators[index] > maxNum)
|
|---|
| 575 | {
|
|---|
| 576 | break;
|
|---|
| 577 | }
|
|---|
| 578 | listPos = index;
|
|---|
| 579 |
|
|---|
| 580 | if (result == prevResult) break;
|
|---|
| 581 |
|
|---|
| 582 | if (result == d) break;
|
|---|
| 583 |
|
|---|
| 584 | prevResult = result;
|
|---|
| 585 |
|
|---|
| 586 | divRes = 1 / (divRes - intDivRes); //Rest
|
|---|
| 587 | }
|
|---|
| 588 |
|
|---|
| 589 | numerator = (int)numerators[listPos];
|
|---|
| 590 | denomerator = (int)denominators[listPos];
|
|---|
| 591 | }
|
|---|
| 592 | else
|
|---|
| 593 | {
|
|---|
| 594 | numerator = (int)Math.Round((d - intPart) / (1D / fixedDenominator), 0);
|
|---|
| 595 | denomerator = fixedDenominator;
|
|---|
| 596 | }
|
|---|
| 597 | if (numerator == denomerator || numerator==0)
|
|---|
| 598 | {
|
|---|
| 599 | if(numerator == denomerator) intPart++;
|
|---|
| 600 | return sign + intPart.ToString(NetFormat).Replace("?", new string(' ', FractionFormat.Length));
|
|---|
| 601 | }
|
|---|
| 602 | else if (intPart == 0)
|
|---|
| 603 | {
|
|---|
| 604 | return sign + FmtInt(numerator, fmt[0]) + "/" + FmtInt(denomerator, fmt[1]);
|
|---|
| 605 | }
|
|---|
| 606 | else
|
|---|
| 607 | {
|
|---|
| 608 | return sign + intPart.ToString(NetFormat).Replace("?", FmtInt(numerator, fmt[0]) + "/" + FmtInt(denomerator, fmt[1]));
|
|---|
| 609 | }
|
|---|
| 610 | }
|
|---|
| 611 |
|
|---|
| 612 | private string FmtInt(double value, string format)
|
|---|
| 613 | {
|
|---|
| 614 | string v = value.ToString("#");
|
|---|
| 615 | string pad = "";
|
|---|
| 616 | if (v.Length < format.Length)
|
|---|
| 617 | {
|
|---|
| 618 | for (int i = format.Length - v.Length-1; i >= 0; i--)
|
|---|
| 619 | {
|
|---|
| 620 | if (format[i] == '?')
|
|---|
| 621 | {
|
|---|
| 622 | pad += " ";
|
|---|
| 623 | }
|
|---|
| 624 | else if (format[i] == ' ')
|
|---|
| 625 | {
|
|---|
| 626 | pad += "0";
|
|---|
| 627 | }
|
|---|
| 628 | }
|
|---|
| 629 | }
|
|---|
| 630 | return pad + v;
|
|---|
| 631 | }
|
|---|
| 632 | }
|
|---|
| 633 | #endregion
|
|---|
| 634 | }
|
|---|
| 635 | }
|
|---|
