Python完成Excel报表自动化思路

💡 Tips1：Pandas的dataframe有强大高效的表格数据处理功能。 💡 Tips2：openpyxl有强大的Excel表格样式操作功能。

  • 使用 Pandas完成数据的清洗和计算处理工作
  • 使用 openpyx完成数据写入表格，样式处理及绘图操作
  • 使用openpyx可以轻松完成Excel模板表格数据写入（同时不影响图片和其他的样式），可以快速完成模板数据的替换，实现固定输出的数据自动化处理。

Q:为什么不能只用pandas？
A:pandas提供输出excel文件的功能，但基本以覆盖或追加文件内容完成输出，不便于操作保留excel中的样式及绘图内容，输出的美观程度对于业务应用的Excel报表有相当的差距。

Pandas读取openpyxl

💡 Tips：语雀支持全功能 markdown 语法，可以点击文档编辑页右下角小键盘查看全部支持的语法和快捷键。

  1. # import pandas as pd
  2. # from openpyxl import Workbook
  3. # from openpyxl import load_workbook
  4. # # openpyxl 读取数据
  5. # file_path = 'data/test_data1.xlsx'
  6. # wb2 = load_workbook(file_path)
  7. # # 读取excel文件中名为 【数值】表数值
  8. # sheet_ranges = wb2['数值']
  10. ''' 将sheet_ranges转为DataFrame '''
  11. df = pd.DataFrame(sheet_ranges.values)
  12. df.columns = df.loc[0] # 将第0行从小设置为表头
  13. df = df.drop(0, axis=0)# 删除成为表头的数据行
  14. df = df.reset_index(drop = True)
  15. df

使用openpyxl输出pandas的DataFrame数据

  1. def draw_sheet(sheet,df,x = 1,y = 1):
  2.     '''
  3.     将dataframe写入sheet中的函数，默认x=1,y=1,从表格左上角开始写入,如果需要从第二行写入，则x = 2 ...
  5.     后续可增加格式化功能
  6.     '''
  8. #     sheet.row_dimensions[x].height = 35
  9. #     for i in range(df.shape[1]):
  10. #         if i == 0:
  11. #             sheet.column_dimensions[chr(y+64)].width = 27
  12. #         else:
  13. #             sheet.column_dimensions[chr(y+i+64)].width = 13.9
  14.     # 将df 写入sheet中
  15.     for i in range(df.shape[0]+1):
  16.         for j in range(df.shape[1]):
  17.             if i == 0:
  18. #                 sheet.cell(x+i,y+j).style = style_title
  19.                 sheet.cell(x+i,y+j).value = df.columns.values[j]
  20.             elif i == df.shape[0]:
  21. #                 sheet.cell(x+i,y+j).style = style_total
  22.                 sheet.cell(x+i,y+j).value = df.iloc[df.shape[0]-1,j]
  23.             else:
  24. #                 sheet.cell(x+i,y+j).style = style_value
  25.                 sheet.cell(x+i,y+j).value = df.iloc[i-1,j]
  26. # 函数调用    
  27. draw_sheet(sheet_ranges,df,1,1)
  28. wb2.save(file_path)

准备一些Excel模板

参考文档

openpyxl官方教程：
pandas官方文档：

Markdown 和快捷键全覆盖

💡 Tips：语雀支持全功能 markdown 语法，可以点击文档编辑页右下角小键盘查看全部支持的语法和快捷键。

  • 支持导入导出 markdown 文件。
  • 支持自动识别粘贴的 markdown 格式内容转换为富文本。

    行内代码

    💡 Tips：可通过 markdown 语法（+ `code` + + 空格）或者快捷键 ctrl/cmd + E快速插入行内代码。

在文本中使用行内代码，可以顺畅地显示代码变量名。

代码块

💡 Tips：输入/代码块或点击上方工具栏点击上方工具栏image.png，选择「代码块」、插入代码卡片。

代码块同时支持多种颜色主题：

  1. export default class QuickSort extends Sort {
  2.   sort(originalArray) {
  3.     const array = [...originalArray];
  5.     if (array.length <= 1) {
  6.       return array;
  7.     }
  9.     // Init left and right arrays.
  10.     const leftArray = [];
  11.     const rightArray = [];
  13.     // Take the first element of array as a pivot.
  14.     const pivotElement = array.shift();
  15.     const centerArray = [pivotElement];
  17.     // Split all array elements between left, center and right arrays.
  18.     while (array.length) {
  19.       const currentElement = array.shift();
  21.       // Call visiting callback.
  22.       this.callbacks.visitingCallback(currentElement);
  24.       if (this.comparator.equal(currentElement, pivotElement)) {
  25.         centerArray.push(currentElement);
  26.       } else if (this.comparator.lessThan(currentElement, pivotElement)) {
  27.         leftArray.push(currentElement);
  28.       } else {
  29.         rightArray.push(currentElement);
  30.       }
  31.     }
  32.     // Sort left and right arrays.
  33.     const leftArraySorted = this.sort(leftArray);
  34.     const rightArraySorted = this.sort(rightArray);
  36.     return leftArraySorted.concat(centerArray, rightArraySorted);
  37.   }
  38. }
  1. export default class QuickSort extends Sort {
  2.   sort(originalArray) {
  3.     const array = [...originalArray];
  5.     if (array.length <= 1) {
  6.       return array;
  7.     }
  9.     // Init left and right arrays.
  10.     const leftArray = [];
  11.     const rightArray = [];
  13.     // Take the first element of array as a pivot.
  14.     const pivotElement = array.shift();
  15.     const centerArray = [pivotElement];
  17.     // Split all array elements between left, center and right arrays.
  18.     while (array.length) {
  19.       const currentElement = array.shift();
  21.       // Call visiting callback.
  22.       this.callbacks.visitingCallback(currentElement);
  24.       if (this.comparator.equal(currentElement, pivotElement)) {
  25.         centerArray.push(currentElement);
  26.       } else if (this.comparator.lessThan(currentElement, pivotElement)) {
  27.         leftArray.push(currentElement);
  28.       } else {
  29.         rightArray.push(currentElement);
  30.       }
  31.     }
  32.     // Sort left and right arrays.
  33.     const leftArraySorted = this.sort(leftArray);
  34.     const rightArraySorted = this.sort(rightArray);
  36.     return leftArraySorted.concat(centerArray, rightArraySorted);
  37.   }
  38. }
  1. export default class QuickSort extends Sort {
  2.   sort(originalArray) {
  3.     const array = [...originalArray];
  5.     if (array.length <= 1) {
  6.       return array;
  7.     }
  9.     // Init left and right arrays.
  10.     const leftArray = [];
  11.     const rightArray = [];
  13.     // Take the first element of array as a pivot.
  14.     const pivotElement = array.shift();
  15.     const centerArray = [pivotElement];
  17.     // Split all array elements between left, center and right arrays.
  18.     while (array.length) {
  19.       const currentElement = array.shift();
  21.       // Call visiting callback.
  22.       this.callbacks.visitingCallback(currentElement);
  24.       if (this.comparator.equal(currentElement, pivotElement)) {
  25.         centerArray.push(currentElement);
  26.       } else if (this.comparator.lessThan(currentElement, pivotElement)) {
  27.         leftArray.push(currentElement);
  28.       } else {
  29.         rightArray.push(currentElement);
  30.       }
  31.     }
  32.     // Sort left and right arrays.
  33.     const leftArraySorted = this.sort(leftArray);
  34.     const rightArraySorted = this.sort(rightArray);
  36.     return leftArraySorted.concat(centerArray, rightArraySorted);
  37.   }
  38. }

数学公式

💡 Tips：输入 /公式或点击上方工具栏点击上方工具栏image.png，选择「公式」、插入公式卡片。

公式支持行内嵌套：openpyxl和pandas组合实现Excel报表自动化 - 图3，也支持块级嵌入。
openpyxl和pandas组合实现Excel报表自动化 - 图4

画板

💡 Tips：输入/画板或点击上方工具栏image.png，选择「画板」、绘制流程图、架构图等各种图形。

openpyxl和pandas组合实现Excel报表自动化 - 图6
openpyxl和pandas组合实现Excel报表自动化 - 图7

openpyxl和pandas组合实现Excel报表自动化 - 图8

文本绘图

💡 Tips：输入/文本绘图点击上方工具栏image.png，选择「文本绘图」、插入文本绘图卡片。
支持 plantuml、mermaid 等多种格式，点击预览可看到图形。具体代码样式见说明文档。

openpyxl和pandas组合实现Excel报表自动化 - 图10openpyxl和pandas组合实现Excel报表自动化 - 图11