Scrapy爬取数据追加Excel遇文件损坏：最后行索引问题修复

安全阿岳

2026-8-4

问题分析与解决

问题描述

尝试获取Excel文件plate列的最后一行，将Scrapy爬取的数据追加到该行之后。Scrapy爬取功能正常，但运行时触发文件损坏错误，猜测是Excel写入逻辑错误导致。

错误代码片段

with pd.ExcelWriter('output_res.xlsx', mode='r+',if_sheet_exists='overlay') as writer:
    df_last=pd.DataFrame('output_res.xlsx')
    lastRow=df_last['plate'].iget(-1)
    df_output = pd.DataFrame(itemList)
    df_output.to_excel(writer, sheet_name='result', index=False, header=True,startrow=lastRow)

完整代码

import scrapy
from scrapy.crawler import CrawlerProcess
import pandas as pd

class plateScraper(scrapy.Spider):
    name = 'scrapePlate'
    allowed_domains = ['dvlaregistrations.dvla.gov.uk']

    def start_requests(self):
        df=pd.read_excel('data.xlsx')
        columnA_values=df['PLATE']
        for row in columnA_values:
            global  plate_num_xlsx
            plate_num_xlsx=row
            base_url =f"https://dvlaregistrations.dvla.gov.uk/search/results.html?search={plate_num_xlsx}&action=index&pricefrom=0&priceto=&prefixmatches=&currentmatches=&limitprefix=&limitcurrent=&limitauction=&searched=true&openoption=&language=en&prefix2=Search&super=&super_pricefrom=&super_priceto="
            url=base_url
            yield scrapy.Request(url)

    def parse(self, response):
        itemList=[]
        for row in response.css('div.resultsstrip'):
            plate = row.css('a::text').get()
            price = row.css('p::text').get()
            if plate_num_xlsx==plate.replace(" ","").strip():
                item= {"plate": plate.strip(), "price": price.strip()}
                itemList.append(item)
                yield  item
            else:
                item = {"plate": plate.strip(), "price": "-"}
                itemList.append(item)
                yield item

        with pd.ExcelWriter('output_res.xlsx', mode='r+',if_sheet_exists='overlay') as writer:
            df_last=pd.DataFrame('output_res.xlsx')
            lastRow=df_last['plate'].iget(-1)
            df_output = pd.DataFrame(itemList)
            df_output.to_excel(writer, sheet_name='result', index=False, header=True,startrow=lastRow)

process = CrawlerProcess()
process.crawl(plateScraper)
process.start()

报错信息

Traceback (most recent call last):
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\utils\defer.py", line 240, in iter_errback
    yield next(it)
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\utils\python.py", line 338, in __next__
    return next(self.data)
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\utils\python.py", line 338, in __next__
    return next(self.data)
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\core\spidermw.py", line 79, in process_sync
    for r in iterable:
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\spidermiddlewares\offsite.py", line 29, in <genexpr>
    return (r for r in result or () if self._filter(r, spider))
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\core\spidermw.py", line 79, in process_sync
    for r in iterable:
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\spidermiddlewares\referer.py", line 336, in <genexpr>
    return (self._set_referer(r, response) for r in result or ())
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\core\spidermw.py", line 79, in process_sync
    for r in iterable:
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\spidermiddlewares\urllength.py", line 28, in <genexpr>
    return (r for r in result or () if self._filter(r, spider))
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\core\spidermw.py", line 79, in process_sync
    for r in iterable:
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\spidermiddlewares\depth.py", line 32, in <genexpr>
    return (r for r in result or () if self._filter(r, response, spider))
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\scrapy\core\spidermw.py", line 79, in process_sync
    for r in iterable:
  File "C:\pythonPro\w_crawl\SimonDarak\scrpy_00.py", line 33, in parse
    with pd.ExcelWriter('output_res.xlsx', mode='a',if_sheet_exists='overlay') as writer:
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\excel\_openpyxl.py", line 73, in __init__
    self._book = load_workbook(self._handles.handle, **engine_kwargs)
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\reader\excel.py", line 317, in load_workbook
    reader.read()
  File "C:\Users\Admin\AppData\Local\Programs\Python310\lib\site-packages\openpyxl\reader\excel.py", line 282, in read
    self.read_worksheets()
  File "C:\Users\Admin\AppData\Local\Programs\Python310\lib\site-packages\openpyxl\reader\excel.py", line 228, in read_worksheets
    ws_parser.bind_all()
  File "C:\Users\Admin\AppData\Local\Programs\Python310\lib\site-packages\openpyxl\worksheet\_reader.py", line 448, in bind_all
    self.bind_cells()
  File "C:\Users\Admin\AppData\Local\Programs\Python310\lib\site-packages\openpyxl\worksheet\_reader.py", line 351, in bind_cells
    for idx, row in self.parser.parse():
  File "C:\Users\Admin\AppData\Local\Programs\Python310\lib\site-packages\openpyxl\worksheet\_reader.py", line 144, in parse
    for _, element in it:
  File "C:\Users\Admin\AppData\Local\Programs\Python310\lib\xml\etree\ElementTree.py", line 1255, in iterator
    data = source.read(16 * 1024)
  File "C:\Users\Admin\AppData\Local\Programs\Python310\lib\zipfile.py", line 925, in read
    data = self._read1(n)
  File "C:\Users\Admin\AppData\Local\Programs\Python310\lib\zipfile.py", line 1015, in _read1
    self._update_crc(data)
  File "C:\Users\Admin\AppData\Local\Programs\Python310\lib\zipfile.py", line 943, in _update_crc
    raise BadZipFile("Bad CRC-32 for file %r" % self.name)
zipfile.BadZipFile: Bad CRC-32 for file 'xl/worksheets/sheet1.xml'

Process finished with exit code -1

问题根源与解决方案

核心错误点

Excel读取方式错误：pd.DataFrame('output_res.xlsx')无法正确读取Excel文件，必须用pd.read_excel()方法。
起始行计算错误：iget(-1)是pandas过时方法，且返回的是plate列最后一行的值，不是行索引。需要用len(df_last)获取已有的行数，作为追加的起始行。
文件打开模式与冲突：Scrapy的parse方法会为每个请求调用一次，多次同时打开/写入Excel文件会导致文件损坏。建议统一收集数据后再写入，或使用Item Pipeline处理。

修正后的代码（统一收集数据后写入）

import scrapy
from scrapy.crawler import CrawlerProcess
import pandas as pd
from os.path import exists

# 全局变量存储所有爬取结果
all_items = []

class plateScraper(scrapy.Spider):
    name = 'scrapePlate'
    allowed_domains = ['dvlaregistrations.dvla.gov.uk']

    def start_requests(self):
        df = pd.read_excel('data.xlsx')
        columnA_values = df['PLATE']
        for row in columnA_values:
            base_url = f"https://dvlaregistrations.dvla.gov.uk/search/results.html?search={row}&action=index&pricefrom=0&priceto=&prefixmatches=&currentmatches=&limitprefix=&limitcurrent=&limitauction=&searched=true&openoption=&language=en&prefix2=Search&super=&super_pricefrom=&super_priceto="
            # 用meta传递plate编号，替代全局变量
            yield scrapy.Request(url=base_url, meta={'plate_num': row})

    def parse(self, response):
        plate_num_xlsx = response.meta['plate_num']
        for row in response.css('div.resultsstrip'):
            plate = row.css('a::text').get()
            price = row.css('p::text').get()
            if plate_num_xlsx == plate.replace(" ", "").strip():
                item = {"plate": plate.strip(), "price": price.strip()}
            else:
                item = {"plate": plate.strip(), "price": "-"}
            all_items.append(item)
            yield item

def write_to_excel():
    file_path = 'output_res.xlsx'
    df_output = pd.DataFrame(all_items)
    # 判断文件是否存在，决定是否写入表头
    if exists(file_path):
        df_last = pd.read_excel(file_path)
        start_row = len(df_last)
        with pd.ExcelWriter(file_path, mode='a', if_sheet_exists='overlay') as writer:
            df_output.to_excel(writer, sheet_name='result', index=False, header=False, startrow=start_row)
    else:
        df_output.to_excel(file_path, sheet_name='result', index=False)

if __name__ == "__main__":
    process = CrawlerProcess()
    process.crawl(plateScraper)
    process.start()
    # 爬取结束后统一写入Excel
    write_to_excel()

关键优化说明

用全局列表统一收集所有爬取结果，爬取完成后一次性写入Excel，避免多次文件操作导致的冲突。
判断文件是否存在，不存在则创建并写入表头；存在则获取已有行数，从下一行开始追加，且不重复写入表头。
用response.meta传递plate编号，替代全局变量，避免多请求下的变量污染。

内容的提问来源于stack exchange，提问作者xlmaster

Scrapy爬取数据追加Excel遇文件损坏：最后行索引问题修复

问题描述 #

错误代码片段 #

完整代码 #

报错信息 #

问题根源与解决方案 #

核心错误点 #

修正后的代码（统一收集数据后写入） #

关键优化说明 #

问题描述

错误代码片段

完整代码

报错信息

问题根源与解决方案

核心错误点

修正后的代码（统一收集数据后写入）

关键优化说明