Write second Header and Merge Cells using Pandas

Question

All,

I have written a script to write header and data into a excel sheet. But my actual requirement is to write sub header as well and need to merge the cells from the 2nd row.

import xlsxwriter
import pandas as pd
import numpy as np
import openpyxl
import time
# Creating a dataframe
df = pd.DataFrame(np.random.randn(10, 3), columns=list('ABC'))
column_list = df

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

df.to_excel(writer, sheet_name='Sheet1', startrow=2, header=False, index=False)

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

header_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 12, 'color' : 'white', 'fg_color': '#00007f','bold': True, 'border' : 1})

for idx, val in enumerate(column_list):
    worksheet.write(0, idx, val, header_fmt)

worksheet.write(1, 1, 'Sample', header_fmt)

font_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 9 })

worksheet.set_column('A:C', None, font_fmt)
worksheet.set_row(0, None, header_fmt)

writer.save()

EDIT:

Expected Output:

There are 4 sections in the expected output, all of them are from different Dataframes. I need to merge all those Dataframes' output into a single sheet as shown in the image.

For subheading you can use pandas.MultiIndex (docs). For the second part of your question can you provide a small example of how you'd like to merge your data? — Jason
– Jason, Commented Feb 2, 2020 at 1:16

Prachiti Prakash Prabhu · Accepted Answer · 2020-02-03 23:38:22Z

1

How about this? Is this what you want?

import xlsxwriter
import pandas as pd
import numpy as np
import openpyxl
import time
# Creating a dataframe                                                                                                                                       
df = pd.DataFrame(np.random.randn(10, 3), columns=list('ABC'))
column_list = df

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

df.to_excel(writer, sheet_name='Sheet1', startrow=2, header=False, index=False)

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

header_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 12, 'color' : 'white', 'fg_color': '#00007f','bold': True, 'border' : 1})

merge_format = workbook.add_format({'align': 'center'})

worksheet.merge_range('A2:C2', 'Sample', merge_format)

for idx, val in enumerate(column_list):
        worksheet.write(0, idx, val,header_fmt)                                                                                                

        font_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 9 })

        worksheet.set_column('A:C',None, font_fmt)
        worksheet.set_row(0,None, header_fmt)

        writer.save()

answered Feb 3, 2020 at 23:38

Prachiti Prakash Prabhu

6841 gold badge5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

karthi-python Over a year ago

you can see 4 sections in the expected output, all of them are from different Dataframe, i need to merge all those Dataframe output into a single sheet as described in the attached format.

Collectives™ on Stack Overflow

Write second Header and Merge Cells using Pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related