Automating Excel Reports with Python xlwings and pandas

Many people find creating weekly, monthly, or quarterly reports in Excel extremely tedious, especially when dealing with multiple workbooks and sheets. The author shares a personal experience of being assigned a reporting task that involved six Excel files, each containing several sheets with numerous tables.

After discovering that the reporting process could be automated, the author spent a week writing Python code to automate the workflow. The main tool introduced is xlwings , which can read and write Excel files and modify cell formatting, while seamlessly integrating with pandas for data manipulation.

The workflow begins by importing the required libraries and loading the raw data from three sheets (raw coal, crude oil, natural gas) in a single Excel file:

<code>import pandas as pd
import xlwings as xw
raw_coal = pd.read_excel(r'统计局数据.xlsx', sheet_name='原煤')
crude_oil = pd.read_excel(r'统计局数据.xlsx', sheet_name='原油')
natural_gas = pd.read_excel(r'统计局数据.xlsx', sheet_name='天然气')
</code>

These DataFrames are merged on the "指标" column to produce a unified table, and only the relevant columns are selected:

<code>data = pd.merge(raw_coal, crude_oil, on='指标')
data = pd.merge(data, natural_gas, on='指标')
finally_data = data[['指标', '原煤产量当期值(万吨)', '原油产量当期值(万吨)', '天然气产量当期值(亿立方米)']]
print(finally_data)
</code>

Next, an Excel workbook is created with xlwings, a sheet named finally_data is added, and the column headers are written to the first row:

<code>wb = xw.Book()
sht = wb.sheets['Sheet1']
sht.name = 'finally_data'
columns = list(finally_data.columns)
sht.range('A1').value = columns
</code>

Three methods for writing the DataFrame to the sheet are presented: writing cell‑by‑cell, writing row‑by‑row, and writing the entire 2‑D array at once. The author prefers the third method:

<code>finally_data1 = finally_data.values
sht.range('A2').value = finally_data1
</code>

After the data is in place, the script calculates the mean of each numeric column, identifies cells above and below the mean, and records their Excel addresses for later formatting:

<code>describe = finally_data.describe()
avg = list(describe.loc['mean', :])
# Example for values above the mean in the coal column
red_原煤 = list(finally_data.index[finally_data['原煤产量当期值(万吨)'] > avg[0]])
red_position1 = ['B' + str(i+2) for i in red_原煤]
# Similar code for oil and gas columns, and for values below the mean (blue)
</code>

Formatting is then applied: the entire range A1:D10 is set to the "宋体" font and borders are added; the header row (A1:D1) receives white font on a black background; cells identified as above‑average are colored red, and those below‑average are colored blue:

<code># Set font and borders
a_range = 'A1:D10'
sht.range(a_range).api.Font.Name = '宋体'
sht.range(a_range).api.Borders(8).LineStyle = 1  # top
sht.range(a_range).api.Borders(9).LineStyle = 1  # bottom
sht.range(a_range).api.Borders(7).LineStyle = 1  # left
sht.range(a_range).api.Borders(10).LineStyle = 1 # right
sht.range(a_range).api.Borders(12).LineStyle = 1 # inner horizontal
sht.range(a_range).api.Borders(11).LineStyle = 1 # inner vertical

# Header style
b_range = 'A1:D1'
sht.range(b_range).api.Font.Color = 0xffffff
sht.range(b_range).color = (0, 0, 0)

# Conditional colors
for i in red:
    sht.range(i).api.Font.Color = 0x0000ff  # red
for i in blue:
    sht.range(i).api.Font.Color = 0xFF0000  # blue

wb.save('结果数据.xlsx')
wb.close()
</code>

The final Excel file meets the visual requirements: black header with white text, "宋体" font throughout, bordered cells, and red/blue highlights for values above or below the column averages.

At the end of the article, the author includes promotional material encouraging readers to scan a QR code to receive free Python learning resources, emphasizing that mastering Python automation requires continued study.