Openpyxl to create dataframe with sheet name and specific cell values?

What I need to do:

• Open Excel Spreadsheet in Python/Pandas
• Create df with [name, balance]

Example:

name	balance
Jones Ministry	45,408.83
Smith Ministry	38,596.20
Doe Ministry	28,596.20

What I have done so far…

import pandas as pd
import openpyxl as op
from openpyxl import load_workbook
from pathlib import Path

Then…

# Excel File
src_file = src_file = Path.cwd() / 'lm_balance.xlsx'

df = load_workbook(filename = src_file)

I viewed all the sheet names by…

df.sheetnames

And created a dataframe with the ‘name’ column

balance_df = pd.DataFrame(df.sheetnames) 

My spreadsheet looks like this…

I now need to loop thru each sheet and add the ‘ending fund balance’ and corresponding ‘value’

• The “Ending Fund Balance” is at different rows, but always the final row. The ‘value’ is always in column ‘G’

How do I go about doing this?

I have read through examples in:

• Automate the Boring Stuff
• Openpyxl documentation
• PBPython.com examples
• Stack Overflow questions

I appreciate your help!

Working samples on github: Github: JohnMillstead: Balance_Study

Answer

To ge a cell value first set the data_only=True on load_workbook, otherwise you could end up getting the cell formula. To get last row of a worksheet you can use ws.max_row. Combine the previous command with the already created dataframe and apply for each worksheet name a function to get the last value from that worksheet at the G column (wb[x][f'G{wb[x].max_row}']).

import pandas as pd
from openpyxl import load_workbook

src_file = 'test_balance.xlsx'
wb = load_workbook(filename = src_file, data_only=True)

df = pd.DataFrame(data=wb.sheetnames, columns=["name"])
df["balance"] = df.name.apply(lambda x: wb[x][f'G{wb[x].max_row}'].value)

print(df)

Output from df

                   name   balance
0        Jones Ministry  15100.08
1        Smith Ministry  45408.83
2        Stark Ministry   1561.75
3          Doe Ministry   7625.75
4       Bright Ministry   3078.30
5      Lincoln Ministry   6644.59
6     Martinez Ministry  11500.54
7       Patton Ministry   9782.65
8         Rich Ministry   8429.88
9        Seitz Ministry   2974.58
10       Bhiri Ministry    622.83
11  Pignatelli Ministry  34992.05
12      Cortez Ministry   -283.48
13      Little Ministry  13755.80
14     Johnson Ministry  -2035.31