Laravel读取Excel 和CSV

1、下载依赖：https://github.com/PHPOffice/PHPExcel ；

　　或使用命令安装：

composer require phpoffice/phpexcel 

2、下载完成后，将PHPExcel文件夹放在laravel的vendor目录下；

　　更改支持PHP7.3语法：

　　将continue改成break

 

3、在你需要使用的控制器引用：

use PHPExcel_IOFactory;
use PHPExcel;

4、读取Excel：

封装（为了防止Class 'PHPExcel_IOFactory' not found 报错，以下使用上述两种引入）：

    // $excel_file是excel文件的绝对路径；$start_row是起始行，默认2；$end_row是截至行，默认最大行，
    // $end_line是截至列：如F,则取A-E列，默认取到E列;  $column_name 是字段列名，数组;$is_del 是否将文件删除
    public static function excel_to_array($excel_file=null, $start_row = null, $end_row = null, $end_line = null, $column_name = [], $is_del = null){
        $test_data = [
            'excel_file'=> $excel_file,
            'start_row'=> $start_row,
            'end_row'=> $end_row,
            'end_line'=> $end_line,
        ];
        if (!$excel_file){
            $state = 0;
            $msg = 'Excel路径参数不全';
            $content = [];
        }else{
            //加载excel文件
            $filename = $excel_file;
            $objPHPExcelReader = PHPExcel_IOFactory::load($filename);
            $sheet = $objPHPExcelReader->getSheet(0); // 读取第一个工作表(编号从 0 开始，sheet)
            if (!$end_row){
                $highestRow = $sheet->getHighestRow(); // 取得最大行数
            }else{
                $highestRow = $end_row;
            }
            $highestColumn = $sheet->getHighestColumn(); // 取得最大列数
            if ($highestColumn > 100001){
                $state = 0;
                $msg = '最大行数100001行，如有超过，请切割文件';
                $content = [];
            }else{
                $arr = ['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']; // 目标列标号
                if (!$start_row){ // 默认从第二行读
                    $start_row = 2;
                }
                if (!$end_line){ // 默认取到E列
                    $end_line = 'F';
                }
                $res_arr = [];
                for ($row = $start_row; $row <= $highestRow; $row++) { // 一次读取一行
                    $row_arr = [];
                    for ($column = 0; $arr[$column] != $end_line; $column++) {
                        $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                        $row_arr[] = $val;
                    }
                    $res_arr[] = $row_arr;
                }
                $data_list = [];
                if(!empty($column_name)){
                    foreach ($res_arr as $key => $value) {
                        $data_lists = [];
                        foreach ($value as $k => $v) {
                            $data_lists[$column_name[$k]] = $v;
                        }
                        $data_list[] = $data_lists;
                    }
                }
                $state = 1;
                $msg = '读取完成';
                $content = $data_list;
                //读取完后删除文件；
                if(!empty($is_del)){
                    if(file_exists($filename)){                            //判断文件存在
                          unlink($filename);     //删除
                    }
                }
            }
        }
        return [
            'state'=> $state,
            'msg'=> $msg,
            'test_data'=> $test_data,
            'content'=> $content,
        ];
    }

5、读取Excel和CSV：

    // $excel_file是excel文件的绝对路径；$start_row是起始行，默认2；$end_row是截至行，默认最大行，
    // $end_line是截至列：如F,则取A-F列，默认取最大列;  $column_name 是字段列名，数组;$is_del 是否将文件删除
    public static function excel_to_array($excel_file=null, $start_row = null, $end_row = null, $end_line = null, $column_name = [], $is_del = null){
        $test_data = [
            'excel_file'=> $excel_file,
            'start_row'=> $start_row,
            'end_row'=> $end_row,
            'end_line'=> $end_line,
        ];
        if (!file_exists($excel_file)){
            $state = 0;
            $msg = '找不到文件！';
            $content = [];
        }else{
            //加载文件
            $filename = $excel_file;
            $type = strtolower(pathinfo($filename, PATHINFO_EXTENSION));
            //根据不同类型分别操作
            if ($type == 'xlsx' || $type == 'xls') {
                $objPHPExcelReader = PHPExcel_IOFactory::load($filename);
                $read_ok = 1;
            }elseif($type == 'csv'){
                //不设置将导致中文列内容返回boolean(false)或乱码
                $objReader = PHPExcel_IOFactory::createReader('CSV')->setDelimiter(',')->setInputEncoding('GBK');
                $objPHPExcelReader = $objReader->load($filename);
                $read_ok = 1;
            }else{
                $state = 0;
                $msg = '文件格式不正确！';
                $content = [];
                $read_ok = 0;
            }
            if(!empty($read_ok)){
                $sheet = $objPHPExcelReader->getSheet(0); // 读取第一个工作表(编号从 0 开始，sheet)
                if (!$end_row){
                    $highestRow = $sheet->getHighestRow(); // 取得最大行数
                }else{
                    $highestRow = $end_row;
                }
                $highestColumn = $sheet->getHighestColumn(); // 取得最大列数
                if ($highestColumn > 100001){
                    $state = 0;
                    $msg = '最大列数100001列，如有超过，请切割文件';
                    $content = [];
                }else{
                    if (!$start_row){ // 默认从第二行读
                        $start_row = 2;
                    }
                    if (!$end_line){ // 默认取最大列
                        $end_line = $highestColumn;
                    }

                    //取得字段，如果没有指定字段，将在表格中的第一行为数据的字段，因此先取出用来作后面数组的键名
                    $filed = [];
                    if(!empty($column_name)){
                        $filed = $column_name;
                    }else{
                        for ($column = 'A'; $column <= $end_line; $column ++) {
                            $cellVal = $sheet->getCell($column . 1)->getValue();//取得列内容
                            $filed[] = $cellVal;
                        }
                    }
                    //开始取出数据并存入数组
                    $data = [];
                    for ($i = $start_row; $i <= $highestRow; $i++) {//ignore row 1
                        $row = [];
                        $e = 0;
                        for ($column = 'A'; $column <= $end_line; $column ++) {
                            $cellVal = $sheet->getCell($column . $i)->getValue();
                            $row[$filed[$e]] = $cellVal;
                            $e = $e + 1;
                        }
                        $data[] = $row;
                    }
                    
                    $state = 1;
                    $msg = '读取完成';
                    $content = $data;
                    //读取完后删除文件；
                    if(!empty($is_del)){
                        if(file_exists($filename)){                            //判断文件存在
                              unlink($filename);     //删除
                        }
                    }
                }
            }

        }
        return [
            'state'=> $state,
            'msg'=> $msg,
            'test_data'=> $test_data,
            'content'=> $content,
        ];
    }

 

 

调用excel_to_array($excel_file)即可：

 

 

 

 

结果如：