6

I am creating a series of Excel Workbooks using EPPlus v3.1.3. When I open the newly created files, if I close it without touching anything it asks me if I want to save my changes. The only thing I've noticed changes if I say "yes" is that the app.xml file is slightly altered - there is no visible difference in the workbook, and the rest of the XML files are the same. I have tried both of these approaches:

ExcelPackage p = new ExcelPackage(new FileInfo(filename));
p.Save();

as well as 

ExcelPackage p = new ExcelPackage();
p.SaveAs(new FileInfo(filename));

and both have the same problem. Is there a way to have the app.xml file output in its final form?

The reason this is an issue is because we use a SAS program to QC, and when the SAS program opens the files as they have been directly output from the EPPlus program it doesn't pick up the values from cells that have formulas in them. If it is opened and "yes" is chosen for "do you want to save changes", it works fine. However, as we are creating several hundred of these, that is not practical.

Also, I am using a template. The template appears normal.

What is particularly strange is that we have been using this system for well over a year, and this is the first time we have encountered this issue.

Is there any way around this? On either the C# or SAS side?

yammerade
  • 629
  • 8
  • 20
  • Have you accidentally turned off formula calculations in Excel? Can you confirm if this is a problem from a different machine as well? – Robert Penridge Sep 24 '15 at 22:41
  • Formula calculations are on and Protected View is disabled. I've tried on two different machines. – yammerade Sep 25 '15 at 13:28

2 Answers2

2

What you are seeing is not unusual actually. Epplus does not actually generate a full XLSX file - rather it creates the raw XML content (all office 2007 document formats are xml-based) and places it in the zip file which is renamed to XLSX. Since it has not been ran through the Excel engine it has not be fully formatted to excels liking.

If it is a simple data sheet then chances are Excel does not have to do much calculation - just basic formatting. So in that case it will not prompt you to save. But even then if you do you will see it change the XLSX file a little. If you really want to see what it is doing behind the scenes rename the file to .zip and look at the xml files inside before and after.

The problem you are running in to is because it is not just a simple table export Excel has to run calculations when opened for the first time. This could be many things - formulas, autofilters, auto column/row height adustments, outlining, etc. Basically, anything that will make the sheet look a little "different" after excel gets done with it.

Unfortunately, there is no easy fix for this. Running it through excel's DOM somehow would be simplest which of course defeats the purpose of using EPPlus. The other thing you could do is see the difference between the before and after of the xml files (and there are a bunch in there you would have to look at) and mimic what excel would change/add in the "after" file version by manually editing the XML content. This is not a very pretty option depending on how extensive the changes would be. You can see how I have done it in other situations here:

Create Pivot Table Filters With EPPLUS

Adding a specific autofilter on a column

Set Gridline Color Using EPPlus?

Community
  • 1
  • 1
Ernie S
  • 13,902
  • 4
  • 52
  • 79
  • Thanks. I figured this would be the answer but I was hoping there was a more straightforward workaround. I think I'll just move all the calculations out of Excel so we don't bomb our QC system. – yammerade Sep 26 '15 at 01:18
  • @LisaMcCusker Sure. Another option I forgot to mention is VBA. You could move the offending logic to a macro which fires when the sheet first opens in Excel and then resaves itself without the VBA. Also not the cleanest way to go about it but figured I would mention it. See here: http://stackoverflow.com/questions/26734787/epplus-pivot-table-copy-values-to-new-sheet/26736549#26736549. – Ernie S Sep 26 '15 at 01:24
2

I ran into this same issue using EPPlus (version 4.1.0, fyi) and found adding the following code before closing fixed the problem:

p.Workbook.Calculate();
p.Workbook.FullCalcOnLoad = false;
Rÿck Nöthing
  • 167
  • 5