使用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)
workboot.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 enuerate(DATA):
for j, col in enuerate(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 xrange(booksheet.nrows):
for col in xrange(booksheet.ncols):
print xlrd.cellname(row, col)
print booksheet.cell(row, col).value