From time to time I find myself needing to use different tools to organize data. During the beginning of the COVID-19 pandemic, I needed to automate the creation of spreadsheets with data of the previous day’s COVID-19 cases. This led me to a Python module called XlsxWriter.
XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format. It can be used to write text, numbers, and formulas to multiple worksheets and it supports features such as formatting, images, charts, page setup, auto filters, conditional formatting, and many others.
One thing to note, XlsxWriter cannot read or modify existing Excel XLSX files but supports more features than any of the alternative modules. In most cases, the files produced are 100% equivalent to files that were produced by Excel.
Extensive documentation is available for this module that covers everything from installation to working with VBA Macros and Pandas. There are also several examples sprinkled throughout the documentation to help you get started.
Learning new Python modules can be quite the task, so I am going to show you the simplest format that I have came up with to get you quickly started in creating a Workbook for Excel.
Before we can get started, you will need to install the XlsxWriter module:
pip install xlsxwriterNow we are ready to move into the fun stuff, the code. Within the code, we will be creating a sample Workbook for Expenses that is complete with cell formatting and calculation.
#!/usr/bin/python3
import xlsxwriter
from datetime import datetime
def create_workbook(filename, expenses):
# Create a Workbook with the Filename that was passed
workbook = xlsxwriter.Workbook(filename + '.xlsx')
# Add a Sheet to the Workbook
worksheet = workbook.add_worksheet('Home Expenses')
# Add Speadsheet Bold Format
bold_text = workbook.add_format({'bold': True})
# Add Speadsheet Money Format
money_format = workbook.add_format({'num_format': '$#,##0'})
# Add a Spreadsheet Date Format
date_format = workbook.add_format({'num_format': 'MMMM DD, YYYY'})
# Create bold Headers in Spreadsheet
worksheet.write('A1', 'Item', bold_text)
worksheet.write('B1', 'Date', bold_text)
worksheet.write('C1', 'Cost', bold_text)
# Adjust Item column width to fit the Item
worksheet.set_column(0, 0, 20)
# Adjust Date column width to fit Date
worksheet.set_column(1, 1, 20)
# Start with Row 1, Column 0
row, col = 1, 0
# Iterate through Nested Dictionary of expenses to get values
for key, value in expenses.items():
# Convert the Date string into a datetime object
date = datetime.strptime(value['date'], "%Y-%m-%d")
# Add the Item to the first Column
worksheet.write(row, col, value['item'])
# Add the Item Date to the second Column
worksheet.write(row, col + 1, date, date_format)
# Add the Item Cost to the third Column
worksheet.write(row, col + 2, value['cost'], money_format)
# Increment to the next Spreadsheet row
row += 1
# Add a row to calculate the Total
worksheet.write(row + 1, 0, 'Total:', bold_text)
# Use the SUM function to calculate the total cost
worksheet.write_formula(row + 1, 2, "=SUM(C2:C8)", money_format)
# Close and save the Workbook
workbook.close()
if __name__ == "__main__":
# A Nested Dictionary of Items to be added to the Spreadsheet
expenses = {
1: {"item": "Home Mortgage", "date": "2021-08-03", "cost": 350},
2: {"item": "Cell Phone", "date": "2021-08-12", "cost": 45},
3: {"item": "Car Insurance", "date": "2021-08-15", "cost": 35},
4: {"item": "Electricity", "date": "2021-08-25", "cost": 400},
5: {"item": "Cable TV/Net", "date": "2021-08-30", "cost": 210},
6: {"item": "Water PSD", "date": "2021-08-30", "cost": 80},
7: {"item": "Trash Pickup", "date": "2021-08-30", "cost": 16}
}
# Create Workbook with 'MyExpenses' as filename and pass the Dictionary
create_workbook("MyExpenses", expenses)