Formula Results not showing in Protected view

Nov 6, 2014 at 9:37 PM
Edited Nov 6, 2014 at 9:41 PM
When I create a new worksheet, We have a total column that sums a series of prices. That value shows as blank in protected view (when you it "enable editing" it immediately shows up). This does not occur when you create the file using the excel API.

I figured it was because the value the tool hadn't calculated it yet, so I downloaded Version 4 Beta 2 and called the calculate method. I then checked via debugging that both the Value and Text properties now have the correct value (they do) after calling calculate, but still the value does not display in protected mode.

I really need this to display by default because it used to deliver invoices, and many of the people receiving them are not competent enough to think to hit the "enable editing" button.

Any help or thoughts on this?

Thanks,
Chris
Developer
Nov 9, 2014 at 8:27 AM
The only advice I can give you is to try to find differences between the EPPlus generated workbook and the one you have generated via Excel API. Perhaps you already know this, but if you rename the workbook from .xlsx to .zip you will be able to navigate within the workbook and view the workbook/worksheet xml. Also, it would be helpful if you could describe in a little more detailed way how to reproduce - then I can have a look at it when we are done with the 4.0 release.

/Mats
Nov 11, 2014 at 12:47 AM
Edited Nov 11, 2014 at 12:54 AM
Okay so I did what you said in regards to converting the xlsx to a zip and the difference in pretty simple.

I'm just going to show the XML of the cell where the problem is occurring. And screen shots of what it's doing. I need to see the total

In the version that EPPlus 4.0 Beta 2 created the XML for the summary/formula node looks like this. This is AFTER calculate is called, and I can read the value property on the cell programmatically.

<c r="L17" s="12">
<f>SUM(L15:L15)</f>
</c>
Image

In the version where it renders the total the XML looks like this. As you can see a v node, mostly likely for value, exists.

<c r="L17" s="12">
<f>SUM(L15:L15)</f>
<v>35</v>
</c>

Image

I made just this change the EPPlus file and the total showed up.

Any help would be greatly appreciated
Developer
Nov 11, 2014 at 10:49 PM
I just did a simple test with the new RC, installed via Nuget and when I look in the worksheet xml the value is set after calling Calculate(). Could it be that you are calling Save() before Calculate(), calling Calculate() on a specific range only or something like that?

This is my code:
using (var pck = new ExcelPackage(new FileInfo(file)))
{
        var ws = pck.Workbook.Worksheets.Add("test");
        ws.Cells["A1:A2"].Value = 3;
        ws.Cells["A3"].Formula = "SUM(A1:A2)";
        pck.Workbook.Calculate();
        pck.Save();
}
Nov 14, 2014 at 12:00 AM
Thanks for the code snippet, that gave me what I needed to get it work.

I changed my code which (based on your example above) effectively called
ws.Calculate(); 
which would give me the value live, but not have it in the sheet when I saved it and changed it to
pck.Workbook.Calculate();
Is that by design? I can see where it could be, but it seems a little odd. Do you mind explaining?

Thanks a ton.
Developer
Nov 15, 2014 at 6:18 AM
There are three ways to perform a formula calculation with EPPlus:
  • Call Calculate on the Workbook - all formulas in the entire workbook will be evaluated.
  • Call Calculate on a specific worksheet - all formulas and their dependencies on that specific worksheet will be evaluated.
  • Call Calculate on a specific range - all formulas and their dependencies in that specific range will be evaluted.
I tried to change pck.Workbook.Calculate() to ws.Calculate() in my example above, same result though (the value is set on A3). Since your workbook seems to have only one worksheet it shouldn´t matter which option you use. If you dont mind you can create an issue on this and enclose your workbook with that issue.
Nov 18, 2014 at 4:34 PM
I can confirm this behavior was corrected with the new RC. Thanks.