CODEWITHSUNDEEP
pythonxlrdxlwt
user2847738
user2847738

Reputation: 11

Writing/Creating a worksheet using xlrd and xlwt in Python

I'm trying to read multiple excel files with multiple sheets (sheet names are the same in all the excel files) and perform some calculations in each of the worksheet and save the calculation data from all the excel files corresponding to a worksheet into a new workbook. My little snippet to perform this is as follows:

import xlrd
import xlwt
import os

wb2 = xlwt.Workbook()
wb2_name = 'AllSummary.xls'
pwd = os.getcwd()

for i in xrange(len(ListofExcelFiles)):
    fname = pwd + os.sep + ListofExcelFiles[i]
    wb1 = xlrd.open_workbook(fname)
    sheetNames = wb1.sheet_names()

    for j in xrange(len(sheetNames)):

        sheet = wb1.sheet_by_name(sheetNames[j])

        #<Read the Excel Data from Worksheet>
        #<Perform Calculation on Data Here>

        #<Create a new worksheet in wb2>
        sheet_all = wb2.add_sheet(sheetNames[j])

        #<Write the data to the worksheet>

wb2.save(wb2_name)
print "Output Excel File Saved!"

I understand that the first iteration on i, creates a new worksheet; which is being duplicated in the next iterations. Can someone shed some light on how to overcome this duplication error? Any help will be much appreciated.

Upvotes: 1

Views: 10832

Answers (1)

Zeinab Abbasimazar
Zeinab Abbasimazar

Reputation: 10439

You can simply add an if statement as below:

if sheetNames[j] in wb2.sheet_names():
    sheet_all = wb2.sheet_by_name(sheetNames[j])

else
    sheet_all = wb2.add_sheet(sheetNames[j])

# Save your calculations' results in "sheet_all"

Upvotes: 0

Related Questions