|
正文开始 php phpoffice 下载excel
支持超过26位 表头
前端ajax执行post后下载文件。
####表头生成方法
~~~
//数字转列名
function stringFromColumnIndex($pColumnIndex = 0)
{
// Using a lookup cache adds a slight memory overhead, but boosts speed
// caching using a static within the method is faster than a class static,
// though it's additional memory overhead
static $_indexCache = array();
if (!isset($_indexCache[$pColumnIndex])) {
// Determine column string
if ($pColumnIndex < 26) {
$_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
} elseif ($pColumnIndex < 702) {
$_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) . chr(65 + $pColumnIndex % 26);
} else {
$_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) . chr(65 + ((($pColumnIndex - 26) % 676) / 26)) . chr(65 + $pColumnIndex % 26);
}
}
return $_indexCache[$pColumnIndex];
}
~~~
####使用demo：
~~~
composer require加上
："phpoffice/phpspreadsheet": "^1.2",
~~~
####头部引入插件：
~~~
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
~~~
####业务开始 [thinkphp5+]
~~~
$head = ['字段1', '字段2', ......];
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//设置表头：
foreach ($head as $n=>$tr_) {
$sheet->setCellValue($stringFromColumnIndex($n).'1' , $tr_);
}
foreach ($list as $key => $item) { //循环设置单元格：
//$key+2,因为第一行是表头，所以写到表格时 从第二行开始写
foreach ($head as $n=>$tr_) {
$sheet->setCellValue($stringFromColumnIndex($n). ($key + 2), $item[$tr_]);
$spreadsheet->getActiveSheet()->getColumnDimension(strtoupper($stringFromColumnIndex($n)))->setWidth(80); //固定列宽
}
}
$filename = 'explort_customer'.date('ymd',time()).'.xls';
ob_end_clean();//一定要提前清空之前的内容
ob_start();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'. $filename .'"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
//删除清空：
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
$xlsData = ob_get_contents();
ob_end_clean();
$this->success('success', '', ['filename' => $filename, 'file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsData)]);
~~~
前端参考我的插件：[lrEle/lrBox]
~~~
lrEle.postAndDone({
url: '/addons/customer/user/exportExcel',
postData: {
'自定义提交field': 'value',
},
successKey: 'code',
successVal: '1',
successFunc: function (res) {
lrBox.noLoading();
var downContent = $('');
var downLoadBtn = $("download");
downLoadBtn.attr("href", res.data.file);
downLoadBtn.attr("download", res.data.filename);
downContent.append(downLoadBtn);
lrBox.msgView('download', downContent, 200);
},
errFunc: function (res) {
lrBox.noLoading();
lrBox.msgTisf(res.msg);
}
});
~~~
正文结束 |
| PHP接口(interface)和抽象类(abstract) | tp5 thinkphp5 动态配置model模型的数据库连接 |