Python辦公自動化（3）對Excel的操作

1.讀取excel文件

1.安裝工具

終端下載讀取excel文檔的工具庫：

?pip3 install -i https://pypi.tuna.tsinghua.edu.cn/simple xlrd

若對版本有特殊需求：?

刪除當前版本：pip3 uninstall xlrd

下載所需要的版本：pip3 install -i https://pypi.tuna.tsinghua.edu.cn/simple xlrd==1.2.0

2.打開目錄中的excel文件

import xlrd
excel = xlrd.open_workbook('question.xlsx')
print(excel)

3.搜索excel文件頁簽

import xlrd
excel = xlrd.open_workbook('question.xlsx')
# 根據頁簽名找到頁簽
book1 = excel.sheet_by_name('題庫')
print(book1.name)
# 獲取第一個頁簽
book2 = excel.sheet_by_index(0)
print(book2.name)
# 遍歷所有頁簽
for i in excel.sheets():print(i.name)

4.?獲得表格中的數據

import xlrd
excel = xlrd.open_workbook('question.xlsx')
book = excel.sheet_by_name('題庫')
for i in book.get_rows():content = []for j in i:content.append(j.value)print(content)

2.新建excel文件

1.安裝工具

終端下載創建excel文檔的工具庫（支持寫入文本、數字、公式等）：

pip3 install -i https://pypi.tuna.tsinghua.edu.cn/simple xlsxwriter

2.創建excel文檔

import xlsxwriter
# 生成excel
excel = xlsxwriter.Workbook('study.xlsx')
# 添加工作簿
book = excel.add_worksheet('考試成績')
title = ['姓名','性別','年齡','成績','等級']
data = [['Adela','woman','20','90','1'],['Zoya','woman','24','95','2']
]
for index,item in enumerate(title):# 向表格中寫入內容，參數1：行索引；參數2：列索引；參數3：內容book.write(0,index,item)
for row_index, row_data in enumerate(data):for col_index, item in enumerate(row_data):book.write(row_index + 1, col_index, item)  # 從第 1 行開始寫入數據
excel.close()

3.復制excel文檔

代碼思路：讀取+新建+寫入

import xlsxwriter
import xlrd
def read():result = []excel = xlrd.open_workbook('study.xlsx')book = excel.sheet_by_name('考試成績')for i in book.get_rows():content = []for j in i:content.append(j.value) # 行中的每一個格子里的數據，并封裝到content列表中，作為一行數據result.append(content) # 將每一個行的數據追加到返回結果集列表中return resultdef write(content):excel = xlsxwriter.Workbook('study_copy.xlsx')book = excel.add_worksheet('考試成績')for index,data in enumerate(content):for sub_index,sub_data in enumerate(data):book.write(index,sub_index,sub_data)excel.close()
if __name__ == '__main__':result = read()write(result)

4.生成excel并附帶圖表

import xlsxwriter
excel = xlsxwriter.Workbook('study1.xlsx')
book = excel.add_worksheet('成績等級')
data = [['優秀','良好','中等','一般'],[1100,2000,1000,500]
]
book.write_column('A1',data[0])
book.write_column('B1',data[1])
# 繪制圖表
# 參數1：圖表類型 type聲明設置類型,column柱狀圖
chart = excel.add_chart({'type':'column'})
# 定義要展示的數據
chart.add_series({'categories':'=成績等級!$A1:$A4','values':'=成績等級!$B1:$B4','name':'成績占比'})
# 設置圖表標題
chart.set_title({'name':'成績占比圖'})
# 向表格中插入圖表
# 參數1：位置；參數2：圖表的引用名
book.insert_chart('A10',chart)
excel.close()

3.讀取并計算數據

1.安裝工具

終端下載操作excel文檔的工具庫（大多用于讀取、寫入和修改等）：

pip3 install -i https://pypi.tuna.tsinghua.edu.cn/simple xlutils

2.讀取數據

編程思路：

1.打開 Excel 文件；

2.獲取第一個工作表；

3.初始化兩個數據：分別是用于存儲按分類匯總的總價goods_type與每行的單品總價count_price；

4.用遍歷對每一行計算單品總價

5.最后返回goods_type和count_price

def read_data():wb = xlrd.open_workbook('goods.xlsx')sh = wb.sheet_by_index(0)goods_type = {} # 分類總價 {水果:20,生活用品:30}count_price = [] # 單品總價 [10,20,30]for r in range(sh.nrows):# sh.cell_value(r,4)獲得格子中的數據，參數1行索引值，參數2列索引值count = sh.cell_value(r,3)*sh.cell_value(r,4)#將計算的小計值追加到單品總價中count_price.append(count)key = sh.cell_value(r,0)# 判斷分類總價中是否包含該商品，如包含則追加計算if goods_type.get(key):goods_type[key] += countelse:goods_type[key] = countreturn goods_type,count_price

3.保存數據

def save(_type,_count):wb = xlrd.open_workbook('goods.xlsx')sh_t = wb.sheet_by_index(0)wb2 = copy(wb) # 復制當前的商品表格sh = wb2.get_sheet(0)for r in range(sh_t.nrows):sh.write(r,sh_t.ncols,_count[r])sh2 = wb2.add_sheet('匯總的數據')for i,key in enumerate(_type.keys()):sh2.write(i,0,key)sh2.write(i,1,_type[key])wb2.save('商品匯總數據.xlsx')

4.主程序

read_data()：讀取并自動匹配匯總數據和單品總價

save()：將結果保存到新的 Excel 文件中

if __name__ == '__main__':t,c = read_data()save(t,c)

4. 拆分Excel文件

將Excel表格中的數據按照A1列分類，并按類別存到不同的sheet頁中

import xlrd
from xlutils.copy import copydef get_data():wb = xlrd.open_workbook('goods.xlsx')sh = wb.sheet_by_index(0)"""{a:[{},{},{}],b:[{},{},{}],c:[{},{},{}],}"""all_data = {}for r in range(sh.nrows):d = {'type':sh.cell_value(r,1),'name':sh.cell_value(r,2),'price':sh.cell_value(r,3),'count':sh.cell_value(r,4)}key = sh.cell_value(r,0)if all_data.get(key):all_data[key].append(d)else:all_data[key] = [d]return all_datadef save(data):wb = xlrd.open_workbook('goods.xlsx')wb2 = copy(wb)for key in data.keys():temp_sheet = wb2.add_sheet(key)for i,d in enumerate(data[key]):temp_sheet.write(i, 0, d.get('type'))temp_sheet.write(i, 1, d.get('name'))temp_sheet.write(i, 2, d.get('price'))temp_sheet.write(i, 3, d.get('count'))wb2.save('表格拆分.xlsx')
if __name__ == '__main__':all_data = get_data()save(all_data)

5. 合并Excel文件

終端下載操作excel文檔的工具庫（除了讀取寫入，還包括格式化單元格、插入圖表、處理多個工作表等功能，功能比xlutils強大，支持大數據量的處理?）：

pip3 install -i https://pypi.tuna.tsinghua.edu.cn/simple openpyxl

?1.合并至同一sheet頁

from openpyxl import load_workbook,Workbook
import os
def copy_data():wb = Workbook() # 獲得excel對象sh = wb.active # 獲得焦點sheet頁all_data = []for name in os.listdir('./銷售表'):path = f'./銷售表/{name}'tmp_wb = load_workbook(path)tmp_sh = tmp_wb.activefor r in range(1,tmp_sh.max_row+1):# 獲取整行數據row_value = []for c in range(1,tmp_sh.max_column+1):value = tmp_sh.cell(r,c).valuerow_value.append(value)# 將獲取的行數據，添加到全局數據里if row_value not in all_data:all_data.append(row_value)# 將數據追加至新的excel中for data in all_data:sh.append(data)wb.save('合并Excel.xlsx')
if __name__ == '__main__':copy_data()

2.合并至多個sheet頁

from openpyxl import load_workbook,Workbook
import os
def copy_data():wb = Workbook()for name in os.listdir('./銷售表'):path = f'./銷售表/{name}'tmp_wb = load_workbook(path)tmp_sh = tmp_wb.active# 創建sheet頁sh = wb.create_sheet(name[:-5]) # 單肩包.xlsx->單肩包for r in range(1,tmp_sh.max_row+1):row_value = []for c in range(1,tmp_sh.max_column+1):value = tmp_sh.cell(r,c).valuerow_value.append(value)sh.append(row_value)del wb['Sheet']wb.save('合并至多個sheet頁.xlsx')
if __name__ == '__main__':copy_data()

6. 生成工資條

?省腦細胞式描述：遍歷原始工作表的每一行，再單獨存到一個新Excel表中

from openpyxl import load_workbook,Workbook
def create_excel():wb = load_workbook('工資數據.xlsx')sh = wb.activetitle = ["工號”,“姓名","部門", "基本工資","提成","加班工資","社保扣除","考勤扣除","應付工資","郵箱"]for i,row in enumerate(sh.rows):if i == 0:continueelse:temp_wbook = Workbook()temp_sh = temp_wbook.activetemp_sh.append(title)row_data = [cell.value for cell in row] #['5732','Adela','研發部'，'8000',......]獲得當前行的數據temp_sh.append(row_data) # 將當前行數據追加直sheet頁中temp_wbook.save(f'./工資條/{row_data[1]}.xlsx')
if __name__ == '__main__':create_excel()

當然你可以再把他們合并成一起當作練習，并在新的sheet頁中顯示工資數的柱狀圖：

import xlsxwriter
from openpyxl import load_workbook, Workbook
import osdef combine_data():wb = Workbook()sh = wb.activeall_data = []for name in os.listdir('./工資條'):path = f'./工資條/{name}'tmp_wb = load_workbook(path)tmp_sh = tmp_wb.activefor r in range(1, tmp_sh.max_row + 1):row_value = []for c in range(1, tmp_sh.max_column + 1):value = tmp_sh.cell(r, c).valuerow_value.append(value)if row_value not in all_data:all_data.append(row_value)for data in all_data:sh.append(data)wb.save('合并工資條.xlsx')return all_datadef create_chart(data):# 創建新Excelwb = xlsxwriter.Workbook('圖表.xlsx')ws = wb.add_worksheet()# 寫入數據for row_num, row in enumerate(data):for col_num, value in enumerate(row):ws.write(row_num, col_num, value)# 創建柱狀圖（假設第2列是姓名，第8列是工資）chart = wb.add_chart({'type': 'column'})chart.add_series({'categories': f'=Sheet1!$B$1:$B${len(data)}','values': f'=Sheet1!$H$1:$H${len(data)}'})# 插入圖表ws.insert_chart('K2', chart)wb.close()if __name__ == '__main__':data = combine_data()  # 合并數據create_chart(data)  # 生成圖表

7.統計加班時間

1.搭建一個數據表格

from datetime import date
from openpyxl import load_workbook,Workbook
def create_data():wb =Workbook()sh = wb.activerows = [['Date','姓名','打卡時間'],[date(2025, 4, 2), '張三', '18:50'],[date(2025, 4, 2), '李四', '19:15'],[date(2025, 4, 4), '王五', '22:30'],[date(2025, 4, 5), '趙六', '18:45'],[date(2025, 4, 6), '錢七', '17:20'],[date(2025, 4, 7), '孫八', '19:05'],[date(2025, 4, 8), '周九', '21:10'],[date(2025, 4, 9), '吳十', '22:35'],[date(2025, 4, 2), '李四', '19:15'],]for row in rows:sh.append(row)wb.save('打卡時間.xlsx')

2.處理表格數據

def statistics():wb = load_workbook('打卡時間.xlsx')sh = wb.activedata = []for i in range(2, sh.max_row + 1):t_data = []for j in range(1, sh.max_column + 1):t_data.append(sh.cell(i, j).value)# 統計計算h, m = t_data[2].split(":")full = int(h) * 60 + int(m)  # 打卡時間轉換成分鐘tmp = full - 18 * 60  # 與18:00的差值t_data.append(tmp)t_data[0] = t_data[0].date()data.append(t_data)n_wb = Workbook()n_sh = n_wb.activefor d in data:n_sh.append(d)n_wb.save('統計時間.xlsx')

3.主程序

if __name__ == '__main__':
??? statistics()
??? create_data()

8.查重復數據

查詢是否有重復的人名

from openpyxl import load_workbook,Workbook
from openpyxl.styles import PatternFill
import os
def dum():wb = load_workbook('打卡時間.xlsx')sh = wb.activeindex = [] #存放重復數據的索引值tmp = []for i,c in enumerate(sh['B']):flag = c.value not in tmpif flag:tmp.append(c.value)else:index.append(i)fill = PatternFill('solid',fgColor='AEEEEE')for i,r in enumerate(sh.rows):if i in index:for c in r:c.fill = fillprint(f'第{i+1}條數據是重復數據')wb.save('查重復數據.xlsx')
if __name__ == '__main__':dum()

9.提取身份證號信息

根據身份證號碼的格式，提取出生辰年月，并進行年齡等數學運算

from openpyxl import load_workbook
from datetime import datetime
def get_code():now_year = datetime.now().yearwb = load_workbook('身份證信息.xlsx')sh = wb.activemax_colunm = sh.max_columnfor i,cell in enumerate(sh['B']):pno = cell.value# 6位行政區號 4位年份 2位月份 2月日期 4位個人識別碼year = pno[6:10]month = pno[10:12]day = pno[12:14]age = now_year - int(year)sh.cell(i + 1, max_colunm + 1).value = yearsh.cell(i + 1, max_colunm + 2).value = monthsh.cell(i + 1, max_colunm + 3).value = daysh.cell(i + 1, max_colunm + 4).value = agewb.save('提取身份證號信息.xlsx')
if __name__ == '__main__':get_code()