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 (tr != null)
{
tr.RowIndex = row.RowNum;
table.Rows.Add(tr);
if (table.LastColumnIndex < tr.LastCellIndex)
{
table.LastColumnIndex = tr.LastCellIndex;
}
}
if (firstRow)
{
firstRow = false;
}
}
if (includeComment)
{
foreach (var keyValuePair in sheet.GetCellComments())
{
var tr = new ToxyRow(keyValuePair.Key.Row);
var i = table.Rows.BinarySearch(tr, RowListComparer.Instance);
if (i >= 0)
{
tr = table.Rows[i];
}
else
{
table.Rows.Insert(~i, tr);
}
var c = new ToxyCell(keyValuePair.Key.Column, null);
var j = tr.Cells.BinarySearch(c, CellListComparer.Instance);
if (j >= 0)
{
c = tr.Cells[j];
}
else
{
tr.Cells.Insert(~j, c);
}
c.Comment = keyValuePair.Value.String.String;
}
}
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);
}
