python 模拟 excel 宏、VBA 制作工资条表格

需求：

 

0、imort

import re
import pandas as pd
import openpyxl
from openpyxl.utils import get_column_letter

 

1、查看表格数据信息

1.1 加载工作簿

wb = openpyxl.load_workbook('工资信息.xlsx')  # 加载工作簿
wb.sheetnames

 

 1.2 备份源 sheet，新建要写入的 sheet

wb.copy_worksheet(wb.get_sheet_by_name('工资条'))  
ws = wb.worksheets[-1]
ws.title = '工资条_bak'    # 重命名 sheet
wb.create_sheet('sheet')

wb.sheetnames

 

 1.3、用 panda 查看内容

# 定义两个变量方便后期引用
bak_sht = wb.get_sheet_by_name('工资条_bak')
new_sht = wb.get_sheet_by_name('sheet')

data = [[cell.value  for cell in row] for row in bak_sht.rows]
idx = range(1, len(data)+1)
cols = [get_column_letter(j+1) for j, _ in enumerate(data[0])]
df = pd.DataFrame(data,
             columns=cols,
             index=idx
            )

df.head()

说明：1、为方便后期更新公式，df 中的行列索引用 excel 中的 A1 样式

　　　2、 为了显示单元格中的公式，没有用 pd.read_excel() 函数，改用 cell.value 的方法

 

1.4 为方便设置单元格样式，封装一个获取单元格样式的函数

def cell_style(cell):
    '''获取给定单元格的四大样式属性'''
    alignment = cell.alignment.copy()    # 对齐方式
    border = cell.border.copy()    # 单元格边框样式
    fill = cell.fill.copy()    # 单元格填充样式
    font = cell.font.copy()    # 单元格值的字体属性
    return alignment, border, fill, font

 

1.5、以循环写入的模式生成特定格式的工资条表格

rows_bak_sht = [[cell for cell in row] for row in bak_sht.rows]

# 写入表头
alignment, border, fill, font = cell_style(cell=rows_bak_sht[0][0])
t = len(rows_bak_sht)*2 - 2    # 最终生成的表格行数

for j, cell in enumerate(rows_bak_sht[0]):
    for i in range(1, t, 2):
        new_cell = new_sht.cell(row=i, column=j+1)
        new_cell.value = cell.value
　　　　 new_cell.alignment = alignment
        new_cell.font = font
        new_cell.border = border
        new_cell.fill = fill

# 写入明细       
alignment, border, fill, font = cell_style(cell=rows_bak_sht[1][0])

for i, row in enumerate(rows_bak_sht) :
    if i > 0:    # 第 1 行是标表头需跳过
        for j, cell in enumerate(row):
            r, c = i*2, j+1
            new_cell = new_sht.cell(row=r, column=c)
            # 保留样式
            new_cell.value = cell.value
　　　　　　  new_cell.alignment = alignment
            new_cell.font = font
            new_cell.border = border
            new_cell.fill = fill
            # H 列和 J 列是公式，序更新
            if new_cell.column in ('H', 'J'):
                new_cell.value = re.sub('\d+', str(r), new_cell.value)
                
wb.save('test.xlsx')

excel 效果：

 

2 实现在每个工资条之间保留一行空白的完整可用的脚本

# import
import re
import openpyxl
from openpyxl.utils import get_column_letter

# 封装函数
def cell_style(cell):
    '''获取给定单元格的四大样式属性'''
    alignment = cell.alignment.copy()    # 对齐方式
    border = cell.border.copy()    # 单元格边框样式
    fill = cell.fill.copy()    # 单元格填充样式
    font = cell.font.copy()    # 单元格值的字体属性
    return alignment, border, fill, font

wb = openpyxl.load_workbook('1.1-2用宏录下Excel的操作.xlsx')  # 加载工作簿
wb.copy_worksheet(wb.get_sheet_by_name('工资条'))
wb.worksheets[-1].title = '工资条_bak'    # 重命名 sheet
wb.create_sheet('sheet')

# 定义两个变量方便后期引用
bak_sht = wb.get_sheet_by_name('工资条_bak')
new_sht = wb.get_sheet_by_name('sheet')

rows_bak_sht = [[cell  for cell in row] for row in bak_sht.rows]

# 插入表头
alignment, border, fill, font = cell_style(cell=rows_bak_sht[0][0])
t = len(rows_bak_sht)*3 - 4    # 最终生成的表格行数

for j, cell in enumerate(rows_bak_sht[0]):
    for i in range(1, t, 3):
        new_cell = new_sht.cell(row=i, column=j+1)
　　　   new_cell.alignment = alignment
        new_cell.value = cell.value
        new_cell.font = font
        new_cell.border = border
        new_cell.fill = fill

# 写入明细       
alignment, border, fill, font = cell_style(cell=rows_bak_sht[1][0])
for i, row in enumerate(rows_bak_sht) :
    if i > 0:
        for j, cell in enumerate(row):
            r, c = i*3-1, j+1
            new_cell = new_sht.cell(row=r, column=c)
            new_cell.value = cell.value
　　　　　　  new_cell.alignment = alignment
            new_cell.font = font
            new_cell.border = border
            new_cell.fill = fill
            # 更新公式
            if new_cell.column in ('H', 'J'):
                new_cell.value = re.sub('\d+', str(r), new_cell.value)
                
wb.save('test.xlsx')

excel 效果：

 

 

版本说明：python=3.6.10

　　　　　openyxl=2.4.8