/// <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 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(); }
/** * 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; }
/// <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 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 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 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)); }
// /** // * 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; Font font = IFont2Font(defaultFont); int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width; DummyEvaluator dummyEvaluator = new DummyEvaluator(); double width = -1; using (Bitmap bmp = new Bitmap(2048, 100)) { Graphics g = Graphics.FromImage(bmp); //rows: bool skipthisrow = false; for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext(); ) { IRow row = (IRow)it.Current; ICell cell = row.GetCell(column); if (cell == null) { continue; } 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. //continue rows; skipthisrow = true; } cell = row.GetCell(region.FirstColumn); colspan = 1 + region.LastColumn - region.FirstColumn; } } if (skipthisrow) { continue; } ICellStyle style = cell.CellStyle; NPOI.SS.UserModel.IFont font1 = wb.GetFontAt(style.FontIndex); CellType cellType = cell.CellType; // for formula cells we compute the cell width for the cached formula result if (cellType == CellType.FORMULA) cellType = cell.CachedFormulaResultType; 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); font = IFont2Font(font1); if (rt.NumFormattingRuns > 0) { // TODO: support rich text fragments } //layout = new TextLayout(str.Iterator, 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. */ double angle = style.Rotation * 2.0 * Math.PI / 360.0; //AffineTransform trans = new AffineTransform(); //trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0)); //trans.Concatenate( //AffineTransform.GetScaleInstance(1, fontHeightMultiple) //); SizeF sf = g.MeasureString(txt, font); 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).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); } else { //width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); double w = Math.Round(g.MeasureString(txt, font).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("F", CultureInfo.InvariantCulture); } } 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.Iterator, 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. */ double angle = style.Rotation * 2.0 * Math.PI / 360.0; //AffineTransform trans = new AffineTransform(); //trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0)); //trans.Concatenate( //AffineTransform.GetScaleInstance(1, fontHeightMultiple) //); //width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); SizeF sf = g.MeasureString(txt, font); 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.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven); width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } } } } } return width; }
/// <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 TestMissingRAttributeBug54288() { // workbook with cells missing the R attribute XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288.xlsx"); // same workbook re-saved in Excel 2010, the R attribute is updated for every cell with the right value. XSSFWorkbook wbRef = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288-ref.xlsx"); XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0); XSSFSheet sheetRef = (XSSFSheet)wbRef.GetSheetAt(0); Assert.AreEqual(sheetRef.PhysicalNumberOfRows, sheet.PhysicalNumberOfRows); // Test idea: iterate over cells in the reference worksheet, they all have the R attribute set. // For each cell from the reference sheet find the corresponding cell in the problematic file (with missing R) // and assert that POI reads them equally: DataFormatter formater = new DataFormatter(); foreach (IRow r in sheetRef) { XSSFRow rowRef = (XSSFRow)r; XSSFRow row = (XSSFRow)sheet.GetRow(rowRef.RowNum); Assert.AreEqual(rowRef.PhysicalNumberOfCells, row.PhysicalNumberOfCells, "number of cells in row[" + row.RowNum + "]"); foreach (ICell c in rowRef.Cells) { XSSFCell cellRef = (XSSFCell)c; XSSFCell cell = (XSSFCell)row.GetCell(cellRef.ColumnIndex); Assert.AreEqual(cellRef.ColumnIndex, cell.ColumnIndex); Assert.AreEqual(cellRef.GetReference(), cell.GetReference()); if (!cell.GetCTCell().IsSetR()) { Assert.IsTrue(cellRef.GetCTCell().IsSetR(), "R must e set in cellRef"); String valRef = formater.FormatCellValue(cellRef); String val = formater.FormatCellValue(cell); Assert.AreEqual(valRef, val); } } } }
public void Test47490() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("GeneralFormatTests.xlsx"); ISheet s = wb.GetSheetAt(1); IRow r; DataFormatter df = new DataFormatter(); r = s.GetRow(1); Assert.AreEqual(1.0, r.GetCell(2).NumericCellValue); Assert.AreEqual("General", r.GetCell(2).CellStyle.GetDataFormatString()); Assert.AreEqual("1", df.FormatCellValue(r.GetCell(2))); Assert.AreEqual("1", df.FormatRawCellContents(1.0, -1, "@")); Assert.AreEqual("1", df.FormatRawCellContents(1.0, -1, "General")); r = s.GetRow(2); Assert.AreEqual(12.0, r.GetCell(2).NumericCellValue); Assert.AreEqual("General", r.GetCell(2).CellStyle.GetDataFormatString()); Assert.AreEqual("12", df.FormatCellValue(r.GetCell(2))); Assert.AreEqual("12", df.FormatRawCellContents(12.0, -1, "@")); Assert.AreEqual("12", df.FormatRawCellContents(12.0, -1, "General")); r = s.GetRow(3); Assert.AreEqual(123.0, r.GetCell(2).NumericCellValue); Assert.AreEqual("General", r.GetCell(2).CellStyle.GetDataFormatString()); Assert.AreEqual("123", df.FormatCellValue(r.GetCell(2))); Assert.AreEqual("123", df.FormatRawCellContents(123.0, -1, "@")); Assert.AreEqual("123", df.FormatRawCellContents(123.0, -1, "General")); }
public void Bug57482() { foreach (PackageAccess access in new PackageAccess[] { PackageAccess.READ_WRITE, PackageAccess.READ }) { FileInfo file = HSSFTestDataSamples.GetSampleFile("57482-OnlyNumeric.xlsx"); OPCPackage pkg = OPCPackage.Open(file, access); try { XSSFWorkbook wb = new XSSFWorkbook(pkg); Assert.IsNotNull(wb.GetSharedStringSource()); Assert.AreEqual(0, wb.GetSharedStringSource().Count); DataFormatter fmt = new DataFormatter(); XSSFSheet s = wb.GetSheetAt(0) as XSSFSheet; Assert.AreEqual("1", fmt.FormatCellValue(s.GetRow(0).GetCell(0))); Assert.AreEqual("11", fmt.FormatCellValue(s.GetRow(0).GetCell(1))); Assert.AreEqual("5", fmt.FormatCellValue(s.GetRow(4).GetCell(0))); // Add a text cell s.GetRow(0).CreateCell(3).SetCellValue("Testing"); Assert.AreEqual("Testing", fmt.FormatCellValue(s.GetRow(0).GetCell(3))); // Try to Write-out and read again, should only work // in Read-write mode, not Read-only mode try { wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; if (access == PackageAccess.READ) Assert.Fail("Shouln't be able to write from Read-only mode"); } catch (InvalidOperationException e) { if (access == PackageAccess.READ) { // Expected } else { // Shouldn't occur in Write-mode throw e; } } // Check again s = wb.GetSheetAt(0) as XSSFSheet; Assert.AreEqual("1", fmt.FormatCellValue(s.GetRow(0).GetCell(0))); Assert.AreEqual("11", fmt.FormatCellValue(s.GetRow(0).GetCell(1))); Assert.AreEqual("5", fmt.FormatCellValue(s.GetRow(4).GetCell(0))); Assert.AreEqual("Testing", fmt.FormatCellValue(s.GetRow(0).GetCell(3))); } finally { pkg.Revert(); } } }
public void Bug56702() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56702.xlsx"); ISheet sheet = wb.GetSheetAt(0); // Get wrong cell by row 8 & column 7 ICell cell = sheet.GetRow(8).GetCell(7); Assert.AreEqual(CellType.Numeric, cell.CellType); // Check the value - will be zero as it is <c><v/></c> Assert.AreEqual(0.0, cell.NumericCellValue, 0.001); // Try to format DataFormatter formatter = new DataFormatter(); formatter.FormatCellValue(cell); // Check the formatting Assert.AreEqual("0", formatter.FormatCellValue(cell)); }
public void Bug54034() { IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("54034.xlsx"); ISheet sheet = wb.GetSheet("Sheet1"); IRow row = sheet.GetRow(1); ICell cell = row.GetCell(2); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); DataFormatter fmt = new DataFormatter(); Assert.AreEqual("yyyy\\-mm\\-dd\\Thh:mm", cell.CellStyle.GetDataFormatString()); Assert.AreEqual("2012-08-08T22:59", fmt.FormatCellValue(cell)); }