TP6|TP5.1 PHPoffice导出|导入
插件合集
安装
composer require phpoffice/phpspreadsheet
TP6基类
<?php /** * Created by PhpStorm. * User: Zhangyongfeng * Date: 2020/12/1 * Time: 11:31 * * ━━━━━━━━━神兽出没━━━━━━━━━ * * ┏┓ ┏┓+ + * ┏┛┻━━━┛┻┓ + + * ┃ ┃ * ┃ ━ ┃ ++ + + + * ████━████ ┃+ * ┃ ┃ + * ┃ ┻ ┃ * ┃ ┃ + + * ┗━┓ ┏━┛ * ┃ ┃ * ┃ ┃ + + + + * ┃ ┃ Code is far away from bug with the animal protecting * ┃ ┃ + 神兽保佑,代码无bug * ┃ ┃ * ┃ ┃ + * ┃ ┗━━━┓ + + * ┃ ┣┓ * ┃ ┏┛ * ┗┓┓┏━┳┓┏┛ + + + + * ┃┫┫ ┃┫┫ * ┗┻┛ ┗┻┛+ + + + * * ━━━━━━━━━感觉萌萌哒━━━━━━━━━ */ namespace zyf; use think\facade\Filesystem; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use PhpOffice\PhpSpreadsheet\Spreadsheet; class Excel { static public function returnData($code = '', $msg = '', $data = []){ return \app\common\controller\Base::returnData($code, $msg, $data); } /** * [exportExcel 导出表格] * @param string $fileName excel表的表名 * @param array $arr 要导出excel表的数据，接受一个二维数组 * @param array $headAr excel表的表头，接受一个一维数组 * @param array $keyAr $arr中对应表头的键的数组，接受一个一维数组 * @return file 到uploads目录下 * @author zyf <1322816443@qq.com> */ public function exportExcel($fileName = '表格', $arr = [], $headAr = [], $keyAr = []) { // 计算所需表头数量 $count = count($headAr)-1; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); // 设置列 $rowAr = self::setRowAr($count); foreach ($rowAr as $k => $v) { if($k > $count) break; $sheet->setCellValue($v.'1', $headAr[$k]); } // 写入值 foreach ($arr as $k => $v) { foreach ($rowAr as $ke => $ve){ if($ke > $count) break; $sheet->setCellValue($ve.($k+2), $v[$keyAr[$ke]]); // 给单元格设置值 $spreadsheet->getActiveSheet()->getColumnDimension($ve)->setWidth(10); // 固定列宽,看着更整齐 } } // 冻结首行 $spreadsheet->getActiveSheet()->freezePaneByColumnAndRow(0,2); // 在输出Excel前，缓冲区中处理BOM头 ob_end_clean(); ob_start(); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$fileName.date('_Ymd_Hi',time()).'.xlsx"'); header('Cache-Control: max-age=0'); $writer = new Xlsx($spreadsheet); $writer->save('php://output'); // 删除清空： $spreadsheet->disconnectWorksheets(); unset($spreadsheet); exit; } /** * [importExcel 导入数据] * @return array * @author zyf <1322816443@qq.com> * */ public function importExcel() { set_time_limit(0); // 文件上传导入 $res = self::uploadFileImport(); if($res['code'] == 999){ return $res; } // 进行读取 $spreadsheet = IOFactory::load($res['data']); $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true); return $sheetData; } /** * [uploadFileImport 导入文件（数据不入库）] * @param file file 文件 * @return array * @author zyf <1322816443@qq.com> * */ public function uploadFileImport() { $param = request()->param(); $file = request()->file('file'); if ($file == null) return $this::returnData(999, '请上传文件'); // 上传路径 $disk = !empty($param['disk']) ? $param['disk'] : 'excel'; // 文件名称 $saveName = Filesystem::disk($disk)->putFile('', $file); $savePath = str_replace(date('Ymd', time()) .'\\', '', '/uploads/' . $disk . "/" . date('Ymd', time()) . "/" . $saveName); return $this::returnData(200, '上传成功', $savePath); } /** * [setRowAr 设置列] * @param int $count 列总数 * @return array * @author zyf <1322816443@qq.com> * */ public function setRowAr($count = 26) { $indData = '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'; $indData = explode(',',$indData); $curCount = 26; for ($i = 0; $i <26 ; $i++) { for ($j = 0; $j < 26 ; $j++) { if($curCount >= $count) return $indData; $indData[] = $indData[$i].$indData[$j]; $curCount++; } } return $indData; } }
TP5.1基类
<?php /** * Created by PhpStorm. * User: Zhangyongfeng * Date: 2020/12/1 * Time: 11:31 * * ━━━━━━━━━神兽出没━━━━━━━━━ * * ┏┓ ┏┓+ + * ┏┛┻━━━┛┻┓ + + * ┃ ┃ * ┃ ━ ┃ ++ + + + * ████━████ ┃+ * ┃ ┃ + * ┃ ┻ ┃ * ┃ ┃ + + * ┗━┓ ┏━┛ * ┃ ┃ * ┃ ┃ + + + + * ┃ ┃ Code is far away from bug with the animal protecting * ┃ ┃ + 神兽保佑,代码无bug * ┃ ┃ * ┃ ┃ + * ┃ ┗━━━┓ + + * ┃ ┣┓ * ┃ ┏┛ * ┗┓┓┏━┳┓┏┛ + + + + * ┃┫┫ ┃┫┫ * ┗┻┛ ┗┻┛+ + + + * * ━━━━━━━━━感觉萌萌哒━━━━━━━━━ */ namespace zyf; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use PhpOffice\PhpSpreadsheet\Spreadsheet; class Excel { /** * [exportExcel 导出表格] * @param string $fileName excel表的表名 * @param array $arr 要导出excel表的数据，接受一个二维数组 * @param array $headAr excel表的表头，接受一个一维数组 * @param array $keyAr $arr中对应表头的键的数组，接受一个一维数组 * @return file 到uploads目录下 * @author zyf <1322816443@qq.com> */ public function exportExcel($fileName = '表格', $arr = [], $headAr = [], $keyAr = []){ // 计算所需表头数量 $count = count($headAr)-1; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); // 设置列 $rowAr = self::setRowAr($count); foreach ($rowAr as $k => $v) { if($k > $count) break; $sheet->setCellValue($v.'1', $headAr[$k]); } // 写入值 foreach ($arr as $k => $v) { foreach ($rowAr as $ke => $ve){ if($ke > $count) break; $sheet->setCellValue($ve.($k+2), $v[$keyAr[$ke]]); // 给单元格设置值 $spreadsheet->getActiveSheet()->getColumnDimension($ve)->setWidth(10); // 固定列宽,看着更整齐 } } // 冻结首行 $spreadsheet->getActiveSheet()->freezePaneByColumnAndRow(0,2); // 在输出Excel前，缓冲区中处理BOM头 ob_end_clean(); ob_start(); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$fileName.date('_Ymd_Hi',time()).'.xlsx"'); header('Cache-Control: max-age=0'); $writer = new Xlsx($spreadsheet); $writer->save('php://output'); // 删除清空： $spreadsheet->disconnectWorksheets(); unset($spreadsheet); exit; } /** * [importExcel 导入数据] * @return array * @author zyf <1322816443@qq.com> * */ public function importExcel(){ set_time_limit(0); // 文件上传导入 $res = self::uploadFileImport(); if($res['code'] == 500){ return $res; } $data = $res['data']; // 修正路径 $filename = str_replace('/uploads', 'uploads', $data); // 进行读取 $spreadsheet = IOFactory::load($filename); $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true); return $sheetData; } /** * [uploadFileImport 导入文件（数据不入库）] * @param file file 文件 * @return array * @author zyf <1322816443@qq.com> * */ public function uploadFileImport(){ // 获取表单上传文件 $file = \request()->file('file'); // 移动到框架应用根目录/public/uploads/ 目录下 if($file){ $savePath = '/uploads/importExcel'; $info = $file->move(ROOT_PATH . 'public' . DS .$savePath); $saveName = strtr($info->getSaveName(),'\\','/'); return self::setResAr(200,'上传成功',$savePath.'/'.$saveName); } return self::setResAr(500,'上传失败'); } /** * [setRowAr 设置列] * @param int $count 列总数 * @return array * @author zyf <1322816443@qq.com> * */ public function setRowAr($count = 26){ $indData = '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'; $indData = explode(',',$indData); $curCount = 26; for ($i = 0; $i <26 ; $i++) { for ($j = 0; $j < 26 ; $j++) { if($curCount >= $count) return $indData; $indData[] = $indData[$i].$indData[$j]; $curCount++; } } return $indData; } /** * [setResAr 设置返回结果格式] * @param int $code 自定义编码 * @param string $msg 返回信息 * @param array $data 返回数据 * @return array * @author zyf <1322816443@qq.com> * */ public function setResAr($code = 200,$msg = '',$data = array()){ return ['code' => $code,'msg' => $msg,'data' => $data]; } }
导出后台
// 导出excel-实现 public function export() { // 获取数据 $systemMod = new \app\common\model\System(); $data = $systemMod->getAll(''); // 设置表头 $headAr = [ '姓名', '邮箱', '电话', ]; $keyAr = [ 'name', 'email', 'tel', ]; $phpOffice = new Excel(); $phpOffice->exportExcel('数据', $data, $headAr, $keyAr); }
导入后台
// 导入excel-实现 public function import() { if(IS_POST){ $phpOffice = new Excel(); $data = $phpOffice->importExcel(); halt($data); } return $this->render(); }
导入前端
导入前台用的layui
<div class="layui-form-item"> <label class="layui-form-label">上传Excel：</label> <div class="layui-input-block"> <div class="layui-upload"> <button type="button" class="layui-btn upload_excel">Excel</button> </div> </div> </div>
导入JS
// 导入上传 upload.render({ elem: '.upload_excel' , url: 'import' , multiple: false , accept: 'file' , before: function(obj){ layer.load(1); } , done: function (res) { console.log(res) }, error: function(index, upload){ layer.closeAll('loading'); } })
