模塊導(dǎo)入

import openpyxl

讀取Excel文件

打開Excel文件

workbook = openpyxl.load_workbook('test.xlsx')

輸出表單名字

# 輸出工作簿中所有表單名字

print(workbook.sheetnames)

# 遍歷所有表單并輸出其名字

for sheet in workbook:

print(sheet.title)

創(chuàng)建表單

newSheet = workbook.create_sheet('newSheetName')

獲取表單對象

# 根據(jù)表單名獲取表單

sheet3 = workbook.get_sheet_by_name('Sheet3')

sheet4 = workbook['newSheetName']

# 獲取當(dāng)前活躍的表單

worksheet = workbook.active

獲取當(dāng)前表單數(shù)據(jù)行列數(shù)

# 獲取當(dāng)前表單數(shù)據(jù)行數(shù)

row_count = worksheet.max_row

# 獲取當(dāng)前表單數(shù)據(jù)列數(shù)

row_count = worksheet.max_column

獲取單元格對象

selectcell = worksheet['A1']

selectcell = worksheet.cell(row=1, column=2) # 行列號從1開始

輸出單元格信息

單元格所在的行、列

print(selectcell.row, selectcell.column)

單元格的坐標(biāo)

print(selectcell.coordinate)

單元格的值

print(selectcell.value)

獲取列對象

selectcol = worksheet['C']

獲取列對象中某一單元格

selectcell = selectcol['2']

獲取連續(xù)多列組成的對象

selectcols = worksheet['B:C']

獲取所有列組成的對象

allcol = worksheet.cols

獲取行對象

selectrow = worksheet['2']

獲取行對象中某一單元格

selectcell = selectcol['C']

獲取連續(xù)多行組成的對象

selectcols = worksheet['1:4']

獲取所有行組成的對象

allrow = worksheet.rows

獲取一定范圍內(nèi)的單元格組成的對象

cell_range = worksheet['A1:C3']

遍歷一定范圍內(nèi)的單元格

for row_range in cell_range: # 先行再列

for cell in row_range:

print(cell.value)

行的字母表示與數(shù)字換算

from openpyxl.utils import get_column_letter, column_index_from_string

print(get_column_letter(2))) # 2 => B

print(column_index_from_string('C')) # C => 3

編輯Excel文件

創(chuàng)建workbook對象

workbook = openpyxl.Workbook()

創(chuàng)建表單

workbook.create_sheet(index=1, title='第二張表')

刪除表單

workbook.remove_sheet(workbook.get_sheet_by_name('Sheet3'))

獲取當(dāng)前活躍的表單

sheet = workbook.active

修改表單名稱

sheet.title = '設(shè)置的表單名稱'

修改單元格的值

sheet['A1'] = 'New Value'

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

方法一

import openpyxl

from openpyxl.utils import get_column_letter, column_index_from_string

workbook = openpyxl.Workbook()

ws1 = workbook.create_sheet('第一頁')

for row in range(40):

ws1.append(range(17))

方法二

import openpyxl

from openpyxl.utils import get_column_letter, column_index_from_string

workbook = openpyxl.Workbook()

ws2 = workbook.create_sheet('第二頁')

rows = [

['Number', 'Batch1', 'Batch2'],

[2,40,30],[3,50,25],

[4,30,30],[5,60,10]

]

for row in rows:

ws2.append(row)

方法三

import openpyxl

from openpyxl.utils import get_column_letter, column_index_from_string

workbook = openpyxl.Workbook()

ws3 = workbook.create_sheet('第三頁')

for row in range(5,30):

for col in range(15,24):

ws3.cell(column=col, row=row, value=123)

修改完畢保存到文件

workbook.save('created.xlsx')

> 想進(jìn)一步了解編程開發(fā)相關(guān)知識，與我一同成長進(jìn)步，請關(guān)注我的公眾號“松果倉庫”，共同分享宅&程序員的各類資源，謝謝！??！