Python自動(dòng)化辦公-讓 Excel 飛起來(lái)

Python 操作 Excel 可能是自動(dòng)化辦公最火熱的需求了，看一看公眾號(hào)文章底部的視頻廣告就知道了，里面盡是一些 5 分鐘搞定 excel，將數(shù)據(jù)生成漂亮的圖表。

5 分鐘雖然有些夸張，但是快速操作 Excel 的需求確是真的。今天就來(lái)分享如何使用 Python 玩轉(zhuǎn) Excel。

主要內(nèi)容：

• Python 操作 Excel 的輪子對(duì)比
• xlwings 讀 Excel
• xlwings 寫(xiě) Excel
• Excel 中插入圖表
• Excel 轉(zhuǎn) Pdf
• Excel 拆分與合并
• Excel 轉(zhuǎn) REST API

Python 操作 Excel 的輪子

Python 操作 Excel 的輪子有很多，導(dǎo)致選擇困難癥，為了幫你做選擇，我這里放個(gè)對(duì)比圖：

從上面的表格中可以看出，xlwings 是唯一一個(gè)全部都支持?的，是 Python 最強(qiáng)大的處理 Excel 的庫(kù)，今天的主角就是它，它有以下優(yōu)點(diǎn)

• 1、Windows、Mac 都能用，Excel、WPS 也都能用。
• 2、功能齊全，支持 Excel 的新建、打開(kāi)、修改、保存，pandas、xlsxwriter 不能全做到。
• 3、語(yǔ)法簡(jiǎn)單，用過(guò)一次后我就記住了。
• 4、可以調(diào)用 VBA，有豐富的 API。
• 5、可以與 pandas 等類庫(kù)集成使用。

xlwings 安裝

pip install xlwings

需要注意的是，請(qǐng)使用操作系統(tǒng)自帶的終端來(lái)運(yùn)行 xlwings 程序，否則可能遇到權(quán)限不足的問(wèn)題。

xlwings 讀取 Excel

讀取 excel 比較簡(jiǎn)單，比如現(xiàn)在有這樣一個(gè) excel：

先確定范圍，這里是 A1 到 F4，多讀一些也沒(méi)關(guān)系，沒(méi)有數(shù)據(jù)的格子會(huì)顯示為 None。

import xlwings as xw

work_book = xw.Book('測(cè)試.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("")

執(zhí)行結(jié)果如下圖所示：

假如無(wú)法預(yù)知數(shù)據(jù)的范圍，可以使用 last_cell 方式獲取最下邊且最右邊的一個(gè)單元格。

比如更好的方式是這樣寫(xiě)：

import xlwings as xw

work_book = xw.Book('測(cè)試.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("")

還有各種靈活的單元格訪問(wèn)方式：

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

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

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

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

在讀取到每一行，每一列的數(shù)據(jù)之后，我們就可以對(duì)這些數(shù)據(jù)進(jìn)行加工，然后寫(xiě)回 excel 了。

xlwings 寫(xiě)入 Excel

現(xiàn)在來(lái)實(shí)現(xiàn)一個(gè)小小的需求：針對(duì)上述讀取的 Excel，我們現(xiàn)在來(lái)統(tǒng)計(jì)分?jǐn)?shù)的總和及平均數(shù)，并寫(xiě)入 Excel 的最后行。

import xlwings as xw

work_book = xw.Book('測(cè)試.xlsx')

sheet1 = work_book.sheets[0]

print(sheet1.book)

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

## 獲取分?jǐn)?shù)的列索引
score_col_index = ""

for cell in sheet1.range((1,1),(1,last_col)):
    if cell.value == '分?jǐn)?shù)':
        score_col_index = cell.column

## 將分?jǐn)?shù)存入列表
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)

## 計(jì)算出結(jié)果后寫(xiě)入 excel

sheet1.range((last_row + 1,1)).value = "合計(jì)"
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()

代碼的邏輯非常簡(jiǎn)單，首先獲取分?jǐn)?shù)所在的列，然后將所有的分?jǐn)?shù)取出來(lái)保存在列表中，對(duì)其求和，求平均值，然后寫(xiě)回 Excel 的最后一行。

Excel 中插入圖表

生成圖表在 Excel 也是很常見(jiàn)的需求，除了可以用 Excel 本身的圖表之外，還可以借助 Python 來(lái)插入圖表。

比如說(shuō)現(xiàn)在有一個(gè)股票的數(shù)據(jù)，我們用 Python 生成該股票的走勢(shì)圖，并插入到 Excel 中。

這里借助了 pandas 庫(kù)，使用前請(qǐng) 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 轉(zhuǎn) Pdf

將一個(gè)工作簿轉(zhuǎn)換為 Pdf 非常簡(jiǎn)單，一行代碼就可以搞定：

import xlwings as xw

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

拆分與合并

現(xiàn)在，我們來(lái)解決這個(gè)問(wèn)題：如何快速地批量處理內(nèi)容相似的 Excel？

批量拆分： 假設(shè)你是公司的財(cái)務(wù)人員，你需要使用 Excel 對(duì)員工工資進(jìn)行核算，之后再打印出來(lái)。但是公司要求員工薪水保密，所以每個(gè)員工的工資需要拆分成一個(gè)獨(dú)立的文件，最后再轉(zhuǎn)成 pdf 通過(guò)郵件發(fā)送出去。

excel 內(nèi)容大致如下：

拆分后：

代碼如下：

import xlwings as xw

work_book = xw.Book('excel拆分練習(xí).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


"""
定義緩存
"""
head_titles = []
rows_content = []
for i in range(last_row - 1):
    rows_content.append([])

"""
讀取 excel 內(nèi)容至緩存
"""
for cell in sheet1.range((1,1),(1, last_col)):
    head_titles.append(cell.value)

col_index = 0
row_index = 0
for cell in sheet1.range((2,1),(last_row, last_col)):
    rows_content[row_index].append(cell.value)
    col_index += 1
    if col_index % last_col == 0:
        row_index += 1
        col_index = 0

"""
將緩存寫(xiě)入 excel
"""

# 遍歷 rows_content

for index, row in enumerate(rows_content):
    work_book = xw.Book()
    sheet1 = work_book.sheets[0]
    for col_index, col in enumerate(row):
        sheet1.range((1,col_index + 1)).value = head_titles[col_index]
        sheet1.range((2,col_index + 1)).value = col
    work_book.save(f'{row[0]}.xlsx')
    work_book.close()

批量合并。假設(shè)你需要對(duì)某些工作內(nèi)容進(jìn)行問(wèn)卷調(diào)查，這時(shí)你用 Excel 做了調(diào)查問(wèn)卷模版。我想你會(huì)這樣做：先把 Excel 通過(guò)工作群分發(fā)給所有員工，再把群里收集到的反饋附件匯總成一個(gè)文件。

現(xiàn)在你可以仿照上面拆分的方法來(lái)進(jìn)行批量合并。

將 excel 內(nèi)容轉(zhuǎn)為 REST API

執(zhí)行：

xlwings restapi run -host 0.0.0.0 -p 5000

就可以將已打開(kāi)的 excel 文件內(nèi)容轉(zhuǎn)換為  REST API 接口：

然后就可以遠(yuǎn)程訪問(wèn)：

最后的話

xlwings 很強(qiáng)大，本文拋磚引玉，如果經(jīng)常使用的話，還是到官方文檔^[1]去獲取更多方法。

另外本文的代碼及樣例文件：https://gitee.com/somenzz/code-example/tree/master/excel^[2]。

都看到這里來(lái)了，說(shuō)明你也是個(gè)愛(ài)學(xué)習(xí)的人，點(diǎn)贊在看支持一下吧，如果還沒(méi)關(guān)注的話，可以關(guān)注一下，順手學(xué)個(gè) Python 實(shí)用技巧。感謝關(guān)注。

留言討論

參考資料