写excel文件-xlsxwriter包的使用

# encoding: utf8
from xlsxwriter.utility import xl_rowcol_to_cell
import pandas as pd


def df_to_excel(df, writer, sheetname, index=True, header=True, startrow=0, startcol=0):
    '''
    please refer the method to_excel from module pandas.DataFrame
    '''
    workbook = writer.book
    if sheetname not in writer.sheets:
        pd.DataFrame().to_excel(writer, sheetname)
    worksheet = writer.sheets[sheetname]

    format_title = workbook.add_format({
        'border': 1, 'align': 'center', 'bg_color': 'cccccc', 'bold': True})
    format_data = workbook.add_format({'border': 1})

    title = list(df.columns.get_values())
    if index is True:
        title = list(df.columns.names) + title
        title[0] = title[0] or 'index'
    if header is True:
        cell = xl_rowcol_to_cell(startrow, startcol)    # 'A1'
        worksheet.write_row(cell, title, format_title)
        startrow += 1
    if index is True:
        cell = xl_rowcol_to_cell(startrow, startcol)    # 'A2'
        worksheet.write_column(cell, df.index.get_values(), format_data)
        startcol += 1
    for i, col in enumerate(df.columns):
        cell = xl_rowcol_to_cell(startrow, startcol+i)  # 'B2'
        worksheet.write_column(cell, df[col], format_data)


if __name__ == "__main__":
    X4 = pd.DataFrame({'X1': [1, 2], 'X2': '自'})
    with pd.ExcelWriter('f2.xlsx', engine='xlsxwriter') as writer:
        # X4.to_excel(writer,sheetname='X4', index=False, header=False, startrow=2, startcol=2)
        df_to_excel(X4, writer, sheetname='X4', index=False, header=True, startrow=2, startcol=2)
        worksheet = writer.sheets['X4']
        worksheet.hide_gridlines(2)