0

I have essentially the converse of the problem answered in Python - Win32com - Open Workbook & Create a New Excel File for Each Tab. I need to iterate recursively thru a set of folders and copy the single tab containing data in a bunch of individual xls files into a single target xls, renaming the tabs appropriately as I go.

I have no formatting or formulae to copy, so nothing fancy needed here. Just first sheet in the individual xls files appended into the target xls.

Thanks in advance for any ideas/snippets.

Community
  • 1
  • 1
Andrew Zimba
  • 1
  • I would suggest using xlwings, which is quite good to handle your problem. – DanEng Mar 25 '15 at 15:59
  • 1
    Even though I created xlwings, I have a feeling that a combination of xlrd/openpyxl/xlwt/xlsxwriter could be better in this situation since it doens't look like the OP needs interactivity. – Felix Zumstein Mar 27 '15 at 13:25

1 Answers1

0

Suppose there is a folder containing two files and a folder:

  • xls (folder containg excel files)

  • all.xlsx (the aggregated excel file that you are going to copy values to)

  • copy.py (python script to do the work)

For each excel file in the xls folder, only cells A1:C3 in Sheet1 contain values. The python script will copy the values in cells A1:C3 of each individual excel file to the aggreated excel file in a separated sheet and rename it.

import os
from xlwings import Workbook, Sheet, Range

#Get the full paths of the excel files
full_paths = [os.path.abspath(os.path.join('xls', filename)) for filename in os.listdir('xls')]

#Full path of the aggregated excel file
aggregated_path = os.path.abspath(os.path.join(os.path.dirname(__file__), 'all.xlsx'))

for number, path in enumerate(full_paths):
    #open the individual excel file
    wb = Workbook(path)
    #Get the values from the individual file
    data = Range('A1').table.value
    wb.close()
    #open the aggregated excel file
    wb = Workbook(aggregated_path)
    #Put the values to the aggregated excel file
    Range('Sheet' + str(number+1), 'A1').value = data
    #Rename the sheet name
    Sheet('Sheet' + str(number+1)).name = str(number+1)
DanEng
  • 378
  • 1
  • 5
  • 17
  • Does the range of data in each sheet need to be known? Is there no way to just wholesale copy a sheet from one workbook to another like you can do manually in Excel by right-clicking a tab and using the Move or Copy entry in the dropdown? – Andrew Zimba Mar 26 '15 at 14:26
  • @AndrewZimba From my own knowledge, xlwings has not implemented the api for copying a whole sheet directly yet. However, this does not mean that it cannot be done by using xlwings. You should start a new question and tag it with 'xlwings'. The author will kindly answer your question. For the workaround, you may take a look at here as well: github.com/ZoomerAnalytics/xlwings/issues/123 – DanEng Mar 26 '15 at 15:02