laravel导入导出Excel操作

官方文档&＃xff1a;

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

Excel导入&＃xff1a;

1. composer安装maatwebsite/excel包

   composer require maatwebsite/excel

2. 创建导入类

   php artisan make:import FilesInfoImport

3. 打开文件FilesInfoImport&＃xff0c;按照以下格式写入

   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;/*** &＃64;param array $row** &＃64;return \Illuminate\Database\Eloquent\Model|null*/public function model(array $row){return new FilesInfo([//FilesInfo为文件表的Model名称&＃xff08;自己新建&＃xff09;&＃xff0c;正常引用你所导入的表的Model]);}
}


4. 控制器方法

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

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

   导入


6. js

   $(&＃39;#import_btn&＃39;).on(&＃39;click&＃39;, function () {var type &＃61; $(this).data(&＃39;type&＃39;);active[type] ? active[type].call(this) : &＃39;&＃39;;
});upload.render({elem: &＃39;#import_btn&＃39;,url: &＃39;/admin/filesInfoImport&＃39; //此处配置你自己的上传接口即可,accept: &＃39;file&＃39; //普通文件,field: "file",exts:&＃39;xls|xlsx|csv&＃39; //允许上传的类型,before: function(obj){ //obj参数包含的信息&＃xff0c;跟 choose回调完全一致&＃xff0c;可参见上文。layui.layer.load();this.data&＃61;{//传给后台的参数}},done: function(res){if(res.code&＃61;&＃61;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(&＃39;上传失败,请重新上传&＃39;);}});

注意&＃xff1a;

如果导入后&＃xff0c;无法访问该文件&＃xff0c;是访问地址的问题&＃xff1f;&＃xff1f;

        1.需要建立软连接命令&＃xff1a;php artisan storage:link

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

        config/filesystems.php下的

        &＃39;root&＃39; &＃61;> storage_path(&＃39;app&＃39;),改为&＃39;root&＃39; &＃61;> storage_path(&＃39;app/public&＃39;),

本地能正常上传&＃xff0c;线上服务器上传失败

报错&＃xff1a;

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

需要在服务器项目文件夹内运行&＃xff1a;sudo chmod -R 777  ***&＃xff08;文件地址&＃xff09;

给storage文件夹设置777权限

Excel导出&＃xff1a;

1. html

   导出表

2. js

   $(&＃39;#export_btn&＃39;).on(&＃39;click&＃39;, function () {window.open("/admin/zdjbExportZf?year&＃61;" &＃43; $("#year").val()&＃43; &＃39;&IDCard&＃61;&＃39;&＃43;$(".search_IDCard").val())
});

3. 控制器

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

4. 调用的导出方法&＃xff08;导出时可设置样式&＃xff09;

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

5. 导出样式如下&＃xff1a;