3

I need to merge data from multiple sheets of an Excel to form a new summary sheet using Python. I am using pandas to read the excel sheets and create new summary sheet. After concatenation the table format is getting lost i.e. Header and borders.

  1. Is there a way to read from source sheet with the format and write to final sheet.
  2. if first is not possible how to format the data after concatenation

Python Code to concatenate:

import pandas as pd
df = []
xlsFile = "some path excel"
sheetNames = ['Sheet1', 'Sheet2','Sheet3']
for nms in sheetNames:
    data = pd.read_excel(xlsFile, sheet_name = nms, header=None, skiprows=1)
    df.append(data)
final = "some other path excel "
df = pd.concat(df)
df.to_excel(final, index=False, header=None)

Sheet 1 Input Data

Sheet 2 Input Data

Sheet 3 Input Data

Summary Sheet output

Balveer Singh
  • 61
  • 1
  • 7
  • Take a look here for the keeping of the styles - https://stackoverflow.com/questions/3723793/preserving-styles-using-pythons-xlrd-xlwt-and-xlutils-copy – Vityata May 22 '18 at 09:07

2 Answers2

0

You can try the following code:

df = pd.concat(pd.read_excel('some path excel.xlsx', sheet_name=None), ignore_index=True)

If you set sheet_name=None you can read all the sheets in the workbook at one time.

Nakshatra
  • 43
  • 1
  • 10
0

I suggest you the library xlrd (https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html?p=4966 and https://github.com/python-excel/xlrd) It is a good library to do that.

from xlrd import open_workbook

path = '/Users/.../Desktop/Workbook1.xls'
wb = open_workbook(path, formatting_info=True)
sheet = wb.sheet_by_name("Sheet1")
cell = sheet.cell(0, 0) # The first cell
print("cell.xf_index is", cell.xf_index)
fmt = wb.xf_list[cell.xf_index]
print("type(fmt) is", type(fmt))
print("Dumped Info:")
fmt.dump()

see also: Using XLRD module and Python to determine cell font style (italics or not) and How to read excel cell and retain or detect its format in Python (I brought the above code from this address)

aAlalirezaSouri
  • 131
  • 1
  • 3