/**
* @param array $dataList 数据列表,为数字索引
* @param string $title 导出文件名
* @param string $tpl excel的模板文件
* @param int $begin 模板的数据起始行数,从1开始计数除去header的行
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public static function CustomSaveExcel($dataList=[],$title,$tpl='',$begin=0){
if(!$tpl||!is_file($tpl)){
throw new \Exception("模板文件不存在!");
}
//引入核心文件
ini_set('memory_limit','1024M');
// 要读取的文件的路径
$spreadSheet = IOFactory::load($tpl);
$letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','ZZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV');
$spreadSheet->setActiveSheetIndex(0);
foreach ($dataList as $key => $row) {
$newobj = $spreadSheet->setActiveSheetIndex(0);
foreach ($row as $rowInex => $rowValue) {
$index = $letter[$rowInex].($begin);
static::dealVal($rowValue);
if(is_numeric($rowValue) && !is_float($rowValue) && !is_double($rowValue) && strlen($rowValue)>10){
$newobj->setCellValueExplicit($index, $rowValue, DataType::TYPE_STRING);
}
else{
$newobj->setCellValue($index, $rowValue);
}
$newobj->getStyle($index)->getAlignment()->setWrapText(true);
}
$begin++;
}
ob_end_clean();
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: inline;filename=\"".$title.".xlsx\"");
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($spreadSheet, 'Xlsx');
$objWriter->save('php://output');
}
public static function dealVal(&$val){
if(strtolower($val)===null||(is_numeric($val)&&$val == 0)){
$val = '/';
}
}
用PhpSpreadsheet读取xlsx表格模板进行数据导出
最新推荐文章于 2024-06-30 17:05:12 发布