CODEWITHSUNDEEP
python-3.xpandasopenpyxlxlsxwriter
karthi-python
karthi-python

Reputation: 41

Write second Header and Merge Cells using Pandas

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: 1

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.

Upvotes: 0

Views: 1203

Answers (1)

Prachiti Prakash Prabhu
Prachiti Prakash Prabhu

Reputation: 684

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

Upvotes: 1

Related Questions