Python读取Excel

最新推荐文章于 2021-11-22 10:18:04 发布

小龙在山东

最新推荐文章于 2021-11-22 10:18:04 发布

阅读量699

点赞数

分类专栏： python 文章标签： python excel

python 专栏收录该内容

246 篇文章 14 订阅

订阅专栏

安装

使用pip安装

pip install xlrd

显示工作表

from xlrd import open_workbook

wb = open_workbook('simple.xls')

for s in wb.sheets():
    print 'Sheet:',s.name
    for row in range(s.nrows):
        values = []
        for col in range(s.ncols):
            values.append(s.cell(row,col).value)
        print ','.join(values)
    print

sheet操作

from xlrd import open_workbook

book = open_workbook('simple.xls')

print book.nsheets

for sheet_index in range(book.nsheets):
    print book.sheet_by_index(sheet_index)

print book.sheet_names()

for sheet_name in book.sheet_names():
    print book.sheet_by_name(sheet_name)

for sheet in book.sheets():
    print sheet

sheet内操作

from xlrd import open_workbook,cellname

book = open_workbook('odd.xls')
sheet = book.sheet_by_index(0)

print sheet.name

print sheet.nrows
print sheet.ncols

for row_index in range(sheet.nrows):
    for col_index in range(sheet.ncols):
        print cellname(row_index,col_index),'-',
        print sheet.cell(row_index,col_index).value

访问特定的cell

from xlrd import open_workbook,XL_CELL_TEXT

book = open_workbook('odd.xls')
sheet = book.sheet_by_index(1)

cell = sheet.cell(0,0)

print cell
print cell.value
print cell.ctype==XL_CELL_TEXT

for i in range(sheet.ncols):
    print sheet.cell_type(1,i),sheet.cell_value(1,i)

遍历sheet里的所有内容

from xlrd import open_workbook

book = open_workbook('odd.xls')
sheet0 = book.sheet_by_index(0)
sheet1 = book.sheet_by_index(1)

print sheet0.row(0)
print sheet0.col(0)
print
print sheet0.row_slice(0,1)
print sheet0.row_slice(0,1,2)
print sheet0.row_values(0,1)
print sheet0.row_values(0,1,2)
print sheet0.row_types(0,1)
print sheet0.row_types(0,1,2)
print
print sheet1.col_slice(0,1)
print sheet0.col_slice(0,1,2)
print sheet1.col_values(0,1)
print sheet0.col_values(0,1,2)
print sheet1.col_types(0,1)
print sheet0.col_types(0,1,2)

辅助函数

from xlrd import cellname, cellnameabs, colname
print cellname(0,0),cellname(10,10),cellname(100,100)
print cellnameabs(3,1),cellnameabs(41,59),cellnameabs(265,358)
print colname(0),colname(10),colname(100)

Unicode

from xlrd import open_workbook
book = open_workbook('dodgy.xls',encoding='cp1252')

单元格类型

Text

Date

from datetime import date,datetime,time
from xlrd import open_workbook,xldate_as_tuple

book = open_workbook('types.xls')
sheet = book.sheet_by_index(0)

date_value = xldate_as_tuple(sheet.cell(3,2).value,book.datemode)
print datetime(*date_value),date(*date_value[:3])
datetime_value = xldate_as_tuple(sheet.cell(3,3).value,book.datemode)
print datetime(*datetime_value)
time_value = xldate_as_tuple(sheet.cell(3,4).value,book.datemode)
print time(*time_value[3:])
print datetime(*time_value)

Error

from xlrd import open_workbook,error_text_from_code

book = open_workbook('types.xls')
sheet = book.sheet_by_index(0)

print error_text_from_code[sheet.cell(5,2).value]
print error_text_from_code[sheet.cell(5,3).value]

Empty/Blank

from xlrd import open_workbook,empty_cell

print empty_cell.value

book = open_workbook('types.xls')
sheet = book.sheet_by_index(0)
empty = sheet.cell(6,2)
blank = sheet.cell(7,2)
print empty is blank, empty is empty_cell, blank is empty_cell

book = open_workbook('types.xls',formatting_info=True)
sheet = book.sheet_by_index(0)
empty = sheet.cell(6,2)
blank = sheet.cell(7,2)
print empty.ctype,repr(empty.value)
print blank.ctype,repr(blank.value)

所有单元格类型

from xlrd import open_workbook
def cell_contents(sheet,row_x):
 result = []
 for col_x in range(2,sheet.ncols):
 cell = sheet.cell(row_x,col_x)
 result.append((cell.ctype,cell,cell.value))
 return result
sheet = open_workbook('types.xls').sheet_by_index(0)
print 'XL_CELL_TEXT',cell_contents(sheet,1)
print 'XL_CELL_NUMBER',cell_contents(sheet,2)
print 'XL_CELL_DATE',cell_contents(sheet,3)
print 'XL_CELL_BOOLEAN',cell_contents(sheet,4)
print 'XL_CELL_ERROR',cell_contents(sheet,5)
print 'XL_CELL_BLANK',cell_contents(sheet,6)
print 'XL_CELL_EMPTY',cell_contents(sheet,7)
print
sheet = open_workbook(
 'types.xls',formatting_info=True
 ).sheet_by_index(0)
print 'XL_CELL_TEXT',cell_contents(sheet,1)
print 'XL_CELL_NUMBER',cell_contents(sheet,2)
print 'XL_CELL_DATE',cell_contents(sheet,3)
print 'XL_CELL_BOOLEAN',cell_contents(sheet,4)
print 'XL_CELL_ERROR',cell_contents(sheet,5)
print 'XL_CELL_BLANK',cell_contents(sheet,6)
print 'XL_CELL_EMPTY',cell_contents(sheet,7)

读取大文件

from xlrd import open_workbook

book = open_workbook('simple.xls',on_demand=True)

for name in book.sheet_names():
    if name.endswith('2'):
        sheet = book.sheet_by_name(name)
        print sheet.cell_value(0,0)
        book.unload_sheet(name)