xlsxwriter Cheat Sheet

xlsxwriter_cheatsheet.py

# xlsxwriter Cheat Sheet
#
# xlsxwriter is an awesome package for writing Excel spreadsheets. It's got excellent documentation. And it's got
# way more functionality than I need. This cheat sheet contains the basic functionality that I use all the time.
#
# While it is possible to put formulas in the spreadsheet, I have had some problems with spreadsheets as email attachments
# when the spreadsheet has formulas and the email client is on mobile.

import xlsxwriter

# Make a workbook
workbook = xlsxwriter.Workbook('ad_hoc_taxes_report.xlsx')

# Add some formats
title = workbook.add_format({'bold': True, 'font_size': 14})
header = workbook.add_format({'bold': True})
money = workbook.add_format({'num_format': 0x07})  # Builtin code for ($#,##0.00_);($#,##0.00)
date_format = workbook.add_format({'num_format': 'mm/dd/yyyy'})

# Add a worksheet
main_worksheet = workbook.add_worksheet(name='summary')

# Merge the top row for the title
# Merging cells: http://xlsxwriter.readthedocs.io/example_merge1.html?highlight=merge_range
first_row = 0
first_col = 0
last_row = 0
last_col = 3
main_worksheet.merge_range(first_row, first_col, last_row, last_col, 'Ad Hoc Tax Report', title)

# Set the width of the first 2 cols
cols = 'A:B'
main_worksheet.set_column(cols, 25)  # default width is 11 = 0.83"

# Add column headers
row = 3
col = 0
main_worksheet.write(row, col, 'Customer', header)
main_worksheet.write(row, col + 1, 'Taxes', header)
row += 1

# Write some data
for job in jobs:
    main_worksheet.write(row, 0, job)
    main_worksheet.write(row, 1, taxes[job], money)
    row += 1

    # Add another worksheet for that job
    job_worksheet = workbook.add_worksheet(name=job)
    job_row = 3
    for ticket in tickets[job]:
        job_worksheet.write(job_row, 0, 'some data')
        job_worksheet.write(job_row, 1, 'more data')
        job_row += 1

workbook.close()