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完成！')