0

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.

Dominique
  • 16,450
  • 15
  • 56
  • 112
Pulath Yaseen
  • 395
  • 1
  • 3
  • 14
  • The module `xlwt` creates `.xls` files. That's a pretty obsolete format. A bit of Googling will find you better alternatives. But if you insist on using it, when your code calls `write()` to put the data in a cell, it can supply the desired format in the same call. I don't understand why you think a loop presents a problem. Your code will have to loop over the incoming data to add it to the spreadsheet using calls to `write()`. – BoarGules Jun 17 '22 at 09:18
  • Data will be entered after the user download the excel that only has titles, and later he uploads the excel that's filled with data to the server. So there won't be the `write()` function. – Pulath Yaseen Jun 17 '22 at 09:42
  • Also, I know that `xlwt` only supports `.xls` (recently). I was thinking about using `openpyxl ` instead. – Pulath Yaseen Jun 17 '22 at 09:47
  • I think you need to explain the process better. "Data will be entered": by whom? If it is your program, that program will have to call `write()`. How else will it get the data into the file? If it is not your program, where does `xlwt` come into the picture? And if the data is going to a database, why do you care about the format anyway? All numbers in Excel are floats. The commas are just formatting. If you are proposing to store numeric values in the database as strings with embedded commas, then I think you should reconsider. – BoarGules Jun 17 '22 at 10:10
  • Sorry for the confusion. I will explain with an example, I have developed a function to create an excel that contains only titles. so the clients download the excel and use their own google sheet or any other ways to update the sheet and fill in their data. When they fill in the data, they might enter `10000` in the Rent column and I need it to be shown as `10,000.00`. I have tried setting the format of a cell using `xlwt`, but what I need to do is set the format for the entire column. – Pulath Yaseen Jun 17 '22 at 10:27
  • You need a more sophisticated library module that allows you to read an existing file, find out how many rows it has and loop through them. I don't know how you got the impression that `xlwt` can do what you want. Look at `openpyxl` (can handle a large subset of Excel functionality) or `xlwings`/`pythoncon` (can do everything that Excel can, but only because they get Excel to do it for them). – BoarGules Jun 17 '22 at 10:43

1 Answers1

0

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.

BoarGules
  • 16,440
  • 2
  • 27
  • 44