• 一個(gè)Excel電子表格文檔稱為一個(gè)工作簿
• 一個(gè)工作簿保存在一個(gè)擴(kuò)展名為.xlsx的文件中
• 一個(gè)工作簿可以包含多個(gè)表
• 用戶當(dāng)前查看的表（或關(guān)閉Excel前最后查看的表）稱為活動(dòng)表
• 在特定行和列的方格稱為單元格、格子

處理Excel表格需要用到openpyxl模塊，該模塊需要手動(dòng)安裝pip install openpyxl

xls和xlsx

簡單來說：
xls是excel2003及以前版本所生成的文件格式
xlsx是excel2007及以后版本所生成的文件格式
（excel 2007之后版本可以打開上述兩種格式，但是excel2013只能打開xls格式）
進(jìn)一步的詳細(xì)解釋參見：excel后綴.xls和.xlsx有什么區(qū)別

基本操作

用到的test.xlsx表格
在這里插入圖片描述

1：用openpyxl模塊打開Excel文檔，查看所有sheet表

openpyxl.load_workbook()函數(shù)接受文件名，返回一個(gè)workbook數(shù)據(jù)類型的值。這個(gè)workbook對(duì)象代表這個(gè)Excel文件，這個(gè)有點(diǎn)類似File對(duì)象代表一個(gè)打開的文本文件。

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")	# 返回一個(gè)workbook數(shù)據(jù)類型的值print(workbook.sheetnames)	# 打印Excel表中的所有表# 結(jié)果：# ["Sheet1", "Sheet2"]

2.1：通過sheet名稱獲取表格

在第10行，使用workbook["Sheet1"]獲取指定sheet表

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值print(workbook.sheetnames)  # 打印Excel表中的所有表sheet = workbook["Sheet1"]  # 獲取指定sheet表print(sheet)# 結(jié)果：# ["Sheet1", "Sheet2"]# 

2.2：獲取活動(dòng)表

使用workbook.active獲取活動(dòng)表

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active     # 獲取活動(dòng)表print(sheet)# 結(jié)果：# 

3.1：獲取表格的尺寸

這里所說的尺寸大小，指的是excel表格中的數(shù)據(jù)有幾行幾列，針對(duì)的是不同的sheet而言
使用sheet.dimensions獲取表格的尺寸
下面打印的A1:B7是什么意思呢？

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook["Sheet1"]  # 獲取指定sheet表print(sheet.dimensions)     # 獲取表格的尺寸大小# 結(jié)果：# A1:B7

4.1：獲取單元格中的數(shù)據(jù)

方法1：指定坐標(biāo)的方式
sheet[“A1”]

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active     # 獲取活動(dòng)表# print(sheet.dimensions)     # 獲取表格的尺寸大小cell1 = sheet["A1"]         # 獲取A1單元格的數(shù)據(jù)cell2 = sheet["B7"]         # 獲取B7單元格的數(shù)據(jù)# cell2 = sheet["B7"].value		# 另一種寫法# 正確示范# cell1.value獲取單元格A1中的值# cell2.value獲取單元格B7中的值print(cell1.value,cell2.value)  # 姓名 18# 錯(cuò)誤示范print(cell1,cell2)  #  

方法2： 指定行列的方式
sheet.cell(row=, column=)方式

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active     # 獲取活動(dòng)表# print(sheet.dimensions)     # 獲取表格的尺寸大小cell1 = sheet.cell(row=1,column=1)         # 獲取第1行第1列的數(shù)據(jù)cell2 = sheet.cell(row=3,column=2)         # 獲取第3行第4的數(shù)據(jù)# 正確示范# cell1.value獲取單元格A1中的值# cell2.value獲取單元格B7中的值print(cell1.value,cell2.value)  # 姓名 41

4.2：獲取單元格的行、列、坐標(biāo)

• .row 獲取某個(gè)格子的行數(shù)；
• .columns 獲取某個(gè)格子的列數(shù)；
• .corordinate 獲取某個(gè)格子的坐標(biāo)；

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表# print(sheet.dimensions)     # 獲取表格的尺寸大小cell = sheet.cell(row=3, column=2)  # 獲取第3行第4的數(shù)據(jù)print(cell.value, cell.row, cell.column, cell.coordinate)"""結(jié)果：41 3 2 B3"""

5：獲取區(qū)間內(nèi)的數(shù)據(jù)

獲取單行單列數(shù)據(jù)的時(shí)候，使用一層for循環(huán)；獲取多行多列、指定區(qū)間的數(shù)據(jù)時(shí)，使用兩層for循環(huán)

獲取指定區(qū)間的數(shù)據(jù)

1. 使用sheet["A1:A5"]拿到指定區(qū)間
2. 使用兩個(gè)for循環(huán)拿到數(shù)據(jù)

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：")print(sheet)cell = sheet["A1:A5"]  # 獲取A1到A5的數(shù)據(jù)print(cell)# 打印A1到A5的數(shù)據(jù)for i in cell:    for j in i:        print(j.value)# 結(jié)果：# 當(dāng)前活動(dòng)表是：# # ((,), (,), (,), (,), (,))# 姓名# 張三# 李四# 王五# 趙六

獲取指定行列的數(shù)據(jù)

• sheet[“A”] — 獲取A列的數(shù)據(jù)
• sheet[“A:C”] — 獲取A,B,C三列的數(shù)據(jù)
• sheet[5] — 只獲取第5行的數(shù)據(jù)

下面的代碼，獲取一列數(shù)據(jù)的時(shí)候，使用一層for循環(huán)

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：" + str(sheet))cell = sheet["2"]  # 獲取第2行的數(shù)據(jù)# 打印A1到A5的數(shù)據(jù)for i in cell:    print(i.value)# 結(jié)果：# 當(dāng)前活動(dòng)表是：# 張三# 74

下面代碼，獲取兩列數(shù)據(jù)的時(shí)候，使用兩層for循環(huán)。注意到，兩列的結(jié)果打印到一起了，可讀性較差

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：" + str(sheet))cell = sheet["A:B"]  # 獲取AB列的數(shù)據(jù)# 打印AB列數(shù)據(jù)for i in cell:    for j in i:        print(j.value)# 結(jié)果：# 當(dāng)前活動(dòng)表是：# 姓名# 張三# 李四# 王五# 趙六# alice# bob# 年齡# 74# 41# 56# 12# 17# 18

按行、列獲取值

iter_rows()：按行讀取
iter_cols()：按列讀取

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：" + str(sheet))# 按行獲取值print("按行獲取值")for i in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=2):    for j in i:        print(j.value)# 按列獲取值print("按列獲取值")for i in sheet.iter_cols(min_row=2, max_row=5, min_col=1, max_col=2):    for j in i:        print(j.value)# 結(jié)果：# 當(dāng)前活動(dòng)表是：# 按行獲取值# 張三# 74# 李四# 41# 王五# 56# 趙六# 12# 按列獲取值# 張三# 李四# 王五# 趙六# 74# 41# 56# 12

獲取活動(dòng)表的行列數(shù)

方法1：使用
sheet.max_row 獲取行數(shù)
sheet.max_column 獲取列數(shù)

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("2.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：" + str(sheet))rows = sheet.max_row        # 獲取行數(shù)column = sheet.max_column   # 獲取列數(shù)print(rows)print(column)"""結(jié)果：當(dāng)前活動(dòng)表是：3816"""

方法2：自己寫一個(gè)for循環(huán)

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：" + str(sheet))row = []column = []# 獲取當(dāng)前活動(dòng)表有多少行for i in sheet.rows:    row.append(list(i))         # i是元組類型，轉(zhuǎn)為列表# 獲取當(dāng)前活動(dòng)表有多少列for i in sheet.columns:    column.append(list(i))    # i是元組類型，轉(zhuǎn)為列表print("行數(shù)："+str(len(row)))print("列數(shù)："+str(len(column)))"""結(jié)果：當(dāng)前活動(dòng)表是：行數(shù)：12列數(shù)：3"""

操作

創(chuàng)建新的excel

第9行代碼用來指定創(chuàng)建的excel的活動(dòng)表的名字：

• 不寫第9行，默認(rèn)創(chuàng)建sheet
• 寫了第9行，創(chuàng)建指定名字的sheet表

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.Workbook()sheet = workbook.activesheet.title = "1號(hào)sheet"workbook.save("1.xlsx")

修改單元格、excel另存為

第9行代碼，通過給單元格重新賦值，來修改單元格的值
第9行代碼的另一種寫法sheet["B1"].value = "age"
第10行代碼，保存時(shí)如果使用原來的（第7行）名字，就直接保存；如果使用了別的名字，就會(huì)另存為一個(gè)新文件

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表sheet["A1"] = "name"workbook.save("test.xlsx")

添加數(shù)據(jù)

插入有效數(shù)據(jù)

使用append()方法，在原來數(shù)據(jù)的后面，按行插入數(shù)據(jù)

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：" + str(sheet))data = [    ["素子",23],    ["巴特",24],    ["塔奇克馬",2]]for row in data:    sheet.append(row)   # 使用append插入數(shù)據(jù)workbook.save("test.xlsx")

插入空行空列

• insert_rows(idx=數(shù)字編號(hào), amount=要插入的行數(shù))，插入的行數(shù)是在idx行數(shù)的下方插入
• insert_cols(idx=數(shù)字編號(hào), amount=要插入的列數(shù))，插入的位置是在idx列數(shù)的左側(cè)插入

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：" + str(sheet))sheet.insert_rows(idx=3, amount=2)sheet.insert_cols(idx=2, amount=1)workbook.save("test.xlsx")

刪除行、列

• delete_rows(idx=數(shù)字編號(hào), amount=要?jiǎng)h除的行數(shù))
• delete_cols(idx=數(shù)字編號(hào), amount=要?jiǎng)h除的列數(shù))

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：" + str(sheet))sheet.delete_rows(idx=10)           # 刪除第10行sheet.delete_cols(idx=1, amount=2)  # 刪除第1列，及往右共2列workbook.save("test.xlsx")

移動(dòng)指定區(qū)間的單元格(move_range)

move_range(“數(shù)據(jù)區(qū)域”,rows=,cols=)：正整數(shù)為向下或向右、負(fù)整數(shù)為向左或向上

import osimport openpyxlpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：" + str(sheet))sheet.move_range("D11:F12",rows=0,cols=-3)  # 移動(dòng)D11到F12構(gòu)成的矩形格子workbook.save("test.xlsx")

字母列號(hào)與數(shù)字列號(hào)之間的轉(zhuǎn)換

核心代碼

from openpyxl.utils import get_column_letter, column_index_from_string# 根據(jù)列的數(shù)字返回字母print(get_column_letter(2))  # B# 根據(jù)字母返回列的數(shù)字print(column_index_from_string("D"))  # 4

舉個(gè)例子：

import osimport openpyxlfrom openpyxl.utils import get_column_letter, column_index_from_stringpath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("2.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是：" + str(sheet))# 根據(jù)列的數(shù)字返回字母print(get_column_letter(2))  # B# 根據(jù)字母返回列的數(shù)字print(column_index_from_string("D"))  # 4

字體樣式

查看字體樣式

import osimport openpyxlimport openpyxl.stylespath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是："+str(sheet))cell = sheet["A1"]font = cell.fontprint("當(dāng)前單元格的字體樣式是")print(font.name, font.size, font.bold, font.italic, font.color)"""當(dāng)前活動(dòng)表是：當(dāng)前單元格的字體樣式是等線 11.0 False False Parameters:rgb=None, indexed=None, auto=None, theme=1, tint=0.0, type="theme""""

修改字體樣式

openpyxl.styles.Font(name=字體名稱,size=字體大小,bold=是否加粗,italic=是否斜體,color=字體顏色)
其中，字體顏色中的color是RGB的16進(jìn)制表示

import osimport openpyxlimport openpyxl.stylespath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print(sheet)cell = sheet["A1"]cell.font = openpyxl.styles.Font(name="微軟雅黑", size=20, bold=True, italic=True, color="FF0000")workbook.save("test.xlsx")

再者，可以使用for循環(huán)，修改多行多列的數(shù)據(jù)，在這里介紹了獲取的方法

import osimport openpyxlimport openpyxl.stylespath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print(sheet)cell = sheet["A"]for i in cell:    i.font = openpyxl.styles.Font(name="微軟雅黑", size=20, bold=True, italic=True, color="FF0000")workbook.save("test.xlsx")

設(shè)置對(duì)齊格式

Alignment(horizontal=水平對(duì)齊模式,vertical=垂直對(duì)齊模式,text_rotation=旋轉(zhuǎn)角度,wrap_text=是否自動(dòng)換行)
水平對(duì)齊：‘distributed’，‘justify’，‘center’，‘left’， ‘centerContinuous’，"right，‘general’
垂直對(duì)齊：‘bottom’，‘distributed’，‘justify’，‘center’，‘top’

import osimport openpyxl.stylespath = r"C:/Users/asuka/Desktop"os.chdir(path)  # 修改工作路徑workbook = openpyxl.load_workbook("test.xlsx")  # 返回一個(gè)workbook數(shù)據(jù)類型的值sheet = workbook.active  # 獲取活動(dòng)表print("當(dāng)前活動(dòng)表是&#