使用 xlsxwriter 格式化多个工作表 [英] Formatting multiple worksheets using xlsxwriter

问题描述

如何使用 Python 中的 xlsxwriter 库将相同的格式复制到同一 Excel 文件的不同工作表中?

How to copy the same formatting to different sheets of the same Excel file using the xlsxwriter library in Python?

我试过的代码是:

import xlsxwriter

import pandas as pd
import numpy as np

from xlsxwriter.utility import xl_rowcol_to_cell

df = pd.DataFrame()
df = pd.read_excel('TrendAnalysis.xlsx')



# Create a Pandas Excel writer using XlsxWriter as the engine.
# Save the unformatted results
writer_orig = pd.ExcelWriter('TrendAnalysis.xlsx', engine='xlsxwriter')
df.to_excel(writer_orig, index=True)
writer_orig.save()

work = ["isu-wise", "ISU-BFS", "ISU-CPG", "ISU-ER", "ISU-GE", "ISU-GOV Domestic", "ISU-GOV Overseas", "ISU-HC"]
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('TrendAnalysis.xlsx', engine='xlsxwriter')
for i in range(0,len(work)):
    df.to_excel(writer, index=True, sheet_name=work[i])

    # Get access to the workbook and sheet
    workbook = writer.book
    worksheet = writer.sheets[work[i]]

    print worksheet
    # We need the number of rows in order to place the totals
    number_rows = len(df.index)

    # Total formatting
    total_fmt = workbook.add_format({'align': 'right', 'num_format': '#,##0',
                                    'bold': True, 'bottom':6})

    # Add total rows
    for column in range(1,3):
        # Determine where we will place the formula
        cell_location = xl_rowcol_to_cell(number_rows+1, column)
        # Get the range to use for the sum formula
        start_range = xl_rowcol_to_cell(1, column)
        end_range = xl_rowcol_to_cell(number_rows, column)
        # Construct and write the formula
        formula = "=SUM({:s}:{:s})".format(start_range, end_range)
        worksheet.write_formula(cell_location, formula, total_fmt)

    # Add a total label
    worksheet.write_string(number_rows+1, 0, "Total",total_fmt)
    i+=1

writer.save()
workbook.close()

它多次创建相同的工作表.在工作簿的第一个之后不导航和工作表.代码没有错误，否则会进行所需的格式设置.

It creates the same sheet multiple times. Doesn't navigate and the sheets after the first one of the workbook. The code is error free and does the required formatting otherwise.

推荐答案

可能为时已晚，但这是我为此所做的.在我的示例中，我有 2 个 DataFrame，我想冻结窗格，向每列添加过滤器，并格式化保存在同一个 Excel 文件中的两个工作表的标题.

It may be too late but here is what I have done for this. In my example I have 2 DataFrame which I would like to freeze the pane, add filter to each column, and format the header of both sheets which are being saved on the same excel file.

writer = pd.ExcelWriter(path_of_excel_file+'output.xlsx')
sheets_in_writer=['Sheet1','sheet2']
data_frame_for_writer=[df1, df2]

for i,j in zip(data_frame_for_writer,sheets_in_writer):
    i.to_excel(writer,j,index=False)

### Assign WorkBook
workbook=writer.book
# Add a header format
header_format = workbook.add_format({'bold': True,'text_wrap': True,'size':10,
                                                      'valign': 'top','fg_color': '#c7e7ff','border': 1})
### Apply same format on each sheet being saved
for i,j in zip(data_frame_for_writer,sheets_in_writer):
    for col_num, value in enumerate(i.columns.values):
        writer.sheets[j].write(0, col_num, value, header_format)
        writer.sheets[j].autofilter(0,0,0,i.shape[1]-1)
        writer.sheets[j].freeze_panes(1,0)
writer.save()

这篇关于使用 xlsxwriter 格式化多个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！