与Office的“开放"合作， XML-到底有多难? [英] Working with Office "open" XML - just how hard is it?
问题描述
我正在考虑将(很大)的Office自动化代码替换为直接与Office XML格式兼容的东西.我只是刚开始，但已经担心它太大了.
I'm considering replacing a (very) large body of Office-automation code with something that works with the Office XML format directly. I'm just starting out, but already I'm worried that it's too big a task.
我将处理Word，Excel和PowerPoint.到目前为止，我只研究了Word和Excel.看起来Word文档应该相当容易操作，但是Excel工作簿看起来像一场噩梦.例如...
I'll be dealing with Word, Excel and PowerPoint. So far I've only looked at Word and Excel. It looks like Word documents should be reasonably easy to manipulate, but Excel workbooks look like a nightmare. For example...
在Word中，看起来您只需删除相应的"w:p"标签就可以删除段落.但是，提供的用于在Excel中删除一行的代码段大约需要150行代码(！).
In Word, it looks like you could delete a paragraph simply by deleting the corresponding "w:p" tag. However, the supplied code snippet for deleting a row in Excel takes about 150 lines of code(!).
Excel代码如此之大的原因是，删除一行意味着更新所有后续行的行索引，修复共享字符串"表等.根据顶部的注释，该代码段为甚至还不完整，因为它不会处理其中有表格的工作簿(我可以接受).
The reason the Excel code is so big is that deleting a row means updating the row indexes of all the subsequent rows, fixing up the "shared strings" table, etc. According to a comment at the top, the code snippet is not even complete, in that it won't deal with a workbook that has tables in it (I can live with that).
我尚不清楚的是，这是否是示例代码的唯一限制.例如，如果工作簿包含数据透视表，是否还会有问题?还是引用同一工作表中数据的图表?还是一些命名范围?您是否还不必更新引用行索引已更改的行的任何单元格(等)的公式?
What I'm not clear on is whether that's the only restriction that the sample code has. For example, would there also be a problem if the workbook contained a Pivot Table? Or a chart that references data from the same sheet? Or some named ranges? Wouldn't you also have to update the formulae for any cells (etc.) that referenced a row whose row index had changed?
[更不用说"calc chain"，(谢天谢地)我认为您可以删除它，因为它只是可以重建的一个chache.]
[That's not to mention the "calc chain", which (thankfully) I think you can simply delete since it's only a chache that can be re-built.]
这是我的问题，尽管确实如此.您需要付出多大的努力才能做一些像正确删除行一样简单的事情?这是一项无法克服的任务吗?
And that's my question, woolly though it is. Just how hard do you have to work do something as simple as deleting a row properly? Is it an insurmountable task?
此外，如果Excel或Word或PowerPoint还有其他类似的问题，我很想现在就听听这些问题，然后再浪费太多时间在盲目的小巷里走.谢谢.
Also, if there are other, similar issues either with Excel or with Word or PowerPoint, I'd love to hear about them now, before I waste too much time going down a blind alley. Thanks.
推荐答案
使用Open XML SDK 2.0已有近两年的时间，我可以说，完成看似微不足道的任务可能要花费数小时甚至数天的时间才能弄清楚如何正确地做.例如，删除Excel行应相当简单明了，易于执行，对吗?不，因为您不仅需要代码来删除行，而且还必须更新所有行索引，更新任何合并的单元格引用，更新超链接引用等.我们的内部删除方法仅需删除约500行代码即可连续，我敢肯定我们没有将所有案件都考虑在内.
Having worked with the Open XML SDK 2.0 for almost two years now I can say that doing seemingly trivial tasks can take many hours and sometimes days to figure out how to do it properly. For example, deleting an Excel row should be fairly straightforward and easy to do right? Nope because not only do you need code to delete your row, but then you have to update all the row indices, update any merged cell references, update hyperlink references, etc. Our internal delete method is close to 500 lines of code to just delete a row and I'm sure we don't have all the cases accounted for either.
我最大的抱怨是缺乏有关如何执行最常见任务的文档. Open XML SDK上的 MSDN 部分非常有限，无论何时您需要做任何复杂的事情，而您实际上是一个人.我不得不阅读 Open XML 标准弄清楚某些元素的含义以及应该如何实施这些元素，因为我在网上几乎找不到.
The biggest complaint I have is the lack of documentation on how to do the most common tasks. The MSDN section on the Open XML SDK is very limited and whenever you need to do anything complicated you are really on your own. I've had to read the Open XML standard a lot to figure out what certain elements mean and how they should be implemented since I could find very little online.
另一个具有挑战性的部分是，如果将元素插入不属于该元素的位置或在元素上放置无效属性，则在尝试打开它时会得到损坏的文件.在大多数情况下，您不会获得有关导致错误的原因的任何信息，您将不得不查看Open XML标准规范以了解所做的错误.
The other challenging part is if you insert an element in a spot where it doesn't belong or put an invalid attribute on an element you will get a corrupt file when you try and open it. Most of the time you will not get any information on what caused the error and you will have to look at the Open XML standard spec to see what you did wrong.
如果您需要快速的周转时间将Office自动化代码转换为Open XML，而您所做的并不是真正的基础，那么我会说通过".如果您有时间和耐心地阅读Word，Excel和PowerPoint XML结构并熟悉它们之间的关系，那么我就继续说下去.我认为，这实际上是对这些Office文档进行非常精细控制的唯一方法，但是当您开始学习时，将会遇到很大的学习困难.
If you need a fast turnaround time on converting that Office automation code into Open XML and what you are doing is not really basic, then I would say pass. If you have time and the patience to read up on the Word, Excel and PowerPoint XML structures and get familiar with how they relate then I say go for it. In my opinion it is really the only way to have very fine control over these office documents, but there will be a great learning curve when you start.
Oh and just for fun here is how much code is needed to add a comment to an Excel cell.
这篇关于与Office的“开放"合作， XML-到底有多难?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
