Creating Excel Sheets in Python

I cannot believe I’m posting this, but anyway, this is about creating Excel files (.xlsx) in Python 😂 (see also my earlier post about avoiding using Excel 👍)

import xlsxwriter

my_data = [
    ["one", "two", "three"],
    ["four", 5, "6"],
    [0, "test", "some text"],
    ["here", "as", "well"],
]

workbook = xlsxwriter.Workbook("my_data.xlsx")
worksheet = workbook.add_worksheet()
for row_num, row_data in enumerate(my_data):
    if row_num == 0:
        worksheet.write_row(
            row_num,
            0,
            row_data,
            workbook.add_format({"bold": True}),
        )
    else:
        worksheet.write_row(row_num, 0, row_data)
worksheet.freeze_panes(1, 0)
worksheet.autofilter(0, 0, 0, len(row_data)-1)
worksheet.set_column(2, 2, 30)
workbook.close()

This is what it outputs as Excel sheet:

Let’s see what we have here in the code. First, this is using xlsxwriter package. It includes everything I need to create the Excel files I somehow need to supply to my managers or customers.

my_data contains list of lists, so I have the data nicely organized right away. In the for loop I use enumerate() to get the row number and the actual row that is the list of data. Row number is here only used for changing the formatting of the first row (indexing starts at zero in rows and columns).

Inside the loop I’m using worksheet.write_row() to write the full row at once. It first takes the row&column arguments, and the column is here 0 to start the output from the first cell of each row. If you only want to write single cell (for example because your data is processed different way) you can use worksheet.write() to output only one specific cell.

write_row() and write() use the types of the actual Python data elements to create the cells with correct data. For example, 5 is saved as a number, but “6” is a string. If you want to enforce specific output you can also use specific methods like write_string() or write_datetime() as needed.

Useful tricks are demonstrated with freezing the top row with worksheet.freeze_panes() and creating autofilter for each column with worksheet.autofilter(), as well as resizing the third column with worksheet.set_column().