设置 xlsx 以在打开时重新计算公式 [英] Set xlsx to recalculate formulae on open

问题描述

我正在生成 xlsx 文件，并且不想在此过程中计算所有公式的值.也就是说，对于带有 的单元格，我想将 设置为 0(或省略它)，并让 Excel 填充值它被打开了.

I am generating xlsx files and would like to not have to compute the values of all formulae during this process. That is, I would like to set <v> to 0 (or omit it) for cells with an <f>, and have Excel fill in the values when it is opened.

一个建议是在启动时运行一个宏Calculate，但一直无法找到有关如何使用签名宏执行此操作以避免提示用户的完整指南.您可以在 xlsx 中的某处设置一个标志会好得多.

One suggestion was to have a macro run Calculate on startup, but have been unable to find a complete guide on how to do this with signed macros to avoid prompting the user. A flag you can set somewhere within the xlsx would be far better.

我不是在寻找涉及使用 Office 程序进行更改的答案.我正在寻找文件格式的详细信息.

推荐答案

Python 模块 XlsxWriter 将公式 值设置为 0(除非实际值已知)，并且 fullCalcOnLoad 属性为 true在 xl/workbook.xml 文件中:

The Python module XlsxWriter sets the formula <v> value to 0 (unless the actual value is known) and the <calcPr> fullCalcOnLoad attribute to true in the xl/workbook.xml file:

<calcPr fullCalcOnLoad="1"/>

这适用于我测试过的所有 Excel 和 OpenOffice、LibreOffice、Google Docs 和 Gnumeric 版本.

This works for all Excel and OpenOffice, LibreOffice, Google Docs and Gnumeric versions that I have tested.

它不起作用的地方是无法重新计算公式值的非电子表格应用程序，例如文件查看器.

The place it won't work is for non-spreadsheet applications that cannot re-calculate the formula value such as file viewers.

这篇关于设置 xlsx 以在打开时重新计算公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！