extract certain cell's value from each excel sheet using python

0
votes

So, 30 excel sheets - monthly expenses with 2 columns expenses - 1 column exp.desc and the other is value:

C2 cell: Expense description D2 cell: Amount

C3 cell: Salary D3 cell: $4300

C4 cell: Office products D4 cell: $350

C5 cell: Bank charges D5 cell: $43 etc.

So, I can access those cells with this code:

Sheet name is '1' so I assigned the sheet to 'sheet1': sheet1 = workbook.get_sheet_by_name('1')

Access to the cells:

exp1name = sheet1['C3'].value

exp1value = str(sheet1['D3'].value)

exp2name....etc

exp2value...etc (same way as the first)

But I don't want to do it 30 or 31 times for every sheet for every day in a month and I need to loop the process.

Loop is supposed to do: Loop two columns in every sheet until sheet31 and print the values

This is pretty basic code for professionals, I tried so many times but couldn't do it, please help. Thanks in advance!

2
pythonexcel

2 Answers

0
votes

Here's some pseudo-code that should help you get started:

for i in range(1, 31):
    sheet = workbook.get_sheet_by_name(str(i))
    print(sheet['C3'].value)
    print(str(sheet['D3'].value))
    print()

The last print is for adding a blank line between sheets.

0
votes

So if I understand it correctly, you want to store all your data into variables? You can't make variables all with same name, so i would use a dictionary, each day as a key, the value holds a dictionary with all your variables:

month = {} # dictionary
for i in range (1,31): # For each day
    sheet_i = workbook.get_sheet_by_name(str(i))
    day = {}
    for j in range (2,5): # Depend on number of rows:
        day[sheet_i['C'+str(j)].value] = sheet_i['D'+str(j)].value
    month['sheet' + str(i)] = day

So if you want to acces your expences of the 4th day of the month:

expences_4th_day_of_the_month = month['sheet4']['expences']