XLS

读写xls文档需要用到两个第三方包，xlrd和xlwt

pip install xlrd xlwt

xlrd

获取xls文件对象

>>> import xlrd
>>> book = xlrd.open_workbook('demo.xlsx')f = open('demo.xlsx')

通过index获取sheet

>>> sheet = book.sheet_by_index(0)

通过name过去sheet

>>> sheet = book.sheet_by_name('Sheet1')

获取行数

>>> sheet.ncols
4

获取列数

>>> sheet.ncols
4

获取单元格

>>> cell = sheet.cell(0, 0)
>>> cell.value
'name'
>>> cell.ctype
1
>>> cell = sheet.cell(0, 0)
>>> cell.value
'name'
>>> cell.ctype
1  # 1代表文本类型
>>> xlrd.XL_CELL_TEXT
1

获取行

# key: value
>>> sheet.row(0)
[text:'name', text:'chinese', text:'math', text:'english']
>>> sheet.row(1)
[text:'a', number:60.0, number:60.0, number:60.0]
>>> sheet.row(2)
[text:'b', number:61.0, number:61.0, number:61.0]
>>> sheet.row(3)
[text:'c', number:62.0, number:62.0, number:62.0]


# row_values(rowx, start_colx=0, end_colx=None)
# 只要value
>>> sheet.row_values(1)
['a', 60.0, 60.0, 60.0]

# 只要某几列数据
>>> sheet.row_values(1, 1)
[60.0, 60.0, 60.0]

xlwt

>>> wbook = xlwt.Workbook()
>>> wsheet = wbook.add_sheet(rsheet.name)
>>> style = xlwt.easyxf('align: vertical center, horizontal center')
>>> wsheet.write(rowx, colx, value, style)
>>> wbook.save('filename.xlsx')

例子

计算总成绩

import xlrd, xlwt
rbook = xlrd.open_workbook('demo.xlsx')
rsheet = rbook.sheet_by_index(0)
n_cols = rsheet.ncols
rsheet.put_cell(0, n_cols, xlrd.XL_CELL_TEXT, 'total', None)

for row in range(1, rsheet.nrows):
    total_rank = sum(rsheet.row_values(row, 1))
    rsheet.put_cell(row, n_cols, xlrd.XL_CELL_NUMBER, total_rank, None)


wbook = xlwt.Workbook()
wsheet = wbook.add_sheet(rsheet.name)
style = xlwt.easyxf('align: vertical center, horizontal center')
for r in range(rsheet.nrows):
    for c in range(rsheet.ncols):
        wsheet.write(r, c, rsheet.cell_value(r, c), style)

wbook.save('demo3.xlsx')