public ToxySpreadsheet Parse()
{
if (!File.Exists(Context.Path))
{
throw new FileNotFoundException("File " + Context.Path + " is not found");
}
bool hasHeader = false;
if (Context.Properties.ContainsKey("HasHeader"))
{
hasHeader = Utility.IsTrue(Context.Properties["HasHeader"]);
}
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 = Parse(workbook, i, extractHeader, extractFooter, hasHeader, fillBlankCells, includeComment, formatter);
ss.Tables.Add(table);
}
return(ss);
}
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 ToxyTable Parse(int sheetIndex)
{
if (!File.Exists(Context.Path))
{
throw new FileNotFoundException("File " + Context.Path + " is not found");
}
bool hasHeader = false;
if (Context.Properties.ContainsKey("HasHeader"))
{
hasHeader = Utility.IsTrue(Context.Properties["HasHeader"]);
}
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"]);
}
IWorkbook workbook = WorkbookFactory.Create(Context.Path);
HSSFDataFormatter formatter = new HSSFDataFormatter();
return(Parse(workbook, sheetIndex, extractHeader, extractFooter, hasHeader, fillBlankCells, includeComment, formatter));
}
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));
}
public void SetUp()
{
// One or more test methods depends on the american culture.
TestCases.CultureShim.SetCurrentCulture("en-US");
// create the formatter to Test
formatter = new HSSFDataFormatter();
// create a workbook to Test with
wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDataFormat format = wb.CreateDataFormat();
// create a row and Put some cells in it
IRow row = sheet.CreateRow(0);
// date value for July 8 1901 1:19 PM
double dateNum = 555.555;
// date value for July 8 1901 11:23 AM
double timeNum = 555.47431;
//valid date formats -- all should have "Jul" in output
string[] goodDatePatterns =
{
"[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy",
"mmm/d/yy\\ h:mm PM;@",
"mmmm/d/yy\\ h:mm;@",
"mmmm/d;@",
"mmmm/d/yy;@",
"mmm/dd/yy;@",
"[$-409]d\\-mmm;@",
"[$-409]d\\-mmm\\-yy;@",
"[$-409]dd\\-mmm\\-yy;@",
"[$-409]mmm\\-yy;@",
"[$-409]mmmm\\-yy;@",
"[$-409]mmmm\\ d\\,\\ yyyy;@",
"[$-409]mmm/d/yy\\ h:mm:ss;@",
"[$-409]mmmm/d/yy\\ h:mm:ss am;@",
"[$-409]mmmmm;@",
"[$-409]mmmmm\\-yy;@",
"mmmm/d/yyyy;@",
"[$-409]d\\-mmm\\-yyyy;@"
};
//valid time formats - all should have 11:23 in output
string[] goodTimePatterns =
{
"HH:MM",
"HH:MM:SS",
"HH:MM;HH:MM;HH:MM",
// This is fun - blue if positive time,
// red if negative time or green for zero!
"[BLUE]HH:MM;[RED]HH:MM;[GREEN]HH:MM",
"yyyy-mm-dd hh:mm",
"yyyy-mm-dd hh:mm:ss",
};
// valid number formats
string[] goodNumPatterns =
{
"#,##0.0000",
"#,##0;[Red]#,##0",
"(#,##0.00_);(#,##0.00)",
"($#,##0.00_);[Red]($#,##0.00)",
"$#,##0.00",
"[$-809]#,##0.00", // international format
"[$-2]#,##0.00", // international format
"0000.00000%",
"0.000E+00",
"0.00E+00",
"[BLACK]0.00;[COLOR 5]##.##"
};
// invalid date formats -- will throw exception in DecimalFormat ctor
string[] badNumPatterns =
{
"#,#$'#0.0000",
"'#','#ABC#0;##,##0",
"000 '123 4'5'6 000",
"#''0#0'1#10L16EE"
};
// create cells with good date patterns
for (int i = 0; i < goodDatePatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dateNum);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodDatePatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
row = sheet.CreateRow(1);
// create cells with time patterns
for (int i = 0; i < goodTimePatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(timeNum);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodTimePatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
row = sheet.CreateRow(2);
// create cells with num patterns
for (int i = 0; i < goodNumPatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(-1234567890.12345);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodNumPatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
row = sheet.CreateRow(3);
// create cells with bad num patterns
for (int i = 0; i < badNumPatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(1234567890.12345);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(badNumPatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
// Built in formats
{ // Zip + 4 format
row = sheet.CreateRow(4);
ICell cell = row.CreateCell(0);
cell.SetCellValue(123456789);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("00000-0000"));
cell.CellStyle = (/*setter*/ cellStyle);
}
{ // Phone number format
row = sheet.CreateRow(5);
ICell cell = row.CreateCell(0);
cell.SetCellValue(5551234567D);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("[<=9999999]###-####;(###) ###-####"));
cell.CellStyle = (/*setter*/ cellStyle);
}
{ // SSN format
row = sheet.CreateRow(6);
ICell cell = row.CreateCell(0);
cell.SetCellValue(444551234);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("000-00-0000"));
cell.CellStyle = (/*setter*/ cellStyle);
}
{ // formula cell
row = sheet.CreateRow(7);
ICell cell = row.CreateCell(0);
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(12.25,12.25)/100");
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("##.00%;"));
cell.CellStyle = (/*setter*/ cellStyle);
}
{ // special cell
row = sheet.CreateRow(8);
ICell cell = row.CreateCell(0);
cell.SetCellValue(1234567890.12345);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = format.GetFormat("#,##0.00 §â§å§Ò.;-#,##0.00 [$§â§å§Ò.-419]");
cell.CellStyle = (/*setter*/ cellStyle);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelExtractor"/> class.
/// </summary>
/// <param name="wb">The wb.</param>
public ExcelExtractor(HSSFWorkbook wb)
: base(wb)
{
this.wb = wb;
_formatter = new HSSFDataFormatter();
}
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);
}
DataTable GetDTFromExcel2003(string extractFile, int datecol1, int datecol2, bool ExtraCol, string SheetName, int Rowindex, IWorkbook workbook, List <int> lstint)
{
DataTable dt = new DataTable();
IFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(workbook);
DataFormatter dataFormatter = new HSSFDataFormatter(new CultureInfo("en-US"));
ISheet sheet = workbook.GetSheet(SheetName);
if (sheet != null)
{
while (sheet.GetRow(Rowindex) != null)
{
dt.Rows.Add();
string RowsValue = "";
for (int j = 0; j < sheet.GetRow(Rowindex).Cells.Count; j++)
{
var cell = sheet.GetRow(Rowindex).GetCell(j);
if (cell != null)
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric || cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
if (Rowindex == 3)
{
dt.Columns.Add(sheet.GetRow(Rowindex).GetCell(j).NumericCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", ""), typeof(string));
RowsValue += sheet.GetRow(Rowindex).GetCell(j).NumericCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", "");
}
else
{
if (datecol1 == j || datecol2 == j)
{
dt.Rows[Rowindex - 4][j] = DateTime.FromOADate(sheet.GetRow(Rowindex).GetCell(j).NumericCellValue).ToString("yyyy/MM/dd");
RowsValue += sheet.GetRow(Rowindex).GetCell(j).NumericCellValue;
}
else
{
dt.Rows[Rowindex - 4][j] = sheet.GetRow(Rowindex).GetCell(j).NumericCellValue.ToString().Replace(",", "").Trim();
RowsValue += sheet.GetRow(Rowindex).GetCell(j).NumericCellValue;
if (ExtraCol)
{
dt.Rows[Rowindex - 4]["ItemIGSTValue"] = 0;
dt.Rows[Rowindex - 4]["ItemcGSTValue"] = 0;
dt.Rows[Rowindex - 4]["ItemSGSTValue"] = 0;
}
}
}
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
if (Rowindex == 3)
{
dt.Columns.Add(sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", ""), typeof(string));
RowsValue += sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", "");
}
else
{
DateTime date;
if (datecol1 == j || datecol2 == j)
{
if (sheet.GetRow(Rowindex).GetCell(j).StringCellValue == "")
{
dt.Rows[Rowindex - 4][j] = "";
}
else
{
if (sheet.GetRow(Rowindex).GetCell(j).StringCellValue.Length == 10)
{
if (!DateTime.TryParse(CLSCommon.DateFormat(sheet.GetRow(Rowindex).GetCell(j).StringCellValue), new System.Globalization.CultureInfo("en-GB"), System.Globalization.DateTimeStyles.None, out date))
{
dtError.Rows.Add(1);
dtError.Rows[dtError.Rows.Count - 1]["Section"] = sheet.SheetName;
dtError.Rows[dtError.Rows.Count - 1]["LineNo"] = (Rowindex + 1).ToString();
dtError.Rows[dtError.Rows.Count - 1]["Error"] = "Date Format Must Be \"DD/MM/YYYY\"";
dtError.Rows[dtError.Rows.Count - 1]["ColumnName"] = lstcol[j];
}
else
{
dt.Rows[Rowindex - 4][j] = DateTime.Parse(CLSCommon.DateFormat(sheet.GetRow(Rowindex).GetCell(j).StringCellValue)).ToString("yyyy/MM/dd");
RowsValue += sheet.GetRow(Rowindex).GetCell(j).StringCellValue;
}
}
else if ((sheet.GetRow(Rowindex).GetCell(j).StringCellValue.Length == 11 || sheet.GetRow(Rowindex).GetCell(j).StringCellValue.Length == 9) &&
((DateTime.TryParseExact((sheet.GetRow(Rowindex).GetCell(j).StringCellValue), "dd-MMM-yyyy", null, DateTimeStyles.None, out date)) ||
(DateTime.TryParseExact((sheet.GetRow(Rowindex).GetCell(j).StringCellValue), "dd-MMM-yy", null, DateTimeStyles.None, out date))
))
{
dt.Rows[Rowindex - 4][j] = DateTime.Parse(sheet.GetRow(Rowindex).GetCell(j).StringCellValue).ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
RowsValue += sheet.GetRow(Rowindex).GetCell(j).StringCellValue;
}
else
{
dtError.Rows.Add(1);
dtError.Rows[dtError.Rows.Count - 1]["Section"] = sheet.SheetName;
dtError.Rows[dtError.Rows.Count - 1]["LineNo"] = (Rowindex + 1).ToString();
dtError.Rows[dtError.Rows.Count - 1]["Error"] = "Date Format Must Be \"DD/MM/YYYY\"";
dtError.Rows[dtError.Rows.Count - 1]["ColumnName"] = lstcol[j];
}
}
}
else
{
var res = from k in lstint
where k == j
select k;
if (res.Count() > 0)
{
double parsedValue;
if (!double.TryParse(sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim(), out parsedValue) && sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim() != "")
{
dtError.Rows.Add(1);
dtError.Rows[dtError.Rows.Count - 1]["Section"] = sheet.SheetName;
dtError.Rows[dtError.Rows.Count - 1]["LineNo"] = (Rowindex + 1).ToString();
dtError.Rows[dtError.Rows.Count - 1]["Error"] = "Invalid Numeric value";//"Invalid Numberic value";
dtError.Rows[dtError.Rows.Count - 1]["ColumnName"] = lstcol[j];
}
else
{
dt.Rows[Rowindex - 4][j] = sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim();
RowsValue += sheet.GetRow(Rowindex).GetCell(j).StringCellValue;
}
}
else
{
dt.Rows[Rowindex - 4][j] = sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim();
RowsValue += sheet.GetRow(Rowindex).GetCell(j).StringCellValue;
}
}
if (ExtraCol)
{
dt.Rows[Rowindex - 4]["ItemIGSTValue"] = 0;
dt.Rows[Rowindex - 4]["ItemcGSTValue"] = 0;
dt.Rows[Rowindex - 4]["ItemSGSTValue"] = 0;
}
}
}
}
}
if (Rowindex == 3 && ExtraCol)
{
dt.Columns.Add("ItemIGSTValue");
dt.Columns.Add("ItemCGSTValue");
dt.Columns.Add("ItemSGSTValue");
}
Rowindex++;
}
}
return(RemoveEmptyRows(dt));
}
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);
}
