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模块:

    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)
        # 通过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
    

    所以我们只要判断单元格的类型是否等于3,如果等于3,那么就是date 类型,然后我们再进行时间格式处理,即可。

    步骤1

    # 判断单元格类型是否为date
    sheet.cell(rows,cols).ctype == 3
    

    步骤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为

    来源:简单快乐_wsh

    物联沃分享整理
    物联沃-IOTWORD物联网 » python3 之 使用xlrd和xlwt模块对excel数据进行读取写入操作

    发表评论