Appending A Excel Spreadsheet As A New Sheet To Multiple Spreadsheets Using Python

I have over 300 unique '.xlsx' spreadsheets. I have another spreadsheet (a data dictionary explaining field names) that I would like to append as a new sheet (tab) to each of the 3

Solution 1:

Here's how you could do it with Python-Excel

import xlrd
import xlwt
from xlutils.copy import copy
import os

ifnot os.path.exists("/new"): os.makedirs("new")

toBeAppended = xlrd.open_workbook("ToBeAppended.xlsx")
sheetToAppend = toBeAppended.sheets()[0]      #If you don't want it to open the first sheet, change the 0 accordingly

dataTuples = []

for row inrange(sheetToAppend.nrows):
    for col inrange(sheetToAppend.ncols):
        dataTuples.append((row, col, sheetToAppend.cell(row,col).value))  

#You need to change this line!
wbNames = ["{}.xlsx".format(num) for num inrange(1,7)]

for name in wbNames:
    wb = copy(xlrd.open_workbook(name))
    newSheet = wb.add_sheet("Appended Sheet")
    for row, col, data in dataTuples:
        newSheet.write(row, col, data)
    wb.save("new/"+name.split('.')[0]+".xls")

So this creates a new folder for your new sheets (just in case it doesn't work). Then it copies the the first sheet of "ToBeAppended.xlsx" and gathers all the data in it. Then it gathers then name of files it needs to change (which for me was "1.xlsx" and so on). Then it creates a copy of each workbook it needs to edit, adds the sheet, and writes all the data too it. Finally, it saves the file.

You'll note that it saves a ".xls" file. This is a limitation of the package, and I don't know any way around it. Sorry

Hope this helps.