場(chǎng)景

今天需要合并天貓訂單數(shù)據(jù)，由于前期6.18活動(dòng)有很多數(shù)據(jù)需要處理，將幾個(gè)月份合并一起，結(jié)果報(bào)錯(cuò)。

問(wèn)題分析

Excel 文件的格式曾經(jīng)發(fā)生過(guò)一次變化，在 Excel 2007 以前，使用擴(kuò)展名為 .xls 格式的文件，這種文件格式是一種特定的二進(jìn)制格式，最多支持 65,536 行，256 列表格。從 Excel 2007 版開(kāi)始，默認(rèn)采用了基于 XML 的新的文件格式 .xlsx ，支持的表格行數(shù)達(dá)到了 1,048,576，列數(shù)達(dá)到了 16,384。需要注意的是，將 .xlsx 格式的文件轉(zhuǎn)換為 .xls 格式的文件時(shí)，65536 行和 256 列之后的數(shù)據(jù)都會(huì)被丟棄。

Pandas 讀取 Excel 文件的引擎是 xlrd ， xlrd 雖然同時(shí)支持 .xlsx 和 .xls 兩種文件格式，但是在源碼文件 xlrd/sheet.py 中限制了讀取的 Excel 文件行數(shù)必須小于 65536，列數(shù)必須小于 256。

if self.biff_version >= 80:
  self.utter_max_rows = 65536
else:
  self.utter_max_rows = 16384
self.utter_max_cols = 256

這就導(dǎo)致，即使是 .xlsx 格式的文件， xlrd 依然不支持讀取 65536 行以上的 Excel 文件（源碼中還有一個(gè)行數(shù)限制是 16384，這是因?yàn)?Excel 95 時(shí)代， xls 文件所支持的最大行數(shù)是 16384）。

解決辦法

openpyxl 是一個(gè)專門(mén)用來(lái)操作 .xlsx 格式文件的 Python 庫(kù)，和 xlrd 相比它對(duì)于最大行列數(shù)的支持和 .xlsx 文件所定義的最大行列數(shù)一致。

首先安裝 openpyxl ：

pip install openpyxl

Pandas 的 read_excel 方法中，有 engine 字段，可以指定所使用的處理 Excel 文件的引擎，填入 openpyxl ，再讀取文件就可以了。

import os
import pandas as pd

# 將文件讀取出來(lái)放一個(gè)列表里面

pwd = '1' # 獲取文件目錄

# 新建列表，存放文件名
file_list = []

# 新建列表存放每個(gè)文件數(shù)據(jù)(依次讀取多個(gè)相同結(jié)構(gòu)的Excel文件并創(chuàng)建DataFrame)
dfs = []

for root,dirs,files in os.walk(pwd): # 第一個(gè)為起始路徑，第二個(gè)為起始路徑下的文件夾，第三個(gè)是起始路徑下的文件。
  for file in files:
    file_path = os.path.join(root, file)
    file_list.append(file_path) # 使用os.path.join(dirpath, name)得到全路徑
    df = pd.read_excel(file_path) # 導(dǎo)入xlsx文件，將excel轉(zhuǎn)換成DataFrame
    dfs.append(df)

# 將多個(gè)DataFrame合并為一個(gè)
df = pd.concat(dfs)

# 數(shù)據(jù)輸出，寫(xiě)入excel文件，不包含索引數(shù)據(jù)
# 數(shù)據(jù)寫(xiě)入 Excel，需要首先安裝一個(gè) engine，由 engine 負(fù)責(zé)將數(shù)據(jù)寫(xiě)入 Excel，pandas 使用 openpyx 或 xlsxwriter 作為寫(xiě)入引擎。
df.to_excel('test\\1.xlsx', index=False,engine='openpyxl') # 導(dǎo)出 Excel，一般不需要索引，將 index 參數(shù)設(shè)為 False

補(bǔ)充知識(shí)：python使用xlrd讀取excel數(shù)據(jù)作為requests的請(qǐng)求參數(shù)，并把返回的數(shù)據(jù)寫(xiě)入excel中

實(shí)現(xiàn)功能：

從excel中的第一列數(shù)據(jù)作為post請(qǐng)求的數(shù)據(jù)，數(shù)據(jù)為json格式；把post返回的結(jié)果寫(xiě)入到excel的第二列數(shù)據(jù)中，并把返回?cái)?shù)據(jù)與excel中的預(yù)期結(jié)果做比較，如果與預(yù)期一致則在案例執(zhí)行結(jié)果中寫(xiě)入成功，否則寫(xiě)入失敗。

每一行的數(shù)據(jù)都不一樣，可實(shí)現(xiàn)循環(huán)調(diào)用

# !/usr/bin/env python
# -*- coding:utf-8 -*-
#import xlwt #這個(gè)專門(mén)用于寫(xiě)入excel的庫(kù)沒(méi)有用到
import xlrd
from xlutils.copy import copy
import requests
import json
old_excel = xlrd.open_workbook('excel.xls')
sheet = old_excel.sheets()[0]
url = 'http://10.1.1.32:1380/service/allocFk2'
headers = {'Content-Type': 'application/json'}
i = 0
new_excel = copy(old_excel)
for row in sheet.get_rows():
  data = row[0].value
  response = requests.post(url=url, headers=headers, data=data)
  text = response.text
  #使用json.loads可以把Unicode類型，即json類型轉(zhuǎn)換成dict類型
  text = json.loads(text)["returnMsg"] #屏蔽這行代碼即可把返回的完整數(shù)據(jù)寫(xiě)入文件中
  ws = new_excel.get_sheet(0)
  ws.write(i,1,text)
  new_excel.save('excel.xls')
  old_excel = xlrd.open_workbook('excel.xls')
  new_excel = copy(old_excel)
  i = i+1

執(zhí)行前的excel格式：

執(zhí)行后的結(jié)果：

調(diào)試過(guò)程中遇到的問(wèn)題：

1、一開(kāi)始在for循環(huán)的最后沒(méi)有增加這兩行代碼

old_excel = xlrd.open_workbook('excel.xls')

new_excel = copy(old_excel)

這樣的話new_excel永遠(yuǎn)都是一開(kāi)始獲取到的那一個(gè)，只會(huì)把最后一個(gè)循環(huán)返回的結(jié)果寫(xiě)入文件，因?yàn)橹暗娜慷急灰婚_(kāi)始獲取的那個(gè)old_excel給覆蓋了，所以每次執(zhí)行完寫(xiě)入操作以后都要重新做一次copy操作，這樣就能保證new_excel是最新的。

2、注意執(zhí)行程序之前要把excel關(guān)閉，否則會(huì)報(bào)錯(cuò)

以上這篇解決使用Pandas 讀取超過(guò)65536行的Excel文件問(wèn)題就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論