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);
}
