[PHP知识库]Excel大文件读取 Laravel

情景

• 正常读取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;
        }
    }
}

创作挑战赛

新人创作奖励来咯，坚持创作打卡瓜分现金大奖