Using EPPlus library to replace formulas by their values in Excel

Question

I've read that Microsoft.Office.Interop.Excel would be the easiest way to replace formulas by their values in Excel but it requires to have Office installed. Since I will need to deploy on a Windows Server (2008 or 2012), I am looking for the best and/or simplest way to accomplish that using EPPlus.

Adding formulas is well documented, e.g.

currentWorksheet.Cells["C4"].Formula = "SUM(C2:C3)";

But I cannot find any example of replacing entire worksheets of formulas by their equivalent values. Basically the Copy followed by the Paste Special option in Excel.

Answer 1

I dont think there is any kind of function built into Epplus that will do that for you en masse. But you can take advantage of the fact that the Cells collection of the Worksheet only contains entries for cells with content. So something like this should not be too painful performance-wise:

currentWorksheet.Cells["C2"].Value = 5;
currentWorksheet.Cells["C3"].Value = 15;
currentWorksheet.Cells["C4"].Formula = "SUM(C2:C3)";

currentWorksheet.Cells["D2"].Value = 15;
currentWorksheet.Cells["D3"].Value = 25;
currentWorksheet.Cells["D4"].Formula = "SUM(D2:D3)";

//Calculate the formulas and the overwrite them with their values
currentWorksheet.Cells.Calculate();
foreach (var cell in currentWorksheet.Cells.Where(cell => cell.Formula != null))
    cell.Value = cell.Value;