玩转 EXCEL

Python 操作 Excel 可能是自动化办公最火热的需求了，看一看公众号文章底部的视频广告就知道了，里面尽是一些 5 分钟搞定 excel，将数据生成漂亮的图标。

5 分钟虽然有些夸张，但是快速操作 Excel 的需求确是真的。 今天就来分享如何使用 Python 来读写 Excel。

Python 操作 Excel 的库

Python 操作 Excel 的库有很多，我们先来个对比：

从上面的表格中可以看出，xlwings 是唯一一个全部打☑️的，是 Python 最强大的处理 Excel 的库，今天的主角就是它，它有以下优点

• 1、Windows、Mac 都能用，Excel、WPS 也都能用。
• 2、功能齐全，支持 Excel 的新建、打开、修改、保存，pandas、xlsxwriter 不能全做到。
• 3、语法简单，用过一次后我就记住了。
• 4、可以调用 VBA，有丰富的 API。
• 5、可以与 pandas 等类库集成使用。

xlwings 安装

pip install xlwings

xlwings 读取 Excel

读取 excel 比较简单，比如现在有这样一个 excel：

先确定范围，这里是 A1 到 F4，多读一些也没关系，没有数据的格子会显示为 None。

import xlwings as xw

work_book = xw.Book('测试.xlsx')

sheet1 = work_book.sheets[0]

print(sheet1.book)

c = 0
for cell in sheet1.range('A1','E6'):
    c += 1
    print(cell.value, end ='\t')
    if c % 5 == 0:
        print("")

执行结果如下图所示：

假如无法预知数据的范围，可以使用 last_cell 方式获取最下边且最右边的一个单元格。

比如更好的方式是这样写：

import xlwings as xw

work_book = xw.Book('测试.xlsx')

sheet1 = work_book.sheets[0]

print(sheet1.book)

last_cell = sheet1.used_range.last_cell 
last_row = last_cell.row
last_col = last_cell.column

c = 0

for cell in sheet1.range((1,1),(last_row, last_col)):
    c += 1
    print(cell.value, end ='\t')
    if c % last_col  == 0:
        print("")

还有各种灵活的单元格访问方式：

# A1单元格
rng=sheet1['A1']
rng=sheet1['a1']

# A1:B5单元格
rng=sheet1['A1:B5']

# 第一行的第一列即a1
rng=sheet1[0,0] 

# B1单元格
rng=sheet1[0,1]

在读取到每一行，每一列的数据之后，我们就可以对这些数据进行加工，然后写回 excel 了。

xlwings 写入 Excel

现在来实现一个小小的需求：针对上述读取的 Excel，我们现在来统计分数的总和及平均数，并写入 Excel 的最后行。

import xlwings as xw

work_book = xw.Book('测试.xlsx')

sheet1 = work_book.sheets[0]

print(sheet1.book)

#last_cell = sheet1.used_range.last_cell
last_row = 4
last_col = 5

## 获取分数的列索引
score_col_index = ""

for cell in sheet1.range((1,1),(1,last_col)):
    if cell.value == '分数':
        score_col_index = cell.column

## 将分数存入列表
score_list = []

for row in range(2,last_row+1):
    cell = sheet1.range((row,score_col_index))
    score_list.append(cell.value)

print(score_list)
sum_score = sum(score_list)
avg_score = sum(score_list) / len(score_list)

## 计算出结果后写入 excel

sheet1.range((last_row + 1,1)).value = "合计"
sheet1.range((last_row + 1,last_col)).value = sum_score


sheet1.range((last_row + 2,1)).value = "平均值"
sheet1.range((last_row + 2,last_col)).value = round(avg_score,2)

work_book.save()
work_book.close()

代码的逻辑非常简单，首先获取分数所在的列，然后将所有的分数取出来保存在列表中，对其求和，求平均值，然后写回 Excel 的最后一行。

Excel 中插入图表

生成图表在 Excel 也是很常见的需求，除了可以用 Excel 本身的图表之外，还可以借助 Python 来插入图表。

比如说现在有一个股票的数据，我们用 Python 生成该股票的走势图，并插入到 Excel 中。

这里借助了 pandas 库，使用前请 pip install pandas 安装一下。

import xlwings as xw
import pandas as pd

wb = xw.Book('300369.xlsx')

sheet1 = wb.sheets[0]
print(sheet1.range('A1:D3').value)

data_frame = sheet1.range('A1:D354').options(pd.DataFrame).value
data_frame.drop(columns = ["股票代码","名称"],inplace = True)
print(data_frame.head())

ax = data_frame.plot()
fig = ax.get_figure()
sheet1.pictures.add(fig, name = '绿盟科技', update = True)
wb.save()

最终的效果如下：

Excel 转 Pdf

将一个工作簿转换为 Pdf 非常简单，一行代码就可以搞定：

import xlwings as xw

wb = xw.Book('300369.xlsx')
sheet1 = wb.sheets[0]
sheet1.to_pdf(path= '300369.pdf')

练习题：拆分与合并

现在，我们来解决这个问题：如何快速地批量处理内容相似的Excel？

批量合并。假设你需要对某些工作内容进行问卷调查，这时你用 Excel 做了调查问卷模版。我想你会这样做：先把 Excel 通过工作群分发给所有员工，再把群里收集到的反馈附件汇总成一个文件。批量拆分。假设你是公司的财务人员，你需要使用 Excel 对员工工资进行核算，之后再打印出来。但是公司要求员工薪水保密，所以每个员工的工资需要拆分成一个独立的文件，最后还需要打印出来。

现在给出 4 个相似的 Excel 文件，请把他合并成一个文件，然后再拆开成 4 个文件。