Format a column in excel using xlwt to currency

Question

I need to create an excel file to upload data to the database. here I use xlwt to create the excel file.

I have a field that should be a currency, let's say "Rent". When the user downloads the excel and fills in the data, the cells under "Rent" should be auto formatted to 12,000,000.00 like format (that has thousand separator commas).

When downloading the data we won't have any idea of how many rows will be there, so we can't just add a loop in range n and add style for those cells.

I have found a similar question here

If there is a method in different packages, suggestions can be made.

Answer 1

What you want is done like this in VBA:

Range("E:F").NumberFormat = "#,##0.00"

The easiest way to achieve that from Python is to use the pythoncom module of Python Extensions for Windows to get Excel to do that for you. The module xlwings does the same thing, using pythoncom, but is an Excel-specific tool (which may have a shallower learning curve) whereas pythoncom will also let you drive from your Python code any other Windows application that offers a COM interface.

The drawback of this approach is that the application has to be installed on the machine where the Python code is running.

As a completely different approach, you might consider doing this inside Excel with a VBA macro.