CODEWITHSUNDEEP
c#excelepplus
Kyle Johnston
Kyle Johnston

Reputation: 41

EPPlus not Inserting Formula Correcty

I'm using EPPlus to manipulate an Excel file. I need to insert a formula that references an entire column. I'm currently using:

currentWorksheet.Cells[2, 4].Formula = "QUARTILE(C:C,1)";

If I manually enter this formula into the Excel sheet it works, or if I insert it with a finite range in EPPlus like so:

=QUARTILE(C2:C1000,1)

But when it's inserted as a whole column range of C:C using EPPlus it results in the following:

=QUARTILE(#REF!,1)

Is there something special needed when referencing entire columns, or is this just a bug in EPPlus?

Upvotes: 2

Views: 4822

Answers (2)

Svartalfar
Svartalfar

Reputation: 121

You can use following code

worksheet.Cells["A1"].Formula= "=QUARTILE(C2:C1000,1)";

Upvotes: 4

Ernie S
Ernie S

Reputation: 14250

Stewart is correct in that the formula should be valid. Try pasting in the rest of your code. I thought together a quick unit test and it worked fine:

[TestMethod]
public void Quartile_Range_Test()
{
    //Throw in some data
    var dtMain = new DataTable("tblData");
    dtMain.Columns.Add(new DataColumn("Col1", typeof(int)));

    for (var i = 0; i < 20; i++)
    {
        var row = dtMain.NewRow();
        row["Col1"] = i * 100;
        dtMain.Rows.Add(row);
    }

    //Clear the file
    var newFile = new FileInfo(@"C:\Temp\Temp.xlsx");
    if (newFile.Exists)
        newFile.Delete();

    using (var package = new ExcelPackage(newFile))
    {
        var currentWorksheet = package.Workbook.Worksheets.Add("Test");
        currentWorksheet.Cells["C1"].LoadFromDataTable(dtMain, false);
        currentWorksheet.Cells[2, 4].Formula = "QUARTILE(C:C,1)";

        package.Save();

    }
}

Upvotes: 2

Related Questions