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;