/** * Formats the given raw cell value, based on the supplied * format index and string, according to excel style rules. * @see #formatCellValue(Cell) */ public String FormatRawCellContents(double value, int formatIndex, String formatString, bool use1904Windowing) { // Is it a date? if (DateUtil.IsADateFormat(formatIndex, formatString)) { if (DateUtil.IsValidExcelDate(value)) { FormatBase dateFormat = GetFormat(value, formatIndex, formatString); if (dateFormat is ExcelStyleDateFormatter) { // Hint about the raw excel value ((ExcelStyleDateFormatter)dateFormat).SetDateToBeFormatted(value); } DateTime d = DateUtil.GetJavaDate(value, use1904Windowing); return(PerformDateFormatting(d, dateFormat)); } // RK: Invalid dates are 255 #s. if (emulateCSV) { return(invalidDateTimeString); } } // else Number FormatBase numberFormat = GetFormat(value, formatIndex, formatString); if (numberFormat == null) { return(value.ToString(currentCulture)); } // When formatting 'value', double to text to BigDecimal produces more // accurate results than double to Double in JDK8 (as compared to // previous versions). However, if the value contains E notation, this // would expand the values, which we do not want, so revert to // original method. String result; String textValue = NumberToTextConverter.ToText(value); if (textValue.IndexOf('E') > -1) { result = numberFormat.Format(value); } else { result = numberFormat.Format(textValue); } // Complete scientific notation by adding the missing +. if (result.Contains("E") && !result.Contains("E-")) { result = result.Replace("E", "E+"); } return(result); }
public void TestSimpleRendering_bug56156() { double dResult = 0.05 + 0.01; // values chosen to produce rounding anomaly String actualText = NumberToTextConverter.ToText(dResult); String jdkText = dResult.ToString("R"); //in c#, the value of dResult.ToString() is 0.06 and equals to actualText; //so we use dResult.ToString("R"), thus the test passes. if (jdkText.Equals(actualText)) { // "0.060000000000000005" throw new Exception("Should not use default JDK IEEE double rendering"); } Assert.AreEqual("0.06", actualText); }
public void OnCell(ICell cell, ICellWalkContext ctx) { double pointValue = GetOrEvalCellValue(cell); /* Silently ignore non-numeric values. * This is Office default behaviour. */ if (Double.IsNaN(pointValue)) { return; } CT_NumVal point = this.ctNumData.AddNewPt(); point.idx = (uint)ctx.OrdinalNumber; point.v = (NumberToTextConverter.ToText(pointValue)); }
private String ConvertCellValueToString() { switch (cellType) { case CellType.Blank: return(""); case CellType.Boolean: return(((BoolErrRecord)_record).BooleanValue ? "TRUE" : "FALSE"); case CellType.String: int sstIndex = ((LabelSSTRecord)_record).SSTIndex; return(book.Workbook.GetSSTString(sstIndex).String); case CellType.Numeric: return(NumberToTextConverter.ToText(((NumberRecord)_record).Value)); case CellType.Error: return(HSSFErrorConstants.GetText(((BoolErrRecord)_record).ErrorValue)); case CellType.Formula: // should really evaluate, but Cell can't call HSSFFormulaEvaluator // just use cached formula result instead break; default: throw new InvalidDataException("Unexpected cell type (" + cellType + ")"); } FormulaRecordAggregate fra = ((FormulaRecordAggregate)_record); FormulaRecord fr = fra.FormulaRecord; switch (fr.CachedResultType) { case CellType.Boolean: return(fr.CachedBooleanValue ? "TRUE" : "FALSE"); case CellType.String: return(fra.StringValue); case CellType.Numeric: return(NumberToTextConverter.ToText(fr.Value)); case CellType.Error: return(HSSFErrorConstants.GetText(fr.CachedErrorValue)); } throw new InvalidDataException("Unexpected formula result type (" + cellType + ")"); }
public void TestAllNumberToText() { int failureCount = 0; NumberToTextConversionExamples.ExampleConversion[] examples = NumberToTextConversionExamples.GetExampleConversions(); for (int i = 0; i < examples.Length; i++) { NumberToTextConversionExamples.ExampleConversion example = examples[i]; try { if (example.IsNaN) { ConfirmNaN(example.RawDoubleBits, example.ExcelRendering); continue; } String actual = NumberToTextConverter.ToText(example.DoubleValue); if (!example.ExcelRendering.Equals(actual)) { failureCount++; String msg = "Error rendering for examples[" + i + "] " + FormatExample(example) + " " + " bad-result='" + actual + "' " + "Excel String=" + example.ExcelRendering; Console.WriteLine(msg); } } catch (Exception e) { failureCount++; Console.WriteLine("Error in excel rendering for examples[" + i + "] " + FormatExample(example) + "':" + e.Message); Console.Write(e.StackTrace); } } if (failureCount > 0) { throw new Exception(failureCount + " error(s) in excel number to text conversion (see std-err)"); } }
public static string cellToString(ICell cell) { if (cell == null) { return(""); } switch (cell.CellType) { case CellType.Numeric: //Cell has Numeric content. if (DateUtil.IsCellDateFormatted(cell)) { return(cell.DateCellValue.ToLongDateString()); } else { return(NumberToTextConverter.ToText(cell.NumericCellValue)); } case CellType.Formula: //Cell contains a formula. if (cell.CachedFormulaResultType == CellType.String) { return(cell.StringCellValue); } else { if (DateUtil.IsCellDateFormatted(cell)) { return(cell.DateCellValue.ToLongDateString()); } else { return(NumberToTextConverter.ToText(cell.NumericCellValue)); } } case CellType.String: //Cell has String content. case CellType.Error: //Cell contains an error. case CellType.Boolean: //Cell contains a boolean. return(cell.StringCellValue); default: //Cell is blank. return(cell.StringCellValue); } }
public void SetUpClass() { System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); // some pre-checks to hunt for a problem in the Maven build // these checks ensure that the correct locale is set, so a Assert.Failure here // usually indicates an invalid locale during test-execution Assert.IsFalse(DateUtil.IsADateFormat(-1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-")); //Locale ul = LocaleUtil.getUserLocale(); //assertTrue(Locale.ROOT.equals(ul) || Locale.getDefault().equals(ul)); String textValue = NumberToTextConverter.ToText(1234.56); Assert.AreEqual(-1, textValue.IndexOf('E')); Object cellValueO = 1234.56d; /*CellFormat cellFormat = new CellFormat("_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-"); * CellFormatResult result = cellFormat.apply(cellValueO); * Assert.AreEqual(" 1,234.56 ", result.text);*/ CellFormat cfmt = CellFormat.GetInstance("_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-"); CellFormatResult result = cfmt.Apply(cellValueO); Assert.AreEqual(" 1,234.56 ", result.Text, "This Assert.Failure can indicate that the wrong locale is used during test-execution, ensure you run with english/US via -Duser.language=en -Duser.country=US"); }
private static String GetConstantText(Object o) { if (o == null) { return(""); // TODO - how is 'empty value' represented in formulas? } if (o is String) { return("\"" + (String)o + "\""); } if (o is Double || o is double) { return(NumberToTextConverter.ToText((Double)o)); } if (o is bool || o is Boolean) { return(((bool)o).ToString().ToUpper()); } if (o is ErrorConstant) { return(((ErrorConstant)o).Text); } throw new ArgumentException("Unexpected constant class (" + o.GetType().Name + ")"); }
public override String ToFormulaString() { return(NumberToTextConverter.ToText(Value)); }
protected override void AppendValueText(StringBuilder sb) { sb.Append(" .value= ").Append(NumberToTextConverter.ToText(field_4_value)); }
public void CopyCellValue(ICell sourceCell, ICell targetCell) { targetCell.CellStyle = sourceCell.CellStyle; if (sourceCell.CellComment != null) { targetCell.CellComment = sourceCell.CellComment; } CellType srcCellType = sourceCell.CellType; targetCell.SetCellType(srcCellType); switch (srcCellType) { case CellType.Numeric: string value; double dateValue = sourceCell.NumericCellValue; ICellStyle cellStyle = sourceCell.CellStyle; short dataFormat = cellStyle.DataFormat; if (DateUtil.IsCellDateFormatted(sourceCell)) { value = getDateValue(dataFormat, cellStyle.GetDataFormatString(), dateValue); } else { if (String.Equals("General", cellStyle.GetDataFormatString(), StringComparison.CurrentCultureIgnoreCase)) { DecimalFormat format = new DecimalFormat("#"); value = format.Format(dateValue); } else { value = NumberToTextConverter.ToText(dateValue); } } targetCell.SetCellValue(value); break; //short format = sourceCell.CellStyle.DataFormat; ////对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理***14/31/57/58日期20/32时间 //if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20 || format == 32) // targetCell.SetCellValue(sourceCell.DateCellValue); //else // targetCell.SetCellValue(sourceCell.NumericCellValue); //break; case CellType.String: targetCell.SetCellValue(sourceCell.RichStringCellValue); break; case CellType.Formula: targetCell.SetCellFormula(sourceCell.CellFormula); break; case CellType.Blank: targetCell.SetCellValue(""); break; case CellType.Boolean: targetCell.SetCellValue(sourceCell.BooleanCellValue); break; case CellType.Error: targetCell.SetCellErrorValue(sourceCell.ErrorCellValue); break; default: break; } }