http://plus-now.com/?p=428
最近找到一個不錯的套件PHPExcel,這個套件解決我資料匯出的問題,以往我在匯出資料都是採用CSV檔,此種方法最簡單,只要用”逗號”隔開就可輕易的匯出檔案,但此種方法也有著很多的缺點,以下列舉幾個我比較常發生的缺點:
1.只能用於BIG5編碼
2.數字前面為0,用excel 開啟0會自動消失
3.如果是UTF8匯出,在轉成BIG5時,容易找不到對應的字元,而產生亂碼
4.匯出的內容有逗號需全部轉為全型,否則檔案的欄位會亂掉
說了這麼多缺點,匯出成CSV真的不好嗎?其實見人見智啦!如果使用時,能避開容易出問題的地方,其實CSV又快又簡單,廢話說了一堆,回到正題吧!
PHPExcel官網:http://www.codeplex.com/PHPExcel
PHPExcel下載:http://phpexcel.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=10717
如果在windows下載下來就可直接使用,在linux還需要安裝ZipArchive,僅提供CentOS安裝方法請參考:
[Linux]CentOS解決Fatal error: Class ‘ZipArchive’ not found in問題
PHPExcel匯出設定檔說明如下:(此檔案需放在Classes裡面)
<?PHP
include ‘PHPExcel.php’;
/** PHPExcel_Writer_Excel2007 */
//include ‘PHPExcel/Writer/Excel2007.php’;
/** Error reporting */
error_reporting(E_ALL);
/** PHPExcel */
require_once ‘../Classes/PHPExcel.php’;
/** PHPExcel_IOFactory */
require_once ‘../Classes/PHPExcel/IOFactory.php’;objPHPExcel = new PHPExcel();
objPHPExcel->setActiveSheetIndex(0);//合併儲存隔
objPHPExcel->getActiveSheet()->mergeCells(‘A1:D2′);//設定漸層背景顏色雙色(灰/白)
objPHPExcel->getActiveSheet()->getStyle(‘A1:D2′)->applyFromArray(
array(
‘font’ => array(
‘bold’ => true
),
‘alignment’ => array(
‘horizontal’ => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
),
‘borders’ => array(
‘top’ => array(
’style’ => PHPExcel_Style_Border::BORDER_THIN
)
),
‘fill’ => array(
‘type’ => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
‘rotation’ => 90,
’startcolor’ => array(
‘rgb’ => ‘DCDCDC’
),
‘endcolor’ => array(
‘rgb’ => ‘FFFFFF’
)
)
)
);//設定字型大小
objPHPExcel->getActiveSheet()->getStyle(‘A1′)->getFont()->setSize(16);//設定A1欄位顯示文字PHPEXCEL TEST
objPHPExcel->getActiveSheet()->setCellValue(‘A1′,’PHPEXCEL TEST’);//設定字體顏色
//objPHPExcel->getActiveSheet()->getStyle(‘A1′)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);//設定背景顏色單色
objPHPExcel->getActiveSheet()->getStyle(‘A3:D3′)->applyFromArray(
array(‘fill’ => array(
‘type’ => PHPExcel_Style_Fill::FILL_SOLID,
‘color’ => array(‘rgb’ => ‘D1EEEE’)
),
)
);//設定欄位值
objPHPExcel->getActiveSheet()->setCellValue(‘A3′,’test1′);
objPHPExcel->getActiveSheet()->setCellValue(‘B3′,’test2′);
objPHPExcel->getActiveSheet()->setCellValue(‘C3′,’test3′);
objPHPExcel->getActiveSheet()->setCellValue(‘D3′,’test4′);// Rename sheet
objPHPExcel->getActiveSheet()->setTitle(sheet);//設定的欄位寬度(自動)
objPHPExcel->getActiveSheet()->getColumnDimension(‘A’)->setAutoSize(true);// Set active sheet index to the first sheet, so Excel opens this as the first sheet
objPHPExcel->setActiveSheetIndex(0);// Export to Excel2007 (.xlsx) 匯出成2007
objWriter = PHPExcel_IOFactory::createWriter(objPHPExcel, ‘Excel2007′);
objWriter->save(‘test.xlsx’);// Export to Excel5 (.xls) 匯出成2003
objWriter = PHPExcel_IOFactory::createWriter(objPHPExcel, ‘Excel5′);
objWriter->save(‘test.xls’);?>
上述的設定檔,匯出的效果如下:
