PhpSpreadsheet处理表格2
<!DOCTYPE html> <html lang="zh-CN"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>ERP商品代码生成</title> <link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet"> <style type="text/css"> .mt16 {margin-top: 12px;} </style> </head> <body> <div class="container"> <div class="row"> <div class="col-lg-6"> <div class="input-group mt16"> <span class="input-group-addon">商品代码</span> <input type="text" name="scode" class="form-control" placeholder="商品代码，如BM111WY"> </div> </div> <div class="col-lg-6"> <div class="input-group mt16"> <span class="input-group-addon">商品名称</span> <input type="text" name="sname" class="form-control" placeholder="商品名称及商品简称"> </div> </div> </div> <div class="row"> <div class="col-lg-6"> <div class="input-group mt16"> <span class="input-group-addon">规格代码</span> <input type="text" name="specode" class="form-control" placeholder="规格代码，如BM111WY01S"> </div> </div> <div class="col-lg-6"> <div class="input-group mt16"> <span class="input-group-addon">规格名称</span> <input type="text" name="spename" class="form-control" placeholder="规格名称，如XX卫衣 白色 S"> </div> </div> </div> <div class="input-group mt16"> <span class="input-group-addon">颜色</span> <input type="text" name="specolor" class="form-control" placeholder="颜色，以空格隔开，如白色 红色 黑色"> </div> <div class="input-group mt16"> <span class="input-group-addon">颜色对应的编号</span> <input type="text" name="specolorid" class="form-control" placeholder="颜色编号，以空格隔开，如01 16 05"> </div> <div class="input-group mt16"> <span class="input-group-addon">尺码</span> <input type="text" name="spesize" class="form-control" placeholder="尺码，以空格隔开，如S M L XL XXL"> </div> <button type="button" class="btn btn-success btn-block mt16">确定提交</button> <table class="table table-bordered mt16"> <thead> <tr> <th>商品代码</th> <th>规格代码</th> <th>规格名称</th> <th>商品名称</th> <th>商品简称</th> </tr> </thead> <tbody> </tbody> </table> <div class="text-center mt16">Powered by 李佳，2019.9</div> </div> <script src="https://cdn.bootcss.com/jquery/3.3.1/jquery.min.js"></script> <script src="https://cdn.bootcss.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <script type="text/javascript"> var shoparr = []; $(":button").click(function(){ var scode = $("input[name='scode']").val(); var sname = $("input[name='sname']").val(); var specode = $("input[name='specode']").val(); var spename = $("input[name='spename']").val(); var specolor = $("input[name='specolor']").val(); var specolorid = $("input[name='specolorid']").val(); var spesize = $("input[name='spesize']").val(); //颜色数组 var specolorarr = specolor.split(/\s+/g); //颜色编号数组 var specoloridarr = specolorid.split(/\s+/g); //尺码数组 var spesizearr = spesize.split(/\s+/g); //清空表格 $("table tbody").empty(); for (var i = 0; i < specolorarr.length; i++) { for (var j = 0; j < spesizearr.length; j++) { shoparr.push([scode,specode+specoloridarr[i]+spesizearr[j],spename+" "+specolorarr[i]+spesizearr[j],sname]); $("table tbody").append("<tr><td>"+scode+"</td><td>"+specode+specoloridarr[i]+spesizearr[j]+"</td><td>"+spename+" "+specolorarr[i]+spesizearr[j]+"</td><td>"+sname+"</td><td>"+sname+"</td></tr>"); } } if (shoparr.length>1 && $("button").length<2) { $("table").after("<button type='button' class='btn btn-success btn-block mt16' id='getxls'>下载表格</button>"); } }); $("body").on('click','#getxls',function(){ DownLoadFile({ url:'{:getHostDomain()}{:addonUrl('arr2xls')}', data:{sparr:shoparr} }); }); var DownLoadFile = function (options) { var config = $.extend(true, { method: 'post' }, options); var $iframe = $('<iframe id="down-file-iframe" />'); var $form = $('<form target="down-file-iframe" method="' + config.method + '" />'); $form.attr('action', config.url); for (var key in config.data) { $form.append('<input type="hidden" name="' + key + '" value="' + config.data[key] + '" />'); } $iframe.append($form); $(document.body).append($iframe); $form[0].submit(); $iframe.remove(); } </script> </body> </html>
前端提交数组，由于通过AJAX提交返回的只能是字符串，不能流类型，所以这里临时构建一个Iframe，通过iframe提交form，然后删除临时Iframe，实现流文件接收。
这里是在 thinkphp里 引入类
use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
处理函数
//由数组生成表格文件 public function arr2xls(){ $sparr = input('post.sparr'); $sparr = explode(',',$sparr); $sp2arr = array_chunk($sparr,4); //创建表格 //$spreadsheet = new Spreadsheet(); //通过模板生成表格 $inputFileName = ADDON_PATH.'webact/static/SpMoban.xlsx'; $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName); $sheet = $spreadsheet->getActiveSheet(); //设置第一行小标题 // $k = 1; // $sheet->setCellValue('A'.$k, '商品代码（必填）'); // $sheet->setCellValue('B'.$k, '规格代码'); // $sheet->setCellValue('C'.$k, '规格名称'); // $sheet->setCellValue('D'.$k, '商品名称'); // $sheet->setCellValue('E'.$k, '商品简称'); for ($i=0; $i < count($sp2arr); $i++) { $sheet->setCellValue('A'.($i+2), $sp2arr[$i][0]); $sheet->setCellValue('B'.($i+2), $sp2arr[$i][1]); $sheet->setCellValue('C'.($i+2), $sp2arr[$i][2]); $sheet->setCellValue('D'.($i+2), $sp2arr[$i][3]); $sheet->setCellValue('E'.($i+2), $sp2arr[$i][3]); } //设置列宽 $spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true); header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//告诉浏览器输出07Excel文件 header("Content-Disposition: attachment;filename=".$sp2arr[0][0].".xlsx");//告诉浏览器输出浏览器名称 header("Cache-Control: max-age=0");//禁止缓存 $writer = new Xlsx($spreadsheet); //$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'xlsx'); $writer->save('php://output'); }
