laravel导入导出Excel操作

官方文档：

https://docs.laravel-excel.com/3.1/getting-started/​​​​​​

Excel导入：

1. composer安装maatwebsite/excel包

   composer require maatwebsite/excel

2. 创建导入类

   php artisan make:import FilesInfoImport

3. 打开文件FilesInfoImport，按照以下格式写入

   <?php
   namespace App\Imports;
   
   use App\Models\FilesInfo;
   use Maatwebsite\Excel\Concerns\ToModel;
   use Illuminate\Support\Facades\DB;
   use Illuminate\Support\Collection;
   use Maatwebsite\Excel\Concerns\Importable;
   
   class FilesInfoImport implements ToModel
   {
       use Importable;
       /**
        * @param array $row
        *
        * @return \Illuminate\Database\Eloquent\Model|null
        */
       public function model(array $row)
       {
           return new FilesInfo([
               //FilesInfo为文件表的Model名称（自己新建），正常引用你所导入的表的Model
           ]);
       }
   }
   

4. 控制器方法

   public function filesInfoImport(Request $request)
       {
           $c = 0;
           if ($request->hasFile('file')) {
               $file = $request->file('file')->store('/excel');
               $tmp_file = 'storage/' . $file;
   
               $data = (new FilesInfoImport())->toArray($tmp_file);
   
               if (!empty($data)) {
                   $arr0 = array_shift($data[0]);//删除Excel第一行[标题]
                   $arr = $data[0];
                   foreach ($arr as $rows) {
                       $item = FilesInfo::create([
                           'number' => $rows[0],
                           'filetype' => $rows[1],
                           'name' => $rows[2],
                           //.......所有字段
                       ]);
                       if ($item) {
                           $c++;
                       } else {
                           exit(json_encode(array('code'=>1, 'msg'=>"部分数据上传失败，已上传".$c."条！")));
                       }
                   }
                   exit(json_encode(array('code'=>1, 'msg'=>'全部提交成功！')));
               }
               else{
                   exit(json_encode(array('code'=>0, 'msg'=>'无文件上传！')));
               }
           }
           else
           {
               exit(json_encode(array('code'=>0, 'msg'=>'请选择上传文件！')));
           }
       }

5. html(该前端框架使用的layui)

   <div class="layui-inline" id="box">
       <button class="layui-btn layui-btn-warm " id="import_btn" data-type="dataImport">导入</button>
   </div>

6. js

   $('#import_btn').on('click', function () {
          var type = $(this).data('type');
          active[type] ? active[type].call(this) : '';
   });
   
   upload.render({
                   elem: '#import_btn'
                   ,url: '/admin/filesInfoImport' //此处配置你自己的上传接口即可
                   ,accept: 'file' //普通文件
                   ,field: "file"
                   ,exts:'xls|xlsx|csv' //允许上传的类型
                   ,before: function(obj){ //obj参数包含的信息，跟 choose回调完全一致，可参见上文。
                       layui.layer.load();
                       this.data={
                           //传给后台的参数
                       }
                   }
                   ,done: function(res){
                       if(res.code==1)
                       {
                           layui.layer.closeAll();
                           return layer.msg(res.msg);
                       }
                       else
                       {
                           layui.layer.closeAll();
                           return layer.msg(res.msg);
                       }
   
                   },
                   error: function(msg){
                       //请求异常回调
                       layui.layer.closeAll();
                       return layer.msg('上传失败,请重新上传');
                   }
               });

注意：

如果导入后，无法访问该文件，是访问地址的问题？？

        1.需要建立软连接命令：php artisan storage:link

        2.如果建立软连接后依旧访问有问题，是因为软连接指定的目录与实际上传的目录不匹配，需要修改上传文件的指定目录

        config/filesystems.php下的

        'root' => storage_path('app'),改为'root' => storage_path('app/public'),

本地能正常上传，线上服务器上传失败

报错：

ErrorException: touch(): Unable to create file
/data/www/***/***/storage/framework/laravel-excel/laravel-excel-
nTTU18NFIgWFB1tGe6nn8RLlICp8U6aj.xls because Permission denied in file
/data/www/***/***/vendor/maatwebsite/excel/src/Files/LocalTemporaryFile.php on line

需要在服务器项目文件夹内运行：sudo chmod -R 777  ***（文件地址）

给storage文件夹设置777权限

Excel导出：

1. html

   <button class="layui-btn layui-btn-normal import_btn" id="export_btn" >导出表</button>

2. js

   $('#export_btn').on('click', function () {
        window.open("/admin/zdjbExportZf?year=" + $("#year").val()+ '&IDCard='+$(".search_IDCard").val())
   });

3. 控制器

   public function zdjbExportZf(Request $request)
       {
           ini_set('max_execution_time', 60000);
           $key = $request->request->all();
           $main = new Zdjb();
           $where = [];
   
           //查询条件
           if ($key) {
               foreach ($key as $k => $v) {
                   if ($v!=null&&$v!="") {
                       switch ($k) {
                           case 'year':
                               array_push($where, ['year', $v]);
                               break;
                           
                       }
                   }
               }
           }
           //根据条件查询sql数据
           $res = $main->where($where)->get()->toArray();
           //遍历sql数据
           foreach ($res as $k => &$v) {
               $v['index'] = $k+1;
               //$v['IDCard'] = $v['IDCard'] . ' ';//身份证号加空格，excel不会显示###
               //如果直接取字段的表内获取的值，在$field直接写字段名即可，比如以上身份证需要转化加空格，需要再次遍历一次，无特殊条件，无需遍历
           }
           $head = [
               '序号',
               '编号',
               '单位名称',
               '姓名',
               '身份证号',
               '开户名',
               '银行账号',
               '开户银行',
               '联系电话',
               '补偿金额（元）',
               '结算日期'
               //...
           ];
           $field = [
               'index',
               '字段名称1',
               '字段名称2'
               '字段名称3'
               '字段名称4'
               '字段名称5'
               '字段名称6'
               '字段名称7'
               '字段名称8'
               '字段名称9'
               '字段名称10'
               //...
           ];//字段名
           $body = $res;//内容
           $main->daochu('导出表', $head, $field, $body,$time);//调用导出的方法
       }

4. 调用的导出方法（导出时可设置样式）

   public function daochu(string $title, array $head, array $field, array $body, array $time) //字段一一对应
   
       {
           $spreadsheet = new Spreadsheet();
           $worksheet = $spreadsheet->getActiveSheet();
           //设置工作表标题名称--最下角的sheet名称
           $worksheet->setTitle('文档');
   
   
           $worksheet->setCellValueByColumnAndRow(1,1,$title);//传来的表格标题
           $worksheet->mergeCellsByColumnAndRow(1,1,11,1);//合并单元格
           $worksheet->setCellValueByColumnAndRow(1,2,'统计日期：2022-05-20 至 2022-05-21');
           $worksheet->mergeCellsByColumnAndRow(1,2,11,2);//合并统计日期的单元格
           $worksheet->setCellValueByColumnAndRow(4,3,'人员信息');
           $worksheet->mergeCellsByColumnAndRow(4,3,9,3);
   
           //设置头部格式
           $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(33.95);
   
   
           $worksheet->getStyle('A1:K1')->getFont()->setBold(true)->setSize(18);
           $spreadsheet->getActiveSheet()->getStyle('A2:K2')->getFont()->setName('Arial')
               ->setSize(9);
           //设置尾部格式
           $spreadsheet->getActiveSheet()->getRowDimension(count($body)+5)->setRowHeight(26.25);
           $worksheet->setCellValueByColumnAndRow(9,(count($body)+5),'合计:');
           $spreadsheet->getActiveSheet()->setCellValue('J'.(count($body)+5), "=SUM(J5:J".(count($body)+4).")");
   
           $worksheet->mergeCellsByColumnAndRow(1,(count($body)+6),11,(count($body)+6));
           $worksheet->mergeCellsByColumnAndRow(1,(count($body)+7),11,(count($body)+7));
   
           $total = count($body) + 8;
           $worksheet->mergeCellsByColumnAndRow(1,$total,3,$total);
           $worksheet->setCellValueByColumnAndRow(1,$total,'初审人');
           $worksheet->mergeCellsByColumnAndRow(4,$total,6,$total);
           $worksheet->setCellValueByColumnAndRow(4,$total,'复核人');
           $worksheet->mergeCellsByColumnAndRow(7,$total,8,$total);
           $worksheet->setCellValueByColumnAndRow(7,$total,'XX签字');
           $worksheet->mergeCellsByColumnAndRow(9,$total,11,$total);
           $worksheet->setCellValueByColumnAndRow(9,$total,'XX签字');
   
           $worksheet->mergeCellsByColumnAndRow(1,$total+1,3,$total+1);
           $worksheet->mergeCellsByColumnAndRow(4,$total+1,6,$total+1);
           $worksheet->mergeCellsByColumnAndRow(7,$total+1,8,$total+1);
           $worksheet->mergeCellsByColumnAndRow(9,$total+1,11,$total+1);
   
           $worksheet->setCellValueByColumnAndRow(1,$total+1,'年       月       日');
           $worksheet->setCellValueByColumnAndRow(4,$total+1,'年       月       日');
           $worksheet->setCellValueByColumnAndRow(7,$total+1,'年       月       日');
           $worksheet->setCellValueByColumnAndRow(9,$total+1,'年       月       日');
   
           $spreadsheet->getActiveSheet()->getRowDimension($total)->setRowHeight(26.25);
           $spreadsheet->getActiveSheet()->getRowDimension($total+1)->setRowHeight(81.75);
           $spreadsheet->getActiveSheet()->getRowDimension($total+2)->setRowHeight(17.1);
   
           $styleArray = [
               'alignment' => [
                   'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                   'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER //垂直居中
               ],
           ];
           $worksheet->getStyle('J3')->getAlignment()->setWrapText(true);
           $worksheet->getStyle('C')->getAlignment()->setWrapText(true);
           $worksheet->getStyle('J')->getAlignment()->setWrapText(true);
           $worksheet->getStyle('A1')->applyFromArray($styleArray);
           $worksheet->getStyle('A3:K'.($total))->applyFromArray($styleArray);
           $styleArray1 = [
               'alignment' => [
                   'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
               ],
           ];
           $worksheet->getStyle('A'.($total+1).':K'.($total+2))->applyFromArray($styleArray1);
           $worksheet->getStyle('A3:K'.($total+2))->getFont()->setSize(9);
   
           $worksheet->mergeCellsByColumnAndRow(1,$total+2,3,$total+2);
           $worksheet->setCellValueByColumnAndRow(1,$total+2,'打印日期: ' . date('Y-m-d'));
   
           $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(4.33);
           $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(8.57);
           $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(25.3);
           $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(7.03);
           $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(16.33);
           $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(7.17);
           $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(18.67);
           $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(14.33);
           $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10.5);
           $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(9.67);
           $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(8.83);
   
           $styleArray2 = [
               'borders' => [
                   'allBorders' => [
                       'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                       'color' => ['argb' => ' 0xFF000000'],
                   ],
               ],
           ];
   
           $worksheet->getStyle('A3:K'.($total-3))->applyFromArray($styleArray2);
           $worksheet->getStyle('A'.($total).':K'.($total+1))->applyFromArray($styleArray2);
   
           //表头
           //设置单元格内容
           foreach ($head as $k => $v) {
               $col = $k + 1;
               if($k>=3&&$k<=8){
                   $row = 4;
               }else{
                   $row = 3;
                   $worksheet->mergeCellsByColumnAndRow($col,3,$col,4);
               }
               $worksheet->setCellValueByColumnAndRow($col, $row, $v);
           }
   
           $len = 5;
           $j = 0;
           foreach ($body as $k => $v) {
               $row = $k + 5;
               for ($n = 0; $n < count($field); $n++) {
                   $col = $n + 1;
                   $val = $v[$field[$n]];
                   $worksheet->setCellValueByColumnAndRow($col, $row, $val);
               }
               $spreadsheet->getActiveSheet()->getRowDimension($row)->setRowHeight(25);
           }
   
           $filename = "{$title}.xlsx";
           header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
           header('Content-Disposition: attachment;filename="' . $filename . '"');
           header('Cache-Control: max-age=0');
   
           $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
           $writer->save('php://output');
       }

5. 导出样式如下：