EPPlus: how can I assign border around each cell after I apply LoadFromCollection?

Question

In my export ActionResult I was able to load the model into my ExcelPackage.

Where I am having trouble is assigning a border around each cell once LoadFromCollection is applied. While the AutoFitColumns correctly applies, the border style I applied only works on Cells["D1"], but not on the table.

BorderAround successfully places a border around the entire table, but I would rather apply to the border to the cells inside the table. Is there a way I can do that?

// Fill worksheet with data to export
var modelCells = worksheet.Cells["D1"];
var border = modelCells.Style.Border.Top.Style = modelCells.Style.Border.Left.Style = modelCells.Style.Border.Right.Style = modelCells.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;                    

modelCells
    .LoadFromCollection(Collection: exportQuery, PrintHeaders: true)
    .AutoFitColumns(); 

Answer 1

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();

Answer 2

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

Answer 3

        var package = new ExcelPackage(new MemoryStream());
        var ws = package.Workbook.Worksheets.Add("Test");
        var modelTable = ws.Cells;
        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;
        modelTable.AutoFitColumns();
        // calculate
        ws.Calculate();

        saveFileDialog_SaveExcel.Filter = "Excel files (*.xlsx)|*.xlsx";
        var dialogResult = saveFileDialog_SaveExcel.ShowDialog();
        if (dialogResult == DialogResult.OK)
        {
            package.SaveAs(new FileInfo(saveFileDialog_SaveExcel.FileName));
        }