分類
電腦和網際網路

phpExcel範例

實用筆記整理
(原始作者Li Ron)

error_reporting(E_ALL);
date_default_timezone_set(‘Asia/Taipei’);
/** PHPExcel */
require_once ‘Classes/PHPExcel.php’;

// 新增Excel物件
$objPHPExcel = new PHPExcel();

// 設定屬性
$objPHPExcel->getProperties()->setCreator(“PHP”)
->setLastModifiedBy(“PHP”)
->setTitle(“Title”)
->setSubject(“Subject”)
->setDescription(“Description”)
->setKeywords(“Keywords”)
->setCategory(“Category”);

//設定操作中的工作表
$objPHPExcel->setActiveSheetIndex(0);

//將工作表命名
$objPHPExcel->getActiveSheet(0)->setTitle(‘第一張表’);

//合併儲存格
$objPHPExcel->getActiveSheet(0)->mergeCells(‘A1:D2’);

//儲存格內容
$objPHPExcel->getActiveSheet(0)->setCellValue(‘A1′,’PHPEXCEL TEST’); //合併後的儲存格,設定時指定左上角那個。
$objPHPExcel->getActiveSheet(0)->setCellValue(‘A3′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘B3′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘C3′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘D3′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘A4′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘B4′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘C4′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘D4′,’test’);

//設定背景顏色單色
$objPHPExcel->getActiveSheet(0)->getStyle(‘A3:D3’)->applyFromArray(
array(‘fill’     => array(
‘type’ => PHPExcel_Style_Fill::FILL_SOLID,
‘color’ => array(‘argb’ => ‘D1EEEE’)
),
)
);
//設定漸層背景顏色雙色(灰/白)   經測試,Excel2007才有漸層
$objPHPExcel->getActiveSheet(0)->getStyle(‘A1:D2’)->applyFromArray(
array(
‘font’   => array(‘bold’ => true,
‘size’ => ’24’),
‘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’))
));

//框線 方法一:使用 setBorderStyle() 函數
$objPHPExcel->getActiveSheet(0)->getStyle(‘A5’)->getBorders()->getTop()  ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet(0)->getStyle(‘B5’)->getBorders()->getBottom() ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet(0)->getStyle(‘C5’)->getBorders()->getleft() ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet(0)->getStyle(‘D5’)->getBorders()->getright() ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet(0)->getStyle(‘A7:C10’)->getBorders()->getAllborders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

//框線 方法二:使用applyFromArray()函數
$styleArray = array(
‘borders’ => array(
‘allborders’ => array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(‘argb’ => ‘000000’),
),
),
);
$objPHPExcel->getActiveSheet(0)->getStyle(‘A12:C15’)->applyFromArray($styleArray);

//框線 方法三:使用物件 + applyFromArray()函數
$style_obj = new PHPExcel_Style();
$style_array = array( ‘borders’ => array(‘allborders’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THIN)),
‘alignment’ => array(‘wrap’=> true,
‘horizontal’ => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
‘vertical’ => PHPExcel_Style_Alignment::VERTICAL_CENTER
),
‘font’ => array(‘size’ => ‘8’)
);
$style_obj->applyFromArray($style_array);
$objPHPExcel->getActiveSheet(0)->setSharedStyle($style_obj, ‘E7:G10’);

//斜線  方法一
$styleArray = array(‘borders’ => array(‘diagonal’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THICK,
‘color’ => array(‘argb’ => ‘FFFF0000’),
),
‘diagonaldirection’ => PHPExcel_Style_Borders::DIAGONAL_UP
//’diagonaldirection’ => PHPExcel_Style_Borders::DIAGONAL_DOWN
//’diagonaldirection’ => PHPExcel_Style_Borders::DIAGONAL_BOTH
),
);
$objPHPExcel->getActiveSheet()->getStyle(“E1″)->applyFromArray($styleArray);

//斜線  方法二
$objPHPExcel->getActiveSheet()->getStyle(‘F1’)->getBorders()->getDiagonal()->applyFromArray(array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(‘argb’ => ‘FFFF0000’)
)
);
$objPHPExcel->getActiveSheet()->getStyle(‘F1’)->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_DOWN);
/*
註:
PHPExcel_Style_Borders::DIAGONAL_UP
PHPExcel_Style_Borders::DIAGONAL_DOWN
PHPExcel_Style_Borders::DIAGONAL_BOTH
*/

//設定一個範圍後套用相同格式
$objPHPExcel->getActiveSheet(0)->mergeCells(‘E12:F13’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘E12’,”Hello \n World”);
$style_obj = new PHPExcel_Style();
$style_array = array( ‘borders’ => array(‘allborders’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THIN)),
‘alignment’ => array(‘wrap’=> true,
‘horizontal’ => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
‘vertical’ => PHPExcel_Style_Alignment::VERTICAL_CENTER
),
‘font’ => array(‘size’ => ‘8’)
);
$style_obj->applyFromArray($style_array);
$objPHPExcel->getActiveSheet(0)->setSharedStyle($style_obj, “E12:G14”);

//設定字型(粗細、顏色)  也可參照上面的方法,用陣列的方式設定。
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4’)->getFont()->setName(‘Candara’);
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4’)->getFont()->setSize(16);
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4’)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4’)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4’)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); //藍色
$objPHPExcel->getActiveSheet(0)->getStyle(‘C4’)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); //紅色
$objPHPExcel->getActiveSheet(0)->getStyle(‘C4’)->getFont()->getColor()->setARGB(‘FF0000’); //紅色

$objPHPExcel->getActiveSheet(0)->setCellValue(‘G2’, ‘2008-12-31’);
$objPHPExcel->getActiveSheet(0)->getStyle(‘G2’)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

/*
註:這裡有列出可用英文單字表示的顏色,並不多。

COLOR_BLACK
COLOR_BLUE
COLOR_DARKBLUE
COLOR_DARKGREEN
COLOR_DARKRED
COLOR_DARKYELLOW
COLOR_GREEN
COLOR_RED
COLOR_WHITE
COLOR_YELLOW

*/

//使用函數
$objPHPExcel->getActiveSheet(0)->setCellValue(‘A5′,’3’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘B5′,’4’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘C5’, ‘=SUM(A5:B5)’);

//設定A3內容為00123,並指定為文字型態。這樣在顯示的時候不會自動把0去掉。$objPHPExcel->getActiveSheet(0)->getCell(“A4”)->setValueExplicit(‘00123’, PHPExcel_Cell_DataType::TYPE_STRING);

//分離儲存格
//$objActSheet->unmergeCells(‘B1:C22’);

//設定欄寬
$objPHPExcel->getActiveSheet(0)->getColumnDimension(‘A’)->setWidth(20);

//設定欄寬(自動欄寬)
//$objPHPExcel->getActiveSheet(0)->getColumnDimension(“A”)->setAutoSize(true);

//設定高度
$objPHPExcel->getActiveSheet(0)->getRowDimension(‘1’)->setRowHeight(150);

//下底線
$objPHPExcel->getActiveSheet(0)->getStyle(“D3”)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);

//旋轉文字
$objPHPExcel->getActiveSheet(0)->getStyle(‘A4’)->getAlignment()->setTextRotation(-90);
//對齊  //注意是 setVertiacl 還是 setHorizontal
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4’)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet(0)->getStyle(‘C4’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
/*
VERTICAL_CENTER 垂直置中
VERTICAL_TOP
HORIZONTAL_CENTER
HORIZONTAL_RIGHT
HORIZONTAL_LEFT
HORIZONTAL_JUSTIFY
*/
//add comment ———————————————–
$objPHPExcel->getActiveSheet()->getComment(‘A6’)->setAuthor(‘PHPExcel’);
$objPHPExcel->getActiveSheet()->getComment(‘A6’)->getText()->createTextRun(‘comment1 comment1 comment1 ‘);
$objPHPExcel->getActiveSheet()->getComment(‘A6’)->setWidth(‘200pt’);
$objPHPExcel->getActiveSheet()->getComment(‘A6’)->setHeight(‘100pt’);
$objPHPExcel->getActiveSheet()->getComment(‘A6’)->setMarginLeft(‘150pt’);
$objPHPExcel->getActiveSheet()->getComment(‘A6’)->getFillColor()->setRGB(‘dea66e’); //背景顏色
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment(‘A6’)->getText()->createTextRun(‘comment2 comment2 comment2 ‘);
$objCommentRichText->getFont()->setBold(true); //文字加粗
$objCommentRichText->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); //文字顏色

———————————————————————————–
———————————————————————————–
// 設定格式:使用物件的方式
$style_obj = new PHPExcel_Style();
$styleArray = array(‘borders’ => array(‘left’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THICK),
‘top’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THIN),
‘right’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THIN),
‘bottom’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THIN)
));
$style_obj->applyFromArray($styleArray);

$letter = PHPExcel_Cell::stringFromColumnIndex(0); //A
$cellname1 = $letter.’1′; // A1
$cellname2 = $letter.7;  // A7
$cell_range = “$cellname1:$cellname2”;
$sheet->setSharedStyle($style_obj, “$cell_range”);

// 設定格式:使用陣列

$styleArray = array(
‘borders’ => array(
‘allborders’ => array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(‘argb’ => ‘000000’)
),
),
‘font’   => array(‘bold’ => true,
‘size’ => ’12’,
‘color’ => array(‘argb’ => ‘FF0000’)
)
);
$objPHPExcel->getActiveSheet(0)->getStyle(‘A12:C15’)->applyFromArray($styleArray);

// 註解

$comment = “This is comment”;
$sheet->getComment(“F1”)->getFillColor()->setRGB(‘FFFAD9’); //背景顏色
$sheet->getComment(“F1”)->setWidth(‘320pt’);
$objCommentRichText = $sheet->getComment(“F1”)->getText()->createTextRun(“$comment”);
$objCommentRichText->getFont()->getColor()->setRGB(‘008080’); //文字顏色
$objCommentRichText->getFont()->setBold(true); //文字加粗

———————————————————————————–
// 設定其它工作表

$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle(‘第二張表’);
$objPHPExcel->getActiveSheet()->setCellValue(‘A3’,”test1″);
$objPHPExcel->getActiveSheet()->setCellValue(‘B3′,’test2’);

$objPHPExcel->setActiveSheetIndex(0);
//若要在 2003 跟 2007 之間切換,選然下面兩段其中一段即可。
//Excel 2007
header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
header(‘Content-Disposition: attachment;filename=”01simple.xlsx”‘);
header(‘Cache-Control: max-age=0’);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’);
/*
//Excel 2003
header(‘Content-Type: application/vnd.ms-excel’);
header(‘Content-Disposition: attachment;filename=”01simple.xls”‘);
header(‘Cache-Control: max-age=0’);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’); //Excel 2003 = Excel 5
*/

//========================================================
$objWriter->save(‘php://output’);
exit;

Loading Facebook Comments ...

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

*