openpyxl It's one for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm Of documents Python library .
Common basic operations are as follows ：
from openpyxl import load_workbook
filename = r'C:\Users\admin\Desktop\ Parameter table telnet1.xlsx'
# load excel surface
wb = load_workbook(filename)
# Get all tabs
sheetnames = wb.sheetnames
print(sheetnames) # ['sheet1', 'Sheet2', 'Sheet3']
# Get the specified tab
sheet = wb['sheet1']
print(sheet) # <Worksheet "sheet1">
# Copy tabs
cp_sheet = wb.copy_worksheet(sheet)
print(cp_sheet) # <Worksheet "sheet1 Copy">
# Create a tab
cr_sheet = wb.create_sheet('create')
print(cr_sheet) # <Worksheet "create">
# Get the specified cell
cell = sheet["A1"]
print(cell) # <Cell 'sheet1'.A1>
cell = sheet.cell(row=1,column=1)
print(cell) # <Cell 'sheet1'.A1>
# Get multiple cells
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 <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 Get all rows sheet.columns Get all columns
for row in sheet.rows:
print(row)
# cell.value Get attribute value
cell = sheet["A1"]
print(cell.value) # Templates
# Write text
cp_sheet['A1'] = ' Copied template '
print(cp_sheet['A1'].value) # Copied template
# Write numbers
cp_sheet['A2'] = 123
print(cp_sheet['A2'].value) # 123
# merge cell
sheet.merge_cells("A2:D2")
# Cancel merging cells
sheet.unmerge_cells("A2:D2")
# Delete sheet
wb.remove(sheet)
# preservation
wb.save(filename)
# close
wb.close()
