Prompted to Save Changes on file created with EPPlus

Question

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?

Answer 1

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?