python怎么打开excel文件并处理_python处理excel文件

一、简介

用python来自动生成excel数据文件。python处理excel文件主要是第三方模块库xlrd、xlwt、xluntils和pyExcelerator，除此之外，python处理excel还可以用win32com和openpyxl模块。

安装:

pip install xlutils

二、使用xlrd读取文件：

importxlrd#打开一个workbook

workbook = xlrd.open_workbook('E:\\Code\\Python\\testdata.xls')#抓取所有sheet页的名称

worksheets =workbook.sheet_names()print('worksheets is %s' %worksheets)#定位到sheet1

worksheet1 = workbook.sheet_by_name(u'Sheet1')"""#通过索引顺序获取

worksheet1 = workbook.sheets()[0]

#或

worksheet1 = workbook.sheet_by_index(0)"""

"""#遍历所有sheet对象

for worksheet_name in worksheets:

worksheet = workbook.sheet_by_name(worksheet_name)"""

#遍历sheet1中所有行row

num_rows =worksheet1.nrowsfor curr_row inrange(num_rows):

row=worksheet1.row_values(curr_row)print('row%s is %s' %(curr_row,row))#遍历sheet1中所有列col

num_cols =worksheet1.ncolsfor curr_col inrange(num_cols):

col=worksheet1.col_values(curr_col)print('col%s is %s' %(curr_col,col))#遍历sheet1中所有单元格cell

for rown inrange(num_rows):for coln inrange(num_cols):

cell=worksheet1.cell_value(rown,coln)printcell"""#其他写法：

cell = worksheet1.cell(rown,coln).value

print cell

#或

cell = worksheet1.row(rown)[coln].value

print cell

#或

cell = worksheet1.col(coln)[rown].value

print cell

#获取单元格中值的类型，类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

cell_type = worksheet1.cell_type(rown,coln)

print cell_type"""

示例：

整体思路为，打开文件，选定表格，读取行列内容，读取表格内数据

importxlrdfrom datetime importdate,datetime

file= 'test3.xlsx'

defread_excel():

wb= xlrd.open_workbook(fil