Appending a excel spreadsheet as a new sheet to multiple spreadsheets using python

Question

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 300 unique spreadsheets.

Is there a relatively simple way to do this task in python?

Answer 1

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

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

if not 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 in range(sheetToAppend.nrows):
    for col in range(sheetToAppend.ncols):
        dataTuples.append((row, col, sheetToAppend.cell(row,col).value))  

#You need to change this line!
wbNames = ["{}.xlsx".format(num) for num in range(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.