用PHPExcel 封裝的導出方法，支持導出無限列
避免PHPExcel_Exception Invalid cell coordinate [1 異常錯誤

/*** EXCEL導出* @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 2024-12-26*/static public function exportExcelData($file_name,$fields,$list,$options = ['download_items' => [],'string_items' => [],]) {ini_set("memory_limit", "128M");ini_set("max_execution_time",  "600");// 創建一個樣式數組$styleArray = array('borders' => array('allborders' => array('style' => \PHPExcel_Style_Border::BORDER_THIN, // 邊框樣式'color' => array('rgb' => '000000'), // 邊框顏色),),);$objectPHPExcel = new \PHPExcel();$objectPHPExcel->setActiveSheetIndex(0);$objectPHPExcel->getActiveSheet()->setTitle($file_name);//設置表格頭的輸出$newFields = $fields;// 鎖定表頭$objectPHPExcel->getActiveSheet()->freezePane('A2');// 表頭設置foreach ($newFields as $fkey => $fval) {if ($fval['selected'] == 1) {$excelColumnNumHeader = self::int_to_chr($fkey);$objectPHPExcel->setActiveSheetIndex()->setCellValue($excelColumnNumHeader . '1', $fval['value']);// 設置表頭加粗$objectPHPExcel->getActiveSheet()->getStyle($excelColumnNumHeader . '1')->getFont()->setBold(true);// 邊框$objectPHPExcel->getActiveSheet()->getStyle($excelColumnNumHeader . '1')->applyFromArray($styleArray);// 背景色$objectPHPExcel->getActiveSheet()->getStyle($excelColumnNumHeader . '1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);$objectPHPExcel->getActiveSheet()->getStyle($excelColumnNumHeader . '1')->getFill()->getStartColor()->setARGB('E0EEE0');// 設置行高$objectPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(18);} else {unset($newFields[$fkey]);}// 畫邊框 }//訂單數據$serviceFields = array_column($newFields, 'key');$coverPicArr = [];foreach ($list as $itemKey => $item) {foreach ($serviceFields as $sfKey => $sfVal) {$excelColumnNum = self::int_to_chr($sfKey); // A-ZZZ$excelRowNum = $itemKey + 2;// 文字垂直居中$objectPHPExcel->getActiveSheet()->getstyle($excelColumnNum . $excelRowNum)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);// 畫邊框 $objectPHPExcel->getActiveSheet()->getStyle($excelColumnNum . $excelRowNum)->applyFromArray($styleArray);// 單元格寬$objectPHPExcel->getActiveSheet()->getColumnDimension($excelColumnNum)->setWidth(20);// 圖片if (isset($options) && in_array($sfVal, $options['download_items'])) {$local_temp_pic_prev = './temp/export/excel_pic/';$pic_link = $item[$sfVal];if (is_array($pic_link)) {$pic_link = $pic_link[0];}$temp_pic = self::getFilename($pic_link);$local_pic_path = $local_temp_pic_prev . $temp_pic;if (!file_exists($local_pic_path)) {$temp_pic = self::download($pic_link, $local_temp_pic_prev);$local_pic_path = $local_temp_pic_prev . $temp_pic;}if (file_exists($local_pic_path)) {$img = new \PHPExcel_Worksheet_Drawing();$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($objectPHPExcel->getActiveSheet()); //把圖片寫到當前的表格中$coverPicArr[] = $local_pic_path; //記錄臨時圖片//設置列寬$objectPHPExcel->getActiveSheet()->getColumnDimension($excelColumnNum)->setWidth(16);// 設置行高$objectPHPExcel->getActiveSheet()->getRowDimension($excelRowNum)->setRowHeight(85);}} else {// 文字// 轉字符型字段if (isset($options) && in_array($sfVal, $options['string_items'])) {$objectPHPExcel->setActiveSheetIndex()->setCellValueExplicit($excelColumnNum . $excelRowNum, $item[$sfVal], \PHPExcel_Cell_DataType::TYPE_STRING);} else {$objectPHPExcel->setActiveSheetIndex()->setCellValue($excelColumnNum . $excelRowNum, $item[$sfVal]);}}}}//生成表格文件ob_end_clean(); //一定要加否則打開excel后報告部分內容有問題，是否讓我們修復盡量嘗試恢復ob_start();//設置輸出文件名及格式header('Content-Disposition:attachment;filename="' . urlencode($file_name) . '-' . date("YmdHis") . '.xlsx"');header('Content-Type:application/vnd.ms-excel;charset=utf-8');//導出.xls格式的話使用Excel5,若是想導出.xlsx需要使用Excel2007$objWriter = \PHPExcel_IOFactory::createWriter($objectPHPExcel, 'Excel2007');$objWriter->save('php://output');//刪除臨時圖片foreach ($coverPicArr as $coverPicItem) {unlink($coverPicItem);}ob_end_flush();exit; //一定要加exit;否則打開excel后報告部分內容有問題，是否讓我們修復盡量嘗試恢復}/*** 下載遠程圖片* @param unknown $url* @param string $path* @return unknown*/static private function download($url, $path = 'images/'){if (!file_exists($path)) {mkdir($path, 0755, true);}$ch = curl_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); // 信任任何證書$file = curl_exec($ch);curl_close($ch);$filename = self::getFilename($url);$resource = fopen($path . $filename, 'a');fwrite($resource, $file);fclose($resource);return $filename;}/*** * @param string $url* @return string*/static private function getFilename($url){return pathinfo($url, PATHINFO_BASENAME);}/*** 導出信息處理* @return unknown[]*/static private function dataFields($fields){$newFields = [];foreach ($fields as &$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*/static public function int_to_chr($index, $start = 65){$str = '';if ($index >= 26) {$les = $index % 26;$index = intval($index / 26);$str .= self::int_to_chr($index - 1);$str .= chr($start + $les);return $str;}return chr($start + $index) . $str;}