【心得】TP6，使用phpspreadsheet库进行EXCEL的数据导入导出

在日常开发中，我们会遇到大批量的数据导出以及导入，之前的PHP旧库现在已经停更了，如下提示：

composer require phpoffice/phpexcel

Package phpoffice/phpexcel is abandoned, you should avoid using it. Use phpoffice/phpspreadsheet instead.

phpoffice/phpexcel包已废弃，应避免使用。请改用phpoffice/phpspreadsheet。

所以我们应该按照提示进行操作，在项目的目录下，也就是vendor的上一级目录下执行：

composer require phpoffice/phpspreadsheet

等待安装完成后即刻进行在所需要使用的控制中引入类以及在extend目录下新建Excel.php文件： 下载EXCEL

<?php
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;
class Excel
{
    //execl模板下载
    public function template_download($studentList = '')
    {
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle('销售号码池');  //设置当前sheet的标题

        //设置宽度为true,不然太窄了
        $newExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);

        //设置第一栏的标题
        $objSheet->setCellValue('A1', 'ID')
            ->setCellValue('B1', '企业名称')
            ->setCellValue('C1', '企业类型')
            ->setCellValue('D1', '归属地')
            ->setCellValue('E1', '客户姓名')
            ->setCellValue('F1', '手机号码')
            ->setCellValue('G1', '企业编号')
            ->setCellValue('H1', '公司地址');

        if($studentList === '')
        {
            //默认数据
            $explame_data_list = array(
                array(
                    's_nid' => '1',
                    's_name' => '小明',
                    's_number' => '18866886688',
                    's_area' => '中国',
                    's_companyName' => '文化有限有限公司',
                    's_companyType' => '公司/个体',
                    's_companyCode' => 'MA88888888888',
                    's_address' => '地球村'
                ),
            );
        }
        else
        {
            $explame_data_list = $studentList;
        }

        $baseRow = 2; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
        foreach ($explame_data_list as $k => $val) {
            $i = $k + $baseRow;
            $objSheet->setCellValue('A' . $i, $val['s_nid'])
                ->setCellValue('B' . $i, $val['s_companyName'])
                ->setCellValue('C' . $i, $val['s_companyType'])
                ->setCellValue('D' . $i, $val['s_area'])
                ->setCellValue('E' . $i, $val['s_name'])
                ->setCellValue('F' . $i, $val['s_number'])
                ->setCellValue('G' . $i, $val['s_companyCode'])
                ->setCellValue('H' . $i, $val['s_address']);
        }

        $this->downloadExcel($newExcel, '客户电话资料', 'Xls');
    }

    //公共文件，用来传入xls并下载
    private function downloadExcel($newExcel, $filename, $format)
    {
        // $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }

        header("Content-Disposition: attachment;filename="
            . $filename . date('Y-m-d') . '.' . strtolower($format));
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($newExcel, $format);

        $objWriter->save('php://output');

        //通过php保存在本地的时候需要用到
        //$objWriter->save($dir.'/demo.xlsx');

        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
        //header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        //header('Pragma: public'); // HTTP/1.0
        exit;
    }

}

上传导入Excel

在需要用到的控制器中只需要引入类库以及excel.php

<?php

namespace app\controller;
use Excel;
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;

public function uploadExcelNumber()
    {
        if(!Request()->isPost())
        {
            return View::fetch('Numberhub/uploadExcel');
        }

        header("content-type:text/html;charset=utf-8");

        $isFile = $_FILES;
        if($isFile['file']['name'] === '')
        {
            return json(['code'=>201,'msg'=>'请上传要导入的文件']);
        }

        $file = request()->file();

        try {
            validate(['file'=>'fileSize:10240000|fileExt:xls'])->check($file);

            $file = request()->file('file');
            // 上传到本地服务器
            $savename = Filesystem::disk('public')->putFile( 'topic', $file);
            //获取文件路径
            $filePath = app()->getRootPath() . 'public/storage' . DIRECTORY_SEPARATOR .  $savename;

            // 有Xls和Xlsx格式两种
            $reader = IOFactory::createReader('Xls');
        } catch (\think\exception\ValidateException $e) {
            return json(['code'=>201,'msg'=>$e->getMessage()]);
        }

        //载入excel文件
        $excel = $reader->load($filePath);

        //读取第一张表
        $sheet = $excel->getSheet(0);
        //获取总行数
        $row_num = $sheet->getHighestRow();
        //获取总列数
        //$col_num = $sheet->getHighestColumn();

        $import_data = []; //数组形式获取表格数据
        for ($i = 2; $i <= $row_num; $i++) {
            $arr = [
                's_companyName'  => $excel->getActiveSheet()->getCell("B" . $i)->getValue(),
                's_companyType'  => $excel->getActiveSheet()->getCell("C" . $i)->getValue(),
                's_area' => $excel->getActiveSheet()->getCell("D" . $i)->getValue(),
                's_name'=> $excel->getActiveSheet()->getCell("E" . $i)->getValue(),
                's_date' => Date('Y-m-d'),
                's_number' => $excel->getActiveSheet()->getCell("F" . $i)->getValue(),
                's_companyCode' => $excel->getActiveSheet()->getCell("G" . $i)->getValue(),
                's_address' => $excel->getActiveSheet()->getCell("H" . $i)->getValue()
            ];

            array_push($import_data,$arr);

        }

        if (empty($import_data)) {
            return json(['status' => '1', 'message' => '数据解析失败']);
        }

        //将数据保存到数据库
        $numberInfo = new Numberpool;

        for($v = 0;$v<count($import_data);$v++)
        {
            if($import_data[$v]['s_companyName'] === null)
            {
                $import_data[$v]['s_companyName'] = '';
            }

            if($import_data[$v]['s_companyCode'] === null)
            {
                $import_data[$v]['s_companyCode'] = '';
            }

            if($import_data[$v]['s_address'] === null)
            {
                $import_data[$v]['s_address'] = '';
            }

            $numberInfo->create($import_data[$v]);
        }

        unlink($filePath);
        return json(['code'=>200,'msg'=>'导入成功']);

    }

以上就是在TP6中使用phpspreadsheet库进行EXCEL的数据导入导出，后续所需要的优化可根据自己的需求进行处理，比如上传去重等。 但是友情提醒一点是，如果批量导入的数据比较大，需要进行分段导入，使用ajax分段或者使用缓存技术，将数据慢慢导入避免超时及压力太大造成系统崩溃。