python实现pandas+openpyxl导出excel报表

由于数据的繁杂和日渐增多，报表成为工作上必不可少的一部分，报表的实现既能将数据有规划的整理也能把一大串繁琐的数据变得更加简洁可观。但是用excel处理大量的报表，工作量可想而知，每次都需要在月头和周头花费大量的时间来整理报表。自从学了python，就方便多了，将数据导入用python整理好后，保存为excel文件简直不要太简单，每次有需要的时候点点运行，更改下需求（比如时间）就可以了，可是单纯的用pandas导出的表格却不太美观，这时我就接触到了一个新的库——openpyxl，这个库也可以往excel中写入数据，但个人还是喜欢用pandas来导出excel，再用openpyxl库来修改excel的样式。
openpyxl只支持对xlsx、xlsm文件进行读、写操作，不支持xls文件

# 所需要的库
import itertools
import pandas as pd
from openpyxl.styles import *
from openpyxl.utils import get_column_letter

1.导出excel文件

自行准备一份数据，这里采用的数据是双索引双表头的。
代码实现：

writer = pd.ExcelWriter('文件名.xlsx', engine='openpyxl')
data.to_excel(writer)
worksheet = writer.sheets['Sheet1']    # 默认创建的表格的工作簿名为Sheet1，若不需要进行openpyxl操作，可不写

用pandas导出的表格如下图：

可以发现这份表格看着着实不美观，想要实现的报表样式如下图：

2.隐藏或删除行、列

因为是双索引双表头的表格，导出都会有一行索引名的行，暂时没有找到方法怎么在导出的时候去掉，只能在后续实现删除该行或隐藏该行。

代码实现：

worksheet.delete_rows(3)  # 删除单行
worksheet.delete_cols(3)   # 删除单列
worksheet.delete_cols(6, 3)    # 多列删除，从第六列开始，删除3列即（F:H），行类似

worksheet.row_dimensions[3].hidden = 1  # 隐藏行
worksheet.column_dimensions[3].hidden = 1  # 隐藏列

3.合并单元格并填入数据

代码实现：

worksheet.merge_cells('A1:A2')
worksheet['A1'].value = '部门'
worksheet.merge_cells('B1:B2')
worksheet['B1'].value = '姓名'

4.添加边框

因为pandas导出的表格除了表头处有明显的边框，其他地方都没有，所以需要通过添加边框来美化表格。
注意这里需要导入Border和Side两个函数
代码实现：

# 设置单元格边框
border_set = Border(Side(style='thin', color='FF000000'),
                    right=Side(style='thin', color='FF000000'),
                    top=Side(style='thin', color='FF000000'),
                    bottom=Side(style='thin', color='FF000000'))    # style的参数有很多，可自行搜索
# 设置所有单元格边框
for i in range(1, worksheet.max_row + 1):
    for j in range(1, worksheet.max_column + 1):
        worksheet.cell(row=i, column=j).border = border_set

5.调整行、列宽

因为默认行列宽的原因，使有些单元格的字显示不全，所以需要调整行列宽。
代码实现：

# 更改行高，这里是只有第二行为50，其他行为20
for i in range(1, worksheet.max_row + 1):
    if i == 2:
        worksheet.row_dimensions[i].height = 50
    else:
        worksheet.row_dimensions[i].height = 20
# 更改列的列宽
for i in range(3, worksheet.max_column + 1):   # 从第三列开始
    worksheet.column_dimensions[get_column_letter(i)].width = 20
    # get_column_letter 获取数字对应的字母，例如 get_column_letter(1)，输出 A

6.更改表格样式

代码实现：

# 根据需求设置字体、颜色、是否加粗、是否居中、是否换行等
title_font = Font(name="微软雅黑", bold=True)  # 字体加粗
data_font = Font(name="微软雅黑")
alignment = Alignment(horizontal='center', vertical="center", wrapText=True)  # 设置为单元格居中和自动换行
# 设置所有列的格式
for cell in itertools.chain(*worksheet[f"A:{get_column_letter(worksheet.max_column)}"]):
    cell.font = data_font
    cell.alignment = alignment
# 第一行加粗
for cell in itertools.chain(*worksheet[f"A1:{get_column_letter(worksheet.max_column)}1"]):
    cell.font = title_font    # 表头加粗
# 也可以直接用以下的代码，但因为示例表格含有多个合并的单元格，以下两行对该表格不起作用
worksheet.column_dimensions['A'].font = title_font   # A列加粗
worksheet.row_dimensions[1].font = title_font     # # 第一行加粗

7.单元格颜色

代码实现：

# 根据需求设置设置单元格颜色
for i in range(2, worksheet.max_row + 1):   # 从第二行开始到最后一行
    worksheet.cell(row=i, column=4).fill = PatternFill(fill_type='solid', fgColor="FFFFCC")  # 第4列的颜色
    worksheet.cell(row=i, column=8).fill = PatternFill(fill_type='solid', fgColor="FFEB9C")  # 第8列的颜色
    worksheet.cell(row=i, column=11).fill = PatternFill(fill_type='solid', fgColor="C6EFCE")  # 第11列的颜色

fill_type 参数：官方文档给出了很多种填充类型，一般纯色填充使用 solid ，其他样式自行尝试
‘none’、‘solid’、‘darkDown’、‘darkGray’、‘darkGrid’、‘darkHorizontal’、‘darkTrellis’、‘darkUp’、‘darkVertical’、‘gray0625’、‘gray125’、‘lightDown’、‘lightGray’、‘lightGrid’、‘lightHorizontal’、‘lightTrellis’、‘lightUp’、‘lightVertical’、‘mediumGray’

8.单元格插入公式、更改数字格式

因为有些表格涉及到有些数据需要手动填写，这时就需要设计一些公式进行计算
代码实现：

# 根据需求为单元格插入公式并更改数字格式
for i in range(4, worksheet.max_row + 1):
    worksheet[f'F{i}'] = f'=IFERROR((D{i}/C{i})," ")'
    worksheet[f'F{i}'].number_format = '0%'

9.实现

下面附上完整代码：

# 绩效报表
import itertools
import pandas as pd
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

# 数据整理
def get_data(data, department, start_time, end_time):
	''''
    return ddd_sum

# 获取汇总所在的行数
def row(ddd_sum):
    sum_list = []
    for i in range(len(ddd_sum)):
        if (('组别小计:' in ddd_sum.index[i]) | ('总计1:' in ddd_sum.index[i]) |
            ('总计2:' in ddd_sum.index[i]) | ('公司总计:' in ddd_sum.index[i])):
            l = i + 4  # 加上0行、空行和双级表头行
            sum_list.append(l)
    return sum_list

# 制作报表格式
def excel(writer, sum_list):
    worksheet = writer.sheets['Sheet1']
    worksheet.row_dimensions[3].hidden = 1  # 隐藏空行
    # 合并单元格
    worksheet.merge_cells('A1:A2')
    worksheet['A1'].value = '部门'
    worksheet.merge_cells('B1:B2')
    worksheet['B1'].value = '姓名'
    for i in sum_list[-3:]:
        worksheet.merge_cells(f'A{i}:B{i}')
    title_font = Font(name="微软雅黑", bold=True)  # 字体加粗
    data_font = Font(name="微软雅黑")
    alignment = Alignment(horizontal='center', vertical="center", wrapText=True)  # 设置为单元格居中和自动换行
    # 设置单元格边框
    border_set = Border(Side(style='thin', color='FF000000'),
                        right=Side(style='thin', color='FF000000'),
                        top=Side(style='thin', color='FF000000'),
                        bottom=Side(style='thin', color='FF000000'))
    # 设置所有单元格边框
    for i in range(1, worksheet.max_row + 1):
        for j in range(1, worksheet.max_column + 1):
            worksheet.cell(row=i, column=j).border = border_set
    # 更改行高
    for i in range(1, worksheet.max_row + 1):
        if i == 2:
            worksheet.row_dimensions[i].height = 50
        else:
            worksheet.row_dimensions[i].height = 20
    # 更改列的列宽
    for i in range(3, worksheet.max_column + 1):
        worksheet.column_dimensions[get_column_letter(i)].width = 20
    # 设置所有格式
    for cell in itertools.chain(*worksheet[f"A:{get_column_letter(worksheet.max_column)}"]):
        cell.font = data_font
        cell.alignment = alignment
    # 设置表头格式
    for r in [1] + sum_list:
        for cell in itertools.chain(*worksheet[f"A{r}:{get_column_letter(worksheet.max_column)}{r}"]):
            cell.font = title_font
    worksheet.column_dimensions['A'].font = title_font
    # 设置单元格颜色
    for i in range(2, worksheet.max_row + 1):
        worksheet.cell(row=i, column=4).fill = PatternFill(fill_type='solid', fgColor="FFFFCC")
        worksheet.cell(row=i, column=8).fill = PatternFill(fill_type='solid', fgColor="FFEB9C")
        worksheet.cell(row=i, column=11).fill = PatternFill(fill_type='solid', fgColor="C6EFCE")
    # 为单元格插入公式并更改数字格式
    for i in range(4, worksheet.max_row + 1):
        worksheet[f'E{i}'] = '公式'
        worksheet[f'F{i}'] = '公式'
        worksheet[f'F{i}'].number_format = '0%'
        worksheet[f'I{i}'] = '公式'
        worksheet[f'J{i}'] = '公式'
        worksheet[f'J{i}'].number_format = '0%'
    # 手动填写统计
    for i in ['C', 'G']:
        worksheet[f'{i}{sum_list[0]}'] = '公式'
        worksheet[f'{i}{sum_list[1]}'] = '公式'
        worksheet[f'{i}{sum_list[2]}'] = '公式'
        worksheet[f'{i}{sum_list[3]}'] = '公式'
        worksheet[f'{i}{sum_list[4]}'] = '公式'
        worksheet[f'{i}{sum_list[5]}'] = '公式'
    return worksheet

if __name__ == '__main__':
	# 设置所需时间
    start_time = '2021.3.1'
    end_time = '2021.3.7'
    data = pd.read_csv('./成交明细.csv', parse_dates=['成交日期'])
    department = pd.read_excel('./部门.xlsx')
    dt = get_data(data, department, start_time, end_time)
    r = row(dt)
    with pd.ExcelWriter(f'./报表{start_time}-{end_time}.xlsx', engine='openpyxl') as writer:
        dt.to_excel(writer)
        excel(writer, r)
        writer.save()
    writer.close()