★ FEATURED ARTICLE 用PHPSpreadsheet 封装的导出Excel方法支持导出无限列安装扩展库仓库改为国内的composer.json文件repositories:[{name:packagist,type:composer,url:https://mirrors.aliyun.com/composer/}]composerrequire symfony/cachecomposerrequire phpoffice/phpspreadsheet封装类useDoctrine\Common\Cache\FilesystemCache;usePhpOffice\PhpSpreadsheet\Cell\DataType;usePhpOffice\PhpSpreadsheet\Settings;usePhpOffice\PhpSpreadsheet\Shared\Drawing;usePhpOffice\PhpSpreadsheet\Spreadsheet;usePhpOffice\PhpSpreadsheet\Style\Alignment;usePhpOffice\PhpSpreadsheet\Style\Border;usePhpOffice\PhpSpreadsheet\Style\Color;usePhpOffice\PhpSpreadsheet\Style\Fill;usePhpOffice\PhpSpreadsheet\Writer\Xlsx;useSymfony\Component\Cache\Adapter\FilesystemAdapter;useSymfony\Component\Cache\Psr16Cache;classCommonFn{/** * EXCEL导出 Spreadsheet版支持php8 * param [string] $file_name 保存的文件名及表格工作区名不加excel后缀名 * param [array] $fields 二维数组 * param [array] $list 数据 * param [array] $options e.g. [ * download_items[需要下载图片的字段项], * string_items[转成字符避免科学计数法转换的字段], * ] * return [string] * Description * example * author Tj * date 2026-10-26 */staticpublicfunctionexportExcelData($file_name,$fields,$list,$options[download_items[],string_items[],]){// 初始化$excelColumnNumHeaderA;//表头列字母,初始化在循环中会自动递增$excelColumnNumA;//表头列字母,初始化在循环中会自动递增// 内存和执行时间设置避免数据量大时导出失败ini_set(memory_limit,256M);ini_set(max_execution_time,600);// 创建一个样式数组$styleArrayarray(bordersarray(allBordersarray(borderStyleBorder::BORDER_THIN,// 边框样式colorarray(rgb000000),// 边框颜色),),);// 必须在创建 $objectSpreadsheet 对象之前调用$filesystemAdapternewFilesystemAdapter(phpspreadsheet,0,$_SERVER[DOCUMENT_ROOT]./temp/phpspreadsheet_cache);$psr16CachenewPsr16Cache($filesystemAdapter);Settings::setCache($psr16Cache);// 表格对象$objectSpreadsheetnewSpreadsheet();$objectSpreadsheet-setActiveSheetIndex(0);$objectSpreadsheet-getActiveSheet()-setTitle($file_name);//设置表格头的输出// 只要selected为1的$filterFieldsarray_filter($fields,function($row){if(isset($row[selected])){return$row[selected]1;}});$fieldsarray_values($filterFields);// 数组键名重置成连续的$newFields$fields;// 锁定表头$objectSpreadsheet-getActiveSheet()-freezePane(A2);// 表头设置foreach($newFieldsas$fkey$fval){$excelColumnNumHeaderself::int_to_chr($fkey);// A-ZZZ$objectSpreadsheet-getActiveSheet()-setCellValue($excelColumnNumHeader.1,$fval[value]);}// 设置行高$objectSpreadsheet-getActiveSheet()-getRowDimension(1)-setRowHeight(18);/**********整行操作优化性能************* */// 设置表头加粗$objectSpreadsheet-getActiveSheet()-getStyle(A1:.$excelColumnNumHeader.1)-getFont()-setBold(true);// 画边框$objectSpreadsheet-getActiveSheet()-getStyle(A1:.$excelColumnNumHeader.1)-applyFromArray($styleArray);// 背景色$objectSpreadsheet-getActiveSheet()-getStyle(A1:.$excelColumnNumHeader.1)-getFill()-setFillType(Fill::FILL_SOLID);$objectSpreadsheet-getActiveSheet()-getStyle(A1:.$excelColumnNumHeader.1)-getFill()-getStartColor()-setARGB(E0EEE0);/**********整行操作END************* *///订单数据$coverPicArr[];foreach($listas$itemKey$item){foreach($newFieldsas$sfKey$sfVal){$excelColumnNumself::int_to_chr($sfKey);// A-ZZZ$excelRowNum$itemKey2;// 单元格宽if(isset($sfVal[width])$sfVal[width]0){$objectSpreadsheet-getActiveSheet()-getColumnDimension($excelColumnNum)-setWidth($sfVal[width]);}else{$objectSpreadsheet-getActiveSheet()-getColumnDimension($excelColumnNum)-setWidth(20);}// 图片if(isset($options)in_array($sfVal[key],$options[download_items])){$local_temp_pic_prev./temp/export/excel_pic/;$pic_link$item[$sfVal[key]];if(is_array($pic_link)){$pic_link$pic_link[0];}$temp_picself::getFilename($pic_link);$local_pic_path$local_temp_pic_prev.$temp_pic;if(!file_exists($local_pic_path)){$temp_picself::download($pic_link,$local_temp_pic_prev);$local_pic_path$local_temp_pic_prev.$temp_pic;}if(file_exists($local_pic_path)){$imgnewDrawing();$img-setPath($local_pic_path);//写入图片路径$img-setHeight(100);//写入图片高度$img-setWidth(100);//写入图片宽度$img-setOffsetX(5);//写入图片在指定格中的X坐标值$img-setOffsetY(5);//写入图片在指定格中的Y坐标值$img-setRotation(0);//设置旋转角度$img-getShadow()-setVisible(true);$img-getShadow()-setDirection(50);$img-setCoordinates($excelColumnNum.$excelRowNum);//设置图片所在表格位置$img-setWorksheet($objectSpreadsheet-getActiveSheet());//把图片写到当前的表格中$coverPicArr[]$local_pic_path;//记录临时图片//设置列宽$objectSpreadsheet-getActiveSheet()-getColumnDimension($excelColumnNum)-setWidth(16);// 设置行高$objectSpreadsheet-getActiveSheet()-getRowDimension($excelRowNum)-setRowHeight(85);}}else{// 文字// 转字符型字段if(isset($options)in_array($sfVal[key],$options[string_items])){$objectSpreadsheet-getActiveSheet()-setCellValueExplicit($excelColumnNum.$excelRowNum,$item[$sfVal[key]],DataType::TYPE_STRING);}else{$objectSpreadsheet-getActiveSheet()-setCellValue($excelColumnNum.$excelRowNum,$item[$sfVal[key]]);}}}}/**********一次设置整个数据区优化性能************* */// 文字垂直居中$objectSpreadsheet-getActiveSheet()-getstyle(A2:.$excelColumnNum.$excelRowNum)-getAlignment()-setVertical(Alignment::VERTICAL_CENTER);// 画边框一次设置整个数据区优化性能$objectSpreadsheet-getActiveSheet()-getStyle(A2:.$excelColumnNum.$excelRowNum)-applyFromArray($styleArray);/**********一次设置整个数据区END************* *///生成表格文件ob_end_clean();//一定要加否则打开excel后报告部分内容有问题是否让我们修复尽量尝试恢复ob_start();//设置输出文件名及格式header(Content-Disposition:attachment;filename.urlencode($file_name).-.date(YmdHis)..xlsx);header(Content-Type:application/vnd.ms-excel;charsetutf-8);//导出.xls格式的话使用Excel5,若是想导出.xlsx需要使用Excel2007$objWriternewXlsx($objectSpreadsheet);$objWriter-save(php://output);//删除临时图片foreach($coverPicArras$coverPicItem){unlink($coverPicItem);}ob_end_flush();$objectSpreadsheet-disconnectWorksheets();unset($objectSpreadsheet);// 销毁exit;//一定要加exit;否则打开excel后报告部分内容有问题是否让我们修复尽量尝试恢复}}/** * 下载远程图片 * param unknown $url * param string $path * return unknown */staticprivatefunctiondownload($url,$pathimages/){if(!file_exists($path)){mkdir($path,0755,true);}$chcurl_init();curl_setopt($ch,CURLOPT_URL,$url);curl_setopt($ch,CURLOPT_RETURNTRANSFER,1);curl_setopt($ch,CURLOPT_CONNECTTIMEOUT,30);curl_setopt($ch,CURLOPT_SSL_VERIFYPEER,false);// 信任任何证书$filecurl_exec($ch);curl_close($ch);$filenameself::getFilename($url);$resourcefopen($path.$filename,a);fwrite($resource,$file);fclose($resource);return$filename;}/** * * param string $url * return string */staticprivatefunctiongetFilename($url){returnpathinfo($url,PATHINFO_BASENAME);}/** * 导出信息处理 * return unknown[] */staticprivatefunctiondataFields($fields){$newFields[];foreach($fieldsas$item){if(isset($item[selected])$item[selected]1){$newFields[$item[key]]$item[value];}}return$newFields;}/** * Notes:将整数转为excel对应的列标 * Function int_to_chr * param $index 0开始的索引号 * param $start * return string * Author tj * Date 2025/04/09 */staticpublicfunctionint_to_chr($index,$start65){$str;if($index26){$les$index%26;$indexintval($index/26);$str.self::int_to_chr($index-1);$str.chr($start$les);return$str;}returnchr($start$index).$str;}}
阅读完成 · 觉得有帮助？
