xlsxwriter例子

##############################################################################
#
# An example of converting a Pandas dataframe to an xlsx file
# with a user defined header format.
#
# Copyright 2013-2019, John McNamara, jmcnamara@cpan.org
#

import pandas as pd

# Create a Pandas dataframe from some data.
data = [10, 20, 30, 40, 50, 60]
df = pd.DataFrame({'Heading': data,
                   'Longer heading that should be wrapped' : data})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter("pandas_header_format.xlsx", engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object. Note that we turn off
# the default header and skip one row to allow us to insert a user defined
# header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'font_name': 'Candara',
    'font_size': 16,
    'font_color': 'red',
    'text_wrap': True,
    'valign': 'top',
    'fg_color': 'green',
    'border': 1})

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)

cell_format = workbook.add_format({
    'bold':True,
    'font_name': '微软雅黑',
    'font_size': 10})

cell_format.set_align('center')
cell_format.set_align('vcenter')

worksheet.set_row(1, 30, cell_format)
worksheet.set_column('B:C', 30, cell_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()