pandas 在excel writer中设置单元格格式 [英] pandas set cell format in excel writer

问题描述

我正在使用熊猫通过python读取和写入excel文件(xlsx文件，使用openpyxl).我的部分数据是看起来像数字的文本.例如，在原始的excel文件中，我有一个单元格

I am using pandas to read and write excel files with python (xlsx files, using openpyxl). Part of my data is text that looks like numbers. For instance, in the original excel file, I have a cell

123456789012345

并以python中的文本形式很好地导入.但是，如果我再次写入excel文件，则输出单元格的格式为常规"，并显示为

and it imports fine as text in python. However, if I write the excel file again, the output cell has format 'General' and shows as

1.23e14

所以我的问题是:有什么方法可以设置熊猫的数字格式?我想将其设置为"@"，即 openpyxl.styles.NumberFormat.FORMAT_TEXT .

So my question is: Is there any way to set the number format in pandas? I would like to set it to '@', i.e., to openpyxl.styles.NumberFormat.FORMAT_TEXT.

不幸的是，pandas.ExcelWriter的文档不是那么高级(即，它不存在).

Unfortunately, the documentation of pandas.ExcelWriter is not so advanced (i.e., it does not exist).

谢谢您的帮助.

推荐答案

我认为在最坏的情况下，您可以直接操作单元格.我没有安装Excel，但是也许您可以检查一下是否可行.

I think in the worst case you can manipulate cells directly. I don't have Excel installed but maybe you can check if this works.

In [67]: df=pd.DataFrame ([123456789012345])

In [68]: writer = pd.ExcelWriter ('e.xlsx', engine='openpyxl')

In [69]: df.to_excel (writer, 'sheet1')

In [70]: ws = writer.sheets['sheet1']

In [71]: ws['A1'].style.number_format.format_code='@'

In [72]: writer.save ()

In [73]: pd.read_excel ('e.xlsx')
Out[73]: 
                 0
0  123456789012345

这篇关于 pandas 在excel writer中设置单元格格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！