tp5.1 phpspreadsheet- 工具类 导入导出(整合优化,非原创,抄一抄,加了一些自己的东西,)

phpspreadsheet-工具类 导入导出(整合优化,非原创,抄一抄,加了一些自己的东西)
1. composer require phpoffice/phpspreadsheet
2. 看最下面的两个demo  (使用框架 : tp5.1 , laravel也能用,改改不就行了..)

 <?php

 namespace app\common\utils;

 use PhpOffice\PhpSpreadsheet\Spreadsheet;

 use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

 use PhpOffice\PhpSpreadsheet\IOFactory;

 /**

  * phpoffice-phpspreadsheet-工具类

  */

 class PhpOffice{

     /**

      * @title 导出表格

      * @param arr：要导出excel表的数据，接受一个二维数组

      * @param fileName：excel表的表名

      * @param headAr：excel表的表头，接受一个一维数组

      * @param keyAr：$arr中对应表头的键的数组，接受一个一维数组

      */

     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;

     }

     //导入

     public function importExcel(){

         set_time_limit(0);

         //文件上传导入

         // $fileController=new FileController();

         $res=self::uploadFileImport();

         $data=$res['data'];

         //手动输入路径

         // $data='muban/muban.xlsx';

         //修正路径

         $filename=str_replace('/uploads', 'uploads',$data);

         //进行读取

         $spreadsheet = IOFactory::load($filename);

         $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

         return $sheetData;

     }

     //文件上传,导入文件专用,数据不入库

     public function uploadFileImport(){

         // 获取表单上传文件

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

         $return  = array('status' => 1, 'info' => '上传成功', 'data' => []);

         // 移动到框架应用根目录/public/uploads/ 目录下

         if($file){

             $savePath = '/uploads/importExcel';

             $info = $file->move(ROOT_PATH . 'public' . DS .$savePath);

             $md5 = $info->hash('md5');

             $sha1= $info->hash('sha1');

             $saveName=strtr($info->getSaveName(),'\\','/');

             return self::setResAr(1,'上传成功',$savePath.'/'.$saveName);

         }

         return self::setResAr(0,'上传失败');

     }

     //设置列一维数组(最多701列)

     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;

     }

     //设置返回结果格式

     public function setResAr($code=0,$msg='',$data=array()){

         return ['code'=>$code,'msg'=>$msg,'data'=>$data];

     }

     // 如何实现..

     // //定义好命名空间

     // use app\common\utils\PhpOffice;

     // use think\Db;

     // class Demo{

     //     //导出demo-实现

     //     public function exportDemo(){

     //         //获取数据

     //         $data=Db::name('config')->select();

     //         //获取表头(自行定义)

     //         $sql="select COLUMN_NAME as name,column_comment as val from INFORMATION_SCHEMA.Columns where table_name='表名'";

     //         $arr=Db::query($sql);

     //         $headAr=[];

     //         $keyAr=[];

     //         foreach ($arr as $k => $v) {

     //             $headAr[]=$v['val'];

     //             $keyAr[]=$v['name'];

     //         }

     //         $phpOffice = new PhpOffice();

     //         $phpOffice->exportExcel('导出表格',$data,$headAr,$keyAr);

     //     }

     //     //导入demo-实现

     //     public function importDemo(){

     //         $phpOffice = new PhpOffice();

     //         $data=$phpOffice->importExcel();

     //         dump($data);

     //         //自行foreach进行数据处理..

     //         //..

     //     }

     // }

     //

 }

 <?php

 //定义好命名空间

 use app\common\utils\PhpOffice;

 use think\Db;

 class Demo{

     //导出demo-实现

     public function exportDemo(){

         //获取数据

         $data=Db::name('config')->select();

         //获取表头(自行定义)

         $sql="select COLUMN_NAME as name,column_comment as val from INFORMATION_SCHEMA.Columns where table_name='表名'";

         $arr=Db::query($sql);

         $headAr=[];

         $keyAr=[];

         foreach ($arr as $k => $v) {

             $headAr[]=$v['val'];

             $keyAr[]=$v['name'];

         }

         $phpOffice = new PhpOffice();

         $phpOffice->exportExcel('导出表格',$data,$headAr,$keyAr);

     }

     //导入demo-实现

     public function importDemo(){

         $phpOffice = new PhpOffice();

         $data=$phpOffice->importExcel();

         dump($data);

         //自行foreach进行数据处理..

         //..

     }

 }

 ?>