# 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)