How to produce beautiful, well formatted Excel reports using Python
Using Python, XlsxWriter, Excel
Imagine we receive a request to pull data from a system. We develop a query that produces a valid result set for the given request. We export the data in Excel and are ready to share it with the end user. Our job is done, right? Not really. Any experienced data analyst will tell you that presentation counts. When creating and sharing reports, basics are not enough. We want to share our findings in professional and well-formatted reports.
Pandas library makes it a breeze to generate Excel outputs from a dataframe. However, it does have some drawbacks when retaining the formatting when exporting the data to Excel. Let’s say we format the numbers and apply conditional formatting to our dataframe. When it is exported to an Excel output not all the formatting is persisted. We can utilize the XlsxWriter module in Python to design well formatted Excel reports.
We will focus on the following topics.
- How to apply conditional formatting to Pandas dataframe.
- How to produce well formatted Excel outputs using XlsxWriter
- Compare the two outputs
Accompanying video tutorial is available on YouTube.
We will cover two methods, first we will apply the conditional formatting to our dataframe and output this to excel. In the second method we will utilize the XlsxWriter module. Here is a tutorial that covers the XlsxWriter. We will utilize the script from this tutorial and build on it to cover the conditional formatting in Excel using Python.
import pandas as pd
import xlsxwriter
#
url = "https://github.com/hnawaz007/pythondataanalysis/blob/main/AutomateExcelReports/AdvWorksData.xlsx?raw=true"
# read from url
df=pd.read_excel(url, sheet_name='Sheet1')
category_sales = df.groupby(['productsubcategory','OrderDate'])[['StandardCost','UnitPriceDiscount','UnitPrice','ListPrice','OrderQuantity','Sales']].sum().sort_values(by='Sales', ascending = False).reset_index()
dsf= category_sales[(category_sales['OrderDate'] == '2014-05-01')]
dsf.head()Method One
We define a function that highlights dataframe rows based on a condition. we check if the value is less than a thousand then define red color, green for values greater than hundred thousand and for all other values define gray color. We return the background for each row. We apply this function to our dataframe and export it to excel.
def highlight_rows(row):
value = row.loc['Sales']
if value < 1000:
color = '#FFB3BA' # Red
elif value > 100000:
color = '#BAFFC9' # Green
else:
color = '#FCFCFA' # Grey
return ['background-color: {}'.format(color) for r in row]
# apply the function to dataframe
dt = dsf.style.apply(highlight_rows, axis=1)
# export to excel
dt.to_excel("report.xlsx", index=False)This produces an Excel output like below. Our data is exported however, it does not look appealing and requires some tweaks for it to be usable. The report should be appealing and approachable. It should provide context and it should make it easy for the user to consume the information provided in it.
To design a quality appealing output we utilize the XlsxWriter. We create a news Excel workbook and a worksheet. Accessing the worksheet object we can style the output.
writer = pd.ExcelWriter(r'G:\\test\\enhancedoutput.xlsx', engine='xlsxwriter')
dsf.to_excel(writer, index=False, sheet_name='report')
#
workbook = writer.book
worksheet = writer.sheets['report']
#Now we have the worksheet object. We can manipulate it
worksheet.set_zoom(90)Using the XlsxWriter we set a title and subtitle for our report. This provides context for the reader. We format the cells, headers and apply conditional formatting to attract the user’s attention.
header_format = workbook.add_format({
"valign": "vcenter",
"align": "center",
"bg_color": "#951F06",
"bold": True,
'font_color': '#FFFFFF',
'border' : 1,
'border_color': ''#D3D3D3'
})#add title
title = "Monthly Sales Report "
#merge cells
format = workbook.add_format()
format.set_font_size(20)
format.set_font_color("#333333")
#
subheader = "Sales report for May"
worksheet.merge_range('A1:AS1', title, format)
worksheet.merge_range('A2:AS2', subheader)
worksheet.set_row(2, 15) # Set the header row height to 15
# puting it all together
# Write the column headers with the defined format.
for col_num, value in enumerate(dsf.columns.values):
#print(col_num, value)
worksheet.write(2, col_num, value, header_format)
We take advantage of the number formatting to make it easy to read and understand.
# Add a number format for cells with money.
currency_format = workbook.add_format({'num_format': '$#,##0.00'})
percent_fmt = workbook.add_format({'num_format': '0.0%'})
# Numbers formatting
worksheet.set_column('C:C', 12, currency_format)
worksheet.set_column('E:E', 12, currency_format)
worksheet.set_column('F:F', 12, currency_format)
worksheet.set_column('H:H', 12, currency_format)
# Percent formatting
worksheet.set_column('D:D', 20, percent_fmt)# Light red fill with dark red text.
lessthanthousand = workbook.add_format({'bg_color': '#FFC7CE','font_color': '#9C0006'})
worksheet.conditional_format('A4:H27', {'type': 'formula','criteria': '=$H4<1000','format': lessthanthousand})
# add borders
worksheet.conditional_format('A4:H27', {'type': 'formula','criteria': '=$H4<1000','format': full_border})
This produces a well formatted report that is easy to read and understand. It provides context to the reader and conveys the information in a professional and well designed. A well adopted report needs accurate information as well as excellent design.
Conclusion
- We have created Excel report outputs using Pandas with conditional formatting.
- We have successfully applied conditional formatting using Python and XlsxWriter.
- We produced a well formatted Excel outputs using XlsxWriter
- The full code can be found here
