Python自动化操作Excel
来自*： 简说Python
二、基础准备
环境：python *.7+
需要安装第三方模块：xlwings
运行编辑器：jupyter notebook
三、动手动脑
*.0 我们为什么要学编程
首先和大家简单聊一下运营（这里运营不单单指传统意义上的运营工作者，其实只有工作场景中有经常用到office等办公软件，处理很多数据的都算）为什么要学习编程了？
站在我个人角度简单聊聊：
运营学编程绝不是为了解决上面这种简单 单一的问题
我们希望的是通过编程提高工作效率、优化工作流程
比如：批量处理excel文件（可能是*000或者更多行数据的增删改）
*.* 环境准备
首先需要你的电脑安装好了Python环境，并且安装好了Python开发工具。
如果你还没有安装，可以参考以下文章：
如果仅用Python来处理数据、爬虫、数据分析或者自动化脚本、机器学习等，建议使用Python基础环境+jupyter即可，安装使用参考Windows/Mac 安装、使用Python环境+jupyter notebook
如果想利用Python进行web项目开发等，建议使用Python基础环境+Pycharm，安装使用参考 ：Windows下安装、使用Pycharm教程，这下全了 和 Mac下玩转Python-安装使用Python/PyCharm 。
*.2 模块安装
本次实验是在jupyter notebook吉印通行，默认你已经安装好了Python基本环境和jupyter，如果没有安装可以看上面的环境安装部分。
首先打开终端，输入：
jupyter notebook
即可进入jupyter，会自动连接服务器，并跳转到网页，新建一个python文件。给文件重命名，容易区分：
在代码块中输入以下指令并运行，即可安装 xlwings（连接操作excel表格模块）：
!pip* install xlwings --user
*.* 模块基本介绍与使用
xlwings
基本介绍：用于Python与Excel之间的交互，可以轻松地从 Excel 调用 Python，也可以利用Python自动化操作Excel，调用VBA，非常方便。
02*yin.com/xlwings/xlwings
基本使用方法：新建一个excel文件，取名为xlwings_wb.xlsx，并新建一个sheet，取名为first_sht，在其A*单元格内插入字符串简说Python。
# 导入xlwings，并起一个别名 xw，方便操作
importxlwings asxw
# *、创建一个app应用，打开Excel程序
# visible=True 表示打开操作Excel过程可见 初次接触可以设置为True，了解其过程
# add_book=False 表示启动app后不用新建个工作簿
app = xw.App(visible= True, add_book= False)
# 2、新建一个工作簿
wb = app.books.add
# *、新建一个sheet，并操作
# *.* 新建sheet 起名为first_sht
sht = wb.sheets.add( 'first_sht')
# *.2 在新建的sheet表中A*位置插入一个值：简说Python
sht.range( 'A*').value = '简说Python'
# *.* 保存新建的工作簿，并起一个名字
wb.save( 'xlwings_wb.xlsx')
# *、关闭工作簿
wb.close
# *、程序运行结束，退出Excel程序
app.quit
通过简单五步，我们就可以完成新建一个excel，并向其中指定sheet中的指定位置输入值了。
四、xlwings模块实战*.* 基础语法一览
导包
# 基础导入包
importxlwings asxw # 程序第一步
打开关闭Excel程序（理解成excel软件打开、关闭）
# visible=True 表示打开操作Excel过程可见 初次接触可以设置为True，了解其过程
# add_book=False 表示启动app后不用新建个工作簿
app = xw.App(visible= True, add_book= False) # 程序第二步
# 关闭excel程序
app.close # 程序最后一步
工作簿相关操作（理解成excel文件）
# *、新建一个工作簿
wb = app.books.add # 程序第三步
# 2、保存新建的工作簿，并起一个名字
# 程序倒数第三步，非常关键，保存操作数据结果
wb.save( 'xlwings_wb.xlsx')
# *、打开一个已经存在的工作簿
wb = app.books.open( 'xlwings_wb.xlsx') # 程序第三步
# *、关闭工作簿
wb.close # 程序倒数第二步
sheet相关操作（理解成工作表）
# 在工作簿中新建一个sheet，起名为 second_sht
sht* = wb.sheets.add( 'second_sht')
# 选中已经存在的sheet
sht2 = wb.sheets( 'first_sht')
# 也可以通过索引选择已存在的sheet
sht* = wb.sheets[ 0] # 选中工作簿中的第一个sheet
# 获取工作簿中工作表的个数
sht_nums = wb.sheets.count
print( '工作簿中的sheet个数为：%d'% sht_nums)
# 当前工作表名字
sht*.name
# 获取指定sheet中数据的行数
sht*.used_range.last_cell.row
# 获取指定sheet中数据的列数
sht*.used_range.last_cell.column
# 删除指定的sheet 比如删除：first_sht
wb.sheets( 'first_sht').delete
单元格相关操作（就是excel单元格子）
写入
# 在工作表中指定位置插入数据
sht*.range( 'B*').value = '简说Python'
# 在工作表指定位置插入多个数据 默认是横向插入
sht*.range( 'B2').value = [ *, 2, *, *]
# 在工作表指定位置竖向插入多个数据
# 设置options(transpose=True)，transpose=True表示转置的意思
sht*.range( 'B*').options(transpose= True).value = [ '老表', '老表Pro', '老表Max', '老表Mini']
# 在工作表指定位置开始插入多行数据
sht*.range( 'B7').value = [[ 'a', 'b'], [ 'c', 'd']]
# 在工作表指定位置开始插入多列数据
sht*.range( 'B9').options(transpose= True).value = [[ 'a', 'b'], [ 'c', 'd']]
# 向单元格写入公式
sht*.range( 'F2').formula = '=sum(B2:E2)'
读取
# 在工作表中读取指定位置数据
print( '单元格B*=', sht*.range( 'B*').value)
# 在工作表中读取指定区域数据 一行
print( '单元格B2:F2=', sht*.range( 'B2:F2').value)
# 在工作表中读取指定区域数据 一列
print( '单元格B*:B*=', sht*.range( 'B*:B*').value)
# 在工作表中读取指定区域数据 一个区域
# 设置options(transpose=True)就可以按列读 不设置就是按行读
print( '单元格B7:C*0=', sht*.range( 'B7:C*0').options(transpose=True).value)
删除
# 删除指定单元格中的数据
sht*.range( 'B*0').clear
# 删除指定范围内单元格数据
sht*.range( 'B7:B9').clear
格式修改
# 选中已经存在的sheet
sht* = wb.sheets( 'second_sht')
# 返回单元格绝对路径
sht*.range( 'B*').get_address
# sht*.range('B*').address
# 合并单元格B* C*
sht*.range( 'B*:C*').api.merge
# 解除合并单元格B* C*
sht*.range( 'B*:C*').api.unmerge
# 向指定单元格添加带超链接文本
# address- 超连接地址
# text_to_display- 超链接文本内容
# screen_tip- 鼠标放到超链接上后显示提示内容
02*yin.com/',
text_to_display= '简说Python CSDN博客',
screen_tip= '点击查看简说Python CSDN博客')
# 获取指定单元格的超链接地址
sht*.range( 'C2').hyperlink
# 自动调试指定单元格高度和宽度
sht*.range( 'B*').autofit
# 设置指定单元格背景颜色
sht*.range( 'B*').color = (9*,*99,22*)
# 返回指定范围内的中第一列的编号 数字，如：A-* B-2
sht*.range( 'A2:B2').column
# 获取或者设置行高/列宽
# row_height/column_width会返回行高/列宽 ，范围内行高/列宽不一致会返回None
# 也可以设置一个新的行高/列宽
sht*.range( 'A2').row_height = 2*
sht*.range( 'B2').column_width = 20
在windows上可以使用以下方法设置单元格文字颜色等格式，如下：
# windows系统下字体设置在 sheet.range.api.Font下
# 颜色
sht*.range( 'A*').api.Font.Color = ( 2**, 0, *2*)
# 字体名字
sht*.range( 'A*').api.Font.Name = '宋体'
# 字体大小
sht*.range( 'A*').api.Font.Size = 28
# 是否加粗
sht*.range( 'A*').api.Font.Bold = True
# 数字格式
sht*.range( 'A*').api.NumberFormat = '0.0'
# -**08 水平居中
# -**** 靠左
# -***2 靠右
sht*.range( 'A*').api.HorizontalAlignment = -**08
# -**08 垂直居中（默认)
# -***0 靠上
# -**07 靠下
# -***0 自动换行对齐。
sht*.range( 'A*').api.VerticalAlignment = -***0
# 设置上边框线风格和粗细
sht*.range( 'A*').api.Borders( 8).LineStyle = *
sht*.range( 'A*').api.Borders( 8).Weight = *
在mac下可以通过以下方法设置字体格式：
# 设置单元格的字体颜色
rgb_tuple = ( 0, *0, 200)
sht*.range( 'B*').api.font_object.color.set(rgb_tuple)
# 获取指定单元格字体颜色
sht*.range( 'B*').api.font_object.color.get
# 获取指定单元格字体名字 可以使用set方法修改字体 set('宋体')
sht*.range( 'B*').api.font_object.name.get
# 设置指定单元格字体格式 可以用get方法查看单元格字体格式
sht*.range( 'B*').api.font_object.font_style.set( '加粗')
# 设置指定单元格字体大小
sht*.range( 'B*').api.font_object.font_size.set( 20)
# 设置边框线粗细
sht*.range( 'B2').api.get_border(which_border= 9).weight.set( *)
# 设置边框线风格
sht*.range( 'B2').api.get_border(which_border= 9).line_style.set( 8)
再次提醒，进行完所有操作后一定要记得执行以下三句：
# 保存新建的工作簿，并起一个名字（如果已存在有名字的excel文件，就直接save即可）
wb.save
# 关闭工作簿（关闭Excel文件）
wb.close
# 程序运行结束，退出Excel程序
app.quit
自动生成统计图
importxlwings asxw
# 新建一个sheet
app = xw.App(visible= True, add_book= False)
wb = app.books.open( 'xlwings_wb.xlsx')
sht* = wb.sheets.add( 'third_sht')
importpandas aspd
importnumpy asnp
# 生成模拟数据
df = pd.DataFrame({
'money':np.random.randint( **, *0, size = [ *, 20])[ 0],
index=pd.date_range( '202*-02-0*', '202*-02-20'), # 行索引和时间相关
df.index.name = '消费日期'# 设置索引名字
sht*.range( 'A*').value = df
# 生成图表
chart* = sht*.charts.add # 创建一个图表对象
chart*.set_source_data(sht*.range( 'A*').expand) # 加载数据
chart*.chart_type = 'line'# 设置图标类型
chart*.top = sht*.range( 'D2').top
chart*.left = sht*.range( 'D2').left # 设置图标开始位置
除了绘制折线图，我们还可以绘制其他类型的图，修改chart_type值即可。
# 查看其他chart_types值
xw.constants.chart_types
返回结果，感兴趣的读者朋友可以自己试试～
( '*d_area',
'*d_area_stacked',
'*d_area_stacked_*00',
'*d_bar_clustered',
'*d_bar_stacked',
'*d_bar_stacked_*00',
'*d_column',
'*d_column_clustered',
'*d_column_stacked',
'*d_column_stacked_*00',
'*d_line', # *D折线图
'*d_pie', # *D饼图
'*d_pie_exploded',
'area', # 面积图
'area_stacked',
'area_stacked_*00',
'bar_clustered', # 柱状图相关
'bar_of_pie',
'bar_stacked',
'bar_stacked_*00',
'bubble', # 气泡图
'bubble_*d_effect',
'column_clustered', # 条形图相关
'column_stacked',
'column_stacked_*00',
'combination',
'cone_bar_clustered',
'cone_bar_stacked',
'cone_bar_stacked_*00',
'cone_col',
'cone_col_clustered',
'cone_col_stacked',
'cone_col_stacked_*00',
'cylinder_bar_clustered',
'cylinder_bar_stacked',
'cylinder_bar_stacked_*00',
'cylinder_col',
'cylinder_col_clustered',
'cylinder_col_stacked',
'cylinder_col_stacked_*00',
'doughnut',
'doughnut_exploded',
'line', # 折线图
'line_markers',
'line_markers_stacked',
'line_markers_stacked_*00',
'line_stacked',
'line_stacked_*00',
'pie',
'pie_exploded',
'pie_of_pie',
'pyramid_bar_clustered',
'pyramid_bar_stacked',
'pyramid_bar_stacked_*00',
'pyramid_col',
'pyramid_col_clustered',
'pyramid_col_stacked',
'pyramid_col_stacked_*00',
'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
importmatplotlib.pyplot asplt
# 随便绘制一个饼图
fig* = plt.figure # 先创建一个图像对象
plt.pie([ 0.*, 0.*, 0.2], # 值
labels=[ 'a', 'b', 'c'], # 标签
explode=( 0, 0.2, 0), # （爆裂）距离
autopct= '%*.*f%%', # 显示百分数格式
shadow= True) # 是否显示阴影
plt.show
# 将饼图添加到excel指定位置 J*7为图片开始位置
sht*.pictures.add(fig*, name= 'matplotlib',
left=sht*.range( 'J*7').left,
top=sht*.range( 'J*7').top, update= True)
本地图片存入excel
# 将本地图片添加到excel指定位置 J*为图片开始位置
pic_path = './092*.jpeg'
sht*.pictures.add(pic_path, name= 'local',
left=sht*.range( 'J*').left,
top=sht*.range( 'J*').top, update= True)
*.2 实战练习一：将消费数据可视化生成带平均线的趋势图，存入excel'''
实战练习一：将消费数据可视化生成带平均线的趋势图，存入excel
数据就是之前生成的模拟数据
importmatplotlib.pyplot asplt
fig, ax = plt.subplots # 创建一个画板
# 生成一条新线- 平均消费数据
money_m = [df[ 'money'].mean(axis= 0) fori inrange(len(df[ 'money']))]
# 建一条线：消费趋势折线图
ax.plot(df.index, df[ 'money'], color= '#f**a0b', label= '每日消费')
# 再建一条线：平均消费直线图
ax.plot(df.index, money_m, linestyle= '--', color= '#*0**02', 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
sht*.pictures.add(fig, name= '消费情况',
left=sht*.range( 'J*').left,
top=sht*.range( 'J*').top, update= True)
*.* 实战练习二：将基金数据以指定的格式存储到excel并生成基金净值走势图
新建一个sheet，然后获取数据
实战练习二：将股票数据以指定的格式存储到excel并生成股票走势图
利用akshare爬取上证指数数据 代号：sh00000*
importakshare asak
importxlwings asxw
# -*、新建一个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= "sh00000*")
sz_index
处理并存储数据到excel
# *、处理下数据
sz_data = sz_index[ '202*0*0*': '202*0922'].iloc[:, 0: *] # 只取今年数据 ohlc
sz_data.index = sz_data.index.strftime( '%m-%d') # 将索引日期格式改为 月-日
# 2、存储数据
sz_sht.range( 'A*').value = sz_data
处理表格中数据格式
# *、处理数据格式
# - 表头字体 格式加粗 背景颜色
sz_sht.range( 'A*:E*').api.font_object.name.set( 'Calibri')
sz_sht.range( 'A*:E*').api.font_object.font_style.set( '加粗')
sz_sht.range( 'A*:E*').api.font_object.color.set(( 2**, 2**, 2**))
# 背景颜色
sz_sht.range( 'A*:E*').color = ( **, ***, 2**)
# mac上居中未实现，有了解的小伙伴可以留言指出下，感谢
# sz_sht.range('B*').api.font_object.horizontalalignment = xw.constants.Constants.xlCenter
# - 添加边框
# * 左+内部竖线
sz_sht.range( 'A*:E*77').api.get_border(which_border= *).weight.set( 2)
# *0 最右侧竖线
sz_sht.range( 'A*:E*77').api.get_border(which_border= *0).weight.set( 2)
# * 上+内部横线
sz_sht.range( 'A*:E*77').api.get_border(which_border= *).weight.set( 2)
# 9 最下面横线
sz_sht.range( 'A*:E*77').api.get_border(which_border= 9).weight.set( 2)
生成ohlc k线图并存储到excel指定位置
# *、生成图表
chart_ohlc = sz_sht.charts.add # 创建一个图表对象
chart_ohlc.set_source_data(sz_sht.range( 'A*').expand) # 加载数据
chart_ohlc.chart_type = 'stock_ohlc'# 设置图标类型
chart_ohlc.top = sz_sht.range( 'G2').top
chart_ohlc.left = sz_sht.range( 'G2').left # 设置图标开始位置
*.* 实战练习三：将基金数据以指定的格式存储到excel并生成基金净值走势图
创建一个新的sheet获取数据
实战练习三：将基金数据以指定的格式存储到excel并生成基金净值走势图
利用akshare爬取易方达蓝筹基金数据 基金代号：00*827
importakshare asak
importmatplotlib.pyplot asplt
importxlwings asxw
# *、新建一个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= "00*827", indicator= "单位净值走势")
数据处理存储数据
# 2、数据处理
yfd_fund[ '单位净值'] = yfd_fund[ '单位净值'].astype(float)
yfd_fund = yfd_fund.round( *) # 数值列只保留*位小数
# *、存储数据
sht_fund.range( 'A*').value = yfd_fund
# 存储后发现有索引列，删除（也可以在数据处理时处理）
sht_fund.range( 'A:A').delete
设置数据格式
# *、设置数据格式
# - 表头字体 格式加粗 背景颜色
sht_fund.range( 'A*:C*').api.font_object.name.set( 'Calibri')
sht_fund.range( 'A*:C*').api.font_object.font_style.set( '加粗')
sht_fund.range( 'A*:C*').api.font_object.color.set(( 2**, 2**, 2**))
# 背景颜色
sht_fund.range( 'A*:C*').color = ( **, ***, 2**)
# mac上居中未实现，有了解的小伙伴可以留言指出下，感谢
# sht_fund.range('B*').api.font_object.horizontalalignment = xw.constants.Constants.xlCenter
# - 添加边框 设置的时候多加一行一列，这样就可以两步设置好边框了
# * 左+内部竖线
sht_fund.range( 'A*:D7*7').api.get_border(which_border= *).weight.set( 2)
# * 上+内部横线
sht_fund.range( 'A*:C7*8').api.get_border(which_border= *).weight.set( 2)
利用matplotlib画图存储
# *、绘制图片
fig, ax = plt.subplots( 2, *, figsize=( 20, 20), dpi= *00) #2行 *列
# 生成一条新线- 平均消费数据
yfd_fund_m = [yfd_fund[ '单位净值'].mean(axis= 0) fori inrange(len(yfd_fund[ '单位净值']))]
# 图一
# 建一条线：基金趋势折线图
ax[ 0].plot(yfd_fund[ '净值日期'], yfd_fund[ '单位净值'], color= '#a*ea*7', label= '单位净值')
# 再建一条线：平均消费直线图
ax[ 0].plot(yfd_fund[ '净值日期'], yfd_fund_m, linestyle= '--', color= '#F9*8*0', label= '平均净值')
# 设置横纵坐标基础说明
ax[ 0].set_xlabel( u'日期')
ax[ 0].set_ylabel( u'单位净值/元')
ax[ 0].set_title( u'易方达基金净值走势图')
# 添加图例（label说明到图片上） loc设置显示位置
ax[ 0].legend(loc= 2)
# 图二
# 建一条线：平均消费直线图
ax[ *].plot(yfd_fund[ '净值日期'], yfd_fund[ '日增长率'], color= '#F9*8*c', label= '日增长率')
# 设置横纵坐标基础说明
ax[ *].set_xlabel( u'日期')
ax[ *].set_ylabel( u'日增长率/%')
ax[ *].set_title( u'易方达基金日增长率走势图')
# 添加图例（label说明到图片上） loc设置显示位置
ax[ *].legend(loc= 2)
# 设置x轴值显示方向
plt.setp(ax[ 0].get_xticklabels, rotation= 70, horizontalalignment= 'right')
plt.setp(ax[ *].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
这篇文章肝太久了，最后实战部分我们下回见，由于本人水平有限，文中错误，敬请指出，互相学习，一起进步。
觉得文章有用的朋友，记得点赞、转发、留言三连支持下，感谢。
--- EOF ---
推荐↓↓↓
