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