openpyxl是一個用於讀寫Excel 2010 xlsx/xlsm/xltx/xltm文件的Python庫。
常用的基本操作如下：
from openpyxl import load_workbook
filename = r'C:\Users\admin\Desktop\參數表telnet1.xlsx'
# 加載excel表
wb = load_workbook(filename)
# 獲取所有標簽頁
sheetnames = wb.sheetnames
print(sheetnames) # ['sheet1', 'Sheet2', 'Sheet3']
# 獲取指定標簽頁
sheet = wb['sheet1']
print(sheet) # <Worksheet "sheet1">
# 復制標簽頁
cp_sheet = wb.copy_worksheet(sheet)
print(cp_sheet) # <Worksheet "sheet1 Copy">
# 創建一個標簽頁
cr_sheet = wb.create_sheet('create')
print(cr_sheet) # <Worksheet "create">
# 獲取指定單元格
cell = sheet["A1"]
print(cell) # <Cell 'sheet1'.A1>
cell = sheet.cell(row=1,column=1)
print(cell) # <Cell 'sheet1'.A1>
# 獲取多個單元格
cell_range = sheet["A1":"C1"]
print(cell_range) # ((<Cell 'sheet1'.A1>, <Cell 'sheet1'.B1>, <Cell 'sheet1'.C1>),)
for cells in cell_range:
for cell in cells:
print(cell)
# <Cell 'sheet1'.A1>
# <Cell 'sheet1'.B1>
# <Cell 'sheet1'.C1>
cell_range = sheet.iter_rows(max_row=1,max_col=3)
print(cell_range) # 生成器<generator object Worksheet._cells_by_row at 0x0FAEAFB0>
for cells in cell_range:
print(cells) # (<Cell 'sheet1'.A1>, <Cell 'sheet1'.B1>, <Cell 'sheet1'.C1>)
# sheet.rows獲取所有行 sheet.columns獲取所有列
for row in sheet.rows:
print(row)
# cell.value獲取屬性值
cell = sheet["A1"]
print(cell.value) # 模板
# 寫入文本
cp_sheet['A1'] = '復制的模板'
print(cp_sheet['A1'].value) # 復制的模板
# 寫入數字
cp_sheet['A2'] = 123
print(cp_sheet['A2'].value) # 123
# 合並單元格
sheet.merge_cells("A2:D2")
# 取消合並單元格
sheet.unmerge_cells("A2:D2")
# 刪除sheet
wb.remove(sheet)
# 保存
wb.save(filename)
#關閉
wb.close()
