C # Epplus export, set cell format, set column header style, set cell grid box, formatted date

//Data source DataTable

DataTable dataTable = new DataTable();

//...

//...



ExcelPackage package = new ExcelPackage();



//Create Sheet

ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(Sheet1);



//Set cell data format

foreach (DataColumn item in dataTable.Columns)

{

 if (item.DataType == typeof(DateTime))

 {

 var i = dataTable.Columns.IndexOf(item);

 //Set the column format to custom "yyyy/MM/dd HH:mm:ss"

 workSheet.Cells[2, i + 1, dataTable.Rows. Count + 1, i + 1].Style.Numberformat.Format = "yyyy/MM/dd HH:mm:ss";

}

}



//Set the first row and column heading style

using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])

{

 r.Style.Font.Color.SetColor(System.Drawing.Color.White);

 r.Style.Font.Bold = true;

 r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;

 r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));

}



//Set cell borders

using (ExcelRange r = workSheet.Cells[startRowFrom + 1, < span style="color: #800080;">1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])

{

 r.Style.Border.Top.Style = ExcelBorderStyle.Thin;

 r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

 r.Style.Border.Left.Style = ExcelBorderStyle.Thin;

 r.Style.Border.Right.Style = ExcelBorderStyle.Thin;



 r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);

 r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);

 r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);

 r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);

}



//Load DataTable to Excel cell

workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);



//output as bytes

byte[] result = package.GetAsByteArray();



return result;

//Data Source DataTable

DataTable dataTable = new DataTable();

//...

//...



ExcelPackage package = new ExcelPackage();



//Create Sheet

ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(Sheet1);



//Set cell data format

foreach (DataColumn item in dataTable.Columns)

{

 if (item.DataType == typeof(DateTime))

 {

 var i = dataTable.Columns.IndexOf(item);

 //Set the column format to custom "yyyy/MM/dd HH:mm:ss"

 workSheet.Cells[2, i + 1, dataTable.Rows. Count + 1, i + 1].Style.Numberformat.Format = "yyyy/MM/dd HH:mm:ss";

}

}



//Set the first row and column heading style

using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])

{

 r.Style.Font.Color.SetColor(System.Drawing.Color.White);

 r.Style.Font.Bold = true;

 r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;

 r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));

}



//Set cell borders

using (ExcelRange r = workSheet.Cells[startRowFrom + 1, < span style="color: #800080;">1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])

{

 r.Style.Border.Top.Style = ExcelBorderStyle.Thin;

 r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

 r.Style.Border.Left.Style = ExcelBorderStyle.Thin;

 r.Style.Border.Right.Style = ExcelBorderStyle.Thin;



 r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);

 r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);

 r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);

 r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);

}



//Load DataTable to Excel cell

workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);



//output as bytes

byte[] result = package.GetAsByteArray();



return result;