public void TestFromFile() { IWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("Formatting.xls"); ISheet sheet = workbook.GetSheetAt(0); HSSFDataFormatter f = new HSSFDataFormatter(); // This one is one of the nasty auto-locale changing ones... Assert.AreEqual("dd/mm/yyyy", sheet.GetRow(1).GetCell(0).StringCellValue); Assert.AreEqual("m/d/yy", sheet.GetRow(1).GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("11/24/06", f.FormatCellValue(sheet.GetRow(1).GetCell(1))); Assert.AreEqual("yyyy/mm/dd", sheet.GetRow(2).GetCell(0).StringCellValue); Assert.AreEqual("yyyy/mm/dd", sheet.GetRow(2).GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("2006/11/24", f.FormatCellValue(sheet.GetRow(2).GetCell(1))); Assert.AreEqual("yyyy-mm-dd", sheet.GetRow(3).GetCell(0).StringCellValue); Assert.AreEqual("yyyy\\-mm\\-dd", sheet.GetRow(3).GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("2006-11-24", f.FormatCellValue(sheet.GetRow(3).GetCell(1))); Assert.AreEqual("yy/mm/dd", sheet.GetRow(4).GetCell(0).StringCellValue); Assert.AreEqual("yy/mm/dd", sheet.GetRow(4).GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("06/11/24", f.FormatCellValue(sheet.GetRow(4).GetCell(1))); // Another builtin fun one Assert.AreEqual("dd/mm/yy", sheet.GetRow(5).GetCell(0).StringCellValue); Assert.AreEqual("d/m/yy;@", sheet.GetRow(5).GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("24/11/06", f.FormatCellValue(sheet.GetRow(5).GetCell(1))); Assert.AreEqual("dd-mm-yy", sheet.GetRow(6).GetCell(0).StringCellValue); Assert.AreEqual("dd\\-mm\\-yy", sheet.GetRow(6).GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("24-11-06", f.FormatCellValue(sheet.GetRow(6).GetCell(1))); // Another builtin fun one Assert.AreEqual("nn.nn", sheet.GetRow(9).GetCell(0).StringCellValue); Assert.AreEqual("General", sheet.GetRow(9).GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("10.52", f.FormatCellValue(sheet.GetRow(9).GetCell(1))); // text isn't quite the format rule... Assert.AreEqual("nn.nnn", sheet.GetRow(10).GetCell(0).StringCellValue); Assert.AreEqual("0.000", sheet.GetRow(10).GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("10.520", f.FormatCellValue(sheet.GetRow(10).GetCell(1))); // text isn't quite the format rule... Assert.AreEqual("nn.n", sheet.GetRow(11).GetCell(0).StringCellValue); Assert.AreEqual("0.0", sheet.GetRow(11).GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("10.5", f.FormatCellValue(sheet.GetRow(11).GetCell(1))); // text isn't quite the format rule... Assert.AreEqual("\u00a3nn.nn", sheet.GetRow(12).GetCell(0).StringCellValue); Assert.AreEqual("\"\u00a3\"#,##0.00", sheet.GetRow(12).GetCell(1).CellStyle.GetDataFormatString()); Assert.AreEqual("\u00a310.52", f.FormatCellValue(sheet.GetRow(12).GetCell(1))); }
public void TestGeneralAtFormat() { IWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("47154.xls"); ISheet sheet = workbook.GetSheetAt(0); IRow row = sheet.GetRow(0); ICell cellA1 = row.GetCell(0); Assert.AreEqual(CellType.Numeric, cellA1.CellType); Assert.AreEqual(2345.0, cellA1.NumericCellValue, 0.0001); Assert.AreEqual("@", cellA1.CellStyle.GetDataFormatString()); HSSFDataFormatter f = new HSSFDataFormatter(); Assert.AreEqual("2345", f.FormatCellValue(cellA1)); }
ToxyTable Parse(IWorkbook workbook, int sheetIndex, bool extractHeader, bool extractFooter, bool hasHeader, bool fillBlankCells, bool includeComment, HSSFDataFormatter formatter) { ToxyTable table = new ToxyTable(); if (workbook.NumberOfSheets - 1 < sheetIndex) { throw new ArgumentOutOfRangeException(string.Format("This file only contains {0} sheet(s).", workbook.NumberOfSheets)); } ISheet sheet = workbook.GetSheetAt(sheetIndex); table.Name = sheet.SheetName; if (extractHeader && sheet.Header != null) { table.PageHeader = sheet.Header.Left + "|" + sheet.Header.Center + "|" + sheet.Header.Right; } if (extractFooter && sheet.Footer != null) { table.PageFooter = sheet.Footer.Left + "|" + sheet.Footer.Center + "|" + sheet.Footer.Right; } bool firstRow = true; table.LastRowIndex = sheet.LastRowNum; foreach (IRow row in sheet) { ToxyRow tr = null; if (!hasHeader || !firstRow) { tr = new ToxyRow(row.RowNum); } else if (hasHeader && firstRow) { table.HeaderRows.Add(new ToxyRow(row.RowNum)); } foreach (ICell cell in row) { if (hasHeader && firstRow) { table.HeaderRows[0].Cells.Add(new ToxyCell(cell.ColumnIndex, cell.ToString())); } else { if (tr.LastCellIndex < cell.ColumnIndex) { tr.LastCellIndex = cell.ColumnIndex; } ToxyCell c = new ToxyCell(cell.ColumnIndex, formatter.FormatCellValue(cell)); if (!string.IsNullOrEmpty(cell.ToString())) { tr.Cells.Add(c); } else if (fillBlankCells) { tr.Cells.Add(c); } if (includeComment && cell.CellComment != null) { c.Comment = cell.CellComment.String.String; } } } if (tr != null) { tr.RowIndex = row.RowNum; table.Rows.Add(tr); if (table.LastColumnIndex < tr.LastCellIndex) { table.LastColumnIndex = tr.LastCellIndex; } } if (firstRow) { firstRow = false; } } for (int j = 0; j < sheet.NumMergedRegions; j++) { var range = sheet.GetMergedRegion(j); table.MergeCells.Add(new MergeCellRange() { FirstRow = range.FirstRow, FirstColumn = range.FirstColumn, LastRow = range.LastRow, LastColumn = range.LastColumn }); } return(table); }
public void TestGetFormattedCellValueHSSFCell() { // Valid date formats -- cell values should be date formatted & not "555.555" IRow row = wb.GetSheetAt(0).GetRow(0); IEnumerator it = row.GetEnumerator(); log("==== VALID DATE FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; string fmtval = formatter.FormatCellValue(cell); log(fmtval); // should not be equal to "555.555" Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); Assert.IsTrue(!"555.555".Equals(fmtval)); string fmt = cell.CellStyle.GetDataFormatString(); //assert the correct month form, as in the original Excel format string monthPtrn = fmt.IndexOf("mmmm") != -1 ? "MMMM" : "MMM"; // this line is intended to compute how "July" would look like in the current locale string jul = new SimpleDateFormat(monthPtrn).Format(new DateTime(2010, 7, 15), CultureInfo.CurrentCulture); // special case for MMMMM = 1st letter of month name if (fmt.IndexOf("mmmmm") > -1) { jul = jul.Substring(0, 1); } // check we found july properly Assert.IsTrue(fmtval.IndexOf(jul) > -1, "Format came out incorrect - " + fmt); } row = wb.GetSheetAt(0).GetRow(1); it = row.GetEnumerator(); log("==== VALID TIME FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; string fmt = cell.CellStyle.GetDataFormatString(); string fmtval = formatter.FormatCellValue(cell); log(fmtval); // should not be equal to "555.47431" Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); Assert.IsTrue(!"555.47431".Equals(fmtval)); // check we found the time properly Assert.IsTrue(fmtval.IndexOf("11:23") > -1, "Format came out incorrect - " + fmt); } // Test number formats row = wb.GetSheetAt(0).GetRow(1); it = row.GetEnumerator(); log("\n==== VALID NUMBER FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; log(formatter.FormatCellValue(cell)); // should not be equal to "1234567890.12345" Assert.IsTrue(!"1234567890.12345".Equals(formatter.FormatCellValue(cell))); } // Test bad number formats row = wb.GetSheetAt(0).GetRow(3); it = row.GetEnumerator(); log("\n==== INVALID NUMBER FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; log(formatter.FormatCellValue(cell)); // should be equal to "1234567890.12345" // in some locales the the decimal delimiter is a comma, not a dot string decimalSeparator = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator; Assert.AreEqual("1234567890" + decimalSeparator + "12345", formatter.FormatCellValue(cell)); } // Test Zip+4 format row = wb.GetSheetAt(0).GetRow(4); ICell cell1 = row.GetCell(0); log("\n==== ZIP FORMAT ===="); log(formatter.FormatCellValue(cell1)); Assert.AreEqual("12345-6789", formatter.FormatCellValue(cell1)); // Test phone number format row = wb.GetSheetAt(0).GetRow(5); cell1 = row.GetCell(0); log("\n==== PHONE FORMAT ===="); log(formatter.FormatCellValue(cell1)); Assert.AreEqual("(555) 123-4567", formatter.FormatCellValue(cell1)); // Test SSN format row = wb.GetSheetAt(0).GetRow(6); cell1 = row.GetCell(0); log("\n==== SSN FORMAT ===="); log(formatter.FormatCellValue(cell1)); Assert.AreEqual("444-55-1234", formatter.FormatCellValue(cell1)); // null Test-- null cell should result in empty String Assert.AreEqual(formatter.FormatCellValue(null), ""); // null Test-- null cell should result in empty String Assert.AreEqual(formatter.FormatCellValue(null), ""); }
public ToxySpreadsheet Parse() { if (!File.Exists(Context.Path)) { throw new FileNotFoundException("File " + Context.Path + " is not found"); } bool hasHeader = false; if (Context.Properties.ContainsKey("GenerateColumnHeader")) { hasHeader = Utility.IsTrue(Context.Properties["GenerateColumnHeader"]); } bool extractHeader = false; if (Context.Properties.ContainsKey("ExtractSheetHeader")) { extractHeader = Utility.IsTrue(Context.Properties["ExtractSheetHeader"]); } bool extractFooter = false; if (Context.Properties.ContainsKey("ExtractSheetFooter")) { extractFooter = Utility.IsTrue(Context.Properties["ExtractSheetFooter"]); } bool showCalculatedResult = false; if (Context.Properties.ContainsKey("ShowCalculatedResult")) { showCalculatedResult = Utility.IsTrue(Context.Properties["ShowCalculatedResult"]); } bool fillBlankCells = false; if (Context.Properties.ContainsKey("FillBlankCells")) { fillBlankCells = Utility.IsTrue(Context.Properties["FillBlankCells"]); } bool includeComment = true; if (Context.Properties.ContainsKey("IncludeComments")) { includeComment = Utility.IsTrue(Context.Properties["IncludeComments"]); } ToxySpreadsheet ss = new ToxySpreadsheet(); ss.Name = Context.Path; IWorkbook workbook = WorkbookFactory.Create(Context.Path); HSSFDataFormatter formatter = new HSSFDataFormatter(); for (int i = 0; i < workbook.NumberOfSheets; i++) { ToxyTable table = new ToxyTable(); ISheet sheet = workbook.GetSheetAt(i); table.Name = sheet.SheetName; if (extractHeader && sheet.Header != null) { table.PageHeader = sheet.Header.Left + "|" + sheet.Header.Center + "|" + sheet.Header.Right; } if (extractFooter && sheet.Footer != null) { table.PageFooter = sheet.Footer.Left + "|" + sheet.Footer.Center + "|" + sheet.Footer.Right; } bool firstRow = true; table.LastRowIndex = sheet.LastRowNum; foreach (IRow row in sheet) { ToxyRow tr = null; if (!hasHeader || !firstRow) { tr = new ToxyRow(row.RowNum); } foreach (ICell cell in row) { if (hasHeader && firstRow) { table.ColumnHeaders.Cells.Add(new ToxyCell(cell.ColumnIndex, cell.ToString())); } else { if (tr.LastCellIndex < cell.ColumnIndex) { tr.LastCellIndex = cell.ColumnIndex; } ToxyCell c = new ToxyCell(cell.ColumnIndex, formatter.FormatCellValue(cell)); if (!string.IsNullOrEmpty(cell.ToString())) { tr.Cells.Add(c); } else if (fillBlankCells) { tr.Cells.Add(c); } if (includeComment && cell.CellComment != null) { c.Comment = cell.CellComment.String.String; } } } if (tr != null) { tr.RowIndex = row.RowNum; table.Rows.Add(tr); } if (firstRow) { firstRow = false; } if (table.LastColumnIndex < tr.LastCellIndex) { table.LastColumnIndex = tr.LastCellIndex; } } for (int j = 0; j < sheet.NumMergedRegions; j++) { var range = sheet.GetMergedRegion(j); table.MergeCells.Add(new MergeCellRange() { FirstRow = range.FirstRow, FirstColumn = range.FirstColumn, LastRow = range.LastRow, LastColumn = range.LastColumn }); } ss.Tables.Add(table); } return(ss); }