利用PHPExcel匯出 .xlsx .xls 檔設定說明

woff · 發表於 2011-11-5 16:42:41

PHPExcel官網：http://www.codeplex.com/PHPExcel
PHPExcel下載：http://phpexcel.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=10717
如果在windows下載下來就可直接使用，在linux還需要安裝ZipArchive，僅提供CentOS安裝方法請參考：
CentOS安裝可使用EXCEL相關套件 http://www.tshopping.com.tw/thread-189405-1-1.html

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

?>
複製代碼

上述的設定檔，匯出的效果如下：

帳號		自動登錄		找回密碼
密碼				註冊

[分享] 利用PHPExcel匯出 .xlsx .xls 檔設定說明