使用PHPExcel類庫將資料匯出為excel文檔

標籤：des   style   http   io   ar   color   os   使用   sp   

下載PHPExcel類庫，http://phpexcel.codeplex.com/，解壓後在Class中找到PHPExcel檔案夾和PHPExcel.php檔案，拷貝到自己的項目代碼中，以下是匯出為excel檔案的方法

<?phpfunction export_data() {require_once(‘PHPExcel.php‘);// 文檔名稱$filename = ‘filename‘;// 建立 PHPExcel 對象$objPHPExcel = new PHPExcel();// 設定產生的文件屬性$objPHPExcel->getProperties()->setCreator("http://www.jianbin.info")->setLastModifiedBy("http://www.jianbin.info")->setTitle("http://www.jianbin.info")->setSubject("http://www.jianbin.info")->setDescription("http://www.jianbin.info")->setKeywords("http://www.jianbin.info")->setCategory("http://www.jianbin.info");$col_number = 65;$col_letter = chr($col_number);$row_number = 1;/**$table 資料格式array(‘head‘ => array(欄位1,欄位2,...,欄位n),‘body‘ => array(‘行1‘ => (資料1,資料2,...,資料n),‘行2‘ => (資料1,資料2,...,資料n),...‘行n‘ => (資料1,資料2,...,資料n),)) */foreach ($table[‘head‘] as $key=>$val){$objPHPExcel->setActiveSheetIndex(0)->setCellValue("{$col_letter}{$row_number}", $val);$objPHPExcel->getActiveSheet()->getColumnDimension("{$col_letter}")->setAutoSize(true);// 自動列表設定對中文不支援$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getAlignment()-> setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getFill()->getStartColor()->setARGB(‘FF58ACFA‘);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getTop()->getColor()->setARGB(‘FFFF0000‘);$col_number++;$col_letter = chr($col_number);}$row_number++;// 構造bodyforeach ($table[‘body‘] as $row){$col_number = 65;$col_letter = chr($col_number);foreach ($row as $val) {$objPHPExcel->setActiveSheetIndex(0)->setCellValue("{$col_letter}{$row_number}", $val);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getAlignment()-> setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getTop()->getColor()->setARGB(‘FFFF0000‘);$col_number++;$col_letter = chr($col_number);}$row_number++;}// 重新命名工作表$objPHPExcel->getActiveSheet()->setTitle($filename);// 預設開啟工作表1$objPHPExcel->setActiveSheetIndex(0);// 設定header輸出到用戶端header(‘Content-Type: application/vnd.ms-excel‘);header(‘Content-Disposition: attachment;filename="‘.$filename.‘.xls"‘);header(‘Cache-Control: max-age=0‘);// If you‘re serving to IE 9, then the following may be neededheader(‘Cache-Control: max-age=1‘);// If you‘re serving to IE over SSL, then the following may be neededheader (‘Expires: Mon, 26 Jul 1997 05:00:00 GMT‘); // Date in the pastheader (‘Last-Modified: ‘.gmdate(‘D, d M Y H:i:s‘).‘ GMT‘); // always modifiedheader (‘Cache-Control: cache, must-revalidate‘); // HTTP/1.1header (‘Pragma: public‘); // HTTP/1.0$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5‘);$objWriter->save(‘php://output‘);exit;}

匯出後的檔案開啟效果如下：

使用PHPExcel類庫將資料匯出為excel文檔