FormulaR1C1 increasing row value EPPlus

Question

I have set a formula as follows:

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

The problem is it increases the value of the Row to -2,-3,... and as so references the same value. How can I make it stick as -1 for all cells?

Could you post more code? It would provide better context with what you are trying to do. — Ernie S, Feb 05 '15 at 23:19

score 3 · Accepted Answer · answered Feb 06 '15 at 13:35

I see what you mean now. I believe what is happening is EPPlus translates the relative row reference to absolute by design but only once for the entire range. You can see this in the source code by the function ExcelCellBase.TranslateFromR1C1. So when it actually sets the formula for the cells it uses ='Dados Projeto'!B2 on all of them and NOT ='Dados Projeto'!R[-1]C.

So, in order to do what you want you will need to do a loop like this:

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

}

FormulaR1C1 increasing row value EPPlus

1 Answers1