CODEWITHSUNDEEP
pythonexcelvba
Bi7n
Bi7n

Reputation: 55

How to use python to append data to specific cell to existing excel table?

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.

Upvotes: 1

Views: 3285

Answers (1)

Bogdan Osyka
Bogdan Osyka

Reputation: 1983

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')

Upvotes: 1

Related Questions