提示保存使用 EPPlus 创建的文件的更改

问题描述

我正在使用 EPPlus v3.1.3 创建一系列 Excel 工作簿.当我打开新创建的文件时，如果我关闭它而不触及任何东西，它会询问我是否要保存我的更改.如果我说是"，我注意到的唯一变化是 app.xml 文件略有改变——工作簿中没有明显的差异，其余的 XML 文件是相同的.这两种方法我都试过了:

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();

还有

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

两者都有同样的问题.有没有办法以最终形式输出 app.xml 文件?

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

这是一个问题的原因是因为我们使用 SAS 程序进行 QC，当 SAS 程序打开文件时，因为它们是直接从 EPPlus 程序输出的，它不会从具有公式的单元格中获取值在他们之中.如果打开它并且为是否要保存更改"选择是"，它工作正常.但是，由于我们要创建数百个这样的，这是不切实际的.

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.

有没有办法解决这个问题?在 C# 或 SAS 端?

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

推荐答案

你看到的其实并不少见.Epplus 实际上并不生成完整的 XLSX 文件，而是创建原始 XML 内容(所有 office 2007 文档格式都是基于 xml 的)并将其放置在重命名为 XLSX 的 zip 文件中.由于它没有通过 Excel 引擎运行，它还没有完全格式化为喜欢的 Excel.

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.

如果它是一个简单的数据表，那么 Excel 可能不需要做太多计算 - 只需基本格式.因此，在这种情况下，它不会提示您保存.但即便如此，如果你这样做，你会看到它稍微改变了 XLSX 文件.如果你真的想看看它在幕后做了什么，将文件重命名为 .zip 并查看前后的 xml 文件.

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.

您遇到的问题是因为它不仅仅是一个简单的表格导出 Excel 在第一次打开时必须运行计算.这可能是很多东西 - 公式，自动过滤器，自动列/行高度调整，大纲等.基本上，在 Excel 完成后任何会使工作表看起来有点不同"的东西.

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.

不幸的是，没有简单的解决方法.以某种方式通过 excel 的 DOM 运行它是最简单的，这当然违背了使用 EPPlus 的目的.您可以做的另一件事是查看 xml 文件之前和之后之间的差异(并且您必须查看其中的一堆)并模仿 excel 在之后"文件版本中更改/添加的内容手动编辑 XML 内容.这不是一个非常漂亮的选择，具体取决于更改的范围.你可以在这里看到我在其他情况下是如何做到的:

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:

使用 EPPLUS 创建数据透视表过滤器

在列上添加特定的自动过滤器

使用 EPPlus 设置网格线颜色?

这篇关于提示保存使用 EPPlus 创建的文件的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！