Python有很强大的Excel读写能力，例如xlrd和xlwt、openpyxl、pandas进行excel的读写，今天主要总结Python通过xlrd和xlwt进行Excel读写
首先安装xlrd和xlwt两个库，命令是：
pip3 install xlrd
Pip3 install xlwt
xlrd
xlrd主要用来读取Excel文件
打开一个Excel文件
workbook = xlrd.open_workbook('Workbook1.xlsx')
获取所有sheet页的名字
print(workbook.sheet_names())
获取一个工作表（sheet）
# 通过sheet页的索引获取sheet表
workbook1 = data.sheets()[0]
# 通过sheet页的索引获取sheet表
workbook1 = data.sheet_by_index(0)
# 通过sheet页的名字获取sheet表
workbook1 = data.sheet_by_name('Sheet1')
获取sheet表的行数和列数
# 行
num_rows = workbook1.nrows
# 列
num_cols = workbook1.ncols
获取整行和整列的值
# 行
first_row = workbook1.row_values(0)
# 列
first_col = workbook1.col_values(0)
用循环获取整个表格内容
for curr_row in range(num_rows):
row_value = workbook1.row_values(curr_row)
print('row%s value is %s' %(curr_row, row_value))
for curr_col in range(num_cols):
col_value = workbook1.col_values(curr_col)
print('col%s value is %s' % (curr_col, col_value))
获取某个单元格的值
for row in range(num_rows):
for col in range(num_cols):
cell = workbook1.cell_value(row, col)
print(cell)
代码整体示例如下：
# -*- coding:UTF-8 -*-
import xlrd
#打开一个excel表
workbook = xlrd.open_workbook('Workbook1.xlsx')
#获取所有sheet页的名字
print(workbook.sheet_names())
#获取一个工作表
workbook1 = workbook.sheet_by_name(u'Sheet1')
num_rows = workbook1.nrows
# for curr_row in range(num_rows):
# row_value = workbook1.row_values(curr_row)
# print('row%s value is %s' %(curr_row, row_value))
num_cols = workbook1.ncols
# for curr_col in range(num_cols):
# col_value = workbook1.col_values(curr_col)
# print('col%s value is %s' % (curr_col, col_value))
for row in range(num_rows):
for col in range(num_cols):
cell = workbook1.cell_value(row, col)
print(cell)
xlwt
xlwt主要用来写入Excel文件
新建一个excel对象
workbook = xlwt.Workbook()
添加一个名为sheet1的表 cell_overwrite_ok设置为True 允许重写覆盖
sheet1 = workbook.add_sheet('sheet1', cell_overwrite_ok=True)
然后根据已有数据通过循环添加数据，最后保存创建的表
workbook.save('Workbook2.xls')
代码整体示例如下：
# -*- coding:UTF-8 -*-
import xlwt
# 创建workbook和sheet对象 注意Workbook的开头W要大写
workbook = xlwt.Workbook()
#添加一个名为sheet1的表
sheet1 = workbook.add_sheet('sheet1', cell_overwrite_ok=True)
title = ['姓名', '年龄', '性别', '分数']
stus = [['lili', 20, '女', 89.9], ['lucy', 21, '女', 90.9], ['make', 22, '男', 91.9], ['mary', 23, '男', 92.9]]
# 向表头写入数据
for i in range(len(title)):
sheet1.write(0, i, title[i])
#向sheet写入数据
for i in range(len(stus)):
for j in range(4):
sheet1.write(i + 1, j, stus[i][j])
#保存数据到‘Workbook2.xls’文件中
workbook.save('Workbook2.xls')
print('创建execel完成！')
