openpyxl 的用法實(shí)例

1.1 Openpyxl 庫的安裝使用

openpyxl 模塊是一個讀寫 Excel 2010 文檔的 Python 庫涯呻，如果要處理更早格式的 Excel 文檔，需要用到額外的庫，openpyxl 是一個比較綜合的工具，能夠同時讀取和修改 Excel 文檔含长。其他很多的與 Excel 相關(guān)的項目基本只支持讀或者寫 Excel 一種功能葛虐。新建、讀取鲫寄、保存工作簿。

1.2 Excel 的新建疯淫、讀取地来、保存

**1.2.1 新建保存工作簿(覆蓋創(chuàng)建）

新建：openpyxl.Workbook()，注意這個的 W 是大寫的（本人吃過虧）熙掺，可以設(shè)置 write_only=True/False 的讀寫方式未斑，默認(rèn)是可寫。保存：workbook.save('工作簿名.xlsx')

from openpyxl import Workbook
wb=Workbook() # 新建工作簿
 wb.save('我的工作簿.xlsx') # 相對路徑保存工作簿

每個 workbook 創(chuàng)建后币绩，默認(rèn)會存在一個 sheet蜡秽。也可以自行創(chuàng)建新的 sheet。

1.2.2 讀取保存工作簿

讀取工作簿：
openpyxl.load_workbook('工作簿名.xlsx')类浪，注意以下相關(guān)參數(shù)的設(shè)置载城。
read_only=False/True False 表示可以讀、寫费就，True 表示只能讀诉瓦、不能寫。
guess_types=False/True False 表示轉(zhuǎn)換數(shù)據(jù)，True 表示不能轉(zhuǎn)換數(shù)據(jù)睬澡。
data_only=False/True False 表示序單元格的真實(shí)信息固额，True 表示只讀取值。

 1-> from openpyxl import load_workbook
 2-> wb=load_workbook('成績表-1.xlsx') # 讀取 工作簿
 3-> wb.save('成績表-2.xlsx') # 保 存工作簿

1.2.3 實(shí)例（批量建新工作簿）

 1-> from openpyxl import Workbook 
 2-> for m in range(1,13): 
 3->     wb = Workbook()  # 新建工作簿
 4->     wb.save('%d 月.xlsx'%m)# 保存工作簿

1.3 工作表對象的獲取方法

1.3.1 工作表獲取方式:

獲取當(dāng)前活動工作表的：

sheet = wb.active

以索引值方式獲取工作表：

sheet= wb.worksheets[2]

以工作表名獲壬反稀： wb['工作表名']斗躏，注意，此表達(dá)方式為切片顯示昔脯，所以沒有成員提示啄糙。很少用

sheet = wb["Sheet-甲乙-甲乙"]

循環(huán)工作表：很好用，一般用sheetnames

wb = wb .worksheets

獲取所有工作表名：wb.sheetnames

wb4 = wb.sheetnames

獲取指定工作表名

wb7 = wb.sheetnames[2].title()

修改工作表名稱

1.3.2 實(shí)例（批量修改工作表名）

1-> import openpyxl 
2-> wb=openpyxl.load_workbook('各年業(yè)績表.xlsx') 
3-> for sh in wb.worksheets:
4->     sh.title=sh.title+'-芝華公司'
5-> wb.save('各年業(yè)績表（修改后）.xlsx')

1.4 工作表的新建云稚、復(fù)制隧饼、刪除

1.4.1 新建工作表

可以在新建的工作簿中新建工作表（在新建工作簿時，會默認(rèn)新建一個工作表）静陈。也能在已經(jīng)存在的工作簿中新建工作表燕雁。

新建工作表時的默認(rèn)工作表名：

wb.create_sheet()  #默認(rèn)工作表名為 Sheet1、 Sheet2鲸拥、 Sheet3……

wb8 = wb.create_sheet('工作表名',指定位置)

1.4.2 復(fù)制工作表

wb.copy_worksheet(wb.worksheets[3])

1.4.3 刪除工作表

workbook.remove(工作表)

wb.remove(wb.worksheets[2])

1.5 關(guān)于工作表的實(shí)例應(yīng)用

1.5.1 實(shí)例應(yīng)用（批量新建12個月工作表）

import openpyxl
wb = openpyxl.Workbook()  # 新建工作簿
for m in range(1, 13):
     wb.create_sheet('%d 月' % m)  # 新建月份工作表
wb.remove(wb['Sheet'])  # 刪除 指定工作表
wb.save('2019 年計劃表.xlsx')  # 保存工作簿拐格。

1.5.2 實(shí)例應(yīng)用（刪除不符合條件的工作表）

import openpyxl 
wb=openpyxl.load_workbook('2018 年.xlsx')  # 讀取 工作簿
for sh in wb: # 循環(huán) 工作簿中的工作表
    if sh.title.split('-')[0]!='北京': # 判斷 工作表是否不等于北京
          wb.remove(sh) # 刪除 工作表
wb.save('北京.xlsx') # 保 存工作簿

1.5.3 實(shí)例應(yīng)用（批量復(fù)制工作表）

import openpyxl

wb = openpyxl.load_workbook('模板.xlsx')
for m in range(1, 13):
    wb.copy_worksheet(wb['測試']).title = '%d 月' % m
    wb.remove(wb['測試'])

wb.save('2018 年各月表格.xlsx')

以上是處理工作表的方式

以下是處理單元格的方法

1.6 單元格信息獲取

1.6.1 單元格數(shù)據(jù)獲取

A1 表示法：工作表['A1']，R1C1 表示法：工作表.cell(行號,列號)

import openpyxl

wb = openpyxl.load_workbook("測試表格.xlsx")

ws = wb.worksheets[0] #不能使sheetnames
sh2 = ws["a1"].value #必須是小寫
sh = ws.cell(1,1).value
print(type(sh2))

wb.save("測試表格.xlsx")

1.6.2 實(shí)例應(yīng)用（匯總各表各單元格數(shù)據(jù)）

import openpyxl
wb = openpyxl.load_workbook('各年業(yè)績表.xlsx')
print(sum([s['b14'].value for s in wb]))
print(sum([s.cell(14,2).value for s in wb]))

1.7 單元格區(qū)域信息獲取

1.7.1 單元格區(qū)域數(shù)據(jù)獲取

1.工作表['起始單元格': '終止單元格']或工作表['起始單元格: 終止單元格']刑赶，此方法是按行讀取的數(shù)據(jù)捏浊。

import openpyxl
wb = openpyxl.load_workbook('demo.xlsx')
sh = wb["天峰集團(tuán)"]
s = sh["a1":"a2"]
print(s)
wb.save("demo.xlsx")

2.工作表['起始行號': '結(jié)束行號']或者工作表['起始行號: 結(jié)束行號']，此方法是按行讀取的數(shù)據(jù)角撞。


import openpyxl
wb = openpyxl.load_workbook('demo.xlsx')
sh = wb["天峰集團(tuán)"]
s = sh["1:3"]
print(s)
wb.save("demo.xlsx")

3.工作表['起始列號': '結(jié)束列號']或者工作表['起始列號: 結(jié)束列號']呛伴，
此方法是按列讀取的數(shù)據(jù)。

import openpyxl
wb = openpyxl.load_workbook('demo.xlsx')
sh = wb["天峰集團(tuán)"]
s = sh["a:w"]
print(s)
wb.save("demo.xlsx")

4.獲融怂（按行）指定工作表所有已用數(shù)據(jù)：
list(workbook.worksheets[索引值].values)

print([[c.value for c in row] for row in ws['a1:d3']])

1.7.2 實(shí)例應(yīng)用

按行求和（方法 1）

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.active
rngs=ws['a2:e71']
print(['%s-%d'%(row[0].value,sum([c.value for c in row][1:])) for row in rngs])

按行求和（方法）

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.active
print(['%s-%d'%(row[0],sum(row[1:])) for row in list(ws.values)[1:]])

按列統(tǒng)計平均值

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.active

f=[sum(l)/len(l) for l in list(zip(*list(ws.values)[1:]))[1:]]
n=[c.value for c in ws['1']][1:]
print(['%s-%.2f'%c for c in list(zip(n,f))])

print(['%s-%.2f'%(l[0],sum(l[1:])/len(l[1:])) for l in list(zip(*list(ws.values)))[1:]])

1.8 行列信息獲取

1.8.1 行列信息獲取

按行獲取工作表使用區(qū)域數(shù)據(jù)：worksheet.rows

按列獲取工作表使用區(qū)域數(shù)據(jù)：worksheet.columns
獲取工作表中最小行號：worksheet.min_row
獲取工作表中最小列號：worksheet.min_column
獲取工作表中最大行號：worksheet.max_row
獲取工作表中最大列號：worksheet.max_column
獲取單元格的行號：cell.row
獲取單元格的列號：cell.column iter
方法獲取指定區(qū)域：
1.按行獲取指定工作表單元格區(qū)域：worksheet.iter_rows(……)
2.按列獲取指定工作表單元格區(qū)域：worksheet.iter_cols(……)
可以通過 min_row、min_col沛申、max_col劣领、max_row 這幾個參數(shù)進(jìn)行單元格區(qū)域的控制


import openpyxl
wb=openpyxl.load_workbook('demo.xlsx')
ws=wb.active
minr=ws.min_row
minc=ws.min_column
maxr=ws.max_row
maxc=ws.max_column
rngs=ws.iter_rows(min_row=minr+1,min_col=minc+2,max_row=maxr-1,max_col=maxc-1)
col=ws.iter_cols(min_row=minr+1,min_col=minc+1,max_row=maxr-1,max_col=minc+1)
total=([sum([v.value for v in row]) for row in rngs])
cp=([[v.value for v in row] for row in col][0])
print(list(zip(cp,total)))

1.8.2 實(shí)例應(yīng)用

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
for row in ws.iter_rows(min_row=36,min_col=2,max_col=4,max_row=40):
    print([c.value for c in row])

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
for row in list(ws.rows)[1:]:
    l=[v.value for v in row]
    print(l[0],sum(l[1:]))

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
for col in list(ws.columns)[1:]:
    l=[v.value for v in col]
    print(l[0],max(l[1:]))

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
total=[sum([c.value for c in row]) for row in ws.iter_rows(min_col=2,min_row=2)]
name=[c.value for c in ws['a']][1:]
print(list(zip(name,total)))

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
total=[sum([c.value for c in col]) for col in ws.iter_cols(min_col=2,min_row=2)]
name=[c.value for c in ws[1][1:]]
print(list(zip(name,total)))

單元格的寫入

1.9.1 單元格與區(qū)域數(shù)據(jù)寫入

A1 表示法：工作表['A1']=值，R1C1 表示法：工作表.cell(行號,列號,值)

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[1]
s = ws['a1']=123
ws.cell(2,3,'我是中國人')
ws.cell(3,3).value='我是四川人'
print(s)
wb.save('test.xlsx')

1.9.2 實(shí)例應(yīng)用（九九乘法表）

import openpyxl
wb=openpyxl.Workbook()
ws=wb.active
ws.title='九九表'
for x in range(1,10):
    for y in range(1,x+1):
        ws.cell(x,y,f'{y}×{x}={x*y}')
wb.save('九九表.xlsx')

1.10 批量寫入數(shù)據(jù)

1.10.1 按行寫入數(shù)據(jù) (追加)

在最后一行寫入數(shù)據(jù)：工作表.append(列表)

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
ws.append({'a':'張三','b':56,'c':'fdgsfg'})
wb.save('test.xlsx')

1.10.2 實(shí)例應(yīng)用（九九乘法表）

wb=openpyxl.load_workbook('demo.xlsx')
ws=wb.active
# for r in [['%d*%d=%d'%(y,x,x*y) for y in range(1,x+1)] for x in range(1,10)]:
#     ws.append(r)
# ws.delete_rows(1)
# wb.save('demo.xlsx')
for row in ws['a1:c6']:
    for c in row:
        c.value=1
wb.save('demo.xlsx')

1.11 循環(huán)方式批量寫入數(shù)據(jù)

1.11.1 循環(huán)獲取單元格對象铁材，再寫入

之前我們可以通過組合單元格來獲取或者寫入數(shù)據(jù)尖淘，但還有一種方法，就是直接循環(huán)單元格區(qū)域來寫入數(shù)據(jù)著觉。與循環(huán)讀取的表示方式基本相同村生，只是多了一個賦值。

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
for row in ws['a1:g9']:
    print(row)
    for c in row:
        print(c.value)
wb.save('test.xlsx')

1.11.2 實(shí)例應(yīng)用（大于等于 90 分為優(yōu)秀）

import openpyxl
wb=openpyxl.load_workbook('demo.xlsx')
ws=wb.active
rngs=ws.row("2:2")
for row in rngs:
    for c in row:
        if c.value>=90:
            c.value=str(c.value)+'（優(yōu)秀）'
wb.save('demo1.xlsx')

1.11.3 實(shí)例應(yīng)用（每個人的總分大于等于300為優(yōu)秀）

最后加一列寫優(yōu)秀

import openpyxl
wb=openpyxl.load_workbook('demo.xlsx')
ws=wb.active
rngs=ws.iter_rows(min_row=2,min_col=2)
for row in rngs:
        sm=sum([c.value for c in row][0:4])
        if sm>=300:
            row[-1].value='優(yōu)秀' #切片用的多
wb.save('demo2.xlsx')

python處理excel完整版