Python自动化操作Excel

admin 2022年9月20日15:21:02安全开发评论19 views13317字阅读44分23秒阅读模式
来自公众号:简说Python

二、基础准备

  • 环境: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文件。Python自动化操作Excel给文件重命名,容易区分:Python自动化操作Excel

在代码块中输入以下指令并运行,即可安装 xlwings(连接操作excel表格模块):

!pip3 install xlwings --user

3.3 模块基本介绍与使用

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

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

Python自动化操作Excel基本使用方法:新建一个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()
Python自动化操作Excel

通过简单五步,我们就可以完成新建一个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 = [1234]

# 在工作表指定位置竖向插入多个数据
# 设置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)'
Python自动化操作Excel
'''
读取
'''

# 在工作表中读取指定位置数据
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)
Python自动化操作Excel
'''
删除
'''

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

# 删除指定范围内单元格数据 
sht1.range('B7:B9').clear()
Python自动化操作Excel
'''
格式修改
'''

# 选中已经存在的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 = 5
sht1.range('A1').api.Borders(8).Weight = 3
...

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

# 设置单元格的字体颜色
rgb_tuple = (010200)
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)
...
Python自动化操作Excel
Python自动化操作Excel
Python自动化操作Excel

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

# 保存新建的工作簿,并起一个名字(如果已存在有名字的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(4550, size = [120])[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  # 设置图标开始位置 

Python自动化操作Excel除了绘制折线图,我们还可以绘制其他类型的图,修改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.50.30.2],  # 值
        labels=['a''b''c'],  # 标签
        explode=(00.20),  # (爆裂)距离
        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)
Python自动化操作Excel
'''
本地图片存入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)
Python自动化操作Excel

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

'''
实战练习一:将消费数据可视化生成带平均线的趋势图,存入excel
数据就是之前生成的模拟数据
'''

import matplotlib.pyplot as plt

fig, ax = plt.subplots()  # 创建一个画板
# 生成一条新线- 平均消费数据
money_m = [df['money'].mean(axis=0for 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)
Python自动化操作Excel

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]  # 只取今年数据 ohlc
sz_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((255255255))
# 背景颜色
sz_sht.range('A1:E1').color = (16156245
# 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  # 设置图标开始位置 
Python自动化操作Excel

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((255255255))
# 背景颜色
sht_fund.range('A1:C1').color = (16156245
# 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(21, figsize=(2020), dpi=100)  #2行 1列

# 生成一条新线- 平均消费数据
yfd_fund_m = [yfd_fund['单位净值'].mean(axis=0for 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)
Python自动化操作Excel

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

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

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

觉得文章有用的朋友,记得点赞、转发、留言三连支持下,感谢。

--- EOF ---


推荐↓↓↓

原文始发于微信公众号(Python编程):Python自动化操作Excel

特别标注: 本站(CN-SEC.COM)所有文章仅供技术研究,若将其信息做其他用途,由用户承担全部法律及连带责任,本站不承担任何法律及连带责任,请遵守中华人民共和国安全法.
  • 我的微信
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
admin
  • 本文由 发表于 2022年9月20日15:21:02
  • 转载请保留本文链接(CN-SEC中文网:感谢原作者辛苦付出):
                  Python自动化操作Excel http://cn-sec.com/archives/1307135.html

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: