Request to save changes to a file created using EPPlus

Question

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

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

and

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

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

The reason for this is that we use the SAS program for QC, and when the SAS program opens the files, since they were directly extracted from the EPPlus program, it does not select values ​​from cells that have formulas in them. If it is open and “yes” is selected for “you want to save changes,” it works fine. However, since we create several hundred of them, this is not practical.

In addition, I am using a template. The pattern looks fine.

What is especially strange is that we have been using this system for more than a year, and this is the first time we have encountered this problem.

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

Answer 1

What you see is actually unusual. Epplus does not actually generate the full XLSX file, but creates raw XML content (all Office 2007 document formats are xml-based) and puts it in a zip file that is renamed to XLSX. Since it did not start through the Excel engine, it is not fully formatted to stand out.

If this is a simple data sheet, then most likely Excel does not have to calculate much - just basic formatting. Therefore, in this case, he will not offer you to save. But even then, if you do, you will see that he slightly modified the XLSX file. If you really want to see what it does behind the scenes, rename the file to .zip and look at the xml files inside before and after.

The problem you are working with is that it is not just a simple table export. Excel should run the calculations the first time it opens. It can be many things - formulas, autofilters, column / row outsourcing, presentation, etc. Basically, everything that makes a sheet a little “different” after excel has done it.

Unfortunately, there is no easy solution for this. Running it through the excel DOM would somehow be simple, which, of course, defeated the goal of using EPPlus. Another thing you could do is to see the difference between the xml files before and after (and there is a bunch that you have to look at) and imitate the fact that Excel will modify / add manual editing of the contents in the "after" version of the file XML This is not a good option, depending on how extensive the changes are. You can see how I did it in other situations:

Create Pivot Table Filters with EPPLUS

Adding a specific autofilter in a column

Set grid color with EPPlus?

Answer 2

I ran into this problem using EPPlus (version 4.1.0, fyi) and found that I added the following code before closing the problem:

 p.Workbook.Calculate(); p.Workbook.FullCalcOnLoad = false;