Adding cell values from multiple sheets in Excel workbook via Python

Question

In my excel workbook, I have three separate spreadsheets (the spreadsheets are identical in format and represent count values for different categories (passenger cars, trucks, etc.)). I need to combine the spreadsheets in the following way and create a new workbook with the combined data (CombinedCount.xlsx):

='Passenger'!B3+'Heavy Truck'!B3+'Light Truck'!B3

Passenger: sheet 1 Heavy Truck: sheet 2 Light Truck: sheet 3

There are a lot of cells to add together--and this code will be applied to a large number of workbooks with the exact same format. Is there a way to automate this process with some code?

I'm a novice at Python, so I'm learning as I go.

score 0 · Accepted Answer · edited May 23 '17 at 12:29

You could use the openpyxl package. Here are the installation instructions, documentation and demos:

https://pythonhosted.org/openpyxl/

Then to add info from different worksheets of one workbook into a new workbook, you could do the following.

1) Load workbook

import openpyxl
wb1 = openpyxl.load_workbook('workbook1.xlsx')

2) Load sheets

ws1 = wb1.get_sheet_by_name('Sheet1')
ws2 = wb1.get_sheet_by_name('Sheet2')
ws3 = wb1.get_sheet_by_name('Sheet3')

3) Add values in cell B3

x = ws1['B3'] + ws2['B3'] + ws3['B3']

4) Finally, create new workbook

wb2 = openpyxl.Workbook()
ws = wb2.active
ws.title = 'CombinedCount'
ws['B3'] = x
wb2.save('CombinedCount.xlsx')

You can loop all this accordinly for workbooks and worksheet you can have.

You can also use other packages to read/write excel files: xlwt, xlrd, xlsxwriter. Take a look at this answer to a similar question. Hope it helps.

Adding cell values from multiple sheets in Excel workbook via Python

1 Answers1