python3 之 使用xlrd和xlwt模块对excel数据进行读取写入操作

python3 之 使用xlrd和xlwt模块对excel数据进行读取写入
一、什么是xlrd模块和xlwt模块
二、如何安装xlrd模块和xlwt模块
三、excel表格准备
四、对excel表进行读写操作
1、使用xlrd模块读取excel文件
1.1 、遇到问题：excel表中是日期格式的单元格，输出的是浮点数
1.2、如何解决
1.2.1、方式一：使用xlrd 的 ==xldate_as_datetime== 来处理
1.2.2、方式二：使用xlrd 的 ==xldate_as_tuple== 来处理
2、使用xlwt模块向excel文件中写入数据
一、什么是xlrd模块和xlwt模块
xlrd模块和xlwt模块，其实是python的第三方工具包。要想使用它，首先我们需要先安装这2个模块。
xlrd模块：用于读取excel表中的数据。
xlwt模块：用户将数据写入excel表中。

二、如何安装xlrd模块和xlwt模块

pip install xlrd
pip install xlwt

 

 

三、excel表格准备

 

四、对excel表进行读写操作

1、使用xlrd模块读取excel文件

首先需要先导入xlrd模块：

def read_excel(excel_path, sheet_name):
# 首先打开excel表，formatting_info=True 代表保留excel原来的格式
xls = xlrd.open_workbook(excel_path, formatting_info=True)
# 通过sheet的名称获得sheet对象
sheet = xls.sheet_by_name(sheet_name)
# 通过sheet的索引去获得sheet对象
# sheet =xls.sheet_by_index(0)
# 定义一个空的列表，用于读取后存入数据
datalist = []
for rows in range(1, sheet.nrows): # 从第2行开始循环去读
# 获取整行的内容
# print(sheet.row_values(rows))
# 定义一个暂存列表
temptlist = []
for cols in range(0, sheet.ncols-2): # 从第1列循环去读取列，读到倒数第3列，倒数2列，分别是用于写入测试时间、测试结果
if cols == 0:
temptlist.append(rows) # 判断如果是第1列，则直接存入行数
else:
temptlist.append(sheet.cell_value(rows, cols)) # 否则 获取单元格内容
datalist.append(temptlist) # 把每一次循环读完一行的所有列之后，将数据追加到datalist列表中
return datalist

if __name__ == "__main__":
print(read_excel("data/test_data.xls", "查询车票"))

输入结果如下：

 

1.1 、遇到问题：excel表中是日期格式的单元格，输出的是浮点数
那么对于excel表中是日期格式的，我们需要进行特殊处理。

1.2、如何解决
首先需要先判断当前单元格是否为date格式，如果是的话，则进行时间格式处理后，再存入列表中。

那么如何去判断单元格的类型呢？
python读取excel中单元格的内容返回的有5种类型:

ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

步骤2：
有2种处理方式。

1.2.1、方式一：使用xlrd 的 xldate_as_datetime 来处理

程序修改之后，变为如下：

import xlrd

def read_excel(excel_path, sheet_name):
# 首先打开excel表，formatting_info=True 代表保留excel原来的格式
xls = xlrd.open_workbook(excel_path, formatting_info=True)
# 通过sheet的名称获得sheet对象
sheet = xls.sheet_by_name(sheet_name)
# 定义一个空的列表，用于读取后存入数据
datalist = []
for rows in range(1, sheet.nrows): # 从第2行开始循环去读
temptlist = []
for cols in range(0, sheet.ncols-2): # 从第1列循环去读取列，读到倒数第3列，倒数2列，分别是用于写入测试时间、测试结果
if cols == 0:
temptlist.append(rows) # 判断如果是第1列，则直接存入行数
elif sheet.cell(rows, cols).ctype == 3: # 判断单元格是否为date格式
val = sheet.cell_value(rows, cols)
date_tmp = xlrd.xldate_as_datetime(val, xls.datemode).strftime("%Y-%m-%d")
temptlist.append(date_tmp)
else:
temptlist.append(sheet.cell_value(rows, cols))
datalist.append(temptlist)
return datalist

if __name__ == "__main__":
print(read_excel("data/test_data.xls", "查询车票"))

输出结果：

 

1.2.2、方式二：使用xlrd 的 xldate_as_tuple 来处理

程序修改之后，变为如下：

import xlrd
from datetime import date

def read_excel(excel_path, sheet_name):
# 首先打开excel表，formatting_info=True 代表保留excel原来的格式
xls = xlrd.open_workbook(excel_path, formatting_info=True)
# 通过sheet的名称获得sheet对象
sheet = xls.sheet_by_name(sheet_name)
# 定义一个空的列表，用于读取后存入数据
datalist = []
for rows in range(1, sheet.nrows): # 从第2行开始循环去读
temptlist = []
for cols in range(0, sheet.ncols-2): # 从第1列循环去读取列，读到倒数第3列，倒数2列，分别是用于写入测试时间、测试结果
if cols == 0:
temptlist.append(rows) # 判断如果是第1列，则直接存入行数
elif sheet.cell(rows, cols).ctype == 3: # 判断单元格是否为date格式
val = sheet.cell_value(rows, cols)
date_value = xlrd.xldate_as_tuple(val, xls.datemode)
date_tmp = date(*date_value[:3]).strftime('%Y-%m-%d')
temptlist.append(date_tmp)
else:
temptlist.append(sheet.cell_value(rows, cols))
datalist.append(temptlist)
return datalist

if __name__ == "__main__":
print(read_excel("data/test_data.xls", "查询车票"))

 

2、使用xlwt模块向excel文件中写入数据

write_excel参数说明：

excel_path：为excel文件的路径；

sheet_name：excel文件中的sheet名称；

rows：第几行；

cols：第几列；

value：表示写入的内容；

import xlwt
import xlrd
import time
from xlutils.copy import copy

def write_excel(excel_path, sheet_name, rows, cols, value):
# 获取当前的系统时间，并格式化
current_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
# 打开excel, 保留原始格式
xls = xlrd.open_workbook(excel_path, formatting_info=True)
# 复制excel
# 需要先安装xlutils工具包`pip install xlutils`，才能导入copy模块，具体导入方式为`from xlutils.copy import copy`
xls_copy = copy(xls)
# 通过sheet名称获取sheet对象
sheet = xls_copy.get_sheet(sheet_name)
if value == "fail":
sheet.write(rows, cols, value, style=xlwt.easyxf('pattern: pattern solid, fore_colour %s;' % "red"))
elif value == "ignore":
sheet.write(rows, cols, value, style=xlwt.easyxf('pattern: pattern solid, fore_colour %s;' % "blue_gray"))
else:
sheet.write(rows, cols, value)
# 设置倒数第二列的宽度和赋值为当前时间
sheet.col(cols-1).width = 5000
sheet.write(rows, cols-1, current_time)
# 保存excel
xls_copy.save(excel_path)

if __name__ == "__main__":
write_excel("data/test_data.xls", "查询车票", 1, 6, "pass")
write_excel("data/test_data.xls", "查询车票", 2, 6, "fail")
write_excel("data/test_data.xls", "查询车票", 3, 6, "ignore")

这里说明，需要先安装xlutils工具包pip install xlutils，才能导入copy模块，具体导入方式为from xlutils.copy import copy。

更改后的excel为：