1

I am a new starter for Python and currently doing some data analysis.

I distributed few surveys using the same format. I want to sum the data from same cell (e.g. A1) from different tables(Say I have 100 surveys) but do not know how to achieve that. I learned that excel sum function could sum the data from different sheets but it does not works when they are in different excel files.

I tried to use Python to sum that and export to a new excel with exact the same format. But I came across the problem of append data to specific cell to the existing file. I used openpyxl for that but they cant read xls file and I cant change my file to xlsx.

Do you have any ideas to solve this or how to quick aggregate data from different table and output them to specific cell?

from openpyxl import load_workbook;
book = load_workbook('C:\Template.xls')
sheet = load_workbook.active
sheet['A1'] = sum(Annual_data
book.save('C:\Template.xls')

It is like you have 100 tables with same format as below, and I want to sum all B2 from 100 tables and output it to another table with same format.

Improve this question
4
  • Can you please provide with an error log. From just quickly viewing your script, I see that you do not have a closing bracket in line for: sum(Annual_data')'. This might already cause a syntax error. Commented Jun 15, 2018 at 11:15
  • Thanks for your reply! Sorry for that, I had that bracket in my code and I just accidently erase it when I change the name of the file and copy it here. The error log is InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format. I have tried xlrd but I don't know how to designate data to specific cell by that Commented Jun 15, 2018 at 11:20
  • The code I used by xlrd is as follow, book_ro = open_workbook('C:\Template.xls') book = copy(book_ro) # creates a writeable copy sheet1 = book.get_sheet(0) # get a first sheet sheet1['D18'] = sum(Annual_data) book.save('C:\Template.xls') And the error is 'Worksheet' object does not support item assignment Commented Jun 15, 2018 at 11:25
  • I would prefer using pandas in your case, in order to write data to a specific cell. Just read your excel file into a pandas data frame, use iloc, toidentify the location of the cell where you want to write your sum value and than save the updated dataframe to a newly create excel file. I will try to provide you with a code in the comment below. Commented Jun 15, 2018 at 11:53

1 Answer 1

Reset to default
1

As I mentioned in the comment above, I would suggest you using pandas. So here's is the script which should do what you intend to achieve. Here I assume that the sum of the columns from the different templates is stored in the variable sum_value:

import pandas as pd
path = 'C:\\Template.xls'
#if you have a header in your input excel document, then remove the header part in the line below
data = pd.read_excel(path, header=None)
#the coordinates of the cell where you want to write in your data
row_pos = 4
col_pos = 5
data.iloc[row_pos, col_pos] = sum_value
data.to_excel('out.xls')
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks a million. The data.to.excel does work but it also break all the format of my excel. Would you know how to fix that.
My code is as follow, data = pd.read_excel('C:\\Template.xls') data.iloc[4, 5] = sum_value data.to_excel('C:\\Template.xls')
It like python read my template, I give data to specific cell, the python write the data to my template. However, in this case, since the template went through python, my format was gone... So it is like pandas output a new file with the same name, but not just modify the value...
What exactly does it break. It is a bit difficult to give any suggestions as I don't know how the input file looks like. I would really suggest you going through some basic tutorials. This won't take much time but will definitely benefit you in the future. Try this one to start with: pbpython.com/improve-pandas-excel-output.html
Thanks very much! I would go through it! By break I mean that the cells in output table locate the same position but the appearance looks totally different. (i.e. A1 still locates in A1 but length, height of cells, colour of cells are all eliminated)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.