Python: 表格excel操作

写操作

import xlwt

# 写操作
plist = [['姓名', '年龄', '性别'], ['猪', '13', '男'], ['狗', '21', '女']]
book = xlwt.Workbook()
sheet = book.add_sheet('人员列表')
# 行列去写
row = 0
for people in plist:  # people   ['姓名', '年龄', '性别']
    col = 0  # 列
    for one in people:  # one '猪'
        sheet.write(row, col, one)
        col += 1
    row += 1
# book.save('people.xls') # 保存excel  如果不写路径会在当前项目存储我们excel
book.save('D:\Test\people.xls')  # 可指定路径
print('数据写入完成')

 结果：

读操作 

import xlrd

rbook = xlrd.open_workbook('people.xls')
sheet = rbook.sheet_by_name('人员列表')
print(sheet.cell(0, 0).value)  # (0,0)
print(sheet.row_values(0))  # (0,i)
print(sheet.col_values(0))  # (i,0)
print(sheet.nrows)  # 行总数
print(sheet.ncols)  # 列总数
# 读所有行
for i in range(sheet.nrows):
    print(sheet.row_values(i))
# 读所有列
for i in range(sheet.ncols):
    print(sheet.col_values(i))

结果：

 更新操作

import xlrd
from xlutils.copy import copy

ubook = xlrd.open_workbook('people.xls')
cbook = copy(ubook)  # 备份
# 通过索引获取 sheet 页
sheet = cbook.get_sheet(0)  # 第一页
sheet.write(2, 1, 88)
cbook.save('D:\Test\peoplebf.xls')  # 新表格
cbook.save('people.xls')   # 原来的

import xlrd
import xlutils.copy

ubook = xlrd.open_workbook('people.xls')
cbook = xlutils.copy.copy(ubook)  # 备份
# 通过索引获取 sheet 页
sheet = cbook.get_sheet(0)  # 第一页
sheet.write(2, 1, 88)
cbook.save('D:\Test\peoplebf.xls')  # 新表格
cbook.save('people.xls')  # 原来的

结果：