Formatting integers with comma separator using openpyxl and to_excel

Question

I am writing DataFrames to excel using to_excel(). I need to use openpyxl instead of XlsxWriter, I think, as the writer engine because I need to open existing Excel files and add sheets. Regardless, I'm deep into other formatting using openpyxl so I'm not keen on changing.

This writes the DataFrame, and formats the floats, but I can't figure out how to format the int dtypes.

import pandas as pd
from openpyxl import load_workbook

df = pd.DataFrame({'county':['Cnty1','Cnty2','Cnty3'], 'ints':[5245,70000,4123123], 'floats':[3.212, 4.543, 6.4555]})

fileName = "Maryland - test.xlsx"
book = load_workbook(fileName)
writer = pd.ExcelWriter(fileName, engine='openpyxl')
writer.book = book

df.to_excel(writer, sheet_name='Test', float_format='%.2f', header=False, index=False, startrow=3)
ws = writer.sheets['Test']

writer.save()
writer.close()

Tried using this, but I think it only works with XlsxWriter:

intFormat = book.add_format({'num_format': '#,###'})
ws.set_column('B:B', intFormat)

This type of thing could be used cell-by-cell with a loop, but there's A LOT of data:

ws['B2'].number_format = '#,###'

Answer 1

This can be fixed by using number_fomat from openpyxl.styles

from openpyxl.styles import numbers

def sth():
   #This will output a number like: 2,000.00
   cell.number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1

Checkout this link for further reading thedocs