python使用xlrd、xlwt、openpyxl读写（大）Excel文件

2024-06-03 02:18| 来源: 网络整理| 查看: 265

文章主要内容

python中使用xlrd、xlwt库读写excel（xls）文件python中使用openpyxl库读写excel（xlsx）文件（一般针对大文件，本例中使用11w行Excel数据）

前期准备：

安装xlrd、xlwt、openpyxl库(这里使用pip安装方法) UP使用的是win10、 pycharm、python3.4（python 3.X版本基本都一样）如果你已经将python加入到环境变量中，直接在cmd中运行 pip install xlrd

即可（xlwt的安装同理）如果没有添加path路径的话：在自己的电脑里面找到python3.4的安装位置找到pip.exe pip安装路径将刚才的路径复制一遍，并在后面加上pip indstall xlrd来实现xlrd库的安装(xlwt、openpyxl的安装同理[pip install xlwt和pip install openpyxl]) cmd安装xlrd库

有关Excel 2007版之前的excel文件后缀为.xls，最大支持65535行数据，xlrd和xlwt主要应用于07版之前的Excel文件。xlrd可以用来读取.xls和.xlsx文件， xlwt写文件只支持.xls，所以对数据的大小有限制 2017版之后的excel文件后缀为.xlsx, 最大支持1048576行，使用openpyxl来弥补xlwt的缺陷来处理大文件。一、使用xlrd、xlwt库读写Excel xlrd和xlwt处理的是.xls文件，单个sheet最大行数是65535，如果有更大需要的，建议使用openpyxl函数，最大行数达到1048576。在使用xlrd和xlwt处理的.xlsx文件如果数据量超过65535行时，就会遇到：ValueError: row index was 65536, not allowed by .xls formatxlrd读取excel文件代码 # -*-coding=utf-8 -*- """ name:maple183 date:2019.1.16 use:to read excel file """ import xlrd # 打开一个workbook（workbook相当于Excel文件， .xls和.xlsx都可以读取） workbook = xlrd.open_workbook('C:\\Users\\D-STONE\\Desktop\\test.xlsx') # 抓取所有sheet页的名称（以列表变量worksheets存储sheet名） worksheets = workbook.sheet_names() print('worksheets is {}'.format(worksheets)) # 定位到sheet1 worksheet1 = workbook.sheet_by_name(u'Sheet1') # 遍历sheet1中所有行row num_rows = worksheet1.nrows for curr_row in range(num_rows): row = worksheet1.row_values(curr_row) print('row{} is {}'.format(curr_row, row)) # 遍历sheet1中所有列col num_cols = worksheet1.ncols for curr_col in range(num_cols): col = worksheet1.col_values(curr_col) print('col{} is {}'.format(curr_col, col)) # 遍历sheet1中所有单元格cell for rown in range(num_rows): for coln in range(num_cols): cell = worksheet1.cell_value(rown, coln) print(cell) """ #通过索引顺序获取 worksheet1 = workbook.sheets()[0] #或 worksheet1 = workbook.sheet_by_index(0) """ """ #遍历所有sheet对象 for worksheet_name in worksheets: worksheet = workbook.sheet_by_name(worksheet_name) """ 使用 xlrd读取excel文件代码，配合xlwt写入到新的.xls文件中（注意：只能写入.xls文件，所以造成了写入文件65535行的限制） # -*-coding=utf-8-*- import xlrd import xlwt """ name:maple183 date:2019.1.16 use:to read excel file """ # 打开一个workbook(要读取的Excel文件的workbook和sheet) workbook = xlrd.open_workbook('C:\\Users\\D-STONE\\Desktop\\test.xlsx') # 抓取所有sheet页的名称 worksheets = workbook.sheet_names() print('worksheets is {}'.format(worksheets)) # 定位到sheet1 worksheet1 = workbook.sheet_by_name(u'Sheet1') # 创建workbook和sheet对象(写入数据的workbook和sheet) workbook = xlwt.Workbook() # 注意Workbook的开头W要大写 sheet2 = workbook.add_sheet('sheet2', cell_overwrite_ok=True) # 遍历sheet1中所有行row num_rows = worksheet1.nrows for curr_row in range(num_rows): row = worksheet1.row_values(curr_row) print(row) if row[2] > 0: # 可以对每行数据进行数学处理，参考下方写入数据的格式，将结果存入新的excel中 sheet2.write(curr_row, 5, 10) # 在第curr_row行、第5栏写入数据10 else: sheet2.write(curr_row, 6, "/") # 在第curr_row行、第6栏写入字符'/' workbook.save('C:\\Users\\D-STONE\\Desktop\\test_2.xls') print("创建excel文件完成！") 二、使用openpyxl库读写大型Excel文件

虽然xlrd可以处理.xlsx文件，但是xlwt不能写.xlsx文件，这就造成了超过65535行的.xlsx文件没法处理的情况。我们使用openpyxl库来处理大型的.xlsx文件。但是在实际操作中会发现对于大型.xlsx文件openpyxl的读取速度非常的慢(测试了一个10W行数据的.xlsx文件，平均1K在1分多钟，数据处理前期可能要快一点，后面是越来越慢…) 测试代码如下：（单个1K文件速度还可以；5K文件大概在17s左右）

import openpyxl import math import time """ name:maple183 date:2019.1.16 use:to read excel file """ start = time.time() count = 0 # 打开一个工作簿 wb = openpyxl.load_workbook('C:\\Users\\D-STONE\\Desktop\\test.xlsx') # 获取所有表名 sheet_names = wb.sheetnames # 得到工作簿的所有工作表名（列表形式）结果形式： ['Sheet1', 'Sheet2', 'Sheet3'] print(sheet_names) # 根据表名打开sheet表 sheet1 = wb[sheet_names[0]] # 打开第一个 sheet 工作表 # 获取sheet中单元格数据 sheet1_max_colum = sheet1.max_column # 获取最大列数 print("最大列数：{}".format(sheet1_max_colum)) sheet1_max_row = sheet1.max_row # 获取最大行数 print("最大行数：{}".format(sheet1_max_row)) # 按行遍历目标Excel并按要求写入数据 list_sheet1_row = [] for j in range(sheet1_max_row): for i in sheet1[j + 1]: list_sheet1_row.append(i.value) print(list_sheet1_row) # 写入数据 sheet1.cell(row=j + 1, column=6, value=round(3.1415, 3)) # 修改第j行第n列的单元格的值为num # 清空列表缓存数据 list_sheet1_row = [] count = count + 1 print("文件读取进度：{0}/{1}".format(count, sheet1_max_row)) wb.save('C:\\Users\\D-STONE\\Desktop\\test_1.xlsx') print("文件创建成功！") end = time.time() print("程序运行时间为：{}".format(end - start)) 三、使用xlrd并配合openpyxl库读写大型Excel文件（解决运行速度问题）

找了相关资料发现openpyxl处理excel速度慢主要是因为读取的速度慢，那么考虑将xlrd和openpyxl配合使用，就是使用xlrd来读取.xlsx文件，openpyxl写入.xlsx文件来解决程序运行时间长的问题测试10W行数据，程序运行时间为27s

# -*-coding=utf-8-*- import xlrd import openpyxl """ name:maple183 date:2019.1.16 use:to read excel file """ start = time.time() # 将要读取的.xlsx文件以列表的形式存入source_xls[] source_xls = [(r'C:\\Users\\D-STONE\\Desktop\\test1.xlsx'), (r'C:\\Users\\D-STONE\\Desktop\\test2.xlsx')] # 也可以在source_xls列表里面只放一个表格：source_xls = [(r'C:\\Users\\D-STONE\\Desktop\\test1.xlsx')] data = [] for i in source_xls: # 遍历source_xls[] wb = xlrd.open_workbook(i) # 打开workbook for sheet in wb.sheets(): # 遍历上面打开的workbook里面的sheet for rownum in range(sheet.nrows): # 遍历上面sheet里面的行数据 data.append(sheet.row_values(rownum)) # 将每行数据存入data列表中 print(data) # 在此获得data[],可以调用函数对里面的数据进行处理，并获得新的列表数据，由于下面部分的数据写入 wk = openpyxl.Workbook() wkts = wk.active # 按行、列分别写入数据 for i in range(len(data)): for j in range(len(data[i])): wkts.cell(i+1, j+1, data[i][j]) wk.save(r'C:\\Users\\D-STONE\\Desktop\\test_3.xlsx') print("文件创建成功！") end = time.time() print("程序运行时间为：{}".format(end - start))

欢迎交流学习，邮箱：[email protected]

【本文地址】

公司简介

联系我们