在Python中阅读Excel文件 [英] Read Excel File in Python

问题描述

我有一个Excel文件

I'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

我想以 Arm_id，DSPCode，Pincode 的形式保存一个字符串。此格式是可配置的，即它可能会更改为 DSPCode，Arm_id，Pincode 。我将其格式保存在列表中，如

I want to save a string in the form Arm_id,DSPCode,Pincode. This format is configurable, ie it might change to DSPCode,Arm_id,Pincode . I save it format in a list like

FORMAT = ['Arm_id', 'DSPName', 'Pincode']

鉴于 FORMAT，我如何阅读具有提供名称的特定列的内容是可配置的。

这是我试过的。目前，我可以阅读文件中的所有内容

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

我的输出是

[[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']]

然后我loo试图找出值[0] 中的$ code> value <0>然后在值[0]中获取 Arm_id，DSPname和Pincode 的索引然后从下一个循环中我知道所有 FORMAT 因子的索引，从而了解我需要获得哪些值。

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.

如何获取excel文件中具有名称的特定列的值？

推荐答案

这是一种方法：

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

你不必使用自定义类，可以简单地使用 dict（）。如果您使用类，则可以通过点符号访问所有值，如你看到上面。

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屋！