代码可以直接复制使用,并且可以保留单元格的格式。
import os
from copy import copy
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
# 读取Excel文件,获取全部Sheet名
wb = load_workbook('./test.xlsx')
sht_nms = wb.sheetnames
# 循环每个Sheet,把Sheet保存成.xlsx文件
for name in sht_nms:
ws = wb[name]
print('start:', ws)
wb2 = Workbook()
ws2 = wb2.active
for i, row in enumerate(ws.iter_rows()):
# 复制数据
for j, cell in enumerate(row):
new_cell = ws2.cell(row=i+1, column=j+1, value=cell.value)
# 保留单元格格式
if cell.has_style:
new_cell.font = copy(cell.font)
new_cell.border = copy(cell.border)
new_cell.fill = copy(cell.fill)
new_cell.number_format = copy(cell.number_format)
new_cell.protection = copy(cell.protection)
new_cell.alignment = copy(cell.alignment)
ws2.title = name
ws2.sheet_view.showGridLines = False
wb2.save('./excel' + os.sep + name + '.xlsx')
print('end:', ws)
网友评论