PHP 匯出Excel

標籤：style   http   使用   os   io   檔案   資料   for   

在開發一個商城網站的時候，客戶需要一個匯出報表的功能，我使用PHPExcel來實現這個功能。

下載PHPExcel_1.8.0_doc.zip，將解壓後的檔案夾裡的Classes上傳到網站的根目錄下，Classes目錄內的內容如下：

匯出檔案Excel.php

require_once dirname(__FILE__).'/Classes/PHPExcel.php';//引入PHPExcel.....此處略去從資料庫擷取資料的過程，$a為需要匯出的數組......$numArr = 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','AZ');// Create new PHPExcel object$objPHPExcel = new PHPExcel();$arr = array('訂單號','下單時間','城市','地區', '客戶名稱', '收貨人', '聯絡電話', '收貨地址', 'ERP客戶名稱', '物流系統客戶名稱', '活動項目', '品牌', '型號', '顏色', '物流系統型號', '訂貨量', '單價', '代收貨款', '紅包', '價保返利', '運費', '實收金額', '付款條件', '訂單來源', '上遊廠商', '是否在倉', '快遞面單號', '訂單狀態', '確認時間', '末次狀態確認時間', '描述', '對應業務', '對應客服', '商家留言', '下單摘要', '業務員', '連絡方式');

// 輸出標題echo date('H:i:s') , " Add some data" , EOL;//設定換行$objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);$objPHPExcel->getActiveSheet()->getStyle('Y')->getAlignment()->setWrapText(true);//設定相應列的寬度$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(15);$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(15);$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(40);$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(15);$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(15);//輸出第一行$objPHPExcel->setActiveSheetIndex(0)    ->setCellValue('A1', $arr[0])            ->setCellValue('B1', $arr[1])            ->setCellValue('C1', $arr[2])            ->setCellValue('D1', $arr[3])            ->setCellValue('E1', $arr[4])            ->setCellValue('F1', $arr[5])            ->setCellValue('G1', $arr[6])            ->setCellValue('H1', $arr[7])            ->setCellValue('I1', $arr[8])            ->setCellValue('J1', $arr[9])    ->setCellValue('K1', $arr[10])    ->setCellValue('L1', $arr[11]);//輸出內容for($i=0;$i<count($a);$i++){$objPHPExcel->setActiveSheetIndex(0)->setCellValue($numArr[0].($i+2), $a[$i]['order_sn'])->setCellValue($numArr[1].($i+2), $a[$i]['add_time'])->setCellValue($numArr[2].($i+2), $a[$i]['city'])->setCellValue($numArr[3].($i+2), $a[$i]['region_name'])->setCellValue($numArr[4].($i+2), $a[$i]['company'])->setCellValue($numArr[5].($i+2), $a[$i]['consignee'])->setCellValue($numArr[6].($i+2), $a[$i]['mobile'])->setCellValue($numArr[7].($i+2), $a[$i]['address'])->setCellValue($numArr[8].($i+2), '')->setCellValue($numArr[9].($i+2), '')->setCellValue($numArr[10].($i+2), '')->setCellValue($numArr[11].($i+2), $a[$i]['brand_name']);}// Rename worksheetecho date('H:i:s') , " Rename worksheet" , EOL;$dirName = date("Ymd");//目錄名$fileName = date("YmdHis");//檔案名稱$objPHPExcel->getActiveSheet()->setTitle($fileName);// Set active sheet index to the first sheet, so Excel opens this as the first sheet$objPHPExcel->setActiveSheetIndex(0);// Save Excel 2007 fileecho date('H:i:s') , " Write to Excel2007 format" , EOL;$callStartTime = microtime(true);if(!opendir('../excel/'.$dirName)){mkdir('../excel/'.$dirName);}$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');$objWriter->save('../excel/'.$dirName.'/'.$fileName.'.xlsx');$end = getCurrentTime();$spend = $end-$begin;if ($spend > 30){echo '<script>if(confirm("執行逾時！")){ window.history.back(-1);}</script>';exit;}header('Location:http://'.$_SERVER['HTTP_HOST'].'/excel/'.$dirName.'/'.$fileName.'.xlsx');