public String Format(double num) { double doubleValue = num; // Format may be p or p;n or p;n;z (okay we never get a z). // Fall back to p when n or z is not specified. String[] formatBits = formatstr.Split(";".ToCharArray()); int f = doubleValue > 0.0 ? 0 : doubleValue < 0.0 ? 1 : 2; String str = (f < formatBits.Length) ? formatBits[f] : formatBits[0]; double wholePart = Math.Floor(Math.Abs(doubleValue)); double decPart = Math.Abs(doubleValue) - wholePart; if (wholePart + decPart == 0) { return "0"; } if (doubleValue < 0.0) { wholePart *= -1.0; } //str = regex.Replace(str, ReplaceWS); // Split the format string into decimal and fraction parts String[] parts = str.Replace(" *", " ").Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); String[] fractParts; if (parts.Length == 2) { fractParts = parts[1].Split("/".ToCharArray()); } else { fractParts = str.Split("/".ToCharArray()); } // Excel supports both #/# and ?/?, but Java only the former for (int i = 0; i < fractParts.Length; i++) { fractParts[i] = fractParts[i].Replace('?', '#'); } if (fractParts.Length == 2) { int fractPart1Length = Math.Min(CountHashes(fractParts[1]), 4); // Any more than 3 and we go around the loops for ever double minVal = 1.0; double currDenom = Math.Pow(10, fractPart1Length) - 1d; double currNeum = 0; for (int i = (int)(Math.Pow(10, fractPart1Length) - 1d); i > 0; i--) { for (int i2 = (int)(Math.Pow(10, fractPart1Length) - 1d); i2 > 0; i2--) { if (minVal >= Math.Abs((double)i2 / (double)i - decPart)) { currDenom = i; currNeum = i2; minVal = Math.Abs((double)i2 / (double)i - decPart); } } } DecimalFormat neumFormatter = new DecimalFormat(fractParts[0]); DecimalFormat denomFormatter = new DecimalFormat(fractParts[1]); CultureInfo cul = CultureInfo.CurrentCulture; if (parts.Length == 2) { DecimalFormat wholeFormatter = new DecimalFormat(parts[0]); String result = wholeFormatter.Format(wholePart, cul) + " " + neumFormatter.Format(currNeum, cul) + "/" + denomFormatter.Format(currDenom, cul); return result; } else { String result = neumFormatter.Format(currNeum + (currDenom * wholePart), cul) + "/" + denomFormatter.Format(currDenom, cul); return result; } } else { throw new ArgumentException("Fraction must have 2 parts, found " + fractParts.Length + " for fraction format " + this.formatstr); } }
public void TestSetDefaultNumberFormat() { IRow row = wb.GetSheetAt(0).GetRow(3); List<ICell> cells = row.Cells; FormatBase defaultFormat = new DecimalFormat("Balance $#,#00.00 USD;Balance -$#,#00.00 USD"); formatter.SetDefaultNumberFormat(defaultFormat); Random rand = new Random((int)DateTime.Now.ToFileTime()); log("\n==== DEFAULT NUMBER FORMAT ===="); foreach(ICell cell in cells) { cell.SetCellValue(cell.NumericCellValue * rand.Next() / 1000000 - 1000); log(formatter.FormatCellValue(cell)); Assert.IsTrue(formatter.FormatCellValue(cell).StartsWith("Balance ")); Assert.IsTrue(formatter.FormatCellValue(cell).EndsWith(" USD")); } }
/// <summary> /// Formats a number or date cell, be that a real number, or the /// answer to a formula /// </summary> /// <param name="cell">The cell.</param> /// <param name="value">The value.</param> /// <returns></returns> private String FormatNumberDateCell(CellValueRecordInterface cell, double value) { // Get the built in format, if there is one int formatIndex = ft.GetFormatIndex(cell); String formatString = ft.GetFormatString(cell); if (formatString == null) { return value.ToString(CultureInfo.InvariantCulture); } else { // Is it a date? if (NPOI.SS.UserModel.DateUtil.IsADateFormat(formatIndex, formatString) && NPOI.SS.UserModel.DateUtil.IsValidExcelDate(value)) { // Java wants M not m for month formatString = formatString.Replace('m', 'M'); // Change \- into -, if it's there formatString = formatString.Replace("\\\\-", "-"); // Format as a date DateTime d = NPOI.SS.UserModel.DateUtil.GetJavaDate(value, false); SimpleDateFormat df = new SimpleDateFormat(formatString); return df.Format(d); } else { if (formatString == "General") { // Some sort of wierd default return value.ToString(CultureInfo.InvariantCulture); } // Format as a number DecimalFormat df = new DecimalFormat(formatString); return df.Format(value); } } }
public String Format(double num) { double doubleValue = num; // Format may be p or p;n or p;n;z (okay we never get a z). // Fall back to p when n or z is not specified. String[] formatBits = formatstr.Split(";".ToCharArray()); int f = doubleValue > 0.0 ? 0 : doubleValue < 0.0 ? 1 : 2; String str = (f < formatBits.Length) ? formatBits[f] : formatBits[0]; double wholePart = Math.Floor(Math.Abs(doubleValue)); double decPart = Math.Abs(doubleValue) - wholePart; if (wholePart + decPart == 0) { return("0"); } if (doubleValue < 0.0) { wholePart *= -1.0; } //str = regex.Replace(str, ReplaceWS); // Split the format string into decimal and fraction parts String[] parts = str.Replace(" *", " ").Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); String[] fractParts; if (parts.Length == 2) { fractParts = parts[1].Split("/".ToCharArray()); } else { fractParts = str.Split("/".ToCharArray()); } // Excel supports both #/# and ?/?, but Java only the former for (int i = 0; i < fractParts.Length; i++) { fractParts[i] = fractParts[i].Replace('?', '#'); } if (fractParts.Length == 2) { int fractPart1Length = Math.Min(CountHashes(fractParts[1]), 4); // Any more than 3 and we go around the loops for ever double minVal = 1.0; double currDenom = Math.Pow(10, fractPart1Length) - 1d; double currNeum = 0; for (int i = (int)(Math.Pow(10, fractPart1Length) - 1d); i > 0; i--) { for (int i2 = (int)(Math.Pow(10, fractPart1Length) - 1d); i2 > 0; i2--) { if (minVal >= Math.Abs((double)i2 / (double)i - decPart)) { currDenom = i; currNeum = i2; minVal = Math.Abs((double)i2 / (double)i - decPart); } } } DecimalFormat neumFormatter = new DecimalFormat(fractParts[0]); DecimalFormat denomFormatter = new DecimalFormat(fractParts[1]); CultureInfo cul = CultureInfo.CurrentCulture; if (parts.Length == 2) { DecimalFormat wholeFormatter = new DecimalFormat(parts[0]); String result = wholeFormatter.Format(wholePart, cul) + " " + neumFormatter.Format(currNeum, cul) + "/" + denomFormatter.Format(currDenom, cul); return(result); } else { String result = neumFormatter.Format(currNeum + (currDenom * wholePart), cul) + "/" + denomFormatter.Format(currDenom, cul); return(result); } } else { throw new ArgumentException("Fraction must have 2 parts, found " + fractParts.Length + " for fraction format " + this.formatstr); } }
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { double s0; String s1; try { s0 = TextFunction.EvaluateDoubleArg(arg0, srcRowIndex, srcColumnIndex); s1 = TextFunction.EvaluateStringArg(arg1, srcRowIndex, srcColumnIndex); } catch (EvaluationException e) { return e.GetErrorEval(); } if (Regex.Match(s1, @"[\d,\#,\.,\$,\,]+").Success) { FormatBase formatter = new DecimalFormat(s1); return new StringEval(formatter.Format(s0)); } else if (s1.IndexOf("/") == s1.LastIndexOf("/") && s1.IndexOf("/") >= 0 && !s1.Contains("-")) { double wholePart = Math.Floor(s0); double decPart = s0 - wholePart; if (wholePart * decPart == 0) { return new StringEval("0"); } String[] parts = s1.Split(' '); String[] fractParts; if (parts.Length == 2) { fractParts = parts[1].Split('/'); } else { fractParts = s1.Split('/'); } if (fractParts.Length == 2) { double minVal = 1.0; double currDenom = Math.Pow(10, fractParts[1].Length) - 1d; double currNeum = 0; for (int i = (int)(Math.Pow(10, fractParts[1].Length) - 1d); i > 0; i--) { for (int i2 = (int)(Math.Pow(10, fractParts[1].Length) - 1d); i2 > 0; i2--) { if (minVal >= Math.Abs((double)i2 / (double)i - decPart)) { currDenom = i; currNeum = i2; minVal = Math.Abs((double)i2 / (double)i - decPart); } } } FormatBase neumFormatter = new DecimalFormat(fractParts[0]); FormatBase denomFormatter = new DecimalFormat(fractParts[1]); if (parts.Length == 2) { FormatBase wholeFormatter = new DecimalFormat(parts[0]); String result = wholeFormatter.Format(wholePart) + " " + neumFormatter.Format(currNeum) + "/" + denomFormatter.Format(currDenom); return new StringEval(result); } else { String result = neumFormatter.Format(currNeum + (currDenom * wholePart)) + "/" + denomFormatter.Format(currDenom); return new StringEval(result); } } else { return ErrorEval.VALUE_INVALID; } } else { try { FormatBase dateFormatter = new SimpleDateFormat(s1); DateTime dt = new DateTime(1899, 11, 30, 0, 0, 0); dt.AddDays((int)Math.Floor(s0)); double dayFraction = s0 - Math.Floor(s0); dt.AddMilliseconds((int)Math.Round(dayFraction * 24 * 60 * 60 * 1000)); return new StringEval(dateFormatter.Format(dt)); } catch (Exception) { return ErrorEval.VALUE_INVALID; } } }
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { double s0; String s1; try { s0 = TextFunction.EvaluateDoubleArg(arg0, srcRowIndex, srcColumnIndex); s1 = TextFunction.EvaluateStringArg(arg1, srcRowIndex, srcColumnIndex); } catch (EvaluationException e) { return e.GetErrorEval(); } if (Regex.Match(s1, "[y|m|M|d|s|h]+").Success) { //may be datetime string ValueEval result = TryParseDateTime(s0, s1); if (result != ErrorEval.VALUE_INVALID) return result; } //The regular expression needs ^ and $. if (Regex.Match(s1, @"^[\d,\#,\.,\$,\,]+$").Success) { //TODO: simulate DecimalFormat class in java. FormatBase formatter = new DecimalFormat(s1); return new StringEval(formatter.Format(s0)); } else if (s1.IndexOf("/", StringComparison.Ordinal) == s1.LastIndexOf("/", StringComparison.Ordinal) && s1.IndexOf("/", StringComparison.Ordinal) >= 0 && !s1.Contains("-")) { double wholePart = Math.Floor(s0); double decPart = s0 - wholePart; if (wholePart * decPart == 0) { return new StringEval("0"); } String[] parts = s1.Split(' '); String[] fractParts; if (parts.Length == 2) { fractParts = parts[1].Split('/'); } else { fractParts = s1.Split('/'); } if (fractParts.Length == 2) { double minVal = 1.0; double currDenom = Math.Pow(10, fractParts[1].Length) - 1d; double currNeum = 0; for (int i = (int)(Math.Pow(10, fractParts[1].Length) - 1d); i > 0; i--) { for (int i2 = (int)(Math.Pow(10, fractParts[1].Length) - 1d); i2 > 0; i2--) { if (minVal >= Math.Abs((double)i2 / (double)i - decPart)) { currDenom = i; currNeum = i2; minVal = Math.Abs((double)i2 / (double)i - decPart); } } } FormatBase neumFormatter = new DecimalFormat(fractParts[0]); FormatBase denomFormatter = new DecimalFormat(fractParts[1]); if (parts.Length == 2) { FormatBase wholeFormatter = new DecimalFormat(parts[0]); String result = wholeFormatter.Format(wholePart) + " " + neumFormatter.Format(currNeum) + "/" + denomFormatter.Format(currDenom); return new StringEval(result); } else { String result = neumFormatter.Format(currNeum + (currDenom * wholePart)) + "/" + denomFormatter.Format(currDenom); return new StringEval(result); } } else { return ErrorEval.VALUE_INVALID; } } else { return TryParseDateTime(s0, s1); } }