运营学Python｜Python&Excel联动(上) / 四六文摘

来源：简说Python

作者：老表

一、写在前面

目前这个系列具体安排还没想好（(fang)搁(ge)置(zi)了好久），主要是围绕Python自动化办公展开，至少包括Python对Excel、World、Txt等文件/数据处理、Python对系统文件处理、Python自动发送邮件、Python数据爬取、Python模拟点击登录、Python数据可视化等方面。

今天这篇文章我们来讲讲如何通过Python玩转Excel，本次分为上、中、下，上主要是打基础，全文1.2w+字，文中有一些为了演示讲解相关功能的案例，还有三个相对完整的小实战练习，希望对大家了解xlwings有所帮助。

中和下主要是给大家分享其他库操作excel，将以实战为主，所以这里也希望大家多多提一些实际需求给我（涉及数据记得脱敏），提问通道：浏览器访问下方链接或者点击阅读原文：

https://shimowendang.com/forms/tHrYqQGtRvV9vR3P/fill?channel=wc

由于本人水平有限，文中错误，敬请指出，互相学习，一起进步。

二、基础准备

环境：python 3.7+
需要安装第三方模块：xlwings
运行编辑器：jupyter notebook

三、动手动脑

3.0 我们为什么要学编程

首先和大家简单聊一下运营（这里运营不单单指传统意义上的运营工作者，其实只有工作场景中有经常用到office等办公软件，处理很多数据的都算）为什么要学习编程了？

站在我个人角度简单聊聊：

运营学编程绝不是为了解决上面这种简单单一的问题
我们希望的是通过编程提高工作效率、优化工作流程
比如：批量处理excel文件（可能是1000或者更多行数据的增删改）

3.1 环境准备

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

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

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

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

3.2 模块安装

本次实验是在jupyter notebook上进行，默认你已经安装好了Python基本环境和jupyter，如果没有安装可以看上面的环境安装部分。

首先打开终端，输入：

jupyter notebook

即可进入jupyter，会自动连接服务器，并跳转到网页，新建一个python文件。

给文件重命名，容易区分：

在代码块中输入以下指令并运行，即可安装 xlwings（连接操作excel表格模块）：

!pip3 install xlwings --user

3.3 模块基本介绍与使用

xlwings
基本介绍：用于Python与Excel之间的交互，可以轻松地从 Excel 调用 Python，也可以利用Python自动化操作Excel，调用VBA，非常方便。

项目地址：https://github.com/xlwings/xlwings

基本使用方法：新建一个excel文件，取名为xlwings_wb.xlsx，并新建一个sheet，取名为first_sht，在其A1单元格内插入字符串简说Python。

# 导入xlwings，并起一个别名 xw，方便操作

import xlwings as xw
# 1、创建一个app应用，打开Excel程序

# visible=True 表示打开操作Excel过程可见 初次接触可以设置为True，了解其过程

# add_book=False 表示启动app后不用新建个工作簿

app = xw.App(visible=True, add_book=False)
# 2、新建一个工作簿

wb = app.books.add()
# 3、新建一个sheet，并操作

# 3.1 新建sheet 起名为first_sht

sht = wb.sheets.add('first_sht')

# 3.2 在新建的sheet表中A1位置插入一个值：简说Python

sht.range('A1').value = '简说Python'

# 3.3 保存新建的工作簿，并起一个名字

wb.save('xlwings_wb.xlsx')
# 4、关闭工作簿

wb.close()

# 5、程序运行结束，退出Excel程序 app.quit()

通过简单五步，我们就可以完成新建一个excel，并向其中指定sheet中的指定位置输入值了。

四、xlwings模块实战

4.1 基础语法一览

导包

# 基础导入包import xlwings as xw  # 程序第一步

打开关闭Excel程序（理解成excel软件打开、关闭）

# visible=True 表示打开操作Excel过程可见 初次接触可以设置为True，了解其过程

# add_book=False 表示启动app后不用新建个工作簿

app = xw.App(visible=True, add_book=False)  # 程序第二步

# 关闭excel程序 app.close() # 程序最后一步

工作簿相关操作（理解成excel文件）

# 1、新建一个工作簿wb = app.books.add()  # 程序第三步

# 2、保存新建的工作簿，并起一个名字# 程序倒数第三步，非常关键，保存操作数据结果wb.save('xlwings_wb.xlsx')  

# 3、打开一个已经存在的工作簿wb = app.books.open('xlwings_wb.xlsx')  # 程序第三步

# 4、关闭工作簿wb.close()  # 程序倒数第二步

sheet相关操作（理解成工作表）

# 在工作簿中新建一个sheet，起名为 second_sht

sht1 = wb.sheets.add('second_sht')
# 选中已经存在的sheet

sht2 = wb.sheets('first_sht')
# 也可以通过索引选择已存在的sheet

sht3 = wb.sheets[0]  # 选中工作簿中的第一个sheet
# 获取工作簿中工作表的个数

sht_nums = wb.sheets.count

print('工作簿中的sheet个数为：%d'% sht_nums)
# 当前工作表名字

sht1.name
# 获取指定sheet中数据的行数

sht1.used_range.last_cell.row
# 获取指定sheet中数据的列数

sht1.used_range.last_cell.column

# 删除指定的sheet 比如删除：first_sht wb.sheets('first_sht').delete()

单元格相关操作（就是excel单元格子）

'''写入'''# 在工作表中指定位置插入数据sht1.range('B1').value = '简说Python'

# 在工作表指定位置插入多个数据 默认是横向插入sht1.range('B2').value = [1, 2, 3, 4]

# 在工作表指定位置竖向插入多个数据# 设置options(transpose=True)，transpose=True表示转置的意思sht1.range('B3').options(transpose=True).value = ['老表', '老表Pro', '老表Max', '老表Mini']

# 在工作表指定位置开始插入多行数据sht1.range('B7').value = [['a', 'b'], ['c', 'd']]

# 在工作表指定位置开始插入多列数据sht1.range('B9').options(transpose=True).value = [['a', 'b'], ['c', 'd']]

# 向单元格写入公式sht1.range('F2').formula = '=sum(B2:E2)'

'''

读取

'''

# 在工作表中读取指定位置数据

print('单元格B1=', sht1.range('B1').value)
# 在工作表中读取指定区域数据 一行

print('单元格B2:F2=', sht1.range('B2:F2').value)
# 在工作表中读取指定区域数据 一列

print('单元格B3:B6=', sht1.range('B3:B6').value)

# 在工作表中读取指定区域数据一个区域 # 设置options(transpose=True)就可以按列读不设置就是按行读 print('单元格B7:C10=', sht1.range('B7:C10').options(transpose=True).value)

'''删除'''# 删除指定单元格中的数据sht1.range('B10').clear()

# 删除指定范围内单元格数据 sht1.range('B7:B9').clear()

'''

格式修改

'''

# 选中已经存在的sheet

sht1 = wb.sheets('second_sht')

# 返回单元格绝对路径

sht1.range('B3').get_address()

# sht1.range('B3').address
# 合并单元格B3 C3

sht1.range('B3:C3').api.merge()
# 解除合并单元格B3 C3

sht1.range('B3:C3').api.unmerge()
# 向指定单元格添加带超链接文本 

# address- 超连接地址

# text_to_display- 超链接文本内容

# screen_tip- 鼠标放到超链接上后显示提示内容

sht1.range('C2').add_hyperlink(address='https://pythonbrief.blog.csdn.net/',

                   text_to_display='简说Python CSDN博客',

                   screen_tip='点击查看简说Python CSDN博客')

                   

# 获取指定单元格的超链接地址

sht1.range('C2').hyperlink
# 自动调试指定单元格高度和宽度

sht1.range('B1').autofit()
# 设置指定单元格背景颜色

sht1.range('B1').color = (93,199,221)
# 返回指定范围内的中第一列的编号 数字，如：A-1 B-2

sht1.range('A2:B2').column

# 获取或者设置行高/列宽 # row_height/column_width会返回行高/列宽，范围内行高/列宽不一致会返回None # 也可以设置一个新的行高/列宽 sht1.range('A2').row_height = 25 sht1.range('B2').column_width = 20

在windows上可以使用以下方法设置单元格文字颜色等格式，如下：

# windows系统下字体设置在 sheet.range().api.Font下# 颜色sht1.range('A1').api.Font.Color = (255,0,124)# 字体名字sht1.range('A1').api.Font.Name = '宋体'# 字体大小sht1.range('A1').api.Font.Size = 28# 是否加粗sht1.range('A1').api.Font.Bold = True# 数字格式sht1.range('A1').api.NumberFormat = '0.0'# -4108 水平居中# -4131 靠左# -4152 靠右sht1.range('A1').api.HorizontalAlignment = -4108# -4108 垂直居中（默认)# -4160 靠上# -4107 靠下# -4130 自动换行对齐。sht1.range('A1').api.VerticalAlignment = -4130# 设置上边框线风格和粗细sht1.range('A1').api.Borders(8).LineStyle = 5sht1.range('A1').api.Borders(8).Weight = 3...

在mac下可以通过以下方法设置字体格式：

# 设置单元格的字体颜色

rgb_tuple = (0, 10, 200)

sht1.range('B1').api.font_object.color.set(rgb_tuple)
# 获取指定单元格字体颜色

sht1.range('B1').api.font_object.color.get()
# 获取指定单元格字体名字 可以使用set方法修改字体 set('宋体')

sht1.range('B1').api.font_object.name.get()
# 设置指定单元格字体格式 可以用get方法查看单元格字体格式

sht1.range('B3').api.font_object.font_style.set('加粗')
# 设置指定单元格字体大小

sht1.range('B3').api.font_object.font_size.set(20)
# 设置边框线粗细

sht1.range('B2').api.get_border(which_border=9).weight.set(4)

# 设置边框线风格 sht1.range('B2').api.get_border(which_border=9).line_style.set(8) ...

再次提醒，进行完所有操作后一定要记得执行以下三句：

# 保存新建的工作簿，并起一个名字（如果已存在有名字的excel文件，就直接save即可）wb.save()# 关闭工作簿（关闭Excel文件）wb.close()# 程序运行结束，退出Excel程序app.quit()

自动生成统计图

import xlwings as xw
# 新建一个sheet

app = xw.App(visible=True, add_book=False)

wb = app.books.open('xlwings_wb.xlsx')

sht3 = wb.sheets.add('third_sht')
import pandas as pd

import numpy as np
# 生成模拟数据

df = pd.DataFrame({

    'money':np.random.randint(45, 50, size = [1, 20])[0],

},

    index=pd.date_range('2021-02-01', '2021-02-20'),  # 行索引和时间相关

)

df.index.name = '消费日期'  # 设置索引名字
sht3.range('A1').value = df

# 生成图表 chart1 = sht3.charts.add() # 创建一个图表对象 chart1.set_source_data(sht3.range('A1').expand()) # 加载数据 chart1.chart_type = 'line' # 设置图标类型 chart1.top = sht3.range('D2').top chart1.left = sht3.range('D2').left # 设置图标开始位置

除了绘制折线图，我们还可以绘制其他类型的图，修改chart_type值即可。

# 查看其他chart_types值xw.constants.chart_types

返回结果，感兴趣的读者朋友可以自己试试～

('3d_area',

 '3d_area_stacked',

 '3d_area_stacked_100',

 '3d_bar_clustered',

 '3d_bar_stacked',

 '3d_bar_stacked_100',

 '3d_column',

 '3d_column_clustered',

 '3d_column_stacked',

 '3d_column_stacked_100',

 '3d_line',  # 3D折线图

 '3d_pie', # 3D饼图

 '3d_pie_exploded',

 'area',  # 面积图

 'area_stacked',

 'area_stacked_100',

 'bar_clustered',  # 柱状图相关

 'bar_of_pie',

 'bar_stacked',

 'bar_stacked_100',

 'bubble',  # 气泡图

 'bubble_3d_effect',

 'column_clustered',  # 条形图相关

 'column_stacked',

 'column_stacked_100',

 'combination',

 'cone_bar_clustered',

 'cone_bar_stacked',

 'cone_bar_stacked_100',

 'cone_col',

 'cone_col_clustered',

 'cone_col_stacked',

 'cone_col_stacked_100',

 'cylinder_bar_clustered',

 'cylinder_bar_stacked',

 'cylinder_bar_stacked_100',

 'cylinder_col',

 'cylinder_col_clustered',

 'cylinder_col_stacked',

 'cylinder_col_stacked_100',

 'doughnut',

 'doughnut_exploded',

 'line',  # 折线图

 'line_markers',

 'line_markers_stacked',

 'line_markers_stacked_100',

 'line_stacked',

 'line_stacked_100',

 'pie',

 'pie_exploded',

 'pie_of_pie',

 'pyramid_bar_clustered',

 'pyramid_bar_stacked',

 'pyramid_bar_stacked_100',

 'pyramid_col',

 'pyramid_col_clustered',

 'pyramid_col_stacked',

 'pyramid_col_stacked_100',

 'radar',

 'radar_filled',

 'radar_markers',

 'stock_hlc',  # 有意思 股票K线图

 'stock_ohlc',

 'stock_vhlc',

 'stock_vohlc',

 'surface',

 'surface_top_view',

 'surface_top_view_wireframe',

 'surface_wireframe',

 'xy_scatter',

 'xy_scatter_lines',

 'xy_scatter_lines_no_markers',

 'xy_scatter_smooth',

 'xy_scatter_smooth_no_markers')

将本地图片或者matplotlib图片保存到excel

'''matplotlib 生成的图片存入excel'''import matplotlib.pyplot as plt# 随便绘制一个饼图fig1 = plt.figure()  # 先创建一个图像对象plt.pie([0.5, 0.3, 0.2],  # 值        labels=['a', 'b', 'c'],  # 标签        explode=(0, 0.2, 0),  # （爆裂）距离        autopct='%1.1f%%',   # 显示百分数格式        shadow=True)  # 是否显示阴影plt.show() 

# 将饼图添加到excel指定位置 J17为图片开始位置sht3.pictures.add(fig1, name='matplotlib',                   left=sht3.range('J17').left,                   top=sht3.range('J17').top, update=True)

'''

本地图片存入excel

'''

# 将本地图片添加到excel指定位置 J1为图片开始位置

pic_path = './0923.jpeg'

sht3.pictures.add(pic_path, name='local', 

                  left=sht3.range('J1').left, 

                  top=sht3.range('J1').top, update=True)

4.2 实战练习一：将消费数据可视化生成带平均线的趋势图，存入excel

'''实战练习一：将消费数据可视化生成带平均线的趋势图，存入excel数据就是之前生成的模拟数据'''import matplotlib.pyplot as plt

fig, ax = plt.subplots()  # 创建一个画板# 生成一条新线- 平均消费数据money_m = [df['money'].mean(axis=0) for i in range(len(df['money']))]# 建一条线：消费趋势折线图ax.plot(df.index, df['money'], color='#f16a0b', label='每日消费')# 再建一条线：平均消费直线图ax.plot(df.index, money_m, linestyle='--', color='#301502', label='平均消费')# 设置横纵坐标基础说明ax.set_xlabel(u'日期') ax.set_ylabel(u'消费金额/元') ax.set_title(u'消费折线图')  # 设置x轴值显示方向plt.setp(ax.get_xticklabels(), rotation=70, horizontalalignment='right')# 添加图例（label说明到图片上） loc设置显示位置ax.legend(loc=2) plt.show()sht3.pictures.add(fig, name='消费情况',                   left=sht3.range('J1').left,                   top=sht3.range('J1').top, update=True)

4.3 实战练习二：将基金数据以指定的格式存储到excel并生成基金净值走势图

新建一个sheet，然后获取数据

'''

实战练习二：将股票数据以指定的格式存储到excel并生成股票走势图

利用akshare爬取上证指数数据 代号：sh000001

'''

import akshare as ak

import xlwings as xw
# -1、新建一个sheet

app = xw.App(visible=True, add_book=False)

wb = app.books.open('xlwings_wb.xlsx')

sz_sht = wb.sheets.add('sz_sht')  # 第一次新建

# sz_sht = wb.sheets['sz_sht']  # 已经存在，直接打开

# 获取数据 open high low close volume index-date # volume 是成交量多少股 # 0、获取数据 sz_index = ak.stock_zh_index_daily(symbol='sh000001') sz_index

处理并存储数据到excel

# 1、处理下数据sz_data = sz_index['20210101':'20210922'].iloc[:,0:4]  # 只取今年数据 ohlcsz_data.index = sz_data.index.strftime('%m-%d') # 将索引日期格式改为 月-日

# 2、存储数据sz_sht.range('A1').value = sz_data

处理表格中数据格式

# 3、处理数据格式

# - 表头字体 格式加粗 背景颜色

sz_sht.range('A1:E1').api.font_object.name.set('Calibri')

sz_sht.range('A1:E1').api.font_object.font_style.set('加粗')

sz_sht.range('A1:E1').api.font_object.color.set((255, 255, 255))

# 背景颜色

sz_sht.range('A1:E1').color = (16, 156, 245) 

# mac上居中未实现，有了解的小伙伴可以留言指出下，感谢

# sz_sht.range('B1').api.font_object.horizontalalignment = xw.constants.Constants.xlCenter

# - 添加边框 # 1 左+内部竖线 sz_sht.range('A1:E177').api.get_border(which_border=1).weight.set(2) # 10 最右侧竖线 sz_sht.range('A1:E177').api.get_border(which_border=10).weight.set(2) # 3 上+内部横线 sz_sht.range('A1:E177').api.get_border(which_border=3).weight.set(2) # 9 最下面横线 sz_sht.range('A1:E177').api.get_border(which_border=9).weight.set(2)

生成ohlc k线图并存储到excel指定位置

# 4、生成图表chart_ohlc = sz_sht.charts.add()  # 创建一个图表对象chart_ohlc.set_source_data(sz_sht.range('A1').expand())  # 加载数据chart_ohlc.chart_type = 'stock_ohlc'  # 设置图标类型chart_ohlc.top = sz_sht.range('G2').top  chart_ohlc.left = sz_sht.range('G2').left  # 设置图标开始位置

4.4 实战练习三：将基金数据以指定的格式存储到excel并生成基金净值走势图

创建一个新的sheet&获取数据

'''

实战练习三：将基金数据以指定的格式存储到excel并生成基金净值走势图

利用akshare爬取易方达蓝筹基金数据 基金代号：005827

'''

import akshare as ak

import matplotlib.pyplot as plt

import xlwings as xw
# 1、新建一个sheet&获取数据

app = xw.App(visible=True, add_book=False)

wb = app.books.open('xlwings_wb.xlsx')

# sht_fund = wb.sheets.add('fund_sht')

sht_fund = wb.sheets['fund_sht']

# 净值日期单位净值日增长率 yfd_fund = ak.fund_em_open_fund_info(fund='005827', indicator='单位净值走势')

数据处理&存储数据

# 2、数据处理 yfd_fund['单位净值'] = yfd_fund['单位净值'].astype(float)yfd_fund = yfd_fund.round(4)  # 数值列只保留4位小数

# 3、存储数据sht_fund.range('A1').value = yfd_fund# 存储后发现有索引列，删除（也可以在数据处理时处理）sht_fund.range('A:A').delete()

设置数据格式

# 4、设置数据格式

# - 表头字体 格式加粗 背景颜色

sht_fund.range('A1:C1').api.font_object.name.set('Calibri')

sht_fund.range('A1:C1').api.font_object.font_style.set('加粗')

sht_fund.range('A1:C1').api.font_object.color.set((255, 255, 255))

# 背景颜色

sht_fund.range('A1:C1').color = (16, 156, 245) 

# mac上居中未实现，有了解的小伙伴可以留言指出下，感谢

# sht_fund.range('B1').api.font_object.horizontalalignment = xw.constants.Constants.xlCenter

# - 添加边框设置的时候多加一行一列，这样就可以两步设置好边框了 # 1 左+内部竖线 sht_fund.range('A1:D717').api.get_border(which_border=1).weight.set(2) # 3 上+内部横线 sht_fund.range('A1:C718').api.get_border(which_border=3).weight.set(2)

利用matplotlib画图存储

# 5、绘制图片fig, ax = plt.subplots(2, 1, figsize=(20, 20), dpi=100)  #2行 1列

# 生成一条新线- 平均消费数据yfd_fund_m = [yfd_fund['单位净值'].mean(axis=0) for i in range(len(yfd_fund['单位净值']))]# 图一# 建一条线：基金趋势折线图ax[0].plot(yfd_fund['净值日期'], yfd_fund['单位净值'], color='#a5ea37', label='单位净值')# 再建一条线：平均消费直线图ax[0].plot(yfd_fund['净值日期'], yfd_fund_m, linestyle='--', color='#F91860', label='平均净值')# 设置横纵坐标基础说明ax[0].set_xlabel(u'日期') ax[0].set_ylabel(u'单位净值/元') ax[0].set_title(u'易方达基金净值走势图')  # 添加图例（label说明到图片上） loc设置显示位置ax[0].legend(loc=2) 

# 图二# 建一条线：平均消费直线图ax[1].plot(yfd_fund['净值日期'], yfd_fund['日增长率'], color='#F9181c', label='日增长率')# 设置横纵坐标基础说明ax[1].set_xlabel(u'日期') ax[1].set_ylabel(u'日增长率/%') ax[1].set_title(u'易方达基金日增长率走势图') # 添加图例（label说明到图片上） loc设置显示位置ax[1].legend(loc=2)

# 设置x轴值显示方向plt.setp(ax[0].get_xticklabels(), rotation=70, horizontalalignment='right')plt.setp(ax[1].get_xticklabels(), rotation=70, horizontalalignment='right')

plt.show()sht_fund.pictures.add(fig, name='易方达基金数据',                   left=sht_fund.range('E2').left,                   top=sht_fund.range('E2').top, update=True)

再次提醒，进行完所有操作后一定要记得执行以下三句：

# 保存新建的工作簿，并起一个名字（如果已存在有名字的excel文件，就直接save即可）

wb.save()

# 关闭工作簿（关闭Excel文件）

wb.close()

# 程序运行结束，退出Excel程序

app.quit()

五、xlwings模块进阶实战

这个实战来自一个读者需求，虽然已经是一周后了，希望对大家学习xlwings有所帮助。

需求分析：输入一个搜索词/值，返回该搜索词/值所在的行列，如果不存在就返回：不存在。

技术分析：(标题起的高深)xlwings和vba联动

操作思路：

直接通过Python编写代码，利用xlwings中cell的find函数进行查找
编写一个vba宏函数，然后利用Python调用vba函数进行查找

这篇文章肝太久了，最后实战部分我们下回见，由于本人水平有限，文中错误，敬请指出，互相学习，一起进步。

运营学Python｜Python&Excel联动(上)