Python and Excel: Writing Data Using XlsxWriter

Handles chart creation, formatting, and much more

XlsxWriter allows you to format and graph data, turning a large sheet of plain numbers into a beautiful presentation to the end-user.

Important note: XlsxWriter will overwrite the existing Excel file. So you cannot have the file open when trying to write with XlsxWriter.

Assuming we have output data ready to export to Excel the following is the basic structure to write data

writer = pd.ExcelWriter(
outputFile, engine='xlsxwriter',
datetime_format='mm/dd/yyyy')
format_excel(writer, outputData, 'Sheet1', 'Table1')
writer.save()

The function format_excel will contain the nuts and bolts of XlsxWriter

def format_excel(writer, data, sheet, table): 
pd.DataFrame(
data, columns=dataColumns).to_excel(
writer, sheet_name=sheet, index=False)
    workbook = writer.book
worksheet = writer.sheets[sheet]
    # Plus 1 to account for headers   
numRows = len(data) + 1
    # Minus 1 because of zero indexing
numColumns = len(dataColumns) - 1

I create a Dataframe out of my output data and then send the Dataframe to an Excelwriter

Formatting

After creating a variable for workbook and worksheet, you can add different formats to your worksheet

positiveFormat = workbook.add_format({
'bold': 'true',
'font_color': 'red'
})
negativeFormat = workbook.add_format({
'bold': 'true',
'font_color': 'green'
})
currencyFormat = workbook.add_format({
'align': 'center',
'num_format': '$#,##0.00'
})
worksheet.conditional_format(1, 0, numRows, 0, {
'type': 'cell',
'criteria': '>',
'value': 0,
'format': positiveFormat
})
worksheet.conditional_format(1, 0, numRows, 0, {
'type': 'cell',
'criteria': '<',
'value': 0,
'format': negativeFormat
})
worksheet.set_column(0, numColumns, 12)
worksheet.set_column('C:C', 15, currencyFormat)

For a full list of format options, go to the section Format methods and Format properties below to see more

Based on particular cell values, I use a conditional format to change particular cells. The parameters are (first_row, first_col, last_row, last_col, format).

I can use set_column to adjust column widths and number formats. The paramter can either be (first_col, last_col, width, format) or (col_range, width, format)

Example of different types of formatting

Creating Tables

The following code will create a table in the worksheet with a total row at the bottom of the data

# Plus 2 for header and total row
numRows = len(data) + 2
numColumns = len(outputColumns) - 1
tableRange = 'A1:' + string.ascii_uppercase[numColumns] + str(numRows)
headers = []
for column in dataColumns:
if column == 'Foo':
headers.append({'header': column, 'total_function': 'sum'})
else:
headers.append({'header': column})
if data:
worksheet.add_table(tableRange, {
'name': table,
'total_row': 1,
'columns': headers
})

The table range needs a starting cell and ending cell (such as A2:G10). Some conversion from integer to string was used to achieve this. Note this method can only handle 26 columns. To get AA and beyond, you’d need to divide the number of columns by 26 and get the remainder.

Creating Charts

This example created a pseudo-Gantt chart based on the steps below

chart = workbook.add_chart({'type': 'bar', 'subtype': 'stacked'})
categories = '=\'' + name + '\'!$A$2:$A$' + str(numRows)
beginValues = '=\'' + name + '\'!$B$2:$B$' + str(numRows)
durationValues = '=\'' + name + '\'!$C$2:$C$' + str(numRows)
chart.add_series({
'categories': categories,
'values': beginValues,
'fill': {
'none': True
}
})
chart.add_series({'categories': categories, 'values': durationValues})
chart.set_title({'name': name})
chart.set_x_axis({
'name': 'Hrs',
'minor_gridlines': {
'visible': True,
}
})
chart.set_y_axis({
'reverse': True,
'major_tick_mark': 'none',
'minor_tick_mark': 'cross'
})
chart.set_legend({'none': True})
chart.set_size({'x_scale': 2, 'y_scale': 2})
worksheet.insert_chart('H2', chart, {'x_offset': 25, 'y_offset': 10})
What a Gantt Chart will look like in Excel
One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.