前言

1. 学习python自动化办公目的

• 学会使用python自动化处理文件，减少重复劳动
• 科学管理文件
• 能够不再使用别的软件合并、分割PDF等

2. 未来持续打卡内容

• Task03 Python与word和PDF
• Task04 简单的Python爬虫
• Task05 Python操作钉钉自动化
• Task06 其它推荐软件和网页

3. 资料来源

本文不详细介绍函数，简单介绍方法，函数可自行百度，也可看课程GitHub链接：https://github.com/datawhalechina/office-automation。
xlwings 文档：https://docs.xlwings.org/zh_CN/latest/quickstart.html

一、xlwings介绍

1. 是什么？有什么优点？

xlwings 是 Python 操作 Excel 的库之一，主要作为两门语言的交互工具，是python 操作excel比较成熟、杰出的库，调用简单方便，而且功能强大。xlwings有以下特点：

• 可以实现通过Excel宏调用Python脚本，以此来完成大规模复杂数据分析的工作
• 对VBA代码能力几乎无要求，大部分情况下可以不写VBA代码直接在Excel中调用Python脚本
• 运行速度快，结合了Python的处理复杂问题的能力和VBA是Excel内嵌语言运行速度快这两个特点，避免了以前python脚本频繁地读取写入Excel花费的冗余时间，处理大量数据的时候，“xlwings” 快速处理的优势就显现出来。并且最终python脚本的处理结果会显示在excel中.

2. 数据结构

• App: 单个Excel程序
  • 一个Excel的实例，在此实例下创建工作薄，要创建工作簿，就必须先创建App实例。
  • 一个App实例可以创建多个工作簿Book
  • 使用xlwings可以创建一个或者多个App
  • 多个App之间是相互独立的
  • 每个App对应一个PID值，PID值是数字编号，可以用来识别不同的App
  • Apps：Excel程序集合
• Book: 单个工作簿
  • Books：工作簿集合
  • 每个App中又可以创建多个工作薄Book
• Sheet: 单个工作表
  • Sheets：工作表集合

import xlwings as xw

xw.App() 打开一个excel应用

xw.Book() 创建一个工作薄

xw.Sheet() 创建一个工作表

3. 操作内容

• 新建：创建一个不存在的工作薄或者工作表
• 打开：打开一个已经存在的工作薄
• 引用：告诉程序，你要操作哪个对象。比如你打开了A、B、C三个工作薄，现在你想操作A工作薄，就要先引用A
• 激活：我们可以同时打开多个工作薄，但是一次只能操作一个工作簿，我们正在操作的这个工作薄称为当前活动工作薄

一个实例： 创建一个excel表格并保存

使用with语句可以不用写退出语句，后续有退出语句介绍。

import xlwings as xw
 
# 打开excel,参数visible表示处理过程是否可视,add_book表示是否打开新的Excel程序
with xw.App(visible=True,add_book=False) as app:
 # 创建一个工作薄
 book = app.books.add()
 # 工作薄中创建一个sheet表
 sht = book.sheets.add()
 # 向表格的A1单元格写入“Hello Python”
 sht.range('A1').value = 'Hello Python'
 # 保存
 book.save('./test.xlsx')

二、xlwings 基础操作

1. APP对象

1) 创建

app=xw.App(visible=True,add_book=False)  # 当然也可以通过app.visible = True设置可见性

visible用来设置程序是否可见，add_book用来设置是否自动创建工作簿（sheet），True表示自动创建（默认），False不创建。当设置成add_book=False时，可以创建App，但是还未生成PID，只有当这个App创建了工作簿后，才会生成自己的PID 。

import xlwings as xw
app=xw.App()
pid = app.pid
print(pid) # 6260就是这个App的PID

2) 退出

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

2. Book对象

1) 创建与打开

	xw.Book	xw.books
新建工作簿	xw.Book()	xw.books.add()
未保存的工作簿	xw.Book(‘Book1’)	xw.books[‘Book1’]
有全路径的工作簿	xw.Book(r’C:/path/to/file.xlsx’)	xw.books.open(r’C:/path/to/file.xlsx’)

• 创建的两种方式：
  • xw.Book()创建一个新的App，并在新App中新建一个Book
  • xw.books.add()在当前App下新建一个Book
• 打开一个已经存在的两种方式：
  • xw.Book(path)创建一个新的App，并在新App中打开一个Book
  • app.books.open(path)在当前App下打开一个Book

wb = app.books.open('绝对或者相对路径的excel文件')
'或者
wb = xw.Book('绝对或者相对路径的excel文件')

2) 激活、保存与关闭

• wb.activate()一个app中可能有多个book,激活一个book作为当前工作簿
• wb.save()保存工作簿
• wb.close()只是关闭并不会保存，所以在关闭之前必须要使用save()进行一下保存才可以。可以考虑使用with搭建上下文，实现关闭资源。
• 在完成所有操作后一定要执行的：

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

3. sheet 表操作

1) 创建sheet

wb.sheets.add(name,after=None,before=None)参数1为工作表名称，省略的话为Excel默认名称，参数2为插入位置，可选before或者after

sht = wb.sheets.add()
# 或者
sht = wb.sheets.add('test',after='sheet2')

2) 引用sheet

三种方式：

• wb.sheets('sheet1') ：指定名称获取sheet工作表
• wb.sheets(1) ：根据序号获取
• xw.sheets.active ：获取当前活动的工作表

3) sheet对象的方法和属性

• 方法
  • 清除工作表所有内容和格式sheet.clear()
  • 清除工作表的所有内容但是保留原有格式
    sheet.clear_contents()
  • 删除工作表 sht.delete()
  • 自动调整行高列宽 sht.autofit('c')
  • 在活动工作簿中选择 sht.select()
• 属性
  • sheet.name sheet.cells sheet.index sheet.names

4. 区域与单元格

1) 引用

操作区域或者单元格之前，首先就要引用。主要方法：

• xw.Range()
• app.range()
• sheet.range()
• sheet[ ]
  括号内可以使用坐标也可以使用编号，如A1

xw.Range('A1:D4')
xw.Range((1,1), (4，4))
xw.Range(xw.Range('A1'),xw.Range('D4'))
xw.Range(xw.Range('A1:E6'),xw.Range('C3:D7'))
xw.Range('NamedRange')
app.range("A1")  # 注意是小写的range
sht.range('A1') 
xw.books['MyBook.xlsx'].sheets[0].range('A1')
sht['A1']
sht['A1:D4']
sht[0,5]
sht[:5,:5]

2) 操作

• 存储与读取：range('A1').value
• 清除与删除:
  • rang.clear_contents()
  • rang.delete(shift=None)
• 其他操作
  • 获取数字格式 ：rang.number_format
  • 设置列宽
  • 合并单元格
  • 获取行高
  • 背景色
  • ……

############存储############
#储存单个值
# ".value“属性
sht.range('A1').value=1
#储存列表
# 将列表[1,2,3]储存在A1：C1中
sht.range('A1').value=[1,2,3]
# 将列表[1,2,3]储存在A1:A3中
sht.range('A1').options(transpose=True).value=[1,2,3]
# 将2x2表格，即二维数组，储存在A1:B2中，如第一行1，2，第二行3，4
sht.range('A1').options(expand='table').value=[[1,2],[3,4]]
############读取############
#读取单个值
# 将A1的值，读取到a变量中
a=sht.range('A1').value

#将值读取到列表中
#将A1到A2的值，读取到a列表中
a=sht.range('A1:A2').value
 
# 将第一行和第二行的数据按二维数组的方式读取
a=sht.range('A1:B2').value
############清除与删除############
# 清除range的内容
rng.clear_contents()
# 清除格式和内容
rng.clear()
# 删除
rng.delete(shift=None)
############其他设置############
# 获取数字格式
rng.number_format
 
# 设置数字格式
rng.number_format = '0.00%'
 
rng.insert(shift=None, copy_origin='format_from_left_or_above')
 
# 返回区域第一行的行号
rng.row
 
# 返回区域的第一列的号，注意返回的列号不是ABCD，而是1234
rng.column
 
# 获取行高 或者设置行高
rng.row_height  
rng.row_height = 20
 
# 获取列宽或设置列宽
rng.column_width
rng.column_width = 20
 
# 自适应行高列宽
rng.autofit()
rng.columns.autofit()
rng.rows.autofit()
 
# 合并单元格
rng.merge(across=False)
rng.merge_area # 返回合并单元格区域
rng.merge_cells # 返回True或者False，测试是否在合并单元格区域
rng.unmerge() # 取消单元格合并
 
# 背景色
rng.color # 获取指定区域的背景色
xw.Range('A1').color = (255,255,255) # 设置背景色
xw.Range('A2').color = None # 去除背景色

三、绘图

1. 使用Python自动生成统计图

一个从创建到结束的完整过程：

import xlwings as xw
import os 
import pandas as pd
import numpy as np

root_path = os.getcwd()# 获得当前工作路径

# 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位置插入数据，并绘图

# 生成模拟数据
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 = '消费日期'  # 设置索引名字

sht.range('A1').value = df #将df写入创建的sheet表sht中

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

#3.3 保存工作簿
wb.save(root_path+'xlwings_wb.xlsx')
# 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位置插入一个值：Datawhale
sht.range('A1').value = 'Datawhale'
# 3.3 保存新建的工作簿，并起一个名字
wb.save(root_path+'xlwings_wb.xlsx')

# 4、关闭工作簿
wb.close()

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

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

其他chart_types值：xw.constants.chart_types

 '3d_line',  # 3D折线图
 '3d_pie', # 3D饼图
 'area',  # 面积图
 'bar_clustered',  # 柱状图相关
 'bubble',  # 气泡图
 'column_clustered',  # 条形图相关
 'line',  # 折线图
 'stock_hlc',  # 有意思 股票K线图

2. 将Matplotlib图片保存到excel中

使用sheet.pictures.add()可以轻松的将Matplotlib 绘制的图标复制到EXCEL中。

import matplotlib.pyplot as plt
import xlwings as xw
#绘图
fig = plt.figure()
plt.plot([1, 2, 3])
#引用一个sheet
sheet = xw.Book().sheets[0]
#在sheet中添加图片
sheet.pictures.add(fig, name='MyPlot', update=True)