3

When using EPPlus, I am trying to insert the result of a formula into a cell

ie: 

Worksheet.Cells["A4"].Value = 3
Worksheet.Cells["A5"].Value = 4
Worksheet.Cells["A6"].Formula = "=SUM(A3:A4)"
Worksheet.Cells["A6"].Calculate()

In the worksheet i will see the formula in the Formula bar, but what I would like is to evaluate the formula in EPPlus and insert the value into the cell. So when clicking into the cell all i see is 7 and not =SUM(A3:A4)

The reason for this, is because I have large worksheet (for business reasons) and having the formulas calculate when opening means the sheet takes about 20 seconds to load

telsokari
  • 213
  • 2
  • 5
  • 12
  • Just skip the `=`, it should be: `Worksheet.Cells["A6"].Formula = "SUM(A3:A4)"` – Hackerman Jun 24 '16 at 13:42
  • Thanks for your response. This didn't work. It still shows the formula and the formula result tries to calculate when the sheet is opened – telsokari Jun 24 '16 at 13:59
  • Look at this Q&A: http://stackoverflow.com/questions/21189163/invoke-formula-in-excel-via-epplus – Hackerman Jun 24 '16 at 14:10
  • I think that "calculate" is replacing the formula with the calculated value. So if you want to keep the formulas, then you can't use "calculate". As a tip to speed things up (at least on the EPPlus side), try to define formatting and formulas for cell ranges instead of setting it for each cell one at a time. This seems to make things run more quickly when you generate the file from EPPlus, and could potentially also speed up the time to open it in Excel - though further testing would be required to verify that. – Taraz Jan 09 '17 at 00:39

2 Answers2

5

just to illustrate swmal answer :

epplus calculate

Xavave
  • 645
  • 11
  • 15
3

If you want to calculate and remove the actual formula before you send the workbook to the client you should set the Formula property to string.Empty after you have called Calculate(). The calculated value is stored in the Value property of the cell.

swmal
  • 171
  • 1
  • 5