转载说明：
原文地址：http://my.oschina.net/alazyer/blog/223354
原文有十处左右的错误，修正后转载于此。
经验证，python 3.4.3下可用。请各位朋友明察。
使用Python操作Excel不是什么难事，需要用到两个Lib， python-xlrd，python-xlwt
安装
pip install xlrd xlwt
支持xls，xlsx格式
写Excel
创建一个文件
workbook = xlwt.Workbook(encoding='utf-8')
增加一个表单
booksheet = workbook.add_sheet('Sheet 1')
增加一行
row = booksheet.row(i)
增加一列
col = booksheet.col(j)
为某个单元格赋值
booksheet.write(i, j, 'Test')
row.write(j, 'Test')
col.write(i, 'Test')
注意: 在add_sheet时, 置参数cell_overwrite_ok=True, 可以覆盖原单元格中数据。
cell_overwrite_ok默认为False, 覆盖的话, 会抛出异常.
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): for j, col in enumerate(row): booksheet.write(i, j, col) booksheet.col(0).width=10 workbook.save('成绩单.xls')
读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
import xlrd workbook = xlrd.open_workbook('成绩单.xls') print("There are {} sheets in the workbook".format(workbook.nsheets)) for booksheet in workbook.sheets(): print(booksheet.name) for row in range(booksheet.nrows): for col in range(booksheet.ncols): print(xlrd.cellname(row, col)) print(booksheet.cell(row, col).value)
