protected virtual string BuildExcelReport(DataSet ds)
{
string fileName = GetExcelFileName();
using (XLWorkbook book = new XLWorkbook())
{
foreach (DataTable dt in ds.Tables)
{
string sheetName = dt.TableName;
DataTable dtc = UtilityHandler.CleanData(dt);
book.Worksheets.Add(dtc, sheetName);
}
FormatExcelSheet(book);
book.SaveAs(fileName);
}
return(fileName);
}
protected virtual DataTable GetDataTable(string tableName, DataTable src, List <ExcelColumn> columns)
{
DataTable des = new DataTable(tableName);
foreach (ExcelColumn rc in columns)
{
des.Columns.Add(rc.ColumnName, rc.DataType);
}
foreach (DataRow row in src.Rows)
{
DataRow r = des.NewRow();
foreach (DataColumn dc in des.Columns)
{
if (src.Columns.Contains(dc.ColumnName))
{
r[dc.ColumnName] = row[dc.ColumnName];
}
}
des.Rows.Add(r);
}
//Change the display Name
foreach (ExcelColumn col in columns)
{
if (UtilityHandler.IsEmpty(col.DisplayName))
{
continue;
}
if (col.ColumnName != col.DisplayName &&
des.Columns.Contains(col.ColumnName))
{
des.Columns[col.ColumnName].ColumnName = col.DisplayName;
}
}
return(UtilityHandler.CleanData(des));
}