Python操作Excel自动化实战案例

2021-12-17 13:53:19 浏览数 (2)

大家好，我是才哥。

今天收到好朋友小一的投稿，他来了~

今天我来交作业啦！

不知道大家还记不记得之前才哥有一篇送书的文章，刚好抽的那本书是我迫切需要的，于是就在文末留了言

感谢大家的点赞，让我最后成功的拿到了这本书，更感谢才哥，这本书，真心不错！

当然，我也不负众望，留言提到的这个需求成功搞定！！

今天也就来给大家分享一下我完成这个需求的 主要思路和核心代码，内容真的比较简单，一看就会的那种！

首先，先来看一下我的需求：

  • ①每日读取数据库最新数据；
  • ②通过对数据进行各项指标的汇总；
  • ③将汇总数据存入excel（保存excel的时候需要用到：excel单元格合并、大标题、小标题的格式、颜色、背景、字体等等）；
  • ④将excel数据保存成图片；
  • ⑤定时发送图片。

因为④⑤涉及到Python对excel的相关操作，所以目前卡在这里，希望自己能够被选中！！如果该需求能够完美解决，后面我也会分享一篇学习笔记供大家一起交流学习

先放一张上述功能完成之后的成效图

非最新数据结果，需要注意！

ok，下面就开始分享我是如何将数据汇总并且做成上面这样一份excel表格的，当然后面还有excel表格保存成图片和图片的定时发送，但是这些不是我的卡脖子问题，大家感兴趣的话我找机会再接着分享

1. 布局

在你准备做这张汇总表之前，你自己一定得先有一个大概的框架，然后才可以让程序每天定时自动化完成。

说直白点，就是你得先明确你的报表是怎样排列的，然后才是用程序去实现。具体一点的，例如：哪一块是大标题、哪一块是内容、数据是横排展示还是竖排展示、备注应该写在哪等等。

最好一点，就是像我这样，直接做一个demo布局

2. 实现

因为涉及到单元格的合并操作，以及excel表格的背景颜色、字体大小、字体加粗、字体标红等操作，所以我使用 xlsxwriter 模块来实现上述操作。

demo图中的虽然涉及到多个部分，但是只要你学会了一个部分的实现，那其他部分也都可以复制拓展。

所以，下面我会针对其中某个部分（下图）详细的介绍它的实现过程：

首先是通过xlsxwriter创建一个workbook，并且新增一个worksheet

相应于在excel中新建一个工作表

代码语言：javascript复制
import xlsxwriter
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

后续的操作都可以基于此worksheet进行，如果你想要创建多张表，复制上面的代码即可

根据当前的复现目标，首先需要设置标题行的行高和标题内容，并且由于标题行需要进行单元格的合并，所以这里使用merge_range方法。其次是使用set_row设置行的高度

代码如下：

代码语言：javascript复制
# 第一个参数是位置，第二个参数是内容，第三个参数是自定义格式
worksheet.merge_range('A1:N1', 'xx模型xx持有清单', set_merge_style(workbook, tag='head1'))
worksheet.set_row(0, 20)

需要解释的set_merge_style这个方法，因为在复现的表格中需要自定义填充单元格的颜色、单元格文字的颜色、加粗显示等等

而且涉及到不同的类型，所以需要针对不同的单元格进行定制化填充，也就有了set_merge_style这个函数，这个函数的部分代码如下：

代码语言：javascript复制
# 自定义单元格格式，截取部分代码，非全部
def set_merge_style(workbook, tag):
    number_format, font_color, align = '', '', 'center'
    if tag == 'head1':
        bold, font_size, border = True, 14, 1
        fg_color = '#8DB4E2'
    else:
        bold, font_size, border = True, 9, 1
        fg_color = '#FFFFFF'
        font_color = 'red'
        number_format = '0.00%'
 
    cell_format = workbook.add_format({
        'bold': bold,
        'font_size': font_size,
        'border': border,
        'align': align,
        'valign': 'vcenter',  # 垂直居中
        'fg_color': fg_color,  # 颜色填充
        'num_format': number_format,
        'font_color': font_color,
        'font_name': 'Arial',
    })

    return cell_format

其实主要就是通过workbook的add_format方法对每一个单元格cell进行自定义，上面的代码想必大家都能看懂

在大标题的下面是各个次级标题，每个次级标题的内容和布局都是相似的，所以可以采用循环设置

下面是循环体的内容，只需改动参数A2:N2的数字和参数标题的内容即可构造不同的次级块

代码语言：javascript复制
worksheet.merge_range('A2:N2', '次级标题1', set_merge_style(workbook, tag='head2'))
worksheet.set_row(1, 15)

当前效果如下：

次级标题的下面就是具体的数据清单的复现，内容包括小标题 行数据

如下图：

可以看到，小标题的背景和数据的背景不一样，所以可以使用上面的方法对小标题进行构造。另外，如果你的数据也像上图中的一样，可以填满3列，那就需要构造3个列标题，对应的就是12个单元格的填充

我写了一个循环，可以很方便的实现上面的需要，直接贴代码

代码语言：javascript复制
columns = ['个股简称', '买入日期', '买入价格', '当前收益']
index = [['A', 'B', 'C', 'D'], ['F', 'G', 'H', 'I'], ['K', 'L', 'M', 'N']]
for i in range(3):
    worksheet.write(index[i][0] str(3), columns[0], set_merge_style(workbook, tag='head3'))
    worksheet.write(index[i][1] str(3), columns[1], set_merge_style(workbook, tag='head3'))
    worksheet.write(index[i][2] str(3), columns[2], set_merge_style(workbook, tag='head3'))
    worksheet.write(index[i][3] str(3), columns[3], set_merge_style(workbook, tag='head3'))

关于为什么E、J 列要空着，后面会提到，别着急

再往下就是行数据的复现，这里同样采用标题的填充方法，不过不同的是，像图中的行数据是填满4行，每行12个单元格，对应的就是48个单元格。

所以，这里我设置了一个双重循环填充数据，也比较简单，代码如下：

代码语言：javascript复制
for i in range(3):
 for j in range(4, 7):
        worksheet.write(index[i][0] str(j), '数据', set_merge_style(workbook, tag='content'))
        worksheet.write(index[i][1] str(j), '...', set_merge_style(workbook, tag='content'))
        worksheet.write(index[i][2] str(j), '...', set_merge_style(workbook, tag='content'))
        worksheet.write(index[i][3] str(j), '...', set_merge_style(workbook, tag='content'))

上面的 str(3) 和 str(j) 对应的都是行的下标，标题在第3行，所以是str(3)，数据在4-7行，所以是str(j)

为了显示美观，在对应的每一列的数据之间设置间隔，这里我在E、J两列设置间隔，列宽1.5，可以更好的区分数据内容，代码如下：

代码语言：javascript复制
worksheet.set_column('E:E', 1.5)
worksheet.set_column('J:J', 1.5)

对上述代码进行封装，或者直接手动更改对应的下标后运行就可以实现开头图片的大部分内容。

剩下也就是最下面的总结和备注的复现了

同样是使用set_row设置行高，merge_range进行单元格合并和内容填充。直接贴代码：

代码语言：javascript复制
# 设置统计
worksheet.set_row(12, 35)
result_str = '统计：E2模型完整交易88次，其中盈利44次，亏损44次。模型累计总收益xx.x%n其中当前最大单次盈利xx.x%，最大单次亏损xx.x%，平均单次收益xx.x%'
worksheet.merge_range('A13:N13', result_str, set_merge_style(workbook, tag='result'))
# 设置备注
worksheet.set_row(13, 26)
note_str = '注：买入->卖出为一次完整交易，当前仍持有的不参与统计n模型开始运行时间：20211008'
worksheet.merge_range('A14:N14', note_str, set_merge_style(workbook, tag='note'))
# 设置结尾
worksheet.set_row(14, 10)

你如果把上述代码从头到尾复制一遍后运行，应该可以成功复现下图：

次级标题2的内容是我添加，你复制次级标题1的代码，改一下set_merge_style的颜色和样式就可以完美复现

以上就是通过Python进行excel表格自动化的核心思路和代码，感兴趣的大家可以动手操作一波

再次感谢才哥和才哥送的书，respect!!

python excel merge row set

0 人点赞