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

