How to read specif cell with pandas library?-CodePudding

I want to read from excel sheet a specific cell: h6. So I try it like this:

import pandas as pd

excel_file = './docs/fruit.xlsx'

df = pd.read_excel(excel_file,'Overzicht')

sheet = df.active

x1 = sheet['H6'].value

print(x1)

But then I get this error:

File "C:\Python310\lib\site-packages\pandas\core\generic.py", line 5575, in __getattr__
    return object.__getattribute__(self, name)
AttributeError: 'DataFrame' object has no attribute 'active'

So my questiion is: How to read specif cell from sheet from excelsheet?

Thank you

Oke, I tried with openpyxl:

import openpyxl


path = "./docs/fruit.xlsx"


wb_obj = openpyxl.load_workbook(path)


sheet_obj = wb_obj.active


cell_obj = sheet_obj.cell(row = 6, column = 9)

print(cell_obj.value)

But then the formula is printed. Like this:

=(H6*1000)/F6/G6

and not the value: 93

CodePudding user response：

You can do this using openpyxl directly or pandas (which internally uses openpyxl behind the scene)...

Using Openpyxl

You will need to use data_only=True when you open the file. Also, make sure you know the row and column number. To read the data in H6, row would be 6 and 8 would be H

import openpyxl
path = "./docs/Schoolfruit.xlsx"
wb_obj = openpyxl.load_workbook(path, data_only=True)
sheet_obj = wb_obj.active  ## Or use sheet_obj = wb_obj['Sheet1'] if you know sheet name

val = sheet_obj.cell(row = 6, column = 8).value 
print(val)

Using Pandas

The other option is to use pandas read_excel() which will read the whole sheet into a dataframe. You can use iloc() or at() to read the specific cell. Note that this is probably the less optimal solution if you need to read just one cell... Another point to note here is that, once you have read the data into a dataframe, the row 1 will be considered as the header and the first row would now be 0. So the row number would be 4 instead of 6. Similarly, the first column would now be 0 and not 1, which would change the position to [4,7]

import pandas as pd
path = "./docs/Schoolfruit.xlsx"
df = pd.read_excel(path, 'Sheet1')
print(df.iloc[4,7])

CodePudding user response：

I found a solution and hope, it works for you.

import pandas as pd

excel_file = './docs/Schoolfruit.xlsx'

df = pd.read_excel(excel_file, sheet_name='active' ,header=None, skiprows=1)

print(df[7][4])

7: Hth column

4: 6th row (skipped first row and index is began from 0)