c# - FormulaR1C1增加行值EPPlus

我设置了一个公式，如下所示：

 var excelfile = new FileInfo("Excel.xlsx");
            using (var package = new ExcelPackage(excelfile))
            {
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {ExcelWorksheet RMWorksheet = workBook.Worksheets["% Mean Removal"];
                     RMWorksheet.Cells[3, 2, (dtDadosPop.Rows.Count + 2), 2].FormulaR1C1 = "'Dados Projeto'!R[-1]C";
                     p.Workbook.Worksheets.Add("% Mean Removal", RMWorksheet);
}
}

问题是它将Row的值增加到-2，-3，...，因此引用了相同的值。如何使所有单元格的值都保持为-1？

最佳答案

我明白你的意思了。我相信正在发生的事情是EPPlus通过设计将相对行引用转换为绝对行，但在整个范围内仅转换一次。您可以通过功能ExcelCellBase.TranslateFromR1C1在源代码中看到它。因此，当它实际为单元格设置公式时，对所有单元格都使用='Dados Projeto'!B2而不是='Dados Projeto'!R[-1]C。

因此，为了执行您想要的操作，您将需要执行以下循环：

[TestMethod]
public void Increae_Row_Test()
{
    //http://stackoverflow.com/questions/28340229/formular1c1-increasing-row-value-epplus

    //Throw in some data
    var dtDadosPop = new DataTable("tblData");
    dtDadosPop.Columns.Add(new DataColumn("Col1"));
    dtDadosPop.Columns.Add(new DataColumn("Col2"));

    for (var i = 0; i < 11; i++)
    {
        var row = dtDadosPop.NewRow();
        row["Col1"] = "Col1 Row" + i;
        row["Col2"] = "Col2 Row" + i;
        dtDadosPop.Rows.Add(row);
    }

    //create the excel file
    var excelfile = new FileInfo(@"c:\temp\temp.xlsx");
    if (excelfile.Exists)
        excelfile.Delete();

    //Use an file in memory for the package "p"
    using (var p = new ExcelPackage())
    using (var package = new ExcelPackage(excelfile))
    {
        ExcelWorkbook workBook = package.Workbook;
        if (workBook != null)
        {
            //add the parts referenced by OP code
            workBook.Worksheets.Add("% Mean Removal");

            var dataws = workBook.Worksheets.Add("Dados Projeto");
            dataws.Cells.LoadFromDataTable(dtDadosPop, true);
            p.Workbook.Worksheets.Add("Dados Projeto", dataws);

            //OP code
            if (workBook.Worksheets.Count > 0)
            {
                ExcelWorksheet RMWorksheet = workBook.Worksheets["% Mean Removal"];
                //RMWorksheet.Cells[3, 2, (dtDadosPop.Rows.Count + 2), 2].FormulaR1C1 = "'Dados Projeto'!R[-1]C";

                for (var i = 1; i <= dtDadosPop.Rows.Count; i++)
                {
                    RMWorksheet.Cells[i + 2, 2].FormulaR1C1 = "'Dados Projeto'!R[-1]C";
                }
                p.Workbook.Worksheets.Add("% Mean Removal", RMWorksheet);
            }
        }

        package.SaveAs(excelfile);
    }

}