Reading Particular Cell Value from Excelsheet in Python

Reading particular cell value from excelsheet in python

To access the value for a specific cell you would use:

value = worksheet.cell(row, column)

How To Read A Particular Cell In Excel In Python And Make It A Variable

The module you're looking for is xlrd, alternatively you could use openpyxl. It loads the workbook, selects the sheet, and then you can get the value of the cell you want.

import xlrd

workbook = xlrd.open_workbook('myfile.xls')
worksheet = workbook.sheet_by_name('Sheet1')

value = worksheet.cell(row, column)

reading excel dataframe starting from specific location based on the cell value

Try:

#index of first row above "start"
row_index = df[df.shift(-1).eq("start").any(axis=1)].index.min()

#name of first column before the column that contains "start"
col_index = df.loc[:,df.shift(-1, axis=1).eq("start").any(0)].columns[0]

#select all rows and columns per the above indices.
>>> df.loc[row_index:, col_index:]

    B      C
0  11    111
1  22  start
2  33    333
3  44    444

Pandas: Read specific Excel cell value into a variable

Elaborating on @FLab's comment use something along those lines:

Edit:

Updated the answer to correspond to the updated question that asks how to read some sheets at once.
So by providing sheet_name=None to read_excel() you can read all the sheets at once and pandas return a dict of DataFrames, where the keys are the Excel sheet names.

import pandas as pd
In [10]:

df = pd.read_excel('Book1.xlsx', sheetname=None, header=None)
df
Out[11]:
{u'Sheet1':    0
 0  1
 1  1, u'Sheet2':     0
 0   1
 1   2
 2  10}
In [13]:
data = df["Sheet1"]
secondary_data = df["Sheet2"]
secondary_data.loc[2,0]
Out[13]:
10

Alternatively, as noted in this post, if your Excel file has several sheets you can pass sheetname a list of strings, sheet names to parse eg.

df = pd.read_excel('Book1.xlsx', sheetname=["Sheet1", "Sheet2"], header=None)

Credits to user6241235 for digging out the last alternative

Extract particular values from Excel file, place it into static text and generate .txt files

The following code works fine on my computer after creating an Excel file with your sample data. Here's what I changed to make it work: The second for loop wasn't necessary so I removed it and outdented the code that was formerly in that loop. I changed the range() invocation to start at one and go one past the number of rows since the row numbers start at zero in xlrd. And I changed the .cell method calls to use the row variable instead of always getting values from row one.

import xlrd


xlsfilename='Testing.xls'
test = xlrd.open_workbook(xlsfilename)
number_subjetcs=4 #will update this to require user input based on number of rows, test file has 4
number_columns=6 #is static

for row in range(1, number_subjetcs + 1): 
    txtfilename = 'testfile' + str(row - 1) + '.txt'
    with open(txtfilename, "w") as f:
        PrinterName = test.sheets()[0].cell(row, 0).value
        ShortName = test.sheets()[0].cell(row, 3).value
        Driver = test.sheets()[0].cell(row, 4).value
        Model = test.sheets()[0].cell(row, 5).value
        Desc = test.sheets()[0].cell(row, 2).value
        Server = test.sheets()[0].cell(row, 1).value
        f.write('VERSION = "0.1"')
        f.write('\n' + 'TIME = "20201015111814"')
        f.write('\n' + 'SYSTEM = "PR0"')
        f.write('\n' + 'RELEASE = "750"')
        f.write('\n' + '* DEVICE = {')
        f.write('\n' + '  NAME = "' + PrinterName + '"')
        f.write('\n' + '  PADEST = "' + ShortName + '"')
        f.write('\n' + '  PATYPE = "'+ Driver + '"')
        f.write('\n' + '  PAMODEL = "'+ Model + '"')
        f.write('\n' + '  PACLASS = ""')
        f.write('\n' + '  PAARCHIVER = ""')
        f.write('\n' + '  PALANGU = ""')
        f.write('\n' + '  PADISABLED = ""') #second file needs to have X here
        f.write('\n' + '  PANOQUERY = ""')
        f.write('\n' + '  PADUPCOPY = ""')
        f.write('\n' + '  PADUPLEX = ""')
        f.write('\n' + '  PASTORELOC = ""')
        f.write('\n' + '  PADFLTUTTL = ""')
        f.write('\n' + '  PADFLTSTTL = ""')
        f.write('\n' + '  PASYNC = ""')
        f.write('\n' + '  PAMONI = ""')
        f.write('\n' + '  PASTANDORT = "' + Desc + '"')
        f.write('\n' + '  PAMSG = "' + Desc + '"')
        f.write('\n' + '  PAMSSERVER = "SERVER_0"')
        f.write('\n' + '  PAMETHOD = "C"')
        f.write('\n' + '  PAPROSNAME = "\\\\' + Server + "\\" + PrinterName + '"')
        f.write('\n' + '  PALOMS = ""')
        f.write('\n' + '  PALPRCMD = ""')
        f.write('\n' + '  PALPDHOST = ""')
        f.write('\n' + '  PALPDPORT = "0"')
        f.write('\n' + '  PACONNTIME = "0"')
        f.write('\n' + '  PAREADTIME = "0"')
        f.write('\n' + '  PATRAYS = "0"')
        f.write('\n' + '  PAXSHIFT = "0"')
        f.write('\n' + '  PAYSHIFT = "0"')
        f.write('\n' + '  PAXSHUNIT = "MM"')
        f.write('\n' + '  PAYSHUNIT = "MM"')
        f.write('\n' + '  PACRYPTMOD = ""')
        f.write('\n' + '  PACRYPTMET = ""')
        f.write('\n' + '  PANOVSERVR = ""')
        f.write('\n' + '  PAPOOLART = "P"')
        f.write('\n' + '  PATRACE2 = ""')
        f.write('\n' + '  PATRACEF = ""')
        f.write('\n' + '  PAPROTDATA = ""')
        f.write('\n' + '  PAPROTCMD = ""')
        f.write('\n' + '  PAPROTRES = ""')
        f.write('\n' + '  PAKEEPFILE = ""')
        f.write('\n' + '  CHGNAME1 = ""')
        f.write('\n' + '  CHGSAPREL1 = "750"')
        f.write('\n' + '  CHGSAPSYS1 = "PR0"')
        f.write('\n' + '  PADEVGRP = ""')
        f.write('\n' + '  COLORPRT = "X"')
        f.write('\n' + '  PRINTMODE = ""')
        f.write('\n' + '  INPUTTRAY = ""')
        f.write('\n' + '  PANOCHOICE = ""')
        f.write('\n' + '}')

---

Related Topics