公式R1C1增加行值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:temptemp.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);
    }
}