CODEWITHSUNDEEP
c#excelepplus
Crna Krv
Crna Krv

Reputation: 147

Using EPPlus library to replace formulas by their values in Excel

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.

Upvotes: 4

Views: 6174

Answers (2)

brodrigs
brodrigs

Reputation: 344

I know this is 3 years old, but if you're reading this now, EPPlus now has the function .ClearFormulas() that does just that, simply do

yourSheetHere.Calculate();   
yourSheetHere.ClearFormulas();

and you're good to go.

Upvotes: 2

Ernie S
Ernie S

Reputation: 14250

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;

Upvotes: 10

Related Questions