Excel大文件读取 Laravel

lbb1022

已于 2022-04-20 10:50:58 修改

阅读量1.4k

点赞数

分类专栏： laravel 文章标签： laravel php

于 2022-04-15 13:46:18 首次发布

本文链接：https://blog.csdn.net/qq_32550561/article/details/124190572

版权

laravel 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

本文介绍了如何通过BlockExcel类的readFromExcel方法，针对内存溢出问题实现分块读取Excel文件。首先确定需要的列，然后按行和列循环读取，仅存储关键数据，确保高效且避免全量数据加载。

摘要由CSDN通过智能技术生成

说明

如果有不能看明白的请留言，我会定时修改文档
类BlockExcel里的readFromExcel方法是分块读取只需要分块儿的可以只看这个方法

情景

正常读取excel内存溢出，所以采用此方法

思路说明

首先读取第一行因为第一行一般是表头。根据表头判断自己需要的列
类BlockExcel中getExcelFirstTable方法获取需要的列记做：ExlData
不要试图获取总行数和总列数这样的话相当于全文件读取一样会溢出
根据开始行数和结束行数使用for循环读取行- 循环行
嵌套循环行和列（ExlData）得到数据将数据合并为数组记做：数组A
如果数组A为空则这个文件读取完成
详细步骤说明会在代码里做标记注释

类与方法说明

类-ExcelFile-方法从这里开始执行


/**
 * 定时任务-读取excel
 */
namespace App\Console\Commands;

use App\Exceptions\BlockHandle;
use App\Exceptions\SaveUploadFile;
use App\Exceptions\SpreadExcelCsv;
use App\Models\YyImportRecord;
use App\Models\YySongImport;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Log;

class ExcelFile extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'excelfile';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'This is a auction excelfile process';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return void
     */
    public function handle()
    {
        Log::info('excelfile任务：'.date('Y-m-d H:i:s'));
        $this->readBlockExcel();
    }

    //分块读取文件
    private function readBlockExcel()
    {
        $item = YyImportRecord::where(['is_implement'=>2])->first();//优先查找正在导入中的
        if(!$item){
        	//没有导入中的就查找还未开始的
            $item = YyImportRecord::where(['is_implement'=>0])->first();
        }
        if($item){
            $keyname = 'YyImportRecord_'.$item->file_url_id;
            $rowSize = 18000;//用我的windows测试最多每次读18000行
            $endRowPre = cache()->store('redis')->get($keyname);//存储的是上一个页数的最后行数
            if($endRowPre){
                $endRowPre = intval($endRowPre);
                $startRow = $endRowPre + 1;
                $endRow = $endRowPre + $rowSize;
            }else{
                $startRow = 2;//从第二行开始读取
                $endRow = $rowSize;
            }
            BlockHandle::readExcel($item,$startRow,$endRow);
        }
        unset($item); 
}

类-BlockHandle-在这里做数据判断

namespace App\Exceptions;
use App\Exceptions\BlockExcel;
use App\Models\YyFileUrl;
use App\Models\YyImportRecord;
use App\Models\YySongImport;
use Illuminate\Support\Facades\Log;
use PhpOffice\PhpSpreadsheet\IOFactory;

class BlockHandle{

    public static function readExcel($item, $startRow, $endRow){
        $str_msg = '季度报表信息';
        $file_url_id =intval($item->file_url_id);
        $info = YyFileUrl::find($file_url_id);
        if(empty($info)){
            $error_str = $str_msg.'录入：'."文件信息不存在";
            SaveUploadFile::message($error_str);
            exit();
        }
        $file_name = env('COMMON_FILE_ADDRESS').$info->file_url;
        if(!file_exists($file_name))
        {
            $error_str = $str_msg.'录入：'."文件".$file_name."不存在";
            SaveUploadFile::message($error_str);
            exit();
        }

        $exl = new BlockExcel();
        //第一次读取获取表头 表头信息存储在redis中
        $kyyname = 'TABLE_YyImportRecord_HEAD_'.$file_url_id;
        if($startRow==2){
            $tableHead = $exl->getExcelFirstTable($file_name,1,1);
            cache()->store('redis')->set($kyyname,$tableHead);
        }else{
            $tableHead = cache()->store('redis')->get($kyyname);
            if(!$tableHead){
                $tableHead = $exl->getExcelFirstTable($file_name,1,1);
                cache()->store('redis')->set($kyyname,$tableHead);
            }
        }
        //主要的分页读取在这里提现
        $excel_orders = $exl->readFromExcel($file_name,$tableHead, $startRow, $endRow);
        if(empty($excel_orders)) {
            //数据库更改为已录入
            YyImportRecord::where(['id'=>$item->id])->update(['is_implement'=>1]);
            SaveUploadFile::message($str_msg.'录入完成 ');
        }else{
            //数据库更改为录入中
            if($startRow==2){
                YyImportRecord::where(['id'=>$item->id])->update(['is_implement'=>2]);
                SaveUploadFile::message($str_msg.'录入中... ');
            }
            //存储当前进行到最后的行数 用作下一次读取
            $keyname = 'YyImportRecord_'.$file_url_id;
            cache()->store('redis')->set($keyname,$endRow);
        }
    }
}

类-BlockExcel-在这里分块（分页读取）

namespace App\Exceptions;
use App\Models\YySongCompany;
use Illuminate\Support\Facades\Log;
use PHPExcel_IOFactory;
class BlockExcel{
    /**
     * 分页读取excel转换成数组
     * @param string $excelFile 文件路径
     * @param array tableHead 记录我这个项目需要的列
     * @param int $startRow 开始读取的行数
     * @param int $endRow 结束读取的行数
     * @return array  最后返回数组是为了让`BlockHandle`中的`readExcel`方法判断文件是否读取完成
     */
    public function readFromExcel($excelFile,$tableHead, $startRow = 1, $endRow = 100) {
        $str_msg = '季度报表信息';
        $ExlData = $tableHead['ExlData'];
        $columns = $tableHead['columns'];

        Log::info('开始行：'.$startRow.'--结束行：'.$endRow);
        $excelType = PHPExcel_IOFactory::identify($excelFile);
        $excelReader = \PHPExcel_IOFactory::createReader($excelType);

        if(strtoupper($excelType) == 'CSV') {
            $excelReader->setInputEncoding('GBK');
        }

        if ($startRow && $endRow) {
            $excelFilter           = new PHPExcelReadFilter();
            $excelFilter->startRow = $startRow;
            $excelFilter->endRow   = $endRow;
            $excelReader->setReadFilter($excelFilter);
        }

        $phpexcel    = $excelReader->load($excelFile);
        $activeSheet = $phpexcel->getActiveSheet();

        $params = array(); //要存储到数据库的字段和值 记做：数组A
        for ($row = $startRow; $row <= $endRow; $row++) {
           $param = array();
            foreach ($ExlData as $letter=>$key){
                $value = $activeSheet->getCellByColumnAndRow($letter ,$row)->getValue();
                //此处可以有对数据的处理
                $param[] = $value;
            }
            if(isset($param[0]) && $param[0]){
                $params[] = $param;
            }
        }

       if(!empty($params)){
            $result = SaveUploadFile::addData($params,$columns);//录入数据到数据库
            if(!$result){
                //此处是录入数据库失败的处理
            }
        }
        $phpexcel->disconnectWorksheets();
        return $params;
    }
    /**
     * 读取excel转换成数组
     * @param string $excelFile 文件路径
     * @param int $highestColumnIndex 我要读到的最大的列
     * @return array
     */
    public function getExcelFirstTable($excelFile,$startRow = 1, $endRow = 1,$highestColumnIndex = 18) {
        $excelType = PHPExcel_IOFactory::identify($excelFile);
        $excelReader = \PHPExcel_IOFactory::createReader($excelType);

        if(strtoupper($excelType) == 'CSV') {
            $excelReader->setInputEncoding('GBK');
        }
        if ($startRow && $endRow) {
            $excelFilter           = new PHPExcelReadFilter();//PHPExcelReadFilter
            $excelFilter->startRow = $startRow;
            $excelFilter->endRow   = $endRow;
            $excelReader->setReadFilter($excelFilter);
        }
        $phpexcel    = $excelReader->load($excelFile);
        $activeSheet = $phpexcel->getActiveSheet();
        $nameArr = SaveUploadFile::getColumsTitle();//这个方法是我需要的列数组
        $nameArrKey = array_flip($nameArr); //反转数组 得到我需要的数据库字段
        $ExlData = array();
        $columns = array();
        $rowd = 1;
        for ($col = 0; $col < $highestColumnIndex; $col++) {
            $title = (string) $activeSheet->getCellByColumnAndRow($col, $rowd)->getValue(); //获取到的值
            
            //值在我要获取的范围内 则加入数组
            if($title && in_array($title,$nameArr)){
                $name = $nameArrKey[$title]; //得到在库里存的字段
                $ExlData[$col] = $name; //键是excel列标记字母 值是字段名字
                $columns[] = $name;//字段对应的行 方便录入数据库使用
            }
        }
        $phpexcel->disconnectWorksheets();

        $result = array(
            'ExlData'=>$ExlData,
            'columns'=>$columns,
        );
        return $result;
    }
}

类-SaveUploadFile-方便读代码给贴出

namespace App\Exceptions;

use App\Models\YyDatum;
use App\Models\YyFileUrl;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class SaveUploadFile
{
    /**
     * 获取要保存的表头的名字
     */
    public static function getColumsTitle(){
    //键：在库里存的字段 值：在excel中的表头名称
        $data = array(
            'seted' => '结算期间',
            'plorm' => '平台',
            'qreee' => '份额',
            'adare' => '接权',
            'sonus' => '付费状态',
            'enuag' => '收入分成-使用量',
            'baage' => '包月收入分成-使用量',
            'rffue' => '打榜收入',
            'aunre' => '广告收入分成',
            'baare' => '包月收入分成',
            'recfg' => '打榜收入分成',
            'cbome' => 'CP分成收入',
        );
        return $data;
    }
    /**
     * excel表列
     * @return array
     */
    public static function headExcel(){
        $arr=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q',
            'R','S','T','U','V','W','X', 'Y','Z','AA','AB','AC','AD','AE','AF','AG',
            'AH','AI','AJ');
        return $arr;
    }
    /**
     * 录入数据库信息
     * @param $params array 要添加到数据库的数据
     * @param $columns array  对应的数据库数据字段
     * @return mixed
     */
    public static function addData($params,$columns){
        DB::beginTransaction();
        try {
            $datumInstance = new YyDatum;
            $batchSize = count($params);

            $collect = collect($params);
            foreach ($collect->chunk($batchSize) as $chunk){
                $chunk_arr = json_decode(json_encode($chunk),true);
                \batch()->insert($datumInstance, $columns, $chunk_arr, $batchSize);
            }
            DB::commit();
            return true;
        } catch (\Exception $e) {
            Log::info('错误信息-addData：'.print_r($e->getMessage(),true));
            DB::rollBack();
            return false;
        }
    }
}