Python에서 Excel 파일 읽기

Python에서 Excel 파일 읽기

---

Excel 파일이 있습니다.

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. 다음과 같은 목록에 형식을 저장합니다.

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

FORMAT구성 가능한 경우 제공된 이름으로 특정 열의 내용을 어떻게 읽습니까 ?

이것이 내가 시도한 것입니다. 현재 파일의 모든 내용을 읽을 수 있습니다.

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

주위 그럼 루프 values[0]밖으로 찾으려고 노력 FORMAT콘텐츠를 values[0]인덱스를 받고 다음과 Arm_id, DSPname and Pincode에서 values[0]다음 다음 루프에서 나는 모든의 인덱스 알고 FORMAT함으로써 내가 얻을 필요가 않는 값 알게 요인을.

그러나 이것은 그렇게 좋지 않은 해결책입니다.

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(). 그러나 클래스를 사용하는 경우 위와 같이 점 표기법을 통해 모든 값에 액세스 할 수 있습니다.

다음은 위 스크립트의 출력입니다.

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

---

다소 늦은 답변이지만 pandas를 사용하면 Excel 파일의 열을 직접 가져올 수 있습니다.

import pandas
import xlrd
df = pandas.read_excel('sample.xls')
#print the column names
print df.columns
#get the values for a given column
values = df['Arm_id'].values
#get a data frame with selected columns
FORMAT = ['Arm_id', 'DSPName', 'Pincode']
df_selected = df[FORMAT]

---

따라서 핵심 부분은 헤더 ( col_names = s.row(0)) 를 잡고 행을 반복 할 때 필요하지 않은 첫 번째 행을 건너 뛰는 것 for row in range(1, s.nrows)입니다. 1부터 범위 (암시 적 0이 아님)를 사용하여 수행됩니다. 그런 다음 zip을 사용하여 열 헤더로 'name'이있는 행을 단계별로 살펴 봅니다.

from xlrd import open_workbook

wb = open_workbook('Book2.xls')
values = []
for s in wb.sheets():
    #print 'Sheet:',s.name
    for row in range(1, s.nrows):
        col_names = s.row(0)
        col_value = []
        for name, col in zip(col_names, range(s.ncols)):
            value  = (s.cell(row,col).value)
            try : value = str(int(value))
            except : pass
            col_value.append((name.value, value))
        values.append(col_value)
print values

---

팬더를 사용하면 쉽게 읽을 수 있습니다.

import pandas as pd 
import xlrd as xl 
from pandas import ExcelWriter
from pandas import ExcelFile 

DataF=pd.read_excel("Test.xlsx",sheet_name='Sheet1')

print("Column headings:")
print(DataF.columns)

테스트 : https://repl.it 참조 : https://pythonspot.com/read-excel-with-pandas/

---

The approach I took reads the header information from the first row to determine the indexes of the columns of interest.

You mentioned in the question that you also want the values output to a string. I dynamically build a format string for the output from the FORMAT column list. Rows are appended to the values string separated by a new line char.

The output column order is determined by the order of the column names in the FORMAT list.

In my code below the case of the column name in the FORMAT list is important. In the question above you've got 'Pincode' in your FORMAT list, but 'PinCode' in your excel. This wouldn't work below, it would need to be 'PinCode'.

from xlrd import open_workbook
wb = open_workbook('sample.xls')

FORMAT = ['Arm_id', 'DSPName', 'PinCode']
values = ""

for s in wb.sheets():
    headerRow = s.row(0)
    columnIndex = [x for y in FORMAT for x in range(len(headerRow)) if y == firstRow[x].value]
    formatString = ("%s,"*len(columnIndex))[0:-1] + "\n"

    for row in range(1,s.nrows):
        currentRow = s.row(row)
        currentRowValues = [currentRow[x].value for x in columnIndex]
        values += formatString % tuple(currentRowValues)

print values

For the sample input you gave above this code outputs:

>>> 1.0,JaVAS,282001.0
2.0,JaVAS,282002.0
3.0,JaVAS,282003.0

And because I'm a python noob, props be to: this answer, this answer, this question, this question and this answer.

---

Although I almost always just use pandas for this, my current little tool is being packaged into an executable and including pandas is overkill. So I created a version of poida's solution that resulted in a list of named tuples. His code with this change would look like this:

from xlrd import open_workbook
from collections import namedtuple
from pprint import pprint

wb = open_workbook('sample.xls')

FORMAT = ['Arm_id', 'DSPName', 'PinCode']
OneRow = namedtuple('OneRow', ' '.join(FORMAT))
all_rows = []

for s in wb.sheets():
    headerRow = s.row(0)
    columnIndex = [x for y in FORMAT for x in range(len(headerRow)) if y == headerRow[x].value]

    for row in range(1,s.nrows):
        currentRow = s.row(row)
        currentRowValues = [currentRow[x].value for x in columnIndex]
        all_rows.append(OneRow(*currentRowValues))

pprint(all_rows)

---

Here is the code to read an excel file and and print all the cells present in column 1 (except the first cell i.e the header):

import xlrd

file_location="C:\pythonprog\xxx.xlsv"
workbook=xlrd.open_workbook(file_location)
sheet=workbook.sheet_by_index(0)
print(sheet.cell_value(0,0))

for row in range(1,sheet.nrows):
     print(sheet.cell_value(row,0))

참고URL : https://stackoverflow.com/questions/22169325/read-excel-file-in-python