c# - FormulaR1C1 增加行值 EPPlus

Question

我设置了一个公式如下:

 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？

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

}