Automate Payroll Generation with Python: Step-by-Step Excel Script

What the article solves

A friend asked for a way to automate the monthly payroll generation that was previously done manually in Excel. The article shows a Python script that can read the original Excel file, duplicate a sheet, copy styles, insert rows, and update formulas to produce a ready‑to‑use payroll sheet.

The original data layout is displayed, followed by the expected final payroll format.

Code Logic Analysis

The program first reads the Excel workbook, copies the source sheet, and creates a new sheet named final_工资条. It then extracts the header row and the style of the first cell (alignment, border, fill, font) using a helper function.

import re
import openpyxl
from copy import copy

wb = openpyxl.load_workbook('工资条.xlsx')
wb.copy_worksheet(wb['工资条'])
ws = wb.worksheets[-1]
ws.title = 'final_工资条'

For each data row, the script inserts two rows beneath it, writes the header with the previously captured style, and updates any formulas that reference column H or J by adjusting the row number with a regular‑expression substitution.

def cell_style(cell):
    alignment = copy(cell.alignment)    # 对齐样式
    border = copy(cell.border)          # 边框样式
    fill = copy(cell.fill)              # 填充样式
    font = copy(cell.font)              # 字体样式
    return alignment, border, fill, font

# ... loop that inserts rows and updates formulas ...

Full Script

import re
import openpyxl
from copy import copy

def cell_style(cell):
    alignment = copy(cell.alignment)    # 对齐样式
    border = copy(cell.border)          # 边框样式
    fill = copy(cell.fill)              # 填充样式
    font = copy(cell.font)              # 字体样式
    return alignment, border, fill, font

wb = openpyxl.load_workbook('工资条.xlsx')
wb.copy_worksheet(wb['工资条'])
ws = wb.worksheets[-1]
ws.title = 'final_工资条'

cells_rows = [[cell for cell in row] for row in ws.rows]

header = [cell.value for cell in cells_rows[0]]

alignment, border, fill, font = cell_style(cell=cells_rows[0][0])

for i, _ in enumerate(cells_rows[1:]):
    if i > 0:
        index = i * 3
        ws.insert_rows(idx=index, amount=2)
        for j, v in enumerate(header):
            r, c = index + 1, j + 1
            cell = ws.cell(row=r, column=c)
            cell.value = v
            cell.alignment = alignment
            cell.font = font
            cell.border = border
            cell.fill = fill
            if cell.coordinate[:1] in ('H', 'J'):
                cell = ws.cell(row=r+1, column=c)
                cell.value = re.sub(r'\d+', str(r+1), cell.value)

wb.save('工资条.xlsx')