EPPlus:应用LoadFromCollection后，如何在每个单元格周围分配边框

本文关键字：单元格 周围 边框 分配 应用 LoadFromCollection EPPlus | 更新日期: 2023-09-27 17:57:23

在我的导出操作结果中，我能够将模型加载到我的ExcelPackage中。

我遇到麻烦的地方是一旦应用了LoadFromCollection，就在每个单元格周围分配一个边框。虽然AutoFitColumns正确应用，但我应用的边框样式仅适用于Cells["D1"]，而不适用于表格。

BorderAround成功地在整个表格周围放置边框，但我宁愿将边框应用于表格内单元格的边框。有没有办法做到这一点？

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

EPPlus:应用LoadFromCollection后，如何在每个单元格周围分配边框

如果我知道模型的列数，我可以用函数计算行数并执行以下操作：

var modelRows = exportQuery.Count()+1;    
string modelRange = "D1:F" + modelRows.ToString();
var modelTable = worksheet.Cells[modelRange];

或者，提供更多上下文。 我验证了 EPPlus 将接受 Cells[] 中的字符串变量，这允许我选择整个表并正确应用边框样式和AutoFitColumns{}。我所要做的就是手动在 modelRange 变量中输入起始列和结束列。

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

这将解决问题 - 工作表。单元格[工作表。尺寸.地址]

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

        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));
        }
相关文章：