Python读写Excel之xlrd和xlwt

Python有很强大的Excel读写能力,例如xlrd和xlwt、openpyxl、pandas进行excel的读写,今天主要总结Python通过xlrd和xlwt进行Excel读写

首先安装xlrd和xlwt两个库,命令是:

1
2
3
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)

代码整体示例如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# -*- 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')

代码整体示例如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# -*- 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完成!')
-------------本文结束感谢您的阅读-------------