首先安装第三方库
pip install xlrd xlwt
XLRD
import xlrd
book = xlrd.open_workbook('demo.xlsx')
print(book.sheets()) # 获取excel中全部sheet
sheet = book.sheet_by_index(0) # 获取excel中的第一个sheet
print(sheet.nrows, sheet.ncols) # 获取sheet的行数和列数
print(sheet.cell(0, 0)) # 获取具体的cell，根据行号和列号，可以获得唯一的cell
cell = sheet.cell(0, 0)
print(cell.ctype) # xlrd.XL_CELL_TEXT 值为1 文本类型
print(xlrd.XL_CELL_TEXT, xlrd.XL_CELL_NUMBER)
print(cell.value) # 打印cell中的值
cell2 = sheet.cell(1, 1)
print(cell2.value)
# ROW
print(sheet.row(1)) # 获取第一行的内容：返回一个list，[text:'李雷1', number:60.0, number:70.0, number:69.0]
print(sheet.row_values(1)) # 获取第一行的值，返回一个list，['李雷1', 60.0, 70.0, 69.0]
print(sheet.row_values(1, 0, 4)) # 类似切片的操作，参数：row, startInx, endInx ，返回一个list：[70.0, 69.0]
# COLUMN
print(sheet.col(2))
print(sheet.col_values(2))
print(sheet.col_values(2, 0, 4))
# sheet.put_cell(rowx, colx, ctype, value, xf_index)
# xf_index: 字体，对齐，一般设置为None即可
XLWT
import xlwt
wbook = xlwt.Workbook()
wsheet = wbook.add_sheet('sheet1')
wsheet.write(r, c, rsheet.cell_value(r, c), style)
wbook.save('output.xlsx')
CODE
import xlrd
import xlwt
rbook = xlrd.open_workbook('demo.xlsx')
rsheet = rbook.sheet_by_index(0)
nc = rsheet.ncols
# 添加一个cell
rsheet.put_cell(0, nc, xlrd.XL_CELL_TEXT, u'总分', None)
for row in range(1, rsheet.nrows):
t = sum(rsheet.row_values(row, 1, nc)) # 计算每一行的分数之和
rsheet.put_cell(row, nc, xlrd.XL_CELL_NUMBER, t, None) # 添加一个cell，在总分的列上
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('output.xlsx') 注意这里不要写成xlsx，在个别机器上可能会打不开，保存为xls即可
wbook.save('output.xls')
