pip install xlrd xlwt 

写excel

#coding=utf-8

import xlwt

workbook = xlwt.Workbook(encoding='utf-8')

booksheet = workbook.add_sheet('Sheet 1',cell_overwrite_ok=True)

workbook.add_sheet('Sheet 2')

DATA = (('学号', '姓名', '年龄', '性别', '成绩'),

            (1001, 'AAAA', 23, '男', 98),

            (1002, 'BBBB', 21, '女', 90),

            (1003, 'CCCC', 24, '女', 100),

            (1004, 'DDDD', 22, '女', 86),

            (1005, 'EEEE', 25, '女', 88),)

 

for i, row in enumerate(DATA):

  forj, col in enumerate(row):

   booksheet.write(i, j, col)

workbook.save('chenji.xls')


chenji.xls显示

wKioL1ZVdcOiBNgSAAAsTl4mP4g701.png



读取excel

读Excel


获取一个文件 

workbook = xlrd.open_workbook('成绩单.xls') 

获取文件中表单数量 

workbook.nsheets 

获取一个表单 

workbook.sheets()[i], 

workbook.sheet_by_index(i) 

workbook.sheet_by_name(u'Sheet1') 

获取行，列数 

sheet.nrows, sheet.ncols 

获取整行，列数据 

sheet.row(i), sheet.col(j) 

获取某个单元格数据 

sheet.cell(i, j).value 

sheet.row(i)[j].value 

sheet.col(j)[i].value



#coding=utf-8

import xlrd


workbook = xlrd.open_workbook('chenji.xls')

print "There are {} sheets in the workbook".format(workbook.nsheets)

for booksheet in workbook.sheets():

    print booksheet.name

    for row in xrange(booksheet.nrows):

        for col in xrange(booksheet.ncols):

            print xlrd.cellname(row, col)  #打印列名A1,b2...

            print booksheet.cell(row, col).value #打印单元格的值