正文

PhpSpreadsheet处理表格2

IP云
IP云V管理员/0 阅读
10/04
小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

<!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');
}