我有一个Excel文件,其中包含多个工作表(sheet1,sheet2,sheet3,sheet4等)。我想使用python进行一些格式更改,并将这些编辑后的文件另存为具有多个工作表的单独的excel文件。
步骤1:读取包含多张纸的文件
步骤2:在工作表中进行更改
步骤3:将更改保存到包含多张图纸的Excel文件中
到目前为止我尝试了什么?
import pandas as pd
import pandas.io.formats.style
import os
from pandas import ExcelWriter
import numpy as np
import xlsxwriter
from itertools import cycle
from xlsxwriter.utility import xl_rowcol_to_cell
df = pd.ExcelFile('data.xlsx')
for i in df.sheet_names:
# make the required changes
writer = pd.ExcelWriter('edited_file.xlsx', engine='xlsxwriter', options={'strings_to_numbers': True}, date_format='mmmm dd yyyy')
df.to_excel(writer, sheet_name= i , startrow=1 , startcol=0, header=False, index=False, encoding='utf8')
workbook = writer.book
worksheet = writer.sheets[i]
data_format1 = workbook.add_format({'bg_color': '#F1EEEE'})
data_format2 = workbook.add_format({'bg_color': '#FFFFFF'})
formats = cycle([data_format1, data_format2])
for row, value in enumerate(df):
data_format = next(formats)
worksheet.set_row(row, cell_format=data_format)
worksheet.write(row, 0, value)
format_header = workbook.add_format()
format_header.set_bold()
format_header.set_border()
format_header.set_bg_color('gray')
format_header.set_font_color('white')
format_data = workbook.add_format()
#worksheet.set_column('A:Z', 20, format_data)
worksheet.set_row(0, 20, format_header)
# Write the header manually
for colx, value in enumerate(df.columns.values):
worksheet.write(0, colx, value)
worksheet.autofilter(0, 0, df.shape[0], df.shape[1])
writer.save()
我无法保存多张纸,仅保存了第一张纸。我怎么做?
循环结束后,您必须移出pd.ExcelWriter
循环并writer.save()
仅调用一次:
writer = pd.ExcelWriter('edited_file.xlsx', engine='xlsxwriter', options={'strings_to_numbers': True}, date_format='mmmm dd yyyy')
for i in df.sheet_names:
...
...
...
worksheet.autofilter(0, 0, df.shape[0], df.shape[1])
writer.save()
此外,我认为您应该ExcelFile
以其他方式使用(至少使用时openpyxl
):
exc = pd.ExcelFile('data.xlsx', engine='openpyxl')
writer = pd.ExcelWriter('edited_file.xlsx', engine='xlsxwriter', options={'strings_to_numbers': True}, date_format='mmmm dd yyyy')
for i in exc.sheet_names:
df = exc.parse(i)
...
...
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句