private void ShowToGrid(ToxyTable table)
{
ssPanel.ReoGridControl.Reset();
ssPanel.ReoGridControl.CurrentWorksheet.ColumnCount = table.LastColumnIndex + 1;
ssPanel.ReoGridControl.CurrentWorksheet.RowCount = table.LastRowIndex + 2;
if (table.HasHeader)
{
foreach (var cell in table.HeaderRows[0].Cells)
{
ssPanel.ReoGridControl.CurrentWorksheet.SetCellData(0, cell.CellIndex, cell.Value);
}
}
foreach (var row in table.Rows)
{
foreach (var cell in row.Cells)
{
ssPanel.ReoGridControl.CurrentWorksheet.SetCellData(row.RowIndex + 1, cell.CellIndex, cell.Value);
}
}
foreach (var cellrange in table.MergeCells)
{
ssPanel.ReoGridControl.CurrentWorksheet.MergeRange(new RangePosition(
new CellPosition(cellrange.FirstRow, cellrange.FirstColumn),
new CellPosition(cellrange.LastRow, cellrange.LastColumn)));
}
}
private void ShowToGrid(ToxyTable table)
{
ssPanel.ReoGridControl.Reset();
ssPanel.ReoGridControl.ColCount = table.LastColumnIndex + 1;
ssPanel.ReoGridControl.RowCount = table.LastRowIndex + 2;
if (table.HasHeader)
{
foreach (var cell in table.ColumnHeaders.Cells)
{
ssPanel.ReoGridControl.SetCellData(new ReoGridPos(0, cell.CellIndex), cell.Value);
}
}
foreach (var row in table.Rows)
{
foreach (var cell in row.Cells)
{
ssPanel.ReoGridControl.SetCellData(new ReoGridPos(row.RowIndex + 1, cell.CellIndex), cell.Value);
}
}
foreach (var cellrange in table.MergeCells)
{
ssPanel.ReoGridControl.MergeRange(new ReoGridRange(
new ReoGridPos(cellrange.FirstRow, cellrange.FirstColumn),
new ReoGridPos(cellrange.LastRow, cellrange.LastColumn)));
}
}
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 TestToxyTableToDataTable_withEmptyColumnHeader()
{
#region create ToxyTable
ToxyTable ttable = new ToxyTable();
ttable.Name = "Test1";
ttable.HeaderRows.Add(new ToxyRow(0));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(0, "C1"));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(1, null));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(2, "C2"));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(3, null));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(4, "C4"));
ToxyRow trow1 = new ToxyRow(1);
trow1.Cells.Add(new ToxyCell(0, "1"));
trow1.Cells.Add(new ToxyCell(1, "2"));
trow1.Cells.Add(new ToxyCell(4, "3"));
trow1.Cells.Add(new ToxyCell(5, "4"));
trow1.LastCellIndex = 5;
ttable.Rows.Add(trow1);
ToxyRow trow2 = new ToxyRow(2);
trow2.Cells.Add(new ToxyCell(0, "5"));
trow2.Cells.Add(new ToxyCell(1, "6"));
trow2.Cells.Add(new ToxyCell(3, "7"));
trow2.LastCellIndex = 3;
ttable.Rows.Add(trow2);
ttable.LastColumnIndex = 3;
#endregion
DataTable dt = ttable.ToDataTable();
Assert.AreEqual("Test1", dt.TableName);
Assert.AreEqual(2, dt.Rows.Count);
Assert.AreEqual(6, dt.Columns.Count);
Assert.AreEqual("C1", dt.Columns[0].Caption);
Assert.AreEqual("Column1", dt.Columns[1].Caption);
Assert.AreEqual("C2", dt.Columns[2].Caption);
Assert.AreEqual("Column2", dt.Columns[3].Caption);
Assert.AreEqual("C4", dt.Columns[4].Caption);
Assert.AreEqual("1", dt.Rows[0][0].ToString());
Assert.AreEqual("2", dt.Rows[0][1].ToString());
Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[0][2].ToString()));
Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[0][3].ToString()));
Assert.AreEqual("3", dt.Rows[0][4].ToString());
Assert.AreEqual("4", dt.Rows[0][5].ToString());
Assert.AreEqual("5", dt.Rows[1][0].ToString());
Assert.AreEqual("6", dt.Rows[1][1].ToString());
Assert.AreEqual("7", dt.Rows[1][3].ToString());
Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][2].ToString()));
Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][4].ToString()));
Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][5].ToString()));
}
public void TestToxyTableToDataTable()
{
#region create ToxyTable
ToxyTable ttable = new ToxyTable();
ttable.Name = "Test1";
ttable.HeaderRows.Add(new ToxyRow(0));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(0, "C1"));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(1, "C2"));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(2, "C3"));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(3, "C4"));
ToxyRow trow1 = new ToxyRow(1);
trow1.Cells.Add(new ToxyCell(0, "1"));
trow1.Cells.Add(new ToxyCell(1, "2"));
trow1.Cells.Add(new ToxyCell(2, "3"));
ttable.Rows.Add(trow1);
ToxyRow trow2 = new ToxyRow(2);
trow2.Cells.Add(new ToxyCell(0, "4"));
trow2.Cells.Add(new ToxyCell(1, "5"));
trow2.Cells.Add(new ToxyCell(3, "6"));
trow2.LastCellIndex = 3;
ttable.Rows.Add(trow2);
ToxyRow trow3 = new ToxyRow(3);
trow3.LastCellIndex = 3;
trow3.Cells.Add(new ToxyCell(1, "7"));
trow3.Cells.Add(new ToxyCell(2, "8"));
trow3.Cells.Add(new ToxyCell(3, "9"));
ttable.Rows.Add(trow3);
ttable.LastColumnIndex = 3;
#endregion
DataTable dt = ttable.ToDataTable();
Assert.AreEqual("Test1", dt.TableName);
Assert.AreEqual(3, dt.Rows.Count);
Assert.AreEqual(4, dt.Columns.Count);
Assert.AreEqual("C1", dt.Columns[0].Caption);
Assert.AreEqual("C2", dt.Columns[1].Caption);
Assert.AreEqual("C3", dt.Columns[2].Caption);
Assert.AreEqual("C4", dt.Columns[3].Caption);
Assert.AreEqual("1", dt.Rows[0][0].ToString());
Assert.AreEqual("2", dt.Rows[0][1].ToString());
Assert.AreEqual("3", dt.Rows[0][2].ToString());
Assert.AreEqual("4", dt.Rows[1][0].ToString());
Assert.AreEqual("5", dt.Rows[1][1].ToString());
Assert.AreEqual("6", dt.Rows[1][3].ToString());
Assert.AreEqual("7", dt.Rows[2][1].ToString());
Assert.AreEqual("8", dt.Rows[2][2].ToString());
Assert.AreEqual("9", dt.Rows[2][3].ToString());
}
public void TestToxyTableToDataTable()
{
#region create ToxyTable
ToxyTable ttable = new ToxyTable();
ttable.Name = "Test1";
ttable.HeaderRows.Add(new ToxyRow(0));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(0, "C1"));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(1, "C2"));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(2, "C3"));
ttable.HeaderRows[0].Cells.Add(new ToxyCell(3, "C4"));
ToxyRow trow1=new ToxyRow(1);
trow1.Cells.Add(new ToxyCell(0,"1"));
trow1.Cells.Add(new ToxyCell(1,"2"));
trow1.Cells.Add(new ToxyCell(2,"3"));
ttable.Rows.Add(trow1);
ToxyRow trow2 = new ToxyRow(2);
trow2.Cells.Add(new ToxyCell(0, "4"));
trow2.Cells.Add(new ToxyCell(1, "5"));
trow2.Cells.Add(new ToxyCell(3, "6"));
trow2.LastCellIndex = 3;
ttable.Rows.Add(trow2);
ToxyRow trow3 = new ToxyRow(3);
trow3.LastCellIndex = 3;
trow3.Cells.Add(new ToxyCell(1, "7"));
trow3.Cells.Add(new ToxyCell(2, "8"));
trow3.Cells.Add(new ToxyCell(3, "9"));
ttable.Rows.Add(trow3);
ttable.LastColumnIndex = 3;
#endregion
DataTable dt = ttable.ToDataTable();
Assert.AreEqual("Test1",dt.TableName);
Assert.AreEqual(3, dt.Rows.Count);
Assert.AreEqual(4, dt.Columns.Count);
Assert.AreEqual("C1", dt.Columns[0].Caption);
Assert.AreEqual("C2", dt.Columns[1].Caption);
Assert.AreEqual("C3", dt.Columns[2].Caption);
Assert.AreEqual("C4", dt.Columns[3].Caption);
Assert.AreEqual("1", dt.Rows[0][0].ToString());
Assert.AreEqual("2", dt.Rows[0][1].ToString());
Assert.AreEqual("3", dt.Rows[0][2].ToString());
Assert.AreEqual("4", dt.Rows[1][0].ToString());
Assert.AreEqual("5", dt.Rows[1][1].ToString());
Assert.AreEqual("6", dt.Rows[1][3].ToString());
Assert.AreEqual("7", dt.Rows[2][1].ToString());
Assert.AreEqual("8", dt.Rows[2][2].ToString());
Assert.AreEqual("9", dt.Rows[2][3].ToString());
}
public void TestParseSheetIndexOutOfRange()
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath("Formatting.xlsx"));
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
try
{
ToxyTable ss = parser.Parse(50);
}
catch (ArgumentOutOfRangeException ex)
{
Assert.IsTrue(ex.Message.Contains("This file only contains 3 sheet(s)."));
}
}
public void TestParseIndexOutOfRange()
{
string path = TestDataSample.GetFilePath("countrylist.csv", null);
ParserContext context = new ParserContext(path);
context.Properties.Add("HasHeader", "1");
ISpreadsheetParser parser = (ISpreadsheetParser)ParserFactory.CreateSpreadsheet(context);
try
{
ToxyTable ss = parser.Parse(1);
}
catch (ArgumentOutOfRangeException ex)
{
Assert.IsTrue(ex.Message.Contains("CSV only has one table"));
}
}
public ExcelTableTemplate Parse(string filepath)
{
var templateMetadata = new TemplateMetaData();
if (filepath.StartsWith("/"))
{
filepath = HostingEnvironment.MapPath(filepath);
}
FilePath = filepath;
ParserContext context = new ParserContext(filepath);
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
foreach (var table in ss.Tables)
{
_currTable = table;
for (_currRowIndex = 0; _currRowIndex < table.Rows.Count; _currRowIndex++)
{
var row = table.Rows[_currRowIndex];
for (_currColIndex = 0; _currColIndex < row.Cells.Count; _currColIndex++)
{
var cell = row.Cells[_currColIndex];
var tabletoken = ParseTable(cell);
if (tabletoken != null)
{
templateMetadata.Tables.Add(tabletoken);
}
else
{
var cellToken = ParseCell(cell);
if (cellToken != null)
{
templateMetadata.Cells.Add(cellToken);
}
}
}
}
}
return(null);
}
public ToxySpreadsheet LoadData(DataContext context)
{
Database db = DatabaseFactory.CreateDatabase(context.ConnectionStringName);
DbCommand cmd = db.GetSqlStringCommand(context.QueryString);
ToxySpreadsheet ss = new ToxySpreadsheet();
ss.Name = context.ConnectionStringName;
int i = 0;
using (IDataReader reader = db.ExecuteReader(cmd))
{
ToxyTable table = new ToxyTable();
table.Name = "Sheet1";
ToxyRow row = new ToxyRow(i);
if (i == 0)
{
for (int j = 0; j < reader.FieldCount; j++)
{
row.Cells.Add(new ToxyCell(j, reader.GetName(i)));
}
}
else
{
for (int j = 0; j < reader.FieldCount; j++)
{
string value = reader.GetString(j);
if (!string.IsNullOrEmpty(value))
{
row.Cells.Add(new ToxyCell(j, value));
}
}
}
table.Rows.Add(row);
i++;
ss.Tables.Add(table);
}
return(ss);
}
public void BaseTestExcelContent(string filename)
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath(filename));
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
Assert.AreEqual(3, ss.Tables.Count);
Assert.AreEqual("Sheet1", ss.Tables[0].Name);
Assert.AreEqual("Sheet2", ss.Tables[1].Name);
Assert.AreEqual("Sheet3", ss.Tables[2].Name);
Assert.AreEqual(5, ss.Tables[0].Rows.Count);
Assert.AreEqual(0, ss.Tables[1].Rows.Count);
Assert.AreEqual(0, ss.Tables[2].Rows.Count);
ToxyTable table = ss.Tables[0];
Assert.AreEqual(1, table.Rows[0].RowIndex);
Assert.AreEqual(2, table.Rows[1].RowIndex);
Assert.AreEqual(3, table.Rows[2].RowIndex);
Assert.AreEqual(4, table.Rows[3].RowIndex);
Assert.AreEqual(5, table.Rows[4].RowIndex);
Assert.AreEqual(1, table.Rows[0].Cells.Count);
Assert.AreEqual(0, table.Rows[1].Cells.Count);
Assert.AreEqual(2, table.Rows[2].Cells.Count);
Assert.AreEqual(2, table.Rows[3].Cells.Count);
Assert.AreEqual(2, table.Rows[4].Cells.Count);
Assert.AreEqual("Employee Info", table.Rows[0].Cells[0].ToString());
Assert.AreEqual(1, table.Rows[0].Cells[0].CellIndex);
Assert.AreEqual("Last name:", table.Rows[2].Cells[0].ToString());
Assert.AreEqual(1, table.Rows[2].Cells[0].CellIndex);
Assert.AreEqual("lastName", table.Rows[2].Cells[1].ToString());
Assert.AreEqual(2, table.Rows[2].Cells[1].CellIndex);
Assert.AreEqual("First name:", table.Rows[3].Cells[0].ToString());
Assert.AreEqual("firstName", table.Rows[3].Cells[1].ToString());
Assert.AreEqual("SSN:", table.Rows[4].Cells[0].ToString());
Assert.AreEqual("ssn", table.Rows[4].Cells[1].ToString());
}
public ToxySpreadsheet Parse()
{
if (!File.Exists(Context.Path))
throw new FileNotFoundException("File " + Context.Path + " is not found");
bool extractHeader=false;
if (Context.Properties.ContainsKey("ExtractHeader"))
{
string sHasHeader = Context.Properties["ExtractHeader"].ToLower();
if (sHasHeader == "1" || sHasHeader == "on" || sHasHeader == "true")
extractHeader = true;
}
char delimiter =',';
if (Context.Properties.ContainsKey("delimiter"))
{
delimiter = Context.Properties["delimiter"][0];
}
Encoding encoding = Encoding.UTF8;
StreamReader sr = null;
try
{
if (Context.Encoding == null)
{
sr = new StreamReader(Context.Path, true);
}
else
{
sr = new StreamReader(Context.Path, true);
}
CsvReader reader=new CsvReader(sr, extractHeader,delimiter);
string[] headers = reader.GetFieldHeaders();
ToxySpreadsheet ss = new ToxySpreadsheet();
ToxyTable t1 = new ToxyTable();
ss.Tables.Add(t1);
int i = 0;
if (headers.Length > 0)
{
t1.HeaderRows.Add(new ToxyRow(i));
i++;
}
for (int j = 0; j < headers.Length;j++ )
{
t1.HeaderRows[0].Cells.Add(new ToxyCell(j, headers[j]));
t1.LastColumnIndex = t1.HeaderRows[0].Cells.Count-1;
}
while(reader.ReadNextRecord())
{
ToxyRow tr=new ToxyRow(i);
tr.LastCellIndex = reader.FieldCount-1;
if (tr.LastCellIndex > t1.LastColumnIndex)
{
t1.LastColumnIndex = tr.LastCellIndex;
}
tr.RowIndex = i;
for (int j = 0; j <= tr.LastCellIndex; j++)
{
if (this.ParseSegment != null)
{
this.ParseSegment(this, new ParseSegmentEventArgs(reader[j], i, j));
}
ToxyCell c = new ToxyCell(j, reader[j]);
if (tr.LastCellIndex < c.CellIndex)
{
tr.LastCellIndex = c.CellIndex;
}
tr.Cells.Add(c);
}
t1.Rows.Add(tr);
i++;
}
t1.LastRowIndex = i - 1;
return ss;
}
finally
{
if (sr != null)
sr.Close();
}
}
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"))
{
string sHasHeader = Context.Properties["HasHeader"].ToLower();
if (sHasHeader == "1" || sHasHeader == "on" || sHasHeader == "true")
{
hasHeader = true;
}
}
char delimiter = ',';
if (Context.Properties.ContainsKey("delimiter"))
{
delimiter = Context.Properties["delimiter"][0];
}
Encoding encoding = Encoding.UTF8;
StreamReader sr = null;
try
{
if (Context.Encoding == null)
{
sr = new StreamReader(Context.Path, true);
}
else
{
sr = new StreamReader(Context.Path, true);
}
CsvReader reader = new CsvReader(sr, hasHeader, delimiter);
string[] headers = reader.GetFieldHeaders();
ToxySpreadsheet ss = new ToxySpreadsheet();
ToxyTable t1 = new ToxyTable();
ss.Tables.Add(t1);
for (int j = 0; j < headers.Length; j++)
{
t1.ColumnHeaders.Cells.Add(new ToxyCell(j, headers[j]));
t1.LastColumnIndex = t1.ColumnHeaders.Cells.Count - 1;
}
int i = 0;
while (reader.ReadNextRecord())
{
ToxyRow tr = new ToxyRow(i);
tr.LastCellIndex = reader.FieldCount - 1;
if (tr.LastCellIndex > t1.LastColumnIndex)
{
t1.LastColumnIndex = tr.LastCellIndex;
}
tr.RowIndex = i;
for (int j = 0; j <= tr.LastCellIndex; j++)
{
if (this.ParseSegment != null)
{
this.ParseSegment(this, new ParseSegmentEventArgs(reader[j], i, j));
}
ToxyCell c = new ToxyCell(j, reader[j]);
if (tr.LastCellIndex < c.CellIndex)
{
tr.LastCellIndex = c.CellIndex;
}
tr.Cells.Add(c);
}
t1.Rows.Add(tr);
i++;
}
t1.LastRowIndex = i - 1;
return(ss);
}
finally
{
if (sr != null)
{
sr.Close();
}
}
}
public new ToxySpreadsheet Parse()
{
if (!File.Exists(Context.Path))
throw new FileNotFoundException("File " + Context.Path + " is not found");
bool hasHeader=false;
if (Context.Properties.ContainsKey("HasHeader"))
{
string sHasHeader = Context.Properties["HasHeader"].ToLower();
if (sHasHeader == "1" || sHasHeader == "on" || sHasHeader == "true")
hasHeader = true;
}
char delimiter =',';
if (Context.Properties.ContainsKey("delimiter"))
{
delimiter = Context.Properties["delimiter"][0];
}
Encoding encoding = Encoding.UTF8;
StreamReader sr = null;
try
{
if (Context.Encoding == null)
{
sr = new StreamReader(Context.Path, true);
}
else
{
sr = new StreamReader(Context.Path, true);
}
CsvReader reader=new CsvReader(sr, hasHeader,delimiter);
string[] headers = reader.GetFieldHeaders();
ToxySpreadsheet ss = new ToxySpreadsheet();
ToxyTable t1 = new ToxyTable();
ss.Tables.Add(t1);
for (int j = 0; j < headers.Length;j++ )
{
t1.ColumnHeaders.Cells.Add(new ToxyCell(j, headers[j]));
}
int i=0;
while(reader.ReadNextRecord())
{
ToxyRow tr=new ToxyRow(i);
tr.LastCellIndex = t1.ColumnHeaders.Cells.Count;
for (int j = 0; j < tr.LastCellIndex; j++)
{
ToxyCell c = new ToxyCell(j, reader[j]);
if (tr.LastCellIndex < c.CellIndex)
{
tr.LastCellIndex = c.CellIndex;
}
tr.Cells.Add(c);
}
t1.Rows.Add(tr);
}
return ss;
}
finally
{
if (sr != null)
sr.Close();
}
}
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 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 (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);
}
if (workbook is XSSFWorkbook)
{
var props= ((XSSFWorkbook)workbook).GetProperties();
if (props.CoreProperties != null)
{
if (props.CoreProperties.Title != null)
{
ss.Properties.Add("Title", props.CoreProperties.Title );
}
else if (props.CoreProperties.Identifier != null)
{
ss.Properties.Add("Identifier", props.CoreProperties.Identifier );
}
else if (props.CoreProperties.Keywords != null)
{
ss.Properties.Add("Keywords", props.CoreProperties.Keywords);
}
else if (props.CoreProperties.Revision != null)
{
ss.Properties.Add("Revision", props.CoreProperties.Revision);
}
else if (props.CoreProperties.Subject != null)
{
ss.Properties.Add("Subject", props.CoreProperties.Subject);
}
else if (props.CoreProperties.Modified != null)
{
ss.Properties.Add("Modified", props.CoreProperties.Modified);
}
else if (props.CoreProperties.LastPrinted != null)
{
ss.Properties.Add("LastPrinted", props.CoreProperties.LastPrinted);
}
else if (props.CoreProperties.Created != null)
{
ss.Properties.Add("Created", props.CoreProperties.Created);
}
else if (props.CoreProperties.Creator != null)
{
ss.Properties.Add("Creator", props.CoreProperties.Creator);
}
else if (props.CoreProperties.Description != null)
{
ss.Properties.Add("Description", props.CoreProperties.Description);
}
}
if (props.ExtendedProperties != null && props.ExtendedProperties.props!=null)
{
var extProps = props.ExtendedProperties.props.GetProperties();
if (extProps.Application != null)
{
ss.Properties.Add("Application", extProps.Application);
}
if (extProps.AppVersion != null)
{
ss.Properties.Add("AppVersion", extProps.AppVersion);
}
if (extProps.Characters>0)
{
ss.Properties.Add("Characters", extProps.Characters);
}
if (extProps.CharactersWithSpaces>0)
{
ss.Properties.Add("CharactersWithSpaces", extProps.CharactersWithSpaces);
}
if (extProps.Company != null)
{
ss.Properties.Add("Company", extProps.Company);
}
if (extProps.Lines > 0)
{
ss.Properties.Add("Lines", extProps.Lines);
}
if (extProps.Manager != null)
{
ss.Properties.Add("Manager", extProps.Manager);
}
if (extProps.Notes> 0)
{
ss.Properties.Add("Notes", extProps.Notes);
}
if (extProps.Pages>0)
{
ss.Properties.Add("Pages", extProps.Pages);
}
if (extProps.Paragraphs>0)
{
ss.Properties.Add("Paragraphs", extProps.Paragraphs);
}
if (extProps.Words>0)
{
ss.Properties.Add("Words", extProps.Words);
}
if (extProps.TotalTime>0)
{
ss.Properties.Add("TotalTime", extProps.TotalTime);
}
}
}
else
{
//HSSFWorkbook
var si = ((HSSFWorkbook)workbook).SummaryInformation;
if (si != null)
{
if (si.Title != null)
{
ss.Properties.Add("Title", si.Title);
}
else if (si.LastSaveDateTime != null)
{
ss.Properties.Add("LastSaveDateTime", si.LastSaveDateTime);
}
else if (si.PageCount > 0)
{
ss.Properties.Add("PageCount", si.PageCount);
}
else if (si.OSVersion > 0)
{
ss.Properties.Add("OSVersion", si.OSVersion);
}
else if (si.Security > 0)
{
ss.Properties.Add("Security", si.Security);
}
else if (si.Keywords != null)
{
ss.Properties.Add("Keywords", si.Keywords);
}
else if (si.EditTime > 0)
{
ss.Properties.Add("EditTime", si.EditTime);
}
else if (si.Subject != null)
{
ss.Properties.Add("Subject", si.Subject);
}
else if (si.CreateDateTime != null)
{
ss.Properties.Add("CreateDateTime", si.CreateDateTime);
}
else if (si.LastPrinted != null)
{
ss.Properties.Add("LastPrinted", si.LastPrinted);
}
else if (si.CharCount != null)
{
ss.Properties.Add("CharCount", si.CharCount);
}
else if (si.Author != null)
{
ss.Properties.Add("Author", si.Author);
}
else if (si.LastAuthor != null)
{
ss.Properties.Add("LastAuthor", si.LastAuthor);
}
else if (si.ApplicationName != null)
{
ss.Properties.Add("ApplicationName", si.ApplicationName);
}
else if (si.RevNumber != null)
{
ss.Properties.Add("RevNumber", si.RevNumber);
}
else if (si.Template != null)
{
ss.Properties.Add("Template", si.Template);
}
}
var dsi = ((HSSFWorkbook)workbook).DocumentSummaryInformation;
if(dsi!=null)
{
if (dsi.ByteCount > 0)
{
ss.Properties.Add("ByteCount", dsi.ByteCount);
}
else if (dsi.Company !=null)
{
ss.Properties.Add("Company", dsi.Company);
}
else if (dsi.Format>0)
{
ss.Properties.Add("Format", dsi.Format);
}
else if (dsi.LineCount!= null)
{
ss.Properties.Add("LineCount", dsi.Company);
}
else if (dsi.LinksDirty)
{
ss.Properties.Add("LinksDirty", true);
}
else if (dsi.Manager!=null)
{
ss.Properties.Add("Manager", dsi.Manager);
}
else if (dsi.NoteCount != null)
{
ss.Properties.Add("NoteCount", dsi.NoteCount);
}
else if (dsi.Scale)
{
ss.Properties.Add("Scale", dsi.Scale);
}
else if (dsi.Company != null)
{
ss.Properties.Add("Company", dsi.Company);
}
else if (dsi.MMClipCount != null)
{
ss.Properties.Add("MMClipCount", dsi.MMClipCount);
}
else if (dsi.ParCount != null)
{
ss.Properties.Add("ParCount", dsi.ParCount);
}
}
}
return ss;
}
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 TestToxyTableToDataTable_withEmptyColumnHeader()
{
#region create ToxyTable
ToxyTable ttable = new ToxyTable();
ttable.Name = "Test1";
ttable.ColumnHeaders.Cells.Add(new ToxyCell(0, "C1"));
ttable.ColumnHeaders.Cells.Add(new ToxyCell(1, null));
ttable.ColumnHeaders.Cells.Add(new ToxyCell(2, "C2"));
ttable.ColumnHeaders.Cells.Add(new ToxyCell(3, null));
ttable.ColumnHeaders.Cells.Add(new ToxyCell(4, "C4"));
ToxyRow trow1 = new ToxyRow(0);
trow1.Cells.Add(new ToxyCell(0, "1"));
trow1.Cells.Add(new ToxyCell(1, "2"));
trow1.Cells.Add(new ToxyCell(4, "3"));
trow1.Cells.Add(new ToxyCell(5, "4"));
trow1.LastCellIndex = 5;
ttable.Rows.Add(trow1);
ToxyRow trow2 = new ToxyRow(1);
trow2.LastCellIndex = 3;
trow2.Cells.Add(new ToxyCell(0, "5"));
trow2.Cells.Add(new ToxyCell(1, "6"));
trow2.Cells.Add(new ToxyCell(3, "7"));
ttable.Rows.Add(trow2);
ttable.LastColumnIndex = 5;
#endregion
DataTable dt = ttable.ToDataTable();
Assert.AreEqual("Test1", dt.TableName);
Assert.AreEqual(2, dt.Rows.Count);
Assert.AreEqual(6, dt.Columns.Count);
Assert.AreEqual("C1", dt.Columns[0].Caption);
Assert.AreEqual("Column1", dt.Columns[1].Caption);
Assert.AreEqual("C2", dt.Columns[2].Caption);
Assert.AreEqual("Column2", dt.Columns[3].Caption);
Assert.AreEqual("C4", dt.Columns[4].Caption);
Assert.AreEqual("1", dt.Rows[0][0].ToString());
Assert.AreEqual("2", dt.Rows[0][1].ToString());
Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[0][2].ToString()));
Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[0][3].ToString()));
Assert.AreEqual("3", dt.Rows[0][4].ToString());
Assert.AreEqual("4", dt.Rows[0][5].ToString());
Assert.AreEqual("5", dt.Rows[1][0].ToString());
Assert.AreEqual("6", dt.Rows[1][1].ToString());
Assert.AreEqual("7", dt.Rows[1][3].ToString());
Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][2].ToString()));
Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][4].ToString()));
Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][5].ToString()));
}
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);
}
