If you want to merge cells dynamically, you can also use: worksheet. Cells[FromRow, FromColumn, ToRow, ToColumn].
If I know the amount of columns the model has, I can count the number of rows with a function and do this:
var modelRows = exportQuery.Count()+1;
string modelRange = "D1:F" + modelRows.ToString();
var modelTable = worksheet.Cells[modelRange];
Or, with more context. I verified that EPPlus will accept a string variable in Cells[], which allows me to select the entire table and apply my border styling and AutoFitColumns{}
correctly. All I have to do manually is enter the starting column and ending column in the modelRange
variable.
var modelCells = worksheet.Cells["D1"];
var modelRows = exportQuery.Count()+1;
string modelRange = "D1:F" + modelRows.ToString();
var modelTable = worksheet.Cells[modelRange];
// Assign borders
modelTable.Style.Border.Top.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Left.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Right.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
// Fill worksheet with data to export
modelCells.LoadFromCollection(Collection: exportQuery, PrintHeaders: true);
modelTable.AutoFitColumns();
This will do the trick - worksheet.Cells[worksheet.Dimension.Address]
using (ExcelPackage excel = new ExcelPackage())
{
excel.Workbook.Worksheets.Add(sheetName);
excel.SaveAs(excelFile);
string headerRange = "A1:" + char.ConvertFromUtf32(dtJobs.Columns.Count + 64) + "1";
// Target a worksheet
var worksheet = excel.Workbook.Worksheets[sheetName];
#region design Header
//worksheet.Cells[headerRange].Style.Font.Bold = true;
worksheet.Cells[headerRange].Style.Font.Size = 11;
worksheet.Cells[headerRange].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[headerRange].Style.Fill.BackgroundColor.SetColor(Color.DarkGray);
//worksheet.Cells[headerRange].Style.WrapText = true;
worksheet.Cells[headerRange].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[headerRange].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[headerRange].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
#endregion
var excelWorksheet = excel.Workbook.Worksheets[sheetName];
excelWorksheet.Cells[excelWorksheet.Dimension.End.Row, 1].LoadFromDataTable(dtJobs, true);
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
excel.SaveAs(excelFile);
return filePath;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With