时间戳

首页 > 解决方案 > Open XML Excel 共享公式不支持某些公式

.net - Open XML Excel 共享公式不支持某些公式

问题描述

Excel 不支持预期的共享公式（由 OpenXML SDK https://www.microsoft.com/en-us/download/details.aspx?id=30425创建）：

打开或保存文件时引发错误，有时会出现以下错误：“一个或多个公式...超过允许的 8192 个字符的限制”

另一方面，以下共享公式可以正常工作：

XML 结构如下所示：

B2: <c r="B2"><f t="shared" ref="B2:B3" si="0">VLOOKUP(A2,Sheet2!A:B,2,0)</f><v></v></c>
B3: <c r="B3"><f t="shared" si="0"></f><v></v></c>

用美元替换公式，它会起作用。通过结构化引用，它不会，等等。

通过 Excel UI 使用此类公式时，扩展公式将导致共享公式或不导致共享公式，具体取决于是否支持该公式（我想）。问题是应该支持所有公式，否则应该在某处记录。

当公式是共享公式（t 值是共享的）时，此值指示此特定单元格的公式所属的组。一组共享公式中的第一个公式保存在 f 元素中。这被认为是“主”公式单元格。共享此公式的后续单元格不需要在其 f 元素中写入公式。相反，特定单元格的属性 si 值用于根据单元格与主公式单元格的相对位置来确定公式表达式应该是什么。

参考：https ://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellformula.aspx

正如其他人经历过的那样？

标签： .netexcelopenxml

解决方案

在与 Microsoft 支持人员进行了长时间的交谈后，他们的回答是：

首先，即使我们清楚地理解共享公式会更容易，但 Excel 的工作方式并非如此。Excel 本身不会创建您尝试创建的共享公式。如果您在 Excel 中输入相同的公式，填写并保存，您将获得存储的重复公式，而不是共享公式。

（这是真的，但 Excel 在它们工作时也会创建共享公式，我的问题是并非所有公式都有效。）

错误很明显，Excel 将无法使用它，这在 2007 年及更高版本的所有 Excel 版本中都是一致的。在这种情况下，作为一种解决方法，您应该模仿 Excel 并将公式复制到工作表中，就像 Excel 所做的那样，它可以正常工作。

我怎么想事先知道 Excel 是否支持该公式？微软甚至没有提出一个官方库来解析 Excel 公式。我们可以使用看起来相当可靠的https://github.com/spreadsheetlab/XLParser，但使用共享公式的重点不是解析公式，而是让 Excel 来做。

可以更改此行为的唯一方法是对 Excel 的行为进行设计更改，但这样做会破坏整个向后兼容性，直到 2007 版本。这不是我们的产品组有兴趣改变的事情。

因此，当保存带有奇怪错误消息的文件时，Excel 会不断崩溃...

当我在内部进行验证以更好地回答您的问题时，我的升级团队现在明确确认，关于文件格式处理中的详细信息的讨论，或所述共享公式在 Excel 中的内存处理不会改变结果。事实上，在某些情况下，由于许多不同的原因无法使用共享公式，其中一些可能在 Excel 外部不明显和可见。最好的经验法则是已经概述的：当 Excel 本身不使用共享公式时，不要使用共享公式。这需要考虑到公式中的细微差异，是否存在绝对或相对引用（如您的示例中的 A:B vs $A:$B），引用是否引用相同的工作表或另一个一个，或在不同的工作簿等，然后还有它用于哪种公式（某些函数，如 OFFSET 或 INDIRECT 依赖于在计算过程中可能发生变化的范围），以及先例或依赖范围/公式是否表明不使用共享公式可能更安全. 文件格式文档没有明确说明在什么条件下可以使用共享公式。它指定文件阅读器可能会遇到此类公式，以及发生这种情况时如何处理，但这并不意味着只要连续的单元格区域在公式栏中包含相同的公式，共享公式总是可能的或被鼓励的。对于文件编写器，不鼓励使用它，除非该方案被限制为与 Excel 编写共享公式时完全相同。

尽管如此，LibreOffice 还是完美地处理了它。看起来像是“修复太复杂”或“修复错误太冒险”的借口。

由于他们建议我创建一个用户语音条目，您可以在那里投票：

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/37328965-full-support-for-shared-formula-matching-the-open

推荐阅读