0

I have 30 different spreadsheets. From each of these, I must take (read) the value of one cell of data. The cell is always the same in all the files. Thus, in total I have to read 30 values. These values have to be written in another unique excel file, in a single column, for example: cell A1 from File1.xlsx has to be pasted in the cell B1 in FinalFile.xlsx; cell A1 from File2.xlsx has to be pasted in the cell B2 in FinalFile.xlsx; ... cell A1 from Filen.xlsx has to be pasted in the cell Bn in FinalFile.xlsx; cell A1 from File30.xlsx has to be pasted in the cell B30 in FinalFile.xlsx.

Can someone help me to write a script that automates this process?

I thank in advance who will be able to answer me.

Marta Gechelin
  • 1
  • [This should help.](http://stackoverflow.com/questions/2942889/reading-parsing-excel-xls-files-with-python) – Aaron Critchley Apr 23 '15 at 15:57
  • 1
    You could use @AaronCritchley's link as starting point and post some code you tried here. This would help answering. – TobiMarg Apr 23 '15 at 15:59
  • There are several modules made to work with excel. Here is one of them http://xlsxwriter.readthedocs.org/en/latest/getting_started.html – Daniel Apr 23 '15 at 16:02

1 Answers1

0

With my wrapper library and without considering charts, images, formulas and styles, you could achieve it with the following sample codes:

>>> import os
>>> import glob
>>> import pyexcel # pip install pyexcel
>>> import pyexcel.ext.xlsx # pip install pyexcel-xlsx
>>> values=[]
>>> for file in glob.glob(os.path.join("your_xlsx_folder", "*.xlsx")):
...     sheet = pyexcel.get_sheet(file_name=file)
...     values.append(sheet['A1']) # read cell 'A1' among all files
>>> sheet = pyexcel.get_sheet(file_name="your_unique_file.xlsx")
>>> sheet.column[1] = values # assign values to second column, or 'B' column
>>> sheet.save_as("newly_created.xlsx") # this is the resulting file

Here's documentation of pyexcel v0.1.4 and installation instructions for pyexcel-xlsx, which uses openpyxl.

chfw
  • 4,502
  • 2
  • 29
  • 32