how to with open xlsx file with python 3

问题:

I have an xlsx file with 1 sheet. I am trying to open it using python 3 (xlrd lib), but I get an empty file!

I use this code:

file_errors_location = "C:\\Users\\atheelm\\Documents\\python excel mission\\errors1.xlsx"
workbook_errors = xlrd.open_workbook(file_errors_location)

and I have no errors, but when I type:

workbook_errors.nsheets

I get "0", even the file has some sheets... when I type:

workbook_errors 

I get:

xlrd.book.Book object at 0x2..

any help? thanks

回答1:

You can use Pandas pandas.read_excel just like pandas.read_csv:

import pandas as pd
file_errors_location = 'C:\\Users\\atheelm\\Documents\\python excel mission\\errors1.xlsx'
df = pd.read_excel(file_errors_location)
print(df)

回答2:

There are two modules for reading xls file : openpyxl and xlrd

This script allow you to transform a excel data to list of dictionnaries using xlrd

import xlrd

workbook = xlrd.open_workbook('C:\\Users\\atheelm\\Documents\\python excel mission\\errors1.xlsx')
workbook = xlrd.open_workbook('C:\\Users\\atheelm\\Documents\\python excel mission\\errors1.xlsx', on_demand = True)
worksheet = workbook.sheet_by_index(0)
first_row = [] # The row where we stock the name of the column
for col in range(worksheet.ncols):
    first_row.append( worksheet.cell_value(0,col) )
# tronsform the workbook to a list of dictionnary
data =[]
for row in range(1, worksheet.nrows):
    elm = {}
    for col in range(worksheet.ncols):
        elm[first_row[col]]=worksheet.cell_value(row,col)
    data.append(elm)
print data