Pandas set cell format to excel writer

Question

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

123456789012345

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

1.23e14

So my question is: is there a way to set the number format in pandas? I would like to set it to "@", that is, to openpyxl.styles.NumberFormat.FORMAT_TEXT .

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

Thank you for your help.

Answer 1

I think in the worst case, you can directly manipulate the cells. I do not 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