站內搜尋

Tuesday, July 20, 2021

[PHP] 如何將網頁表格(table)轉成Excel的xlsx檔 (使用PHPExcel)

先下載PHPExcel套件(這篇是用1.7.X版示範),然後程式碼如下...
//程式碼大概就下面七行 ob_start(); include_once('PHPExcel.php'); $obj = new ExcelService(); $html_table = '<table><tr><td>123中文</td></tr></table>'; //要轉成Excel的表格 $html_table = mb_convert_encoding(html_entity_decode($html_table), 'HTML-ENTITIES', 'UTF-8'); //避免中文變成亂碼 $excelFile = $obj->generateExcel($html_table, 'myexcel', 'my_excel_folder'); //產生的Excel的檔名 並指定要放在哪個資料夾 ob_end_clean(); header('Location: my_excel_folder/myexcel.xlsx'); //固定的Class 可以不用改 class ExcelService { public function generateExcel($content, $fname, $tmp_path = '') { if ($fname == '') $fname = date('YmdHis'); if ($tmp_path == '') $tmp_path = 'temp'; $filename = $fname . '.xlsx'; $htmlname = $fname . '.html'; $excelFile = $tmp_path . '/' . $filename; $htmlfile = $tmp_path . '/' . $htmlname; file_put_contents($htmlfile, $content); $objReader = new PHPExcel_Reader_HTML; $objPHPExcel = $objReader->load($htmlfile); $objPHPExcel->getProperties()->setCreator($uid); $objPHPExcel->getProperties()->setLastModifiedBy($uid); $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Document"); $objPHPExcel->getProperties()->setSubject("XLSX Report"); $objPHPExcel->getProperties()->setDescription("XLSX report document for Office"); //下面這段是設定stlye與格式 不設定也可以 $style = array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, ) ); $objPHPExcel->getActiveSheet()->getDefaultStyle()->applyFromArray($style); //設定全部置左 $objPHPExcel->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //設定全部格式為文字 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $str_col = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn(); //eg.J $str_dim = $objPHPExcel->setActiveSheetIndex(0)->calculateWorksheetDimension(); //eg.A1:J21 foreach (range('A', $str_col) as $columnID) { $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true); } $objPHPExcel->getActiveSheet()->getStyle($str_dim)->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->removeRow(1); //移除第一列空白 $objWriter->save($excelFile); unlink($htmlfile); if (file_exists($excelFile)) { return $excelFile; } return false; } }

No comments:

Post a Comment