使用 Python 读取 Excel 文件，如何获取指定列名的特定列的值? [英] Reading Excel File using Python, how do I get the values of a specific column with indicated column name?
问题描述
我有一个 Excel 文件:
Arm_id DSPName DSPCode HubCode PinCode PPTL1 JavaS 01 AGR 282001 1,22 JavaS 01 AGR 282002 3,43 JavaS 01 AGR 282003 5,6我想以 Arm_id,DSPCode,Pincode 形式保存一个字符串.此格式是可配置的，即它可能会更改为 DSPCode,Arm_id,Pincode.我将它保存在一个列表中:
FORMAT = ['Arm_id', 'DSPName', 'Pincode']如果 FORMAT 是可配置的，我如何读取具有提供名称的特定列的内容?
这是我试过的.目前我可以读取文件中的所有内容
from xlrd import open_workbookwb = open_workbook('sample.xls')对于 wb.sheets() 中的 s:#print '工作表:',s.name值 = []对于范围内的行(s.nrows):col_value = []对于范围内的 col(s.ncols):值 = (s.cell(row,col).value)尝试:值 = str(int(value))除了:通过col_value.append(value)values.append(col_value)打印值我的输出是:
<预><代码>[[u'Arm_id', u'DSPName', u'DSPCode', u'HubCode', u'PinCode', u'PPTL'],['1', u'JaVAS', '1', u'AGR', '282001', u'1,2'],['2', u'JaVAS', '1', u'AGR', '282002', u'3,4'],['3', u'JaVAS', '1', u'AGR', '282003', u'5,6']]然后我循环遍历 values[0] 试图找出 values[0] 中的 FORMAT 内容，然后获取的索引Arm_id, DSPname 和 Pincode 在 values[0] 然后从下一个循环我知道所有 FORMAT 因素的索引，从而得到知道我需要获得哪个值.
但这是一个糟糕的解决方案.
如何获取 Excel 文件中具有名称的特定列的值?
这是一种方法:
from xlrd import open_workbook类臂(对象):def __init__(self, id, dsp_name, dsp_code, hub_code, pin_code, pptl):self.id = idself.dsp_name = dsp_nameself.dsp_code = dsp_codeself.hub_code = hub_codeself.pin_code = pin_codeself.pptl = pptldef __str__(self):return("手臂对象:\n"" Arm_id = {0}\n"" DSPName = {1}\n"" DSPCode = {2}\n"" 中心代码 = {3}\n"" 密码 = {4} \n""PPTL = {5}".format(self.id, self.dsp_name, self.dsp_code,self.hub_code, self.pin_code, self.pptl))wb = open_workbook('sample.xls')对于 wb.sheets() 中的工作表:number_of_rows = sheet.nrowsnumber_of_columns = sheet.ncols项目 = []行 = []对于范围内的行(1，number_of_rows):值 = []对于范围内的列(number_of_columns):值 = (sheet.cell(row,col).value)尝试:值 = str(int(value))除了值错误:经过最后:值.附加(值)item = Arm(*values)items.append(item)对于项目中的项目:打印项目print("访问单个值(例如 DSPName):{0}".format(item.dsp_name))打印您不必使用自定义类，您只需使用 dict().但是，如果您使用类，则可以通过点表示法访问所有值，如上所示.
这是上面脚本的输出:
手臂对象:Arm_id = 1DSP 名称 = JavaSDSP 代码 = 1中心代码 = AGR密码 = 282001PPTL = 1访问单个值(例如 DSPName):JaVAS手臂对象:Arm_id = 2DSP 名称 = JavaSDSP 代码 = 1中心代码 = AGR密码 = 282002PPTL = 3访问单个值(例如 DSPName):JaVAS手臂对象:Arm_id = 3DSP 名称 = JavaDSP 代码 = 1中心代码 = AGR密码 = 282003PPTL = 5访问单个值(例如 DSPName):JaVASI've an Excel File:
Arm_id DSPName DSPCode HubCode PinCode PPTL
1 JaVAS 01 AGR 282001 1,2
2 JaVAS 01 AGR 282002 3,4
3 JaVAS 01 AGR 282003 5,6
I want to save a string in the form Arm_id,DSPCode,Pincode. This format is configurable, i.e. it might change to DSPCode,Arm_id,Pincode. I save it in a list like:
FORMAT = ['Arm_id', 'DSPName', 'Pincode']
How do I read the content of a specific column with provided name, given that the FORMAT is configurable?
This is what I tried. Currently I'm able to read all the content in the file
from xlrd import open_workbook
wb = open_workbook('sample.xls')
for s in wb.sheets():
#print 'Sheet:',s.name
values = []
for row in range(s.nrows):
col_value = []
for col in range(s.ncols):
value = (s.cell(row,col).value)
try : value = str(int(value))
except : pass
col_value.append(value)
values.append(col_value)
print values
My output is :
[
[u'Arm_id', u'DSPName', u'DSPCode', u'HubCode', u'PinCode', u'PPTL'],
['1', u'JaVAS', '1', u'AGR', '282001', u'1,2'],
['2', u'JaVAS', '1', u'AGR', '282002', u'3,4'],
['3', u'JaVAS', '1', u'AGR', '282003', u'5,6']
]
Then I loop around values[0] trying to find out the FORMAT content in values[0] and then getting the index of Arm_id, DSPname and Pincode in the values[0] and then from next loop I know the index of all the FORMAT factors , thereby getting to know which value do I need to get .
But this is such a poor solution.
How do I get the values of a specific column with name in excel file?
This is one approach:
from xlrd import open_workbook
class Arm(object):
def __init__(self, id, dsp_name, dsp_code, hub_code, pin_code, pptl):
self.id = id
self.dsp_name = dsp_name
self.dsp_code = dsp_code
self.hub_code = hub_code
self.pin_code = pin_code
self.pptl = pptl
def __str__(self):
return("Arm object:\n"
" Arm_id = {0}\n"
" DSPName = {1}\n"
" DSPCode = {2}\n"
" HubCode = {3}\n"
" PinCode = {4} \n"
" PPTL = {5}"
.format(self.id, self.dsp_name, self.dsp_code,
self.hub_code, self.pin_code, self.pptl))
wb = open_workbook('sample.xls')
for sheet in wb.sheets():
number_of_rows = sheet.nrows
number_of_columns = sheet.ncols
items = []
rows = []
for row in range(1, number_of_rows):
values = []
for col in range(number_of_columns):
value = (sheet.cell(row,col).value)
try:
value = str(int(value))
except ValueError:
pass
finally:
values.append(value)
item = Arm(*values)
items.append(item)
for item in items:
print item
print("Accessing one single value (eg. DSPName): {0}".format(item.dsp_name))
print
You don't have to use a custom class, you can simply take a dict(). If you use a class however, you can access all values via dot-notation, as you see above.
Here is the output of the script above:
Arm object:
Arm_id = 1
DSPName = JaVAS
DSPCode = 1
HubCode = AGR
PinCode = 282001
PPTL = 1
Accessing one single value (eg. DSPName): JaVAS
Arm object:
Arm_id = 2
DSPName = JaVAS
DSPCode = 1
HubCode = AGR
PinCode = 282002
PPTL = 3
Accessing one single value (eg. DSPName): JaVAS
Arm object:
Arm_id = 3
DSPName = JaVAS
DSPCode = 1
HubCode = AGR
PinCode = 282003
PPTL = 5
Accessing one single value (eg. DSPName): JaVAS
这篇关于使用 Python 读取 Excel 文件，如何获取指定列名的特定列的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
