Python操作Excel自动化实战案例

👆👆👆关注我，和老表一起学Python、云服务器

大家好，我是老表，今天给大家分享一篇Python操作Excel自动化实战案例。

主要思路和核心代码，内容真的比较简单，一看就会的那种！

首先，先来看一下原作者小一的需求：

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

文章篇幅原因，本文原作者主要是给大家分享了第三点，利用Python创建Excel数据模板+数据填充。

关于Python读/写数据库数据，大家可以查看：Python数据存储读取，6千字搞定各种方法

关于Python发送邮件相关介绍，大家可以查看：搞定，爬取公众号文章转换成PDF，自动邮件发送给自己！

关于Python操作Excel更多基础学习，大家还可以查看：运营学Python，Python&Excel联动(上)

首先需要你的电脑安装好了Python环境，并且安装好了Python开发工具。

如果你还没有安装，可以参考以下文章：

如果仅用Python来处理数据、爬虫、数据分析或者自动化脚本、机器学习等，建议使用Python基础环境+jupyter即可，安装使用参考Windows/Mac 安装、使用Python环境+jupyter notebook

如果想利用Python进行web项目开发等，建议使用Python基础环境+Pycharm，安装使用参考 ：Windows下安装、使用Pycharm教程，这下全了 和 Mac下玩转Python-安装&使用Python/PyCharm 。

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

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

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

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

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

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

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

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

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

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

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

import xlsxwriter
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

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

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

代码如下：

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

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

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

# 自定义单元格格式，截取部分代码，非全部
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的数字和参数标题的内容即可构造不同的次级块

worksheet.merge_range('A2:N2', '次级标题1', set_merge_style(workbook, tag='head2'))
worksheet.set_row(1, 15)

当前效果如下：

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

如下图：

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

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个单元格。

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

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，可以更好的区分数据内容，代码如下：

worksheet.set_column('E:E', 1.5)
worksheet.set_column('J:J', 1.5)

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

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

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

# 设置统计
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表格自动化的核心思路和代码，感兴趣的读者朋友可以动手操作一波。

点赞+留言+转发，就是对我最大的支持啦～

今日晚上20:00，老表将会在朋友圈发赠书一本，想参与的可以扫下方二维码添加我好友。

非机器人，不定期回复消息

加了，看我微信个人介绍提示

--End--

如何找到我：


学习更多：
整理了我开始分享学习笔记到现在超过250篇优质文章，涵盖数据分析、爬虫、机器学习等方面，别再说不知道该从哪开始，实战哪里找了