Yii使用PhpSpreadsHeet 生成 Excel 和导入 Excel 前后端分离版和未分离版

痛定思痛。 2023-02-15 12:45 93阅读 0赞

### Yii使用PhpSpreadsHeet 生成 Excel 和导入 Excel 前后端分离版和未分离版 ###

前提下载**PhpSpreadsHeet**依赖 ：

composer require phpoffice/phpspreadsheet

### 1.开始正题： ###

#### 1.1 这里使用这个数据库 检测工具表： ####

DROP TABLE IF EXISTS `test_tool`;
    CREATE TABLE `test_tool` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '检测工具id',
      `tool_number` varchar(50) NOT NULL COMMENT '工具编号',
      `tool_name` varchar(50) NOT NULL COMMENT '工具名称',
      `tool_brand` varchar(50) DEFAULT NULL COMMENT '工具品牌',
      `tool_class` varchar(50) NOT NULL COMMENT '工具分类',
      `tool_calibration_cycle` varchar(50) NOT NULL COMMENT '工具校验周期',
      `tool_status` varchar(50) NOT NULL COMMENT '工具状态',
      `tool_functions` text NOT NULL COMMENT '工具功能',
      `tool_scope` varchar(255) DEFAULT NULL COMMENT '工具适用范围',
      `purchase_time` datetime DEFAULT NULL COMMENT '购置日期',
      `supplier` varchar(50) DEFAULT NULL COMMENT '供应商',
      `storage_address` varchar(255) DEFAULT NULL COMMENT '存放地址',
      `tool_notes` text COMMENT '工具备注',
      `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
      `updated_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
      `tool_file` varchar(255) DEFAULT NULL COMMENT '工具图片地址',
      `create_by` int(11) DEFAULT NULL COMMENT '记录创建者user_id',
      `updated_by` int(11) DEFAULT NULL COMMENT '记录更新者user_id',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
    INSERT INTO `test_tool` VALUES ('1', '', '弛度观测仪', null, '1', '7', '1', '剪指甲', '全部', null, null, null, '使用方便', '2020-05-06 17:35:44', '2020-05-20 10:05:34', '/uploads/5ec4906e2e768_login_bg.jpg', null, null);
    INSERT INTO `test_tool` VALUES ('2', '', '电力测量工具', null, '1', '7', '1', '剪指甲', '全部', null, null, null, '使用方便', '2020-05-06 17:36:07', '2020-05-20 09:31:55', '', null, null);
    INSERT INTO `test_tool` VALUES ('3', '', '弧垂弛度观测仪', null, '1', '7', '1', '剪指甲', '全部', null, null, null, '使用方便', '2020-05-06 17:36:11', '2020-05-20 09:31:55', '', null, null);
    INSERT INTO `test_tool` VALUES ('4', '', '接地电阻测试仪', null, '1', '7', '1', '剪指甲', '全部', null, null, null, '使用方便', '2020-05-06 17:36:14', '2020-05-20 09:31:55', '', null, null);
    INSERT INTO `test_tool` VALUES ('5', '', '电磁式电压互感器', null, '1', '7', '1', '剪指甲', '全部', null, null, null, '使用方便', '2020-05-06 17:36:17', '2020-05-20 09:31:55', '', null, null);

#### 1.2 创建ExitController 这个控制器名字随便你们这么写。 ####

<?php
    
    
    namespace app\controllers;
    
    use app\models\TestTool;
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Style\Color;
    use PhpOffice\PhpSpreadsheet\Style\Fill;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    use yii\web\Controller;
    
    class ExitController extends  Controller
    { 
        /** * @throws \PhpOffice\PhpSpreadsheet\Exception * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception */
        public function actionSamples()
        { 
            $spreadsheet = new Spreadsheet();
            $worksheet = $spreadsheet->getActiveSheet();
            //图片
            $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            //设置工作表标题名称
            //$worksheet->setTitle('phpspreadsheet——检测工具');
            //表头 1
            $spreadsheet->getActiveSheet()->setTitle('检测工具');
            //设置单元格内容
            $k=1;
            $worksheet->setCellValue('A' . $k, 'id');
            $worksheet->setCellValue('B' . $k, '工具编号');
            $worksheet->setCellValue('C' . $k, '工具名称');
            $worksheet->setCellValue('D' . $k, '工具品牌');
            $worksheet->setCellValue('E' . $k, '工具分类');
            $worksheet->setCellValue('F' . $k, '工具校验周期');
            $worksheet->setCellValue('G' . $k, '工具状态');
            $worksheet->setCellValue('H' . $k, '工具功能');
            $worksheet->setCellValue('I' . $k, '工具适用范围');
            $worksheet->setCellValue('J' . $k, '购置日期');
            $worksheet->setCellValue('K' . $k, '供应商');
            $worksheet->setCellValue('L' . $k, '存放地址');
            $worksheet->setCellValue('M' . $k, '工具备注');
            $worksheet->setCellValue('N' . $k, '工具图片地址');
            // 设置个表格宽度
            $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
            $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(30);
            $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);
            $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(12);
            $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(12);
            $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(30);
            $styleArray = [
                'font' => [
                    'bold' => true,
                    'color'=>[
                        'argb'=>Color::COLOR_BLUE
                    ]
                ],
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                ],
            ];
            //背景颜色
            $worksheet->getStyle('A1:N1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFFF0000');// 设置背景色
            //设置单元格样式
            $worksheet->getStyle('A1:N1')->applyFromArray($styleArray)->getFont()->setSize(12);
            //访问的数据库
            $jzInfo = TestTool::find()->all();
            $len = count($jzInfo);
            $j = 0;
            for ($i=0; $i < $len; $i++) { 
                $j = $i + 2; //从表格第3行开始
                $worksheet->setCellValueByColumnAndRow(1, $j, $jzInfo[$i]['id']);
                $worksheet->setCellValueByColumnAndRow(2, $j, $jzInfo[$i]['tool_number']);
                $worksheet->setCellValueByColumnAndRow(3, $j, $jzInfo[$i]['tool_name']);
                $worksheet->setCellValueByColumnAndRow(4, $j, $jzInfo[$i]['tool_brand']);
                $worksheet->setCellValueByColumnAndRow(5, $j, $jzInfo[$i]['tool_class']);
                $worksheet->setCellValueByColumnAndRow(6, $j, $jzInfo[$i]['tool_calibration_cycle']);
                $worksheet->setCellValueByColumnAndRow(7, $j, $jzInfo[$i]['tool_status']);
                $worksheet->setCellValueByColumnAndRow(8, $j, $jzInfo[$i]['tool_functions']);
                $worksheet->setCellValueByColumnAndRow(9, $j, $jzInfo[$i]['tool_scope']);
                $worksheet->setCellValueByColumnAndRow(10, $j, $jzInfo[$i]['purchase_time']);
                $worksheet->setCellValueByColumnAndRow(11, $j, $jzInfo[$i]['supplier']);
                $worksheet->setCellValueByColumnAndRow(12, $j, $jzInfo[$i]['storage_address']);
                $worksheet->setCellValueByColumnAndRow(13, $j, $jzInfo[$i]['tool_notes']);
                $worksheet->setCellValueByColumnAndRow(14, $j, $jzInfo[$i]['tool_file']);
            }
            $styleArrayBody = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => '666666'],
                    ],
                ],
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                ],
            ];
             $total_jzInfo = $len + 1;
            //添加所有边框/居中
            $worksheet->getStyle('A1:N'.$total_jzInfo)->applyFromArray($styleArrayBody);
            //设置文件前缀
            $filename = date('Y-m-d', time()) . rand(1000, 9999);
            $filename = $filename.'无忧巡检工具列表.xlsx';
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="'.$filename.'"');
            header('Cache-Control: max-age=0');
            $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save('php://output');
        }
        public function actionImp()
        { 
            
        }
    }

#### 1.3访问url 我本机是具体看你们自己的: ####

http://localhost/yii-excal/basic/web/index.php?r=exit/samples

就会下载这个 **无忧巡检工具列表.xlsx**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmc3NA_size_16_color_FFFFFF_t_70]  
这里面的数据就是来自数据库，具体看我上面的代码。

#### 1.4 创建其他有导入和导出的页面方式。 ####

##### 1.4.1 创建Excel #####

<?php
    
    namespace app\common\tools;
    use PhpOffice\PhpSpreadsheet\Reader\Exception;
    use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
    use PhpOffice\PhpSpreadsheet\Reader\Xls;
    use PhpOffice\PhpSpreadsheet\IOFactory;
    use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Style\Color;
    use PhpOffice\PhpSpreadsheet\Style\Fill;
    
    class Excel
    { 
    
        # 导入excel
        static function importExecl($file = '', $sheet = 0, $columnCnt = 0, &$options = [])
        { 
            try { 
                /* 转码 */
                $file = iconv("utf-8", "gb2312", $file);
    
                if (empty($file) OR !file_exists($file)) { 
                    throw new \Exception('文件不存在!');
                }
    
                /** @var Xlsx $objRead */
                $objRead = IOFactory::createReader('Xlsx');
    
                if (!$objRead->canRead($file)) { 
                    /** @var Xls $objRead */
                    $objRead = IOFactory::createReader('Xls');
    
                    if (!$objRead->canRead($file)) { 
                        throw new \Exception('只支持导入Excel文件！');
                    }
                }
    
                /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
                empty($options) && $objRead->setReadDataOnly(true);
                /* 建立excel对象 */
                $obj = $objRead->load($file);
                /* 获取指定的sheet表 */
                $currSheet = $obj->getSheet($sheet);
    
                if (isset($options['mergeCells'])) { 
                    /* 读取合并行列 */
                    $options['mergeCells'] = $currSheet->getMergeCells();
                }
                if (0 == $columnCnt) { 
                    /* 取得最大的列号 */
                    $columnH = $currSheet->getHighestColumn();
                    /* 兼容原逻辑，循环时使用的是小于等于 */
                    $columnCnt = Coordinate::columnIndexFromString($columnH);
                }
    
                /* 获取总行数 */
                $rowCnt = $currSheet->getHighestRow();
                $data   = [];
                /* 读取内容 */
                for ($_row = 1; $_row <= $rowCnt; $_row++) { 
                    $isNull = true;
    
                    for ($_column = 1; $_column <= $columnCnt; $_column++) { 
                        $cellName = Coordinate::stringFromColumnIndex($_column);
                        $cellId   = $cellName . $_row;
                        $cell     = $currSheet->getCell($cellId);
                        if (isset($options['format'])) { 
                            /* 获取格式 */
                            $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                            /* 记录格式 */
                            $options['format'][$_row][$cellName] = $format;
                        }
                        if (isset($options['formula'])) { 
                            /* 获取公式，公式均为=号开头数据 */
                            $formula = $currSheet->getCell($cellId)->getValue();
    
                            if (0 === strpos($formula, '=')) { 
                                $options['formula'][$cellName . $_row] = $formula;
                            }
                        }
                        if (isset($format) && 'm/d/yyyy' == $format) { 
                            /* 日期格式翻转处理 */
                            $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                        }
                        $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());
    
                        if (!empty($data[$_row][$cellName])) { 
                            $isNull = false;
                        }
                    }
                    /* 判断是否整行数据为空，是的话删除该行数据 */
                    if ($isNull) { 
                        unset($data[$_row]);
                    }
                }
                return $data;
            } catch (\Exception $e) { 
                throw $e;
            }
        }
        # 导出
        /** * @param $data * @param $title * @throws Exception * @throws \PhpOffice\PhpSpreadsheet\Exception * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception */
         static function exportExcel($data,$title)
        { 
            $spreadsheet = new Spreadsheet();
            $worksheet = $spreadsheet->getActiveSheet();
            //设置工作表标题名称
            $worksheet->setTitle('检测工具');
            //设置宽度
            $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
            $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(28);
            $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(18);
            $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(18);
            $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(18);
            $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(50);
            $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(15);
            //设置样式
            $styleArray = [
                'font' => [
                    'bold' => true,
                    'color'=>[
                        'argb'=>Color::COLOR_BLUE
                    ]
                ],
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                ],
            ];
            //背景颜色
            $worksheet->getStyle('A1:R1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('787FF6');// 设置背景色
            //设置单元格样式
            $worksheet->getStyle('A1:R1')->applyFromArray($styleArray)->getFont()->setSize(12);
            //表头 设置单元格内容
            foreach ($title as $key => $value) { 
                $worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
            }
            $row = 2; //从第二行开始
            //样式
            $styleArrayBody = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => '666666'],
                    ],
                ],
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                ],
            ];
            //数据库的长度+1
            $length= count($data)+1;
            $worksheet->getStyle('A1:R'.$length)->applyFromArray($styleArrayBody);
            foreach ($data as $item) { 
                $column = 1; //从第一列设置并初始化
                foreach ($item as $value) { 
                    $worksheet->setCellValueByColumnAndRow($column, $row, $value); //哪一列哪一行设置哪个值
                    $column++; //列数加1
                }
                $row++; //行数加1
            }
            //输出到浏览器
            $fileName = date('Y-m-d', time()) . rand(1000, 9999);
            $fileName = $fileName.'无忧巡检工具列表';
            $fileType = 'Xlsx';
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            self::excelBrowserExport($fileName, $fileType);
            $writer->save('php://output');
        }
        #格式校验
        static public function excelBrowserExport($fileName, $fileType)
        { 
            //文件名称校验
            if (!$fileName) { 
                throw new Exception('文件名不能为空');
            }
            //Excel文件类型校验
            $type = ['Excel2007', 'Xlsx', 'Excel5', 'xls'];
            if (!in_array($fileType, $type)) { 
                throw new Exception('未知文件类型');
            }
            if ($fileType == 'Excel2007' || $fileType == 'Xlsx') { 
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
                header('Cache-Control: max-age=0');
            } else { 
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
                header('Cache-Control: max-age=0');
            }
        }
    
    }

##### 1.4.2 创建TestController #####

<?php
    namespace app\controllers;
    
    
    use app\models\TestTool;
    use app\models\UploadForm;
    use app\common\tools\Excel;
    use PhpOffice\PhpSpreadsheet\IOFactory;
    use PhpOffice\PhpSpreadsheet\Reader\Exception;
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use Yii;
    use yii\web\UploadedFile;
    
    class TestController extends \yii\web\Controller
    { 
        # 用来测试的index页面
        public function actionIndex()
        { 
            # 文件上传的model
            $file = new UploadForm();
            return $this->render('index', [
                'file' => $file
            ]);
    
        }
        # 导入表格
        public function actionImport_excel_data(){ 
            $model = new UploadForm();
            if (Yii::$app->request->isPost) { 
                $model->file = UploadedFile::getInstance($model, 'file');
                if ($model->file && $model->validate()) { 
                    $path = 'uploads/';
                    if (!file_exists($path)) { 
                        mkdir($path, 0755, true);
                    }
                    $time = time();
                    $rand = rand(100,999);
                    $basename = $model->file->baseName;
                    $filename = md5($basename.$time.$rand);
                    $fname = $path . $filename . '.' . $model->file->extension;
                    if ($model->file && $model->validate()) { 
                        $model->file->saveAs($fname);
                    }
                    $fpath = Yii::$app->BasePath.'/web/'.$fname;
    // echo $fpath;
    // exit;
                    $data = Excel::importExecl($fpath);
                    # 得到表头
                    $biaotou = $data[1];
                    $count  = count($data);
    // var_dump($data);
    // exit;
                    if ($biaotou['A'] != 'id'||$biaotou['B'] != '工具编号'||$biaotou['C'] != '工具名称'
                        ||$biaotou['D'] != '工具品牌'||$biaotou['E'] != '工具分类'||$biaotou['F'] != '工具校验周期'
                        ||$biaotou['G'] != '工具状态'||$biaotou['H'] != '工具功能'||$biaotou['I'] != '工具适用范围'
                        ||$biaotou['J'] != '购置日期'||$biaotou['K'] != '供应商'||$biaotou['L'] != '存放地址'
                        ||$biaotou['M'] != '工具备注'||$biaotou['N'] != '记录创建时间'||$biaotou['O'] != '记录更新时间'
                        ||$biaotou['P'] != '工具图片地址'||$biaotou['Q'] != '记录创建者'||$biaotou['R'] != '记录更新者'
                    ){ 
                        return "表格表头不匹配";
                    }
                    # 这里要从第二行开始读 因为第一行是表头
                    # 拿到数据后写入数据里面 建议这里用批量写入的方式而不是再循环里面写入数据库
                    for ($i = 2; $i<$count+1; $i++){ 
                           var_dump($data[$i]);
    // echo 'id'.$data[$i]['A']."</br>";
    // echo '工具编号'.$data[$i]['B']."</br>";
    // echo '工具品牌'.$data[$i]['C']."</br>";
    // echo '工具分类'.$data[$i]['D']."</br>";
    // echo '工具适用范围'.$data[$i]['E']."</br>";
                    }
                }
            }
            return "";
        }
        # 导出表格
        /** * @throws \Exception */
        public function actionExport_excel_data(){ 
    
            $data=TestTool::find()->all();
            $title = ['id', '工具编号', '工具名称','工具品牌','工具分类','工具校验周期','工具状态','工具功能'
                ,'工具适用范围','购置日期','供应商','存放地址','工具备注','记录创建时间','记录更新时间','工具图片地址',
                '记录创建者','记录更新者'];
             Excel::exportExcel($data,$title);
        }
    }

##### 1.4.3 创建UploadForm 因为使用到了文件上传 #####

<?php
    
    
    namespace app\models;
    
    
    class UploadForm extends \yii\base\Model
    { 
    
        /** * @var */
        public $file;
    
        /** * @return array the validation rules. */
        public function rules()
        { 
            return [
                [['file'], 'file'],
            ];
        }
    }

##### 1.4.4 创建视图 viwes/test/ index.php #####

<?php
    use yii\bootstrap\ActiveForm;
    ?>
    
    
    <h2>导入表格</h2>
    <?php $form = ActiveForm::begin(['options' => ['enctype' => 'multipart/form-data'],'action' => ['import_excel_data'],'method'=>'post']) ?>
    
    <?= $form->field($file, 'file')->fileInput() ?>
    
    <button>导入表格</button>
    
    <?php ActiveForm::end() ?>
    
    <a  href="index.php?r=test/export_excel_data">下载表格</a>

完成以上就是可以访问：

http://localhost/yii-excal/basic/web/index.php?r=test/index

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmc3NA_size_16_color_FFFFFF_t_70 1]  
就可以完成导入和导出。

#### 1.5创建前后端分离的api方式： ####

使用导入前下载一个模板 然后再模板中输入 对应的数据 就可以说进行导入：

##### 1.5.1创建ExcelTools #####

<?php
    
    
    namespace app\helper;
    use PhpOffice\PhpSpreadsheet\Reader\Exception;
    use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
    use PhpOffice\PhpSpreadsheet\Reader\Xls;
    use PhpOffice\PhpSpreadsheet\IOFactory;
    use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Style\Color;
    use PhpOffice\PhpSpreadsheet\Style\Fill;
    
    class ExcelTools
    { 
    
       // 导入excel
        static function importExecl($file = '', $sheet = 0, $columnCnt = 0, &$options = [])
        { 
            try { 
                /* 转码 */
                $file = iconv("utf-8", "gb2312", $file);
    
                if (empty($file) OR !file_exists($file)) { 
                    throw new \Exception('文件不存在!');
                }
    
                /** @var Xlsx $objRead */
                $objRead = IOFactory::createReader('Xlsx');
    
                if (!$objRead->canRead($file)) { 
                    /** @var Xls $objRead */
                    $objRead = IOFactory::createReader('Xls');
    
                    if (!$objRead->canRead($file)) { 
                        throw new \Exception('只支持导入Excel文件！');
                    }
                }
                /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
                empty($options) && $objRead->setReadDataOnly(true);
                /* 建立excel对象 */
                $obj = $objRead->load($file);
                /* 获取指定的sheet表 */
                $currSheet = $obj->getSheet($sheet);
    
                if (isset($options['mergeCells'])) { 
                    /* 读取合并行列 */
                    $options['mergeCells'] = $currSheet->getMergeCells();
                }
                if (0 == $columnCnt) { 
                    /* 取得最大的列号 */
                    $columnH = $currSheet->getHighestColumn();
                    /* 兼容原逻辑，循环时使用的是小于等于 */
                    $columnCnt = Coordinate::columnIndexFromString($columnH);
                }
                /* 获取总行数 */
                $rowCnt = $currSheet->getHighestRow();
                $data   = [];
                /* 读取内容 */
                for ($_row = 1; $_row <= $rowCnt; $_row++) { 
                    $isNull = true;
                    for ($_column = 1; $_column <= $columnCnt; $_column++) { 
                        $cellName = Coordinate::stringFromColumnIndex($_column);
                        $cellId   = $cellName . $_row;
                        $cell     = $currSheet->getCell($cellId);
                        if (isset($options['format'])) { 
                            /* 获取格式 */
                            $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                            /* 记录格式 */
                            $options['format'][$_row][$cellName] = $format;
                        }
                        if (isset($options['formula'])) { 
                            /* 获取公式，公式均为=号开头数据 */
                            $formula = $currSheet->getCell($cellId)->getValue();
                            if (0 === strpos($formula, '=')) { 
                                $options['formula'][$cellName . $_row] = $formula;
                            }
                        }
                        if (isset($format) && 'm/d/yyyy' == $format) { 
                            /* 日期格式翻转处理 */
                            $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                        }
                        $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());
                        if (!empty($data[$_row][$cellName])) { 
                            $isNull = false;
                        }
                    }
                    /* 判断是否整行数据为空，是的话删除该行数据 */
                    if ($isNull) { 
                        unset($data[$_row]);
                    }
                }
                return $data;
            } catch (\Exception $e) { 
                throw $e;
            }
        }
    
       // 导出
        /** * @param $data * @param $title * @throws Exception * @throws \PhpOffice\PhpSpreadsheet\Exception * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception */
        static function exportExcel($data,$title)
        { 
            $spreadsheet = new Spreadsheet();
            $worksheet = $spreadsheet->getActiveSheet();
            //设置工作表标题名称
            $worksheet->setTitle('检测工具');
            //设置宽度
            $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
            $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(28);
            $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(18);
            $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(18);
            $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(18);
            $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(50);
            $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(15);
            //设置样式
            $styleArray = [
                'font' => [
                    'bold' => true,
                    'color'=>[
                        'argb'=>Color::COLOR_BLUE
                    ]
                ],
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                ],
            ];
            //背景颜色
            $worksheet->getStyle('A1:R1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('787FF6');// 设置背景色
            //设置单元格样式
            $worksheet->getStyle('A1:R1')->applyFromArray($styleArray)->getFont()->setSize(12);
            //表头 设置单元格内容
            foreach ($title as $key => $value) { 
                $worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
            }
            $row = 2; //从第二行开始
            //样式
            $styleArrayBody = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => '666666'],
                    ],
                ],
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                ],
            ];
            //数据库的长度+1
            $length= count($data)+1;
            $worksheet->getStyle('A1:R'.$length)->applyFromArray($styleArrayBody);
            foreach ($data as $item) { 
                $column = 1; //从第一列设置并初始化
                foreach ($item as $value) { 
                    $worksheet->setCellValueByColumnAndRow($column, $row, $value); //哪一列哪一行设置哪个值
                    $column++; //列数加1
                }
                $row++; //行数加1
            }
            //输出到浏览器
            $fileName = date('Y-m-d', time()) . rand(1000, 9999);
            $fileName = $fileName.'EWY-Tools';
            $fileType = 'Xlsx';
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            self::excelBrowserExport($fileName, $fileType);
            $writer->save('php://output');
        }
        //格式校验
        static public function excelBrowserExport($fileName, $fileType)
        { 
            //文件名称校验
            if (!$fileName) { 
                throw new Exception('文件名不能为空');
            }
            //Excel文件类型校验
            $type = ['Excel2007', 'Xlsx', 'Excel5', 'xls'];
            if (!in_array($fileType, $type)) { 
                throw new Exception('未知文件类型');
            }
            if ($fileType == 'Excel2007' || $fileType == 'Xlsx') { 
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
                header('Cache-Control: max-age=0');
            } else { 
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
                header('Cache-Control: max-age=0');
            }
        }
    
        /** * 模板下载 导出 * @param $title * @throws Exception * @throws \PhpOffice\PhpSpreadsheet\Exception * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception */
        static function exportTemplate($title)
        { 
            $spreadsheet = new Spreadsheet();
            $worksheet = $spreadsheet->getActiveSheet();
            //设置工作表标题名称
            $worksheet->setTitle('检测工具');
            //设置宽度
            $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
            $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(28);
            $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(18);
            $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(18);
            $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(18);
            $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(50);
            $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(15);
            //设置样式
            $styleArray = [
                'font' => [
                    'bold' => true,
                    'color'=>[
                        'argb'=>Color::COLOR_BLUE
                    ]
                ],
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                ],
            ];
            //背景颜色
            $worksheet->getStyle('A1:R1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('787FF6');// 设置背景色
            //设置单元格样式
            $worksheet->getStyle('A1:R1')->applyFromArray($styleArray)->getFont()->setSize(12);
            //表头 设置单元格内容
            foreach ($title as $key => $value) { 
                $worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
            }
            //输出到浏览器
            $fileName = date('Y-m-d', time()) . rand(1000, 9999);
            $fileName = $fileName.'EWY-ToolTemplate';
            $fileType = 'Xlsx';
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            self::excelBrowserExport($fileName, $fileType);
            $writer->save('php://output');
        }
    
    }

1.5.2 下载工具模板

/** * * 下载工具模板 * @throws \PhpOffice\PhpSpreadsheet\Exception * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception */
        public function actionDownload()
        { 
            $title = ['id', '工具编号', '工具名称','工具品牌','工具分类','工具校验周期','工具状态','工具功能'
                ,'工具适用范围','购置日期','供应商','存放地址','工具备注','记录创建时间','记录更新时间','工具图片地址',
                '记录创建者','记录更新者'];
            ExcelTools::exportTemplate($title);
        }

1.5.3 导入到数据库

public function actionImport_excel_data()
        { 
            $params = \Yii::$app->request->getBodyParams();
            $model = new UploadForm();
            if (Yii::$app->request->isPost) { 
                $model->File = UploadedFile::getInstance($model, 'File');
                if ($model->File && $model->validate()) { 
                    $path = 'uploads/';
                    if (!file_exists($path)) { 
                        mkdir($path, 0755, true);
                    }
                    $time = time();
                    $rand = rand(100,999);
                    $basename = $model->File->baseName;
                    $filename = md5($basename.$time.$rand);
                    $Fname = $path . $filename . '.' . $model->File->extension;
                    if ($model->File && $model->validate()) { 
                        $model->File->saveAs($Fname);
                    }
                    $fpath = Yii::$app->BasePath.'/web/'.$Fname;
                    $data = ExcelTools::importExecl($fpath);
                    # 得到表头
                    $biaotou = $data[1];
                    $count  = count($data);
                    if ($biaotou['A'] != 'id'||$biaotou['B'] != '工具编号'||$biaotou['C'] != '工具名称'
                        ||$biaotou['D'] != '工具品牌'||$biaotou['E'] != '工具分类'||$biaotou['F'] != '工具校验周期'
                        ||$biaotou['G'] != '工具状态'||$biaotou['H'] != '工具功能'||$biaotou['I'] != '工具适用范围'
                        ||$biaotou['J'] != '购置日期'||$biaotou['K'] != '供应商'||$biaotou['L'] != '存放地址'
                        ||$biaotou['M'] != '工具备注'||$biaotou['N'] != '记录创建时间'||$biaotou['O'] != '记录更新时间'
                        ||$biaotou['P'] != '工具图片地址'||$biaotou['Q'] != '记录创建者'||$biaotou['R'] != '记录更新者'
                    ){ 
                        return "表格表头不匹配";
                    }
                    # 这里要从第二行开始读 因为第一行是表头
                    # 拿到数据后写入数据里面 建议这里用批量写入的方式而不是再循环里面写入数据库
                    for ($i = 2; $i<$count+1; $i++){ 
                        $model = new TestTool();
                        $model->tool_number=$data[$i]['B'];
                        $model->tool_name=$data[$i]['C'];
                        $model->tool_brand=$data[$i]['D'];
                        $model->tool_class=$data[$i]['E'];
                        $model->tool_calibration_cycle=$data[$i]['F'];
                        $model->tool_status=$data[$i]['G'];
                        $model->tool_functions=$data[$i]['H'];
                        $model->tool_scope=$data[$i]['I'];
                      // $model->purchase_time=$data[$i]['J']; 待处理
                        $model->supplier=$data[$i]['K'];
                        $model->storage_address=$data[$i]['L'];
                        $model->tool_notes=$data[$i]['M'];
                        $model->save();
                    }
                    return "导入成功";
                }
            }
            return "";
        }

导出和上面的的导出基本以上这里就不展示的。

具体调试api 大家百度下PostMan excel 接口调试方式就是可以。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmc3NA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200605150921830.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmc3NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmc3NA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200605150903259.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmc3NA==,size_16,color_FFFFFF,t_70