如何阻止 Excel 使用 XlsxWriter 以科学记数法显示数字? [英] How can I stop Excel from displaying numbers in scientific notation using XlsxWriter?

问题描述

我正在使用 Pandas 将数据导出到 XLSX，而 Pandas 又使用 XlsxWriter.我已经尝试了我发现的格式选项(例如 float_format，尽管它似乎是基于源的错误答案)，但我找不到任何阻止 Excel用科学记数法解释数字.

I'm exporting data to XLSX using pandas, which in turn is using XlsxWriter. I've experimented with the formatting options I've found (e.g. float_format, despite the fact that it seemed like the wrong answer based on the source), but I can't find anything that prevents Excel from interpreting numbers in scientific notation.

例如我写0.000000000001，Excel中底层单元格值为0.000000000001，但显示值为1E-12.我知道我可以将数字写成字符串，但我希望 Excel 中的值是数字.

For example, if I write 0.000000000001, the underlying cell value in Excel is 0.000000000001, but the display value is 1E-12. I know that I can write the number as a string, but I'd like the value to be numeric in Excel.

推荐答案

您无法阻止 Excel 以它认为合适的任何格式显示未格式化的数字.保证格式的唯一方法是为数据指定数字格式.

You cannot stop Excel from displaying an unformatted number in whatever format it thinks is suitable. The only way to guarantee the format is to specify a numerical format for the data.

不幸的是，目前在 XlsxWriter 和 Pandas 中无法做到这一点.

Unfortunately there is currently no way to do this in XlsxWriter and or, as a result, in Pandas.

待办事项列表上有这样的功能，但它可能不会暂时可用.

A feature such as this is on the TODO list however it probably won't be available for a while.

作为一种解决方法，我建议获取对基础工作簿和工作表的引用，并使用来自 Pandas 数据帧和 XlsxWriter 格式的相同数据覆盖您希望格式化的任何单元格.稍后我会更新一个示例来说明如何做到这一点.

As a workaround I recommend getting a reference to the underlying workbook and worksheet and overwriting any cells that you wish to be formatted with the same data from the Pandas dataframe and a XlsxWriter format. I'll update later with an example of how to do that.

免责声明:我编写了 XlsxWriter 和 Pandas/XlsxWriter 集成代码.这里的问题确实与 XlsxWriter 限制有关，而不是 Pandas.

Disclaimer: I wrote XlsxWriter and the Pandas/XlsxWriter integration code. The issue here is really to do with XlsxWriter limitations and not Pandas.

这篇关于如何阻止 Excel 使用 XlsxWriter 以科学记数法显示数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！