Use with..open to read a list of xls files to copy into a single workbook with multiple sheets
Question: First timer here. My overall goal is to copy exactly, the data in 3 different
xls files into one xls workbook with a sheet for each original xls file. In a
non-programmatic sense, I want to copy and paste the data from each xls file
into it's own sheet in a new workbook (xls or xlsx file). I've been using xlrd
& xlwt to do this and with the help of searching around SO, I've been able to
get most of the code set. However, I'm having difficult time comprehending how
to use the with...open command to read the data from each original xls file
and put it on it's own sheet. As you will probably learn from my code block,
my Python skills are limited. Thx!

    
    
        import xlwt as xlwt
        from os.path import join
        import xlrd
    
    
        wb = xlwt.Workbook()
        path = r'C:\data_path\\'
        xls1 = 'file1.xls'
        xls2 = 'file2.xls'
        xls3 = 'file3.xls'
    
        Sheet1 = 'file1_data'
        Sheet2 = 'file2_data'
        Sheet3 = 'file3_data'
    
        names = [Sheet1, Sheet2, Sheet3]
        dataset = [path + xls1, path + xls2, path + xls3]
    
       for name in names:
           wb.add_sheet(name)
       for n, data in enumerate(dataset):
           **I feel there should be some type of with..open statement here**
           ws = wb.get_sheet(n)
           ws.write(0,0, data)
    
       wb.save(join(path,'test.xls'))
    


Answer: Assuming that each source file has only one sheet:

    
    
    import xlwt as xlwt
    from os.path import join
    import xlrd
    
    output = xlwt.Workbook()
    
    path = r'C:\data_path\\'
    
    xls1 = 'file1.xls'
    xls2 = 'file2.xls'
    xls3 = 'file3.xls'
    
    Sheet1 = 'file1_data'
    Sheet2 = 'file2_data'
    Sheet3 = 'file3_data'
    
    names = [Sheet1, Sheet2, Sheet3]
    dataset = [path + xls1, path + xls2, path + xls3]
    
    
    for n, data in enumerate(dataset):
        book = xlrd.open_workbook(data, formatting_info=True)
    
        sheet = book.sheet_by_index(0)
        r = output.add_sheet(names[n])
    
        for row in range(sheet.nrows):
            for column in range(sheet.ncols):
                cell_val = sheet.cell_value(rowx=row, colx=column)
                r.write(row, column, cell_val)
    
    output.save(join(path,'test.xls'))
    

