c# - 使用 EPPlus 将公式复制到多个单元格

Question

我正在使用 Epplus 并将公式输入到单元格 K1 和 K2 中，这段代码非常适合此操作。但是，我需要将公式从 K1 和 K2 向右“拖动”，直到到达包含数据的最后一列。

我当前的代码是

ws.Cells["K1"].Formula = "=LEFT(K7,(FIND(\" \", K7,1))";
ws.Cells["K2"].Formula = "=RIGHT(K7,LEN(K7)-FIND(\" \", K&,1))";
ws.Cells["K2"].Style.WrapText = true;
ws.Cells["K2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["K2"].Style.HorizontalAlighment = ExcelHorizontalAlignment.Center;

Answer 1

另一种实现你想要的结果的方法是这个

using (var pck = new ExcelPackage(new FileInfo("C:\\Test\\Test.xlsx")))
{
    ExcelWorksheet ws = pck.Workbook.Worksheets[0];
    var startColumnIndex = 11;
    const int dataSourceRowIndex = 3; 

    while (ws.Cells[dataSourceRowIndex, startColumnIndex].Value != null)
    {
        ws.Cells[1, startColumnIndex].Formula = $"=LEFT({ws.Cells[dataSourceRowIndex, startColumnIndex].Address},(FIND(\" \",{ws.Cells[dataSourceRowIndex, startColumnIndex].Address},1)-1))";
        ws.Cells[2, startColumnIndex].Formula = $"=RIGHT({ws.Cells[dataSourceRowIndex, startColumnIndex].Address},LEN({ws.Cells[dataSourceRowIndex, startColumnIndex].Address})-FIND(\" \",{ws.Cells[dataSourceRowIndex, startColumnIndex].Address},1))";
        ws.Cells[2, startColumnIndex].Style.WrapText = true;
        ws.Cells[2, startColumnIndex].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
        ws.Cells[2, startColumnIndex].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        startColumnIndex++;
    }
}