|
目录
1.最大行，最大列
2.删除工作表
3.复制表单并重命名
4.隐藏或显示工作表
5.遍历
6.插入公式
7.冻结窗格
8.添加筛选
1.最大行，最大列
#最大行
max_rows
#最大列
max_columns
2.删除工作表
from openpyxl import load_workbook
wb = load_workbook('123.xlsx)
ws = wb.active
wb.create_sheet('456')
#删除工作表
wb.remove('456')
del wb['456']
wb.save('123.xlsx')
wb.close()
3.复制表单并重命名
from openpyxl import load_workbook
wb = wb.load_workbook('123.xlsx')
ws = wb.active
target = wb.copy_worksheet(ws)
target.title = '456'
wb.save('123.xlsx')
wb.close()
4.隐藏或显示工作表
from openpyxl import load_workbook
wb = wb.load_workbook('123.xlsx')
ws = wb.active
#隐藏工作表
ws.sheet_state = 'hidden'
#显示工作表
ws.sheet_state = 'visible'
wd.save('123.xlsx')
5.遍历
#遍历工作薄名字的表单
for sheet in wb:
print(sheet.title)
#遍历所有的行
from openpyxl import load_workbook
wb = load_workbook('123.xlsx')
ws = wb.active
for row in sheet.rows:
for cell in row:
print(cell.value)
#遍历所有的列
from openpyxl import load_workbook
wb = load_workbook('123.xlsx')
ws = wb.active
for col in sheet.columns:
print(col）
6.插入公式
from openpyxl import load_workbook
wb = load_workbook('123.xlsx')
ws = wb.active
#求和
ws['B6'] = '=SUM(B2:B5)'
wb.save('123.xlsx')
7.冻结窗格
from openpyxl import load_workbook
wb = load_workbook('123.xlsx')
ws = wb.active
#冻结窗格
ws.freeze_panes = 'B2'
wb.save('123.xlsx')
8.添加筛选
from openpyxl import load_workbook
wb = load_workbook('123.xlsx')
ws = wb.active
#对整张表进行筛选
ws.auto_filter.ref = ws.dimensions
wb.save('123.xlsx')
|