Hướng dẫn format header excel python

An example of converting a Pandas dataframe to an Excel file with a user defined header format using Pandas and XlsxWriter.

##############################################################################
#
# An example of converting a Pandas dataframe to an xlsx file
# with a user defined header format.
#
# SPDX-License-Identifier: BSD-2-Clause
# Copyright 2013-2022, John McNamara, 
#

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,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    '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)

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

I have an excel sheet with multiple header like:

_________________________________________________________________________
____|_____|        Header1    |        Header2     |        Header3      |
ColX|ColY |ColA|ColB|ColC|ColD||ColD|ColE|ColF|ColG||ColH|ColI|ColJ|ColDK|
1   | ds  | 5  | 6  |9   |10  | .......................................
2   | dh  |  ..........................................................
3   | ge  |  ..........................................................
4   | ew  |  ..........................................................
5   | er  |  ..........................................................

Now here you can see that first two columns do not have headers they are blank but other columns have headers like Header1, Header2 and Header3. So I want to read this sheet and merge it with other sheet with similar structure.

I want to merge it on first column 'ColX'. Right now I am doing this:

import pandas as pd

totalMergedSheet = pd.DataFrame([1,2,3,4,5], columns=['ColX'])
file = pd.ExcelFile('ExcelFile.xlsx')
for i in range (1, len(file.sheet_names)):
    df1 = file.parse(file.sheet_names[i-1])
    df2 = file.parse(file.sheet_names[i])
    newMergedSheet = pd.merge(df1, df2, on='ColX')
    totalMergedSheet = pd.merge(totalMergedSheet, newMergedSheet, on='ColX')

But I don't know its neither reading columns correctly and I think will not return the results in the way I want. So, I want the resulting frame should be like:

________________________________________________________________________________________________________
____|_____|        Header1    |        Header2     |        Header3      |        Header4     |        Header5      |
ColX|ColY |ColA|ColB|ColC|ColD||ColD|ColE|ColF|ColG||ColH|ColI|ColJ|ColK| ColL|ColM|ColN|ColO||ColP|ColQ|ColR|ColS|
1   | ds  | 5  | 6  |9   |10  | ..................................................................................
2   | dh  |  ...................................................................................
3   | ge  |  ....................................................................................
4   | ew  |  ...................................................................................
5   | er  |  ......................................................................................

Any suggestions please. Thanks.