/// <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);
}
