public static void GenerateWorksheet(ExcelWorksheets worksheets, DataTable dataTable, string strWorksheetName)
{
try
{
ExcelWorksheet ws;
if (worksheets.Count(w => w.Name == strWorksheetName) == 0)
{
ws = worksheets.Add(strWorksheetName);
Log.LogMessage("Adding new Worksheet: " + strWorksheetName);
}
else
{
ws = worksheets[strWorksheetName];
ws.Cells.Clear();
Log.LogMessage("Clearing Values for Worksheet: " + strWorksheetName);
}
int cols = ws.Dimension?.Columns ?? dataTable.Columns.Count;
object[] columns = dataTable.Rows[0].ItemArray;
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
for (int x = 1; x <= columns.Count(); x++)
{
if (Config.Dates.Contains(ws.Cells[1, x].Value))
{
ws.Column(x).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
}
}
Log.LogMessage("Added data to Worksheet: " + strWorksheetName);
// format header cells
using (var range = ws.Cells[1, 1, 1, cols])
{
Log.LogMessage("Formating Header Cells for Worksheet: " + strWorksheetName);
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
// Create autofilter for the range of cells
ws.Cells[ws.Dimension?.Address].AutoFilter = true;
// Autofit columns for all cells
ws.Cells[ws.Dimension?.Address].AutoFitColumns();
Log.LogMessage("Created " + strWorksheetName + " Worksheet");
}
catch (Exception ex)
{
Log.LogMessage("Error Creating " + strWorksheetName + ", Error: " + ex.Message);
}
}
