Python与openpyxl的不解之缘【2】

目录

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')