KartozaOpenpyxl 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.
Share on Twitter Share on Facebook
Comments
There are currently no comments
New Comment