Reading and Writing XLSX File with Openpyxl
Posted by: Zulfikar Akbar Muzakki | in Python | 3 years, 7 months ago | 0 comments

Openpyxl is a Python library used for manipulating Excel files. I came across a ticket that required exporting data to XLSX format, and I used Openpyxl for that as it’s pretty straightforward.

Reading an XLSX File

Reading a XLSX file is simple. First, we load the file into a Workbook object and choose the sheets. If the file contains only one sheet, we can use .active to get the sheet. Active will return the active sheet, and in the case when there’s only 1 sheet, that sheet will always be active.

from openpyxl import load_workbook

wb = load_workbook(filename = 'empty_book.xlsx')
sheet = book.active

We can get a cell value by entering a cell coordinate, which will be useful when we need a specific cell value.

print(sheet['D18'].value)

or by looping through the row, which can be used when we import data.

# min_row indicate the minimum row index that will be processed (1-based)
# min_col indicate the minimum column index that will be processed (1-based)
for row in sheet.iter_rows(min_row=2, min_col=1):
	print(row[2].value, row[3].value, row[4].value)

In this example, I only print the cell value but in a real case, we could literally do anything with those data.

Write to XLSX File

Writing to an XLSX File is an easy feat with Openpyxl. We need to first create a workbook object before finally saving it to the desired destination. This example creates a workbook containing two worksheets.

from openpyxl import Workbook

def export_excel():
    wb = Workbook()
    dest_filename = 'empty_book.xlsx'
    
    # Get first sheet
    ws1 = wb.active
    ws1.title = "Range"
    
    # Write data to cell
    for row in range(1, 40):
        # append() will append iterables, which will assign each element to each cell.
    	ws1.append(range(10))
    
    # Create second sheet
    ws2 = wb.create_sheet(title="Phi")
    ws2['F5'] = 3.14
    
    
    ws3 = wb.create_sheet(title="Data")
    for row in range(10, 20):
        for col in range(27, 54):
            # Fill the cell with the column letter
            ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
    
    # Save the workbook
    wb.save(filename=dest_filename)

We must call save() at the end to finally export the workbook, otherwise the workbook will exist only as an object.

Those are simple examples to write XLSX to file. For more complex formatting such as style, you can read the detailed documentation here: https://openpyxl.readthedocs.io/en/stable/index.html

Returning an XLSX File in Django

It's more often that what we want to achieve in our Django app is not simply saving the XLSX file, but to return it the as a request’s reponse. The way of creating workbook object is basically the same, but now we save it to a response object. We'll use the previous code to write XLSX file with some updates.

First, add this import.

from django.http import HttpResponse

In the previous code, we save the workbook to a file. Now, we will save it to a response object. We must explicitly set the content type of the response, which is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. Note that the value is predefined, so for an XLSX file the content type will always be the same and you cannot change it. Using a different content type might make the browser falsely think it is a different media type. We also need to set the content disposition to attachment to make the response be downloaded instead of displayed on the browser, and provide the filename for the file.

Now, replace wb.save(filename=dest_filename) this code and remove dest_filename = 'empty_book.xlsx' as we won't need it anymore.

# Set the content type of XLSX file 
response = HttpResponse(
    content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
)
# Set the content disposition as attachment and provide the filename
response['Content-Disposition'] = 'attachment; filename=export.xlsx

And finally, save the workbook to the response object and return it with

workbook.save(response)
return response

So now, our code looks like this

from django.http import HttpResponse
from openpyxl import Workbook

def export_excel():
    wb = Workbook()
    
    # Get first sheet
    ws1 = wb.active
    ws1.title = "Range"
    
    # Write data to cell
    for row in range(1, 40):
        # append() will append iterables, which will assign each element to each cell.
    	ws1.append(range(10))
    
    # Create second sheet
    ws2 = wb.create_sheet(title="Phi")
    ws2['F5'] = 3.14
    
    
    ws3 = wb.create_sheet(title="Data")
    for row in range(10, 20):
        for col in range(27, 54):
            # Fill the cell with the column letter
            ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
    
    # Save the workbook
    response = HttpResponse(
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    )
    # Set the content disposition as attachment and provide the filename
    response['Content-Disposition'] = 'attachment; filename=export.xlsx
    workbook.save(response)
    return respons

Conclusion

Using Openpyxl to read and write XLSX file is pretty easy, and will basically cover most of our requirements. Now, you can try to read and write simple XLSX then move on to read and write XLSX with more complex formatting.

Current rating: 4

Comments

There are currently no comments

New Comment

required

required (not published)

optional

required

Have a question? Get in touch!