xlsxwriter 和 LibreOffice 没有显示公式的结果 [英] xlsxwriter and LibreOffice not showing formula's result

问题描述

我正在尝试使用简单的公式创建 Excel 文件:

I'm trying to create an Excel file with a simple formula:

import xlsxwriter
workbook = xlsxwriter.Workbook('testxlsx.xlsx', {'strings_to_numbers': True})
ws = workbook.add_worksheet()

ws.write('A2', 'Number one')
ws.write('B2', '1')
ws.write('A3', 'Number two')
ws.write('B3', "1000")
ws.write('A4', "Number three")
ws.write('B4', "1050")
ws.write('A5', "Number four")
ws.write('B5', "3")
ws.write('A6', "Result")
ws.write('B6', '=IF(B5=3,ROUND(100-(B3/B4*100),1),ROUND(100-(B3/(B4*1.502)*100),1))')

workbook.close()

生成的文件在 Excel 中运行良好，但在 LibreOffice Calc 中打开时，不会计算公式.我需要重新输入数值，然后它才能工作.

The generated file works perfectly in Excel, but when opened in LibreOffice Calc the formula is not evaluated. I need to reenter the numeric values and then it works.

我做错了什么?

推荐答案

来自 xlsxwriter docs:

From the xlsxwriter docs:

XlsxWriter 不计算公式的结果，而是将值 0 存储为公式结果.然后它会在 XLSX 文件中设置一个全局标志，以说明在打开文件时应重新计算所有公式和函数.这是 Excel 文档中推荐的方法，通常它适用于电子表格应用程序.但是，不具备计算公式功能的应用程序(例如 Excel 查看器)或某些移动应用程序只会显示 0 结果.

XlsxWriter doesn’t calculate the result of a formula and instead stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened. This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don’t have a facility to calculate formulas, such as Excel Viewer, or some mobile applications will only display the 0 results.

至于为什么重新计算不会自动发生，来自 ask.libreoffice.org 答案:

As for why the recalculation doesn't automatically occur, from an ask.libreoffice.org answer:

LibreOffice 有意不重新计算旧电子表格，因为随着公式在不同版本之间或在不同电子表格程序之间更新，结果可能会有所不同.转到工具 - 选项 - LibreOffice Calc，在文件加载时重新计算"下，将两个下拉菜单Excel 2007 及更高版本"和ODF 电子表格(LibreOffice 未保存)"更改为始终重新计算".单击确定，关闭电子表格和 LibreOffice.现在在 LibreOffice 中打开文件，您应该看到公式已重新计算.

LibreOffice intentionally does not recalculate older spreadsheets, because as formulas are updated from version to version or between different spreadsheet programs, the results can be different. Go to Tools – Options – LibreOffice Calc, under 'Recalculation on file load', change the two drop-downs, 'Excel 2007 and newer' and 'ODF Spreadsheet (not saved by LibreOffice)', to 'Always recalculate'. Click Ok, close the spreadsheet and LibreOffice. Now open the file in LibreOffice and you should see that the formulas have recalculated.

还要转到工具 - 单元格内容并确保自动计算是已选中.

Also go to Tools – Cell Contents and be sure that AutoCalculate is selected.

我已确认设置始终重新计算"将不再有效.或提示"为我工作.或者，您可以随时按下 control-shift-F9.

I've confirmed that setting "always recalculate" or "prompt" worked for me. Alternatively, you can always hit control-shift-F9.

这篇关于xlsxwriter 和 LibreOffice 没有显示公式的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！