The XlsxWriter library of Python is a nice gem for a VBA developer – you would be really interested to see the problems a Python developer should be facing, just to achieve a simple part Excel automatization. Still, these are the advantages and the disadvantages of the library:
Advantages:
- It has a high degree of fidelity with files produced by Excel. In most cases the files produced are 100% equivalent to files produced by Excel.
- It has extensive documentation, example files and tests.
- It is fast and can be configured to use very little memory even for very large output files.
- It supports more Excel features than any of the alternative modules.
Disadvantages:
- It cannot read or modify existing Excel XLSX files.
Pretty much, it creates xlsx files every time you need them. You do not need to have Excel installed, which is quite ok, if you are a Linux user 🙂 Writing to an Excel spreadsheet is a piece of cake, if you are coming from the VBA world. The structure is the same – Workbook contains Worksheets. Worksheet contain ranges and cells. Ranges contain cells. Pretty much this is it. If you want to install the library, simply write pip install XlsxWriter in the command prompt/terminal.
Then, to refer to the cells you may use the A1 or the RowCol notation. I have created a small example, which adds “Hello World” to a worksheet. Then it loops from 1 to 1000 and writes different values on the frist 3 columns. Like this:
This is the beautiful code, available also in GitHub:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
#pip install XlsxWriter import xlsxwriter from xlsxwriter.utility import xl_rowcol_to_cell wbk = xlsxwriter.Workbook('hello.xlsx') wks = wbk.add_worksheet() wks.write('A1', 'Hello world') wks2 = wbk.add_worksheet() i = -1 for x in range(1, 1000, 11): i+=1 cella = xl_rowcol_to_cell(i, 0) #0,0 is A1! cellb = xl_rowcol_to_cell(i, 1) cellc = xl_rowcol_to_cell(i, 2) print cella wks2.write(cella,x) wks2.write(cellb,x*3) wks2.write(cellc,x*4.5) wbk.close() |
Cheers!