/// <summary> /// extracts the dataset from table stored inside sheet name /// </summary> /// <param name="tblDescriptor"></param> /// <returns></returns> public List <string> ValidateTable(ExcelTableDescriptor tblDescriptor) { if (this._Data == null) { throw new InvalidOperationException("Table is empty!!!"); } return(this._Validator.ValidateTable(tblDescriptor, _Data)); }
/// <summary> /// Force column types of read data to table definition ones /// </summary> /// <param name="tblDescriptor"></param> /// <returns></returns> public DataTable GetTypedTable(ExcelTableDescriptor tblDescriptor) { var newTable = new DataTable(); // create columns with name defined in table descriptor foreach (var col in tblDescriptor.Columns) { Type colType = null; switch (col.ColumnType) { case ExcelColumnDescriptor.EnumColumnType.Boolean: colType = typeof(bool); break; case ExcelColumnDescriptor.EnumColumnType.Number: colType = typeof(double); break; case ExcelColumnDescriptor.EnumColumnType.Date: colType = typeof(DateTime); break; case ExcelColumnDescriptor.EnumColumnType.String: colType = typeof(string); break; } newTable.Columns.Add(new DataColumn(col.Name, colType) { AllowDBNull = true }); } //fill rows foreach (DataRow row in this._Data.Rows) { var newRow = newTable.NewRow(); foreach (var col in tblDescriptor.Columns) { var index = tblDescriptor.Columns.IndexOf(col); if (col.ColumnType == ExcelColumnDescriptor.EnumColumnType.Date) { newRow[index] = DateTime.FromOADate(Convert.ToDouble(row[index])); } else { newRow[index] = row[index]; } } newTable.Rows.Add(newRow); } return(newTable); }
/// <summary> /// It Adds the table to a sheet. Only one table per sheet is addmitted /// </summary> /// <param name="table"></param> /// <returns></returns> public ExcelContentEditor AddSheetTable(ExcelTableDescriptor table) { if (_Tables.ContainsKey(table.Name)) { throw new ArgumentException("Table already exists", nameof(table)); } if (_Tables.Count(x => x.Value.SheetName == table.SheetName) > 0) { throw new ArgumentException($"Table already added to sheet {table.SheetName}", nameof(table)); } _Tables.Add(table.Name, table); return(this); }
public void WriteFileWithFormulaSucceeded() { //***** GIVEN string outputFileName = "XlsxFileWriterTest_WriteFileWithFormulaSucceeded.xlsx"; var table = new ExcelTableDescriptor() { Name = "TestTable", SheetName = "Sheet01", Columns = new List <ExcelColumnDescriptor>() { new ExcelColumnDescriptor() { Name = "COL1", HeaderText = "COl #1", ColumnType = ExcelColumnDescriptor.EnumColumnType.Number }, new ExcelColumnDescriptor() { Name = "COL2", HeaderText = "COl #2", ColumnType = ExcelColumnDescriptor.EnumColumnType.String }, new ExcelColumnDescriptor() { Name = "COL3", HeaderText = "COl #3", ColumnType = ExcelColumnDescriptor.EnumColumnType.Boolean }, new ExcelColumnDescriptor() { Name = "COL4", HeaderText = "COl #4", ColumnType = ExcelColumnDescriptor.EnumColumnType.String } } }; var tableData = new List <ExcelDataRowDescriptor>() { new ExcelDataRowDescriptor() { RowNumber = 1 }.AddCell("COL1", 1).AddCell("COL2", "title #1").AddCell("COL3", true).AddCell("COL4", DateTime.Now.ToString()), new ExcelDataRowDescriptor() { RowNumber = 2 }.AddCell("COL1", 2).AddCell("COL2", "title #2").AddCell("COL3", null).AddCell("COL4", null) }; var table2 = new ExcelTableDescriptor() { Name = "FormulaTable", SheetName = "Sheet02", Columns = new List <ExcelColumnDescriptor>() { new ExcelColumnDescriptor() { Name = "COL1", HeaderText = "ID", ColumnType = ExcelColumnDescriptor.EnumColumnType.Number }, new ExcelColumnDescriptor() { Name = "COL2", HeaderText = "ID2", ColumnType = ExcelColumnDescriptor.EnumColumnType.Number }, new ExcelColumnDescriptor() { Name = "COL3", HeaderText = "Title", ColumnType = ExcelColumnDescriptor.EnumColumnType.Formula }, } }; var fixedPartOfFormula = "VLOOKUP($A{0},'{1}'!$A$1:$Z$1000,{2})"; //=VLOOKUP(A2;Sheet01!A1:D5; 2) var tableData2 = new List <ExcelDataRowDescriptor>() { new ExcelDataRowDescriptor() { RowNumber = 1 } .AddCell("COL1", 1) .AddCell("COL2", 4) .AddCell("COL3", String.Format(fixedPartOfFormula, 2, table.SheetName, 2)), new ExcelDataRowDescriptor() { RowNumber = 1 } .AddCell("COL1", 1) .AddCell("COL2", 2) .AddCell("COL3", String.Format(fixedPartOfFormula, 3, table.SheetName, 2)) }; //***** WHEN ExcelContentEditor editor = new ExcelContentEditor(CultureInfo.InvariantCulture, "dd/MM/yyyy") .SelectFolder(this.OutputFolder) .AddSheetTable(table) .FillTable(table.Name, tableData) .AddSheetTable(table2) .FillTable(table2.Name, tableData2) .SaveAs(outputFileName); //this._SavedFiles.Add(outputFileName); //***** ASSERT Assert.IsTrue(File.Exists(Path.Combine(OutputFolder, outputFileName)), $"Unable to find file {outputFileName} in folder {OutputFolder}"); }
public void WriteFileSucceeded() { //***** GIVEN string outputFileName = "XlsxFileWriterTest_WriteFileSucceeded.xlsx"; var table = new ExcelTableDescriptor() { Name = "TestTable", SheetName = "Sheet01", Columns = new List <ExcelColumnDescriptor>() { new ExcelColumnDescriptor() { Name = "COL1", HeaderText = "COl #1", ColumnType = ExcelColumnDescriptor.EnumColumnType.String }, new ExcelColumnDescriptor() { Name = "COL2", HeaderText = "COl #2", ColumnType = ExcelColumnDescriptor.EnumColumnType.Number }, new ExcelColumnDescriptor() { Name = "COL3", HeaderText = "COl #3", ColumnType = ExcelColumnDescriptor.EnumColumnType.Boolean }, new ExcelColumnDescriptor() { Name = "COL4", HeaderText = "COl #4", ColumnType = ExcelColumnDescriptor.EnumColumnType.String } } }; var tableData = new List <ExcelDataRowDescriptor>() { new ExcelDataRowDescriptor() { RowNumber = 1 }.AddCell("COL1", "asjhg").AddCell("COL2", 22).AddCell("COL3", true).AddCell("COL4", DateTime.Now.ToString()), new ExcelDataRowDescriptor() { RowNumber = 2 }.AddCell("COL1", "asjhg").AddCell("COL2", 22.5).AddCell("COL3", null).AddCell("COL4", null) }; //***** WHEN ExcelContentEditor editor = new ExcelContentEditor(CultureInfo.InvariantCulture, "dd/MM/yyyy") .SelectFolder(this.OutputFolder) .AddSheetTable(table) .FillTable(table.Name, tableData) .SaveAs(outputFileName); this._SavedFiles.Add(outputFileName); //***** ASSERT Assert.IsTrue(File.Exists(Path.Combine(OutputFolder, outputFileName)), $"Unable to find file {outputFileName} in folder {OutputFolder}"); ExcelContentEditor reader = new ExcelContentEditor(CultureInfo.InvariantCulture, "dd/MM/yyyy") .SelectFile(this.OutputFolder, outputFileName) .OpenFile() .ReadTableFromSheet(table.SheetName, new DataTableDelimiter() { MaxColumnNumber = table.Columns.Count }); Assert.IsNotNull(reader.ReadData as DataTable, "NUll data reade from file " + outputFileName); Assert.AreEqual(2, (reader.ReadData as DataTable).Rows.Count); }
/// <summary> /// extracts the dataset from table stored inside sheet name /// </summary> /// <returns></returns> public List <string> ValidateTable(ExcelTableDescriptor tblDescriptor, DataTable data) { if (data == null) { throw new ArgumentNullException(nameof(data)); } var errors = new List <string>(); if (data.Columns.Count != tblDescriptor.Columns.Count) { errors.Add($"Wrong Column number: expected <{tblDescriptor.Columns.Count}> but found <{data.Columns.Count}>"); } foreach (var col in tblDescriptor.Columns) { int columnIndex = tblDescriptor.Columns.IndexOf(col); if (data.Columns[columnIndex].ColumnName.Trim() != col.HeaderText.Trim()) { errors.Add($"Column {columnIndex}: Wrong Column Name; expected <{col.HeaderText}> but found <{data.Columns[columnIndex].ColumnName}>"); } } // check column types if (_ValidateColumnType) { foreach (var col in tblDescriptor.Columns) { int index = tblDescriptor.Columns.IndexOf(col); var columnType = data.Columns[index].DataType; switch (col.ColumnType) { case ExcelColumnDescriptor.EnumColumnType.String: if (columnType != typeof(string)) { errors.Add($"Column {col.Name}: expected {col.ColumnType} but found {columnType.Name}"); } break; case ExcelColumnDescriptor.EnumColumnType.Number: if (columnType != typeof(int) && columnType != typeof(int?) && columnType != typeof(double) && columnType != typeof(double?)) { errors.Add($"Column {col.Name}: expected {col.ColumnType} but found {columnType.Name}"); } break; case ExcelColumnDescriptor.EnumColumnType.Date: if (columnType != typeof(DateTime?) && columnType != typeof(DateTime)) { errors.Add($"Column {col.Name}: expected {col.ColumnType} but found {columnType.Name}"); } break; case ExcelColumnDescriptor.EnumColumnType.Boolean: if (columnType != typeof(bool) && columnType != typeof(bool?)) { errors.Add($"Column {col.Name}: expected {col.ColumnType} but found {columnType.Name}"); } break; default: throw new NotImplementedException(); } } } if (errors.Count > 0) { return(errors); } //validate row data foreach (DataRow row in data.Rows) { StringBuilder error = new StringBuilder(); foreach (var col in tblDescriptor.Columns) { object cellValue = row[col.HeaderText]; if (cellValue == null) { continue; } switch (col.ColumnType) { case ExcelColumnDescriptor.EnumColumnType.String: break; case ExcelColumnDescriptor.EnumColumnType.Date: DateTime outDate; double oaDateValue; bool isOADate = Double.TryParse(cellValue.ToString(), out oaDateValue); if (!isOADate) { error.AppendFormat("Cell {0}: invalid OA DateTime;", col.Name); } else { if (!!String.IsNullOrEmpty(cellValue.ToString()) && !DateTime.TryParse(cellValue.ToString(), out outDate)) { error.AppendFormat("Cell {0}: invalid DateTime;", col.Name); } } break; case ExcelColumnDescriptor.EnumColumnType.Number: double outNumber; if (!String.IsNullOrEmpty(cellValue.ToString()) && !double.TryParse(cellValue.ToString(), out outNumber)) { error.AppendFormat("Cell {0}: invalid number;", col.Name); } break; case ExcelColumnDescriptor.EnumColumnType.Boolean: bool outBool; if (!String.IsNullOrEmpty(cellValue.ToString()) && !bool.TryParse(cellValue.ToString(), out outBool)) { error.AppendFormat("Cell {0}: invalid boolean;", col.Name); } break; default: error.AppendFormat("Cell {0}: unknown format", col.Name); break; } if (error.Length > 0) { errors.Add($"Row {data.Rows.IndexOf(row)} is Invali! " + error.ToString()); } } } return(errors); }