Python | Loop statement to creat file for every sheet in excel file

Question

I am fairly new to python and am trying to figure out how i can export data from every sheet in an excel workbook in to a different file for every sheet.

For example, in the first workbook, every sheet has data from a different brand; lets call this file my allbrandsmasterlist excel file. I also have a perbrandbreakdown file which is the template i want every brand to pasted in to but with a different file for every brand.

In summary, I want to copy all the contents of every brand in to a new finalbrand file which keeps the same template from perbrandbreakdown but has automatically moved the data from each brand in the allbrandsmasterlist sheet in to its own file. The end result should be 1 file for every sheet/brand in the first file.

I have made some progress but i need help cleaning up this code and making it run the loop for all sheets in the first file where as now it is just running it once and only creates the file for the first brand ( only file output is for brand0 or first sheet). Thanks in advance and let me know if i can clear up any confusion

here are the latest changes

       #import excel writing and reading modules
import xlwt
import xlrd
file = 'i:/My Client Data/CLASSHOG FINAL TEMPLATES AND FILES/item master new august.xlsx'
workbook1 = xlrd.open_workbook(file)
print ('There are %s sheets in %s'% (workbook1.nsheets,file))
"""for i in range(workbook.nsheets):
    print(i)"""


for i in range(workbook1.nsheets):
    if workbook1.nsheets > 1:
        workbookwt = xlwt.Workbook()
        sheet1 = workbookwt.add_sheet('ITEM DETAILS')


        #open template file to copy from
        workbook = xlrd.open_workbook('I:\My Client Data\CLASSHOG FINAL TEMPLATES AND FILES/PERFORMANCE MACHINE.xlsx')

        #set current sheet to first one in workbook
        sheet = workbook.sheet_by_index(0)

        #declare contents of rows individually -  manual
        datarow0 = [sheet.cell_value(0, col) for col in range(sheet.ncols)]
        datarow1 = [sheet.cell_value(1, col) for col in range(sheet.ncols)]
        datarow2 = [sheet.cell_value(2, col) for col in range(sheet.ncols)]
        datarow3 = [sheet.cell_value(3, col) for col in range(sheet.ncols)]
        datarow4 = [sheet.cell_value(4, col) for col in range(sheet.ncols)]


        #declare contents of columns individually -  manual
        datacolA = [sheet.cell_value(row,0) for row in range(sheet.nrows)]
        datacolB = [sheet.cell_value(row,1) for row in range(sheet.nrows)]


        #number of worksheets, columns and rows respectively
        print("The number of worksheets is %s:" % (workbook.nsheets))
        print("The number of columns is %s:" % (sheet.ncols))
        print("The number of rows is %s:" % (sheet.nrows))

        #add first sheet and paste columns-manual


        for index, value in enumerate(datarow0):
            sheet1.write(0, index, value)


        for index, value in enumerate(datarow1):  
            sheet1.write(1, index, value)

        for index, value in enumerate(datarow2):
            sheet1.write(2, index, value)

        for index, value in enumerate(datarow3):
            sheet1.write(3, index, value) 
        #----------------------------------------------------

        sheet = workbookwt.add_sheet('CLEAN FILE')

        sheetswb1 = workbook1.sheet_by_index(i)
        datarow0 = [sheetswb1.cell_value(0, col) for col in range(sheetswb1.ncols)]
        datarow1 = [sheetswb1.cell_value(1, col) for col in range(sheetswb1.ncols)]

        for r in range(sheetswb1.nrows):
            for c in range(sheetswb1.ncols):
               sheet.write(r, c, sheetswb1.cell_value(r, c))


        #for index, value in enumerate(datarow0):
        #sheet.write(0, index, value)

        #for index, value in enumerate(datarow1):
        #sheet.write(1, index, value)
        #----------------------------------------------------

        sheet = workbookwt.add_sheet('SC FILE')
        sheet3 = workbook.sheet_by_index(2)

        #----------------------------------------------------

        sheet = workbookwt.add_sheet('EBAY FILE')
        sheet4 = workbook.sheet_by_index(3)

        #----------------------------------------------------
        sheet = workbookwt.add_sheet('PRICING STRATEGY')
        sheet5 = workbook.sheet_by_index(4)
        datarow0 = [sheet5.cell_value(0, col) for col in range(sheet5.ncols)]
        datarow1 = [sheet5.cell_value(1, col) for col in range(sheet5.ncols)]
        datarow2 = [sheet5.cell_value(2, col) for col in range(sheet5.ncols)]



        for index, value in enumerate(datarow0):
            sheet.write(0, index, value)

        for index, value in enumerate(datarow1):
            sheet.write(1, index, value)

        for index, value in enumerate(datarow2):
            sheet.write(2, index, value)



        workbookwt.save('i:/pythonvirioutput/output%s.xls' % ("brand" + str(i)))
        print('Operation run and file saved for brand' + str(i))



    else: print('The workbook has less than 2 sheets')
    break

output

There are 441 sheets in i:/My Client Data/CLASSHOG FINAL TEMPLATES AND FILES/item master new august.xlsx
The number of worksheets is 5:
The number of columns is 2:
The number of rows is 5:
Operation run and file saved for brand0

Answer 1

Yes, this seems to be only working , because you get the current sheet as -

sheet = workbook.sheet_by_index(0)

You are only getting the first sheet, always, hence it always only writes out the data from the first sheet, you should instead use i here, to get each sheet separately. Code -

sheet = workbook.sheet_by_index(i)