Use with..open to read a list of xls files to copy into a single workbook with multiple sheets

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

Answers


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

Need Your Help

Check if a remote desktop connection is active in Windows 7

windows-7 command-line remote-desktop

As part of a business solution we are offering several remote desktops to a user base. Currently the users must go system by system attempting to connect and find one that is not already being use...

Repeater access elements

qt qml repeater qtquick2

I have the following Repeater: