/// <summary> /// Gets the value. /// </summary> /// <param name="cell">The ICell object.</param> /// <param name="dataFormatter">The data formatter.</param> /// <param name="formulaEvaluator">The formula evaluator.</param> /// <returns>Value string.</returns> public static string GetValue(ICell cell, DataFormatter dataFormatter, IFormulaEvaluator formulaEvaluator) { string ret = string.Empty; if (null == cell) { return ret; } ret = dataFormatter.FormatCellValue(cell, formulaEvaluator); // remove line break return ret.Replace("\n", " "); }
public void TestColours() { //DataFormatter dfUS = new DataFormatter(Locale.US); DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US")); String[] formats = new String[] { "##.##", "[WHITE]##.##", "[BLACK]##.##;[RED]-##.##", "[COLOR11]##.##;[COLOR 43]-##.00", }; foreach (String format in formats) { Assert.AreEqual( "12.34", dfUS.FormatRawCellContents(12.343, -1, format), "Wrong format for: " + format ); Assert.AreEqual( "-12.34", dfUS.FormatRawCellContents(-12.343, -1, format), "Wrong format for: " + format ); } // Ensure that random square brackets remain Assert.AreEqual("12.34[a]", dfUS.FormatRawCellContents(12.343, -1, "##.##[a]")); Assert.AreEqual("[ab]12.34[x]", dfUS.FormatRawCellContents(12.343, -1, "[ab]##.##[x]")); }
// // Initialize from a stream of Excel file // protected void InitializeMembers(Stream excelFileStream) { this.workbook = WorkbookFactory.Create(excelFileStream); if (this.workbook != null) { this.dataFormatter = new DataFormatter(CultureInfo.InvariantCulture); this.formulaEvaluator = WorkbookFactory.CreateFormulaEvaluator(this.workbook); } }
public void TestLocale() { DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US")); DataFormatter dfFR = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("fr-FR")); Assert.AreEqual("1234", dfUS.FormatRawCellContents(1234, -1, "@")); Assert.AreEqual("1234", dfFR.FormatRawCellContents(1234, -1, "@")); Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.34, -1, "@")); Assert.AreEqual("12,34", dfFR.FormatRawCellContents(12.34, -1, "@")); }
public void TestAMPM() { DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US")); Assert.AreEqual("06:00", dfUS.FormatRawCellContents(0.25, -1, "hh:mm")); Assert.AreEqual("18:00", dfUS.FormatRawCellContents(0.75, -1, "hh:mm")); Assert.AreEqual("06:00 AM", dfUS.FormatRawCellContents(0.25, -1, "hh:mm AM/PM")); Assert.AreEqual("06:00 PM", dfUS.FormatRawCellContents(0.75, -1, "hh:mm AM/PM")); Assert.AreEqual("1904-01-01 06:00:00 AM", dfUS.FormatRawCellContents(0.25, -1, "yyyy-mm-dd hh:mm:ss AM/PM", true)); Assert.AreEqual("1904-01-01 06:00:00 PM", dfUS.FormatRawCellContents(0.75, -1, "yyyy-mm-dd hh:mm:ss AM/PM", true)); }
public void SetUp() { wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("new sheet"); style = wb.CreateCellStyle(); IDataFormat fmt = wb.CreateDataFormat(); style.DataFormat=(fmt.GetFormat("hh:mm:ss")); cell11 = sheet.CreateRow(0).CreateCell(0); form = new DataFormatter(); Evaluator = new HSSFFormulaEvaluator(wb); }
public void TestLocaleBasedFormats() { DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US")); // Standard formats Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]General")); Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]@")); // Regular numeric style formats Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]##")); Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]00")); }
/** * TODO Fix these so that they work */ public void DISABLEDtestCustomFormats() { DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true); String fmt; fmt = "\"At\" H:MM AM/PM \"on\" DDDD MMMM D\",\" YYYY"; Assert.AreEqual( "At 4:20 AM on Thursday May 17, 2007", dfUS.FormatRawCellContents(39219.1805636921, -1, fmt) ); fmt = "0 \"dollars and\" .00 \"cents\""; Assert.AreEqual("19 dollars and .99 cents", dfUS.FormatRawCellContents(19.99, -1, fmt)); }
public void doTest49928Core(IWorkbook wb) { DataFormatter df = new DataFormatter(); ISheet sheet = wb.GetSheetAt(0); ICell cell = sheet.GetRow(0).GetCell(0); ICellStyle style = cell.CellStyle; String poundFmt = "\"\u00a3\"#,##0;[Red]\\-\"\u00a3\"#,##0"; // not expected normally, id of a custom format should be greater // than BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX short poundFmtIdx = 6; Assert.AreEqual(poundFmt, style.GetDataFormatString()); Assert.AreEqual(poundFmtIdx, style.DataFormat); Assert.AreEqual("\u00a31", df.FormatCellValue(cell)); IDataFormat dataFormat = wb.CreateDataFormat(); Assert.AreEqual(poundFmtIdx, dataFormat.GetFormat(poundFmt)); Assert.AreEqual(poundFmt, dataFormat.GetFormat(poundFmtIdx)); }
public void TestTruthFile() { Stream truthFile = HSSFTestDataSamples.OpenSampleFileStream("54686_fraction_formats.txt"); TextReader reader = new StreamReader(truthFile); IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("54686_fraction_formats.xls"); ISheet sheet = wb.GetSheetAt(0); DataFormatter formatter = new DataFormatter(); IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); // Skip over the header row String truthLine = reader.ReadLine(); String[] headers = truthLine.Split("\t".ToCharArray()); truthLine = reader.ReadLine(); for (int i = 1; i < sheet.LastRowNum && truthLine != null; i++) { IRow r = sheet.GetRow(i); String[] truths = truthLine.Split("\t".ToCharArray()); // Intentionally ignore the last column (tika-1132), for now for (short j = 3; j < 12; j++) { ICell cell = r.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); String truth = Clean(truths[j]); String testKey = truths[0] + ":" + truths[1] + ":" + headers[j]; String formatted = Clean(formatter.FormatCellValue(cell, Evaluator)); if (truths.Length <= j) { continue; } Assert.AreEqual(truth, formatted, testKey); } truthLine = reader.ReadLine(); } reader.Close(); }
public void TestBug54786() { DataFormatter formatter = new DataFormatter(); String format = "[h]\"\"h\"\" m\"\"m\"\""; Assert.IsTrue(DateUtil.IsADateFormat(-1, format)); Assert.IsTrue(DateUtil.IsValidExcelDate(_15_MINUTES)); Assert.AreEqual("1h 0m", formatter.FormatRawCellContents(_15_MINUTES, -1, format, false)); Assert.AreEqual("0.041666667", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]'h'", false)); Assert.AreEqual("1h 0m\"", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]\"\"h\"\" m\"\"m\"\"\"", false)); Assert.AreEqual("1h", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]\"\"h\"\"", false)); Assert.AreEqual("h1", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"h\"\"[h]", false)); Assert.AreEqual("h1", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"h\"\"h", false)); Assert.AreEqual(" 60", formatter.FormatRawCellContents(_15_MINUTES, -1, " [m]", false)); Assert.AreEqual("h60", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"h\"\"[m]", false)); Assert.AreEqual("m1", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"m\"\"h", false)); try { Assert.AreEqual("1h 0m\"", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]\"\"h\"\" m\"\"m\"\"\"\"", false)); Assert.Fail("Catches exception because of invalid format, i.e. trailing quoting"); } catch (Exception e) { //Assert.IsTrue(e.Message.Contains("Cannot format given Object as a Number")); } }
public void TestMMMMM() { //DataFormatter dfUS = new DataFormatter(Locale.US); DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US")); DateTime c = new DateTime(2010, 6, 1, 2, 0, 0, 0); System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("en-US"); Assert.AreEqual("2010-J-1 2:00:00", dfUS.FormatRawCellContents( DateUtil.GetExcelDate(c, false), -1, "YYYY-MMMMM-D h:mm:ss" )); }
public void TestErrors() { DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true); // Create a spreadsheet with some formula errors in it IWorkbook wb = new HSSFWorkbook(); ISheet s = wb.CreateSheet(); IRow r = s.CreateRow(0); ICell c = r.CreateCell(0, CellType.Error); c.SetCellErrorValue(FormulaError.DIV0.Code); Assert.AreEqual(FormulaError.DIV0.String, dfUS.FormatCellValue(c)); c.SetCellErrorValue(FormulaError.REF.Code); Assert.AreEqual(FormulaError.REF.String, dfUS.FormatCellValue(c)); }
public void TestExcelStyleDateFormatterStringOnMillis() { // Test directly with the .000 style SimpleDateFormat formatter1 = new ExcelStyleDateFormatter("ss.000"); CultureInfo culture = System.Globalization.CultureInfo.GetCultureInfo("en-US"); DateTime dt = DateTime.Now.Date; Assert.AreEqual("00.001", formatter1.Format(dt.AddMilliseconds(1L), culture)); Assert.AreEqual("00.010", formatter1.Format(dt.AddMilliseconds(10L), culture)); Assert.AreEqual("00.100", formatter1.Format(dt.AddMilliseconds(100L), culture)); Assert.AreEqual("01.000", formatter1.Format(dt.AddMilliseconds(1000L), culture)); Assert.AreEqual("01.001", formatter1.Format(dt.AddMilliseconds(1001L), culture)); Assert.AreEqual("10.000", formatter1.Format(dt.AddMilliseconds(10000L), culture)); Assert.AreEqual("10.001", formatter1.Format(dt.AddMilliseconds(10001L), culture)); // Test directly with the .SSS style SimpleDateFormat formatter2 = new ExcelStyleDateFormatter("ss.fff"); Assert.AreEqual("00.001", formatter2.Format(dt.AddMilliseconds(1L), culture)); Assert.AreEqual("00.010", formatter2.Format(dt.AddMilliseconds(10L), culture)); Assert.AreEqual("00.100", formatter2.Format(dt.AddMilliseconds(100L), culture)); Assert.AreEqual("01.000", formatter2.Format(dt.AddMilliseconds(1000L), culture)); Assert.AreEqual("01.001", formatter2.Format(dt.AddMilliseconds(1001L), culture)); Assert.AreEqual("10.000", formatter2.Format(dt.AddMilliseconds(10000L), culture)); Assert.AreEqual("10.001", formatter2.Format(dt.AddMilliseconds(10001L), culture)); // Test via DataFormatter DataFormatter dfUS = new DataFormatter(culture, true); Assert.AreEqual("01.010", dfUS.FormatRawCellContents(0.0000116898, -1, "ss.000")); }
private FormatBase GetFormat(double cellValue, int formatIndex, String formatStrIn) { // // Might be better to separate out the n p and z formats, falling back to p when n and z are not set. // // That however would require other code to be re factored. // String[] formatBits = formatStrIn.split(";"); // int i = cellValue > 0.0 ? 0 : cellValue < 0.0 ? 1 : 2; // String formatStr = (i < formatBits.length) ? formatBits[i] : formatBits[0]; String formatStr = formatStrIn; // Excel supports positive/negative/zero, but java // doesn't, so we need to do it specially int firstAt = formatStr.IndexOf(';'); int lastAt = formatStr.LastIndexOf(';'); // p and p;n are ok by default. p;n;z and p;n;z;s need to be fixed. if (firstAt != -1 && firstAt != lastAt) { int secondAt = formatStr.IndexOf(';', firstAt + 1); if (secondAt == lastAt) { // p;n;z if (cellValue == 0.0) { formatStr = formatStr.Substring(lastAt + 1); } else { formatStr = formatStr.Substring(0, lastAt); } } else { if (cellValue == 0.0) { // p;n;z;s formatStr = formatStr.Substring(secondAt + 1, lastAt - (secondAt + 1)); } else { formatStr = formatStr.Substring(0, secondAt); } } } // Excel's # with value 0 will output empty where Java will output 0. This hack removes the # from the format. if (emulateCsv && cellValue == 0.0 && formatStr.Contains("#") && !formatStr.Contains("0")) { formatStr = formatStr.Replace("#", ""); } FormatBase format = (FormatBase)formats[formatStr]; if (format != null) { return(format); } // Is it one of the special built in types, General or @? if (formatStr.Equals("General", StringComparison.CurrentCultureIgnoreCase) || "@".Equals(formatStr)) { if (DataFormatter.IsWholeNumber(cellValue)) { return(generalWholeNumFormat); } return(generalDecimalNumFormat); } // Build a formatter, and cache it format = CreateFormat(cellValue, formatIndex, formatStr); formats[formatStr] = format; return(format); }
public void TestOther() { //DataFormatter dfUS = new DataFormatter(Locale.US, true); DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true); Assert.AreEqual(" 12.34 ", dfUS.FormatRawCellContents(12.34, -1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-")); Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.34, -1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-")); Assert.AreEqual(" - ", dfUS.FormatRawCellContents(0.0, -1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-")); Assert.AreEqual(" $- ", dfUS.FormatRawCellContents(0.0, -1, "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* \"-\"??_-;_-@_-")); }
/** * Compute width of a column based on a subset of the rows and return the result * * @param sheet the sheet to calculate * @param column 0-based index of the column * @param useMergedCells whether to use merged cells * @param firstRow 0-based index of the first row to consider (inclusive) * @param lastRow 0-based index of the last row to consider (inclusive) * @return the width in pixels */ public static double GetColumnWidth(ISheet sheet, int column, bool useMergedCells, int firstRow, int lastRow) { IWorkbook wb = sheet.Workbook; DataFormatter formatter = new DataFormatter(); IFont defaultFont = wb.GetFontAt((short)0); //str = new AttributedString((defaultChar)); //copyAttributes(defaultFont, str, 0, 1); //layout = new TextLayout(str.Iterator, fontRenderContext); //int defaultCharWidth = (int)layout.Advance; int defaultCharWidth = 0; using (Font font = IFont2Font(defaultFont)) { defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width; } double width = -1; for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) { IRow row = sheet.GetRow(rowIdx); if (row != null) { ICell cell = row.GetCell(column); if (cell == null) { continue; } double cellWidth = GetCellWidth(cell, defaultCharWidth, formatter, useMergedCells); width = Math.Max(width, cellWidth); } } return width; }
/** * Compute width of a single cell * * @param cell the cell whose width is to be calculated * @param defaultCharWidth the width of a single character * @param formatter formatter used to prepare the text to be measured * @param useMergedCells whether to use merged cells * @return the width in pixels */ public static double GetCellWidth(ICell cell, int defaultCharWidth, DataFormatter formatter, bool useMergedCells) { ISheet sheet = cell.Sheet; IWorkbook wb = sheet.Workbook; IRow row = cell.Row; int column = cell.ColumnIndex; int colspan = 1; for (int i = 0; i < sheet.NumMergedRegions; i++) { CellRangeAddress region = sheet.GetMergedRegion(i); if (ContainsCell(region, row.RowNum, column)) { if (!useMergedCells) { // If we're not using merged cells, skip this one and move on to the next. return -1; } cell = row.GetCell(region.FirstColumn); colspan = 1 + region.LastColumn - region.FirstColumn; } } ICellStyle style = cell.CellStyle; CellType cellType = cell.CellType; //IFont defaultIFont = wb.GetFontAt((short)0); // appears unnecessary? //Font windowsFont = IFont2Font(defaultFont); // this needs to be disposed // for formula cells we compute the cell width for the cached formula result if (cellType == CellType.Formula) cellType = cell.CachedFormulaResultType; IFont font = wb.GetFontAt(style.FontIndex); //AttributedString str; //TextLayout layout; double width = -1; using (Font windowsFont = IFont2Font(font)) using (Bitmap bmp = new Bitmap(2048, 100)) using (Graphics g = Graphics.FromImage(bmp)) { if (cellType == CellType.String) { IRichTextString rt = cell.RichStringCellValue; String[] lines = rt.String.Split("\n".ToCharArray()); for (int i = 0; i < lines.Length; i++) { String txt = lines[i] + defaultChar; //str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.length()); // moved to using clause if (rt.NumFormattingRuns > 0) { // TODO: support rich text fragments } //layout = new TextLayout(str.getIterator(), fontRenderContext); if (style.Rotation != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is added by the standard Excel autosize. */ //AffineTransform trans = new AffineTransform(); //trans.concatenate(AffineTransform.getRotateInstance(style.Rotation*2.0*Math.PI/360.0)); //trans.concatenate( // AffineTransform.getScaleInstance(1, fontHeightMultiple) // ); double angle = style.Rotation*2.0*Math.PI/360.0; SizeF sf = g.MeasureString(txt, windowsFont); double x1 = Math.Abs(sf.Height*Math.Sin(angle)); double x2 = Math.Abs(sf.Width*Math.Cos(angle)); double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven); width = Math.Max(width, (w/colspan/defaultCharWidth)*2 + cell.CellStyle.Indention); //width = Math.Max(width, // ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) + // cell.getCellStyle().getIndention()); } else { //width = Math.Max(width, // ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) + // cell.getCellStyle().getIndention()); double w = Math.Round(g.MeasureString(txt, windowsFont).Width, 0, MidpointRounding.ToEven); width = Math.Max(width, (w/colspan/defaultCharWidth)*2 + cell.CellStyle.Indention); } } } else { String sval = null; if (cellType == CellType.Numeric) { // Try to get it formatted to look the same as excel try { sval = formatter.FormatCellValue(cell, dummyEvaluator); } catch (Exception) { sval = cell.NumericCellValue.ToString(); } } else if (cellType == CellType.Boolean) { sval = cell.BooleanCellValue.ToString().ToUpper(); } if (sval != null) { String txt = sval + defaultChar; //str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.length()); //layout = new TextLayout(str.getIterator(), fontRenderContext); if (style.Rotation != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is added by the standard Excel autosize. */ //AffineTransform trans = new AffineTransform(); //trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0)); //trans.concatenate( // AffineTransform.getScaleInstance(1, fontHeightMultiple) // ); //width = Math.max(width, // ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) + // cell.getCellStyle().getIndention()); double angle = style.Rotation * 2.0 * Math.PI / 360.0; SizeF sf = g.MeasureString(txt, windowsFont); double x1 = sf.Height * Math.Sin(angle); double x2 = sf.Width * Math.Cos(angle); double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } else { //width = Math.max(width, // ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) + // cell.getCellStyle().getIndention()); double w = Math.Round(g.MeasureString(txt, windowsFont).Width, 0, MidpointRounding.ToEven); width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } } } } return width; }
public void Test57236() { // Having very small numbers leads to different formatting, Excel uses the scientific notation, but POI leads to "0" /* DecimalFormat format = new DecimalFormat("#.##########", new DecimalFormatSymbols(Locale.Default)); double d = 3.0E-104; Assert.AreEqual("3.0E-104", format.Format(d)); */ DataFormatter formatter = new DataFormatter(true); XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("57236.xlsx"); for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++) { ISheet sheet = wb.GetSheetAt(sheetNum); for (int rowNum = sheet.FirstRowNum; rowNum < sheet.LastRowNum; rowNum++) { IRow row = sheet.GetRow(rowNum); for (int cellNum = row.FirstCellNum; cellNum < row.LastCellNum; cellNum++) { ICell cell = row.GetCell(cellNum); String fmtCellValue = formatter.FormatCellValue(cell); //System.out.Println("Cell: " + fmtCellValue); Assert.IsNotNull(fmtCellValue); Assert.IsFalse(fmtCellValue.Equals("0")); } } } }
public void Test50756() { HSSFWorkbook wb = OpenSample("50756.xls"); ISheet s = wb.GetSheetAt(0); IRow r17 = s.GetRow(16); IRow r18 = s.GetRow(17); DataFormatter df = new DataFormatter(); Assert.AreEqual(10.0, r17.GetCell(1).NumericCellValue); Assert.AreEqual(20.0, r17.GetCell(2).NumericCellValue); Assert.AreEqual(20.0, r17.GetCell(3).NumericCellValue); Assert.AreEqual("GENERAL", r17.GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("GENERAL", r17.GetCell(2).CellStyle.GetDataFormatString()); Assert.AreEqual("GENERAL", r17.GetCell(3).CellStyle.GetDataFormatString()); Assert.AreEqual("10", df.FormatCellValue(r17.GetCell(1))); Assert.AreEqual("20", df.FormatCellValue(r17.GetCell(2))); Assert.AreEqual("20", df.FormatCellValue(r17.GetCell(3))); Assert.AreEqual(16.0, r18.GetCell(1).NumericCellValue); Assert.AreEqual(35.0, r18.GetCell(2).NumericCellValue); Assert.AreEqual(123.0, r18.GetCell(3).NumericCellValue); Assert.AreEqual("GENERAL", r18.GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("GENERAL", r18.GetCell(2).CellStyle.GetDataFormatString()); Assert.AreEqual("GENERAL", r18.GetCell(3).CellStyle.GetDataFormatString()); Assert.AreEqual("16", df.FormatCellValue(r18.GetCell(1))); Assert.AreEqual("35", df.FormatCellValue(r18.GetCell(2))); Assert.AreEqual("123", df.FormatCellValue(r18.GetCell(3))); }
public void TestPaddingSpacesCSV() { //DataFormatter dfUS = new DataFormatter(Locale.US); DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true); Assert.AreEqual("12.34 ", dfUS.FormatRawCellContents(12.343, -1, "##.##_ ")); Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.343, -1, "##.##_ ")); Assert.AreEqual(". ", dfUS.FormatRawCellContents(0.0, -1, "##.##_ ")); Assert.AreEqual("12.34 ", dfUS.FormatRawCellContents(12.343, -1, "##.##_1")); Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.343, -1, "##.##_1")); Assert.AreEqual(". ", dfUS.FormatRawCellContents(0.0, -1, "##.##_1")); Assert.AreEqual("12.34 ", dfUS.FormatRawCellContents(12.343, -1, "##.##_)")); Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.343, -1, "##.##_)")); Assert.AreEqual(". ", dfUS.FormatRawCellContents(0.0, -1, "##.##_)")); Assert.AreEqual(" 12.34", dfUS.FormatRawCellContents(12.343, -1, "_-##.##")); Assert.AreEqual("- 12.34", dfUS.FormatRawCellContents(-12.343, -1, "_-##.##")); Assert.AreEqual(" .", dfUS.FormatRawCellContents(0.0, -1, "_-##.##")); Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "##.##* ")); Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "##.##* ")); Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "##.##* ")); Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "##.##*1")); Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "##.##*1")); Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "##.##*1")); Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "##.##*)")); Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "##.##*)")); Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "##.##*)")); Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "*-##.##")); Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "*-##.##")); Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "*-##.##")); }
public void Test48968() { HSSFWorkbook wb = OpenSample("48968.xls"); Assert.AreEqual(1, wb.NumberOfSheets); DataFormatter fmt = new DataFormatter(); // Check the dates ISheet s = wb.GetSheetAt(0); ICell cell_d20110325 = s.GetRow(0).GetCell(0); ICell cell_d19000102 = s.GetRow(11).GetCell(0); ICell cell_d19000100 = s.GetRow(21).GetCell(0); Assert.AreEqual(s.GetRow(0).GetCell(3).StringCellValue, fmt.FormatCellValue(cell_d20110325)); Assert.AreEqual(s.GetRow(11).GetCell(3).StringCellValue, fmt.FormatCellValue(cell_d19000102)); // There is no such thing as 00/01/1900... Assert.AreEqual("00/01/1900 06:14:24", s.GetRow(21).GetCell(3).StringCellValue); Assert.AreEqual("31/12/1899 06:14:24", fmt.FormatCellValue(cell_d19000100)); // Check the cached values Assert.AreEqual("HOUR(A1)", s.GetRow(5).GetCell(0).CellFormula); Assert.AreEqual(11.0, s.GetRow(5).GetCell(0).NumericCellValue); Assert.AreEqual("MINUTE(A1)", s.GetRow(6).GetCell(0).CellFormula); Assert.AreEqual(39.0, s.GetRow(6).GetCell(0).NumericCellValue); Assert.AreEqual("SECOND(A1)", s.GetRow(7).GetCell(0).CellFormula); Assert.AreEqual(54.0, s.GetRow(7).GetCell(0).NumericCellValue); // Re-evaulate and check HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb); Assert.AreEqual("HOUR(A1)", s.GetRow(5).GetCell(0).CellFormula); Assert.AreEqual(11.0, s.GetRow(5).GetCell(0).NumericCellValue); Assert.AreEqual("MINUTE(A1)", s.GetRow(6).GetCell(0).CellFormula); Assert.AreEqual(39.0, s.GetRow(6).GetCell(0).NumericCellValue); Assert.AreEqual("SECOND(A1)", s.GetRow(7).GetCell(0).CellFormula); Assert.AreEqual(54.0, s.GetRow(7).GetCell(0).NumericCellValue); // Push the time forward a bit and check double date = s.GetRow(0).GetCell(0).NumericCellValue; s.GetRow(0).GetCell(0).SetCellValue(date + 1.26); HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb); Assert.AreEqual("HOUR(A1)", s.GetRow(5).GetCell(0).CellFormula); Assert.AreEqual(11.0 + 6.0, s.GetRow(5).GetCell(0).NumericCellValue); Assert.AreEqual("MINUTE(A1)", s.GetRow(6).GetCell(0).CellFormula); Assert.AreEqual(39.0 + 14.0 + 1, s.GetRow(6).GetCell(0).NumericCellValue); Assert.AreEqual("SECOND(A1)", s.GetRow(7).GetCell(0).CellFormula); Assert.AreEqual(54.0 + 24.0 - 60, s.GetRow(7).GetCell(0).NumericCellValue); }
public void TestElapsedTime() { //DataFormatter dfUS = new DataFormatter(Locale.US); DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US")); double hour = 1.0 / 24.0; Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * hour, -1, "hh:mm")); Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * hour, -1, "hh:mm")); Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * hour, -1, "hh:mm")); Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * hour, -1, "hh:mm")); Assert.AreEqual("00:00", dfUS.FormatRawCellContents(24 * hour, -1, "hh:mm")); Assert.AreEqual("02:00", dfUS.FormatRawCellContents(26 * hour, -1, "hh:mm")); Assert.AreEqual("20:00", dfUS.FormatRawCellContents(44 * hour, -1, "hh:mm")); Assert.AreEqual("02:00", dfUS.FormatRawCellContents(50 * hour, -1, "hh:mm")); Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * hour, -1, "[hh]:mm")); Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * hour, -1, "[hh]:mm")); Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * hour, -1, "[hh]:mm")); Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * hour, -1, "[hh]:mm")); Assert.AreEqual("24:00", dfUS.FormatRawCellContents(24 * hour, -1, "[hh]:mm")); Assert.AreEqual("26:00", dfUS.FormatRawCellContents(26 * hour, -1, "[hh]:mm")); Assert.AreEqual("44:00", dfUS.FormatRawCellContents(44 * hour, -1, "[hh]:mm")); Assert.AreEqual("50:00", dfUS.FormatRawCellContents(50 * hour, -1, "[hh]:mm")); Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * hour, -1, "[hh]")); Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * hour, -1, "[hh]")); Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * hour, -1, "[hh]")); Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * hour, -1, "[hh]")); Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * hour, -1, "[hh]")); Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * hour, -1, "[hh]")); Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * hour, -1, "[hh]")); Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * hour, -1, "[hh]")); double minute = 1.0 / 24.0 / 60.0; Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * minute, -1, "[mm]:ss")); Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * minute, -1, "[mm]:ss")); Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * minute, -1, "[mm]:ss")); Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * minute, -1, "[mm]:ss")); Assert.AreEqual("24:00", dfUS.FormatRawCellContents(24 * minute, -1, "[mm]:ss")); Assert.AreEqual("26:00", dfUS.FormatRawCellContents(26 * minute, -1, "[mm]:ss")); Assert.AreEqual("44:00", dfUS.FormatRawCellContents(44 * minute, -1, "[mm]:ss")); Assert.AreEqual("50:00", dfUS.FormatRawCellContents(50 * minute, -1, "[mm]:ss")); Assert.AreEqual("59:00", dfUS.FormatRawCellContents(59 * minute, -1, "[mm]:ss")); Assert.AreEqual("60:00", dfUS.FormatRawCellContents(60 * minute, -1, "[mm]:ss")); Assert.AreEqual("61:00", dfUS.FormatRawCellContents(61 * minute, -1, "[mm]:ss")); Assert.AreEqual("119:00", dfUS.FormatRawCellContents(119 * minute, -1, "[mm]:ss")); Assert.AreEqual("120:00", dfUS.FormatRawCellContents(120 * minute, -1, "[mm]:ss")); Assert.AreEqual("121:00", dfUS.FormatRawCellContents(121 * minute, -1, "[mm]:ss")); Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * minute, -1, "[mm]")); Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * minute, -1, "[mm]")); Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * minute, -1, "[mm]")); Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * minute, -1, "[mm]")); Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * minute, -1, "[mm]")); Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * minute, -1, "[mm]")); Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * minute, -1, "[mm]")); Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * minute, -1, "[mm]")); Assert.AreEqual("59", dfUS.FormatRawCellContents(59 * minute, -1, "[mm]")); Assert.AreEqual("60", dfUS.FormatRawCellContents(60 * minute, -1, "[mm]")); Assert.AreEqual("61", dfUS.FormatRawCellContents(61 * minute, -1, "[mm]")); Assert.AreEqual("119", dfUS.FormatRawCellContents(119 * minute, -1, "[mm]")); Assert.AreEqual("120", dfUS.FormatRawCellContents(120 * minute, -1, "[mm]")); Assert.AreEqual("121", dfUS.FormatRawCellContents(121 * minute, -1, "[mm]")); double second = 1.0 / 24.0 / 60.0 / 60.0; Assert.AreEqual("86400", dfUS.FormatRawCellContents(86400 * second, -1, "[ss]")); Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * second, -1, "[ss]")); Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * second, -1, "[ss]")); Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * second, -1, "[ss]")); Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * second, -1, "[ss]")); Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * second, -1, "[ss]")); Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * second, -1, "[ss]")); Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * second, -1, "[ss]")); Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * second, -1, "[ss]")); Assert.AreEqual("59", dfUS.FormatRawCellContents(59 * second, -1, "[ss]")); Assert.AreEqual("60", dfUS.FormatRawCellContents(60 * second, -1, "[ss]")); Assert.AreEqual("61", dfUS.FormatRawCellContents(61 * second, -1, "[ss]")); Assert.AreEqual("119", dfUS.FormatRawCellContents(119 * second, -1, "[ss]")); Assert.AreEqual("120", dfUS.FormatRawCellContents(120 * second, -1, "[ss]")); Assert.AreEqual("121", dfUS.FormatRawCellContents(121 * second, -1, "[ss]")); //boolean jdk_1_5 = System.getProperty("java.vm.version").startsWith("1.5"); //if(!jdk_1_5) { // YK: the tests below were written under JDK 1.6 and assume that // the rounding mode in the underlying decimal formatters is HALF_UP // It is not so JDK 1.5 where the default rounding mode is HALV_EVEN and cannot be changed. Assert.AreEqual("27:18:08", dfUS.FormatRawCellContents(1.1376, -1, "[h]:mm:ss")); Assert.AreEqual("28:48:00", dfUS.FormatRawCellContents(1.2, -1, "[h]:mm:ss")); Assert.AreEqual("29:31:12", dfUS.FormatRawCellContents(1.23, -1, "[h]:mm:ss")); Assert.AreEqual("31:26:24", dfUS.FormatRawCellContents(1.31, -1, "[h]:mm:ss")); Assert.AreEqual("27:18:08", dfUS.FormatRawCellContents(1.1376, -1, "[hh]:mm:ss")); Assert.AreEqual("28:48:00", dfUS.FormatRawCellContents(1.2, -1, "[hh]:mm:ss")); Assert.AreEqual("29:31:12", dfUS.FormatRawCellContents(1.23, -1, "[hh]:mm:ss")); Assert.AreEqual("31:26:24", dfUS.FormatRawCellContents(1.31, -1, "[hh]:mm:ss")); Assert.AreEqual("57:07.2", dfUS.FormatRawCellContents(.123, -1, "mm:ss.0;@")); Assert.AreEqual("57:41.8", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.0;@")); Assert.AreEqual("57:41.76", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.00;@")); Assert.AreEqual("57:41.760", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.000;@")); Assert.AreEqual("24:00.0", dfUS.FormatRawCellContents(123456.6, -1, "mm:ss.0")); //} }
/// <summary> /// Lazily the load workbook. /// </summary> private void LazyLoadWorkbook() { // Check to lazily initialize workbook object if (_workbook == null) { // Check if file path is valid if (!string.IsNullOrWhiteSpace(_dashboardFile)) { _workbook = this.LoadWorkbook(_dashboardFile); _formulaEvaluator = new HSSFFormulaEvaluator(_workbook); _dataFormatter = new HSSFDataFormatter(new CultureInfo("en-US")); } } }
public void TestDateWindowing() { //DataFormatter dfUS = new DataFormatter(Locale.US); DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US")); Assert.AreEqual("1899-12-31 00:00:00", dfUS.FormatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss")); Assert.AreEqual("1899-12-31 00:00:00", dfUS.FormatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss", false)); Assert.AreEqual("1904-01-01 00:00:00", dfUS.FormatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss", true)); }
/// <summary> /// Returns a string representation of the cell /// This method returns a simple representation, /// anthing more complex should be in user code, with /// knowledge of the semantics of the sheet being Processed. /// Formula cells return the formula string, /// rather than the formula result. /// Dates are Displayed in dd-MMM-yyyy format /// Errors are Displayed as #ERR<errIdx> /// </summary> public override String ToString() { switch (CellType) { case CellType.BLANK: return ""; case CellType.BOOLEAN: return BooleanCellValue ? "TRUE" : "FALSE"; case CellType.ERROR: return NPOI.SS.Formula.Eval.ErrorEval.GetText(((BoolErrRecord)record).ErrorValue); case CellType.FORMULA: return CellFormula; case CellType.NUMERIC: string format = this.CellStyle.GetDataFormatString(); DataFormatter formatter = new DataFormatter(); return formatter.FormatCellValue(this); case CellType.STRING: return StringCellValue; default: return "Unknown Cell Type: " + CellType; } }
public void TestScientificNotation() { //DataFormatter dfUS = new DataFormatter(Locale.US); DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US")); Assert.AreEqual("1.23E+01", dfUS.FormatRawCellContents(12.343, -1, "0.00E+00")); Assert.AreEqual("-1.23E+01", dfUS.FormatRawCellContents(-12.343, -1, "0.00E+00")); Assert.AreEqual("0.00E+00", dfUS.FormatRawCellContents(0.0, -1, "0.00E+00")); }
// /** // * Drawing context to measure text // */ //private static FontRenderContext fontRenderContext = new FontRenderContext(null, true, true); /** * Compute width of a column and return the result * * @param sheet the sheet to calculate * @param column 0-based index of the column * @param useMergedCells whether to use merged cells * @return the width in pixels */ public static double GetColumnWidth(ISheet sheet, int column, bool useMergedCells) { //AttributedString str; //TextLayout layout; IWorkbook wb = sheet.Workbook; DataFormatter formatter = new DataFormatter(); IFont defaultFont = wb.GetFontAt((short) 0); //str = new AttributedString((defaultChar)); //copyAttributes(defaultFont, str, 0, 1); //layout = new TextLayout(str.Iterator, fontRenderContext); //int defaultCharWidth = (int)layout.Advance; int defaultCharWidth = 0; using (Font font = IFont2Font(defaultFont)) { defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width; } //DummyEvaluator dummyEvaluator = new DummyEvaluator(); double width = -1; foreach (IRow row in sheet) { ICell cell = row.GetCell(column); if (cell == null) { continue; } double cellWidth = GetCellWidth(cell, defaultCharWidth, formatter, useMergedCells); width = Math.Max(width, cellWidth); } return width; }
public void TestInvalidDate() { //DataFormatter df1 = new DataFormatter(Locale.US); DataFormatter df1 = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US")); //Assert.AreEqual("-1.0", df1.FormatRawCellContents(-1, -1, "mm/dd/yyyy")); //in java -1.toString() is "-1.0", but in C# -1.ToString() is "-1". Assert.AreEqual("-1", df1.FormatRawCellContents(-1, -1, "mm/dd/yyyy")); //DataFormatter df2 = new DataFormatter(Locale.US); DataFormatter df2 = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true); Assert.AreEqual("###############################################################################################################################################################################################################################################################", df2.FormatRawCellContents(-1, -1, "mm/dd/yyyy")); }
/** * Processes the given sheet */ public void ProcessSheet( SheetContentsHandler sheetContentsExtractor, StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) { DataFormatter formatter; if (locale == null) { formatter = new DataFormatter(); } else { formatter = new DataFormatter(locale); } InputSource sheetSource = new InputSource(sheetInputStream); SAXParserFactory saxFactory = SAXParserFactory.newInstance(); try { SAXParser saxParser = saxFactory.newSAXParser(); XMLReader sheetParser = saxParser.GetXMLReader(); ContentHandler handler = new XSSFSheetXMLHandler( styles, strings, sheetContentsExtractor, formatter, formulasNotResults); sheetParser.SetContentHandler(handler); sheetParser.Parse(sheetSource); } catch (ParserConfigurationException e) { throw new RuntimeException("SAX Parser appears to be broken - " + e.GetMessage()); } }
public void TestEscapes() { DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US")); Assert.AreEqual("1901-01-01", dfUS.FormatRawCellContents(367.0, -1, "yyyy-mm-dd")); Assert.AreEqual("1901-01-01", dfUS.FormatRawCellContents(367.0, -1, "yyyy\\-mm\\-dd")); Assert.AreEqual("1901.01.01", dfUS.FormatRawCellContents(367.0, -1, "yyyy.mm.dd")); Assert.AreEqual("1901.01.01", dfUS.FormatRawCellContents(367.0, -1, "yyyy\\.mm\\.dd")); Assert.AreEqual("1901/01/01", dfUS.FormatRawCellContents(367.0, -1, "yyyy/mm/dd")); Assert.AreEqual("1901/01/01", dfUS.FormatRawCellContents(367.0, -1, "yyyy\\/mm\\/dd")); }