利用PHPExcel匯出xlsx及xls檔設定說明 @ Avril Note

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’);

?>