2014年11月11日 星期二

【PHP】phpExcel範例 

require_once("PHPExcel.php");
 $objPHPExcel = new PHPExcel();
 $objPHPExcel->getProperties()->setTitle(" List");
 $objPHPExcel->getProperties()->setCategory(" List");
 $objPHPExcel->setActiveSheetIndex(0);
 
 $objPHPExcel->getActiveSheet()->setCellValue('A1', '商品編號');
 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(13);
 $objPHPExcel->getActiveSheet()->setCellValue('B1', '商品名稱');
 $objPHPExcel->getActiveSheet()->setCellValue('C1', '醒目標題');
 $objPHPExcel->getActiveSheet()->setCellValue('D1', '頁面標題');
 $objPHPExcel->getActiveSheet()->setCellValue('E1', '頁面關鍵字');
 $objPHPExcel->getActiveSheet()->setCellValue('F1', '頁面描述');
 $objPHPExcel->getActiveSheet()->setCellValue('G1', '中類屬性');
 $objPHPExcel->getActiveSheet()->setCellValue('H1', '小類屬性');
 $objPHPExcel->getActiveSheet()->setCellValue('I1', '市價');
 $objPHPExcel->getActiveSheet()->setCellValue('J1', '售價');
 $objPHPExcel->getActiveSheet()->setCellValue('K1', '成本');
 $objPHPExcel->getActiveSheet()->setCellValue('L1', '庫存數');
 $objPHPExcel->getActiveSheet()->setCellValue('M1', '安全庫存數');
 $objPHPExcel->getActiveSheet()->setCellValue('N1', '上架日');
 $objPHPExcel->getActiveSheet()->setCellValue('O1', '下架日');
 $objPHPExcel->getActiveSheet()->setCellValue('P1', '商品介紹');
 $objPHPExcel->getActiveSheet()->setCellValue('Q1', '功能一標題');
 $objPHPExcel->getActiveSheet()->setCellValue('R1', '功能一內容');
 $objPHPExcel->getActiveSheet()->setCellValue('S1', '功能二標題');
 $objPHPExcel->getActiveSheet()->setCellValue('T1', '功能二內容');
 $objPHPExcel->getActiveSheet()->setCellValue('U1', '功能三標題');
 $objPHPExcel->getActiveSheet()->setCellValue('V1', '功能三內容');
 $objPHPExcel->getActiveSheet()->setCellValue('W1', '功能四標題');
 $objPHPExcel->getActiveSheet()->setCellValue('X1', '功能四內容');
 $objPHPExcel->getActiveSheet()->setCellValue('Y1', '功能五標題');
 $objPHPExcel->getActiveSheet()->setCellValue('Z1', '功能五內容');
 $objPHPExcel->getActiveSheet()->setCellValue('AA1', '功能六標題');
 $objPHPExcel->getActiveSheet()->setCellValue('AB1', '功能六內容');
 $objPHPExcel->getActiveSheet()->setCellValue('AC1', '功能七標題');
 $objPHPExcel->getActiveSheet()->setCellValue('AD1', '功能七內容');
 $objPHPExcel->getActiveSheet()->setCellValue('AE1', '功能八標題');
 $objPHPExcel->getActiveSheet()->setCellValue('AF1', '功能八內容');
 $objPHPExcel->getActiveSheet()->setCellValue('AG1', '商品圖片');

 $i = 1;
 foreach($datas['data'] as $rs)
 {
  $i++;
  $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $a);
  $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $b);
  $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $c);
  $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $d);
  $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $e);
  $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $f);
  $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $g);
  $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $h);
  $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $i);
  $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $j);
  $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $k);
  $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, $l);
  $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, $m);
  $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, $n);
  $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, $o;
  $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, $p);
  $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, $q);
  $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, $r);
  $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, $s);
  $objPHPExcel->getActiveSheet()->setCellValue('T'.$i, $t);
  $objPHPExcel->getActiveSheet()->setCellValue('U'.$i, $u);
  $objPHPExcel->getActiveSheet()->setCellValue('V'.$i, $v);
  $objPHPExcel->getActiveSheet()->setCellValue('W'.$i, $w);
  $objPHPExcel->getActiveSheet()->setCellValue('X'.$i, $x);
  $objPHPExcel->getActiveSheet()->setCellValue('Y'.$i, $y);
  $objPHPExcel->getActiveSheet()->setCellValue('Z'.$i, $z);
  $objPHPExcel->getActiveSheet()->setCellValue('AA'.$i, $aa);
  $objPHPExcel->getActiveSheet()->setCellValue('AB'.$i, $ab);
  $objPHPExcel->getActiveSheet()->setCellValue('AC'.$i, $ac);
  $objPHPExcel->getActiveSheet()->setCellValue('AD'.$i, $ad);
  $objPHPExcel->getActiveSheet()->setCellValue('AE'.$i, $ae);
  $objPHPExcel->getActiveSheet()->setCellValue('AF'.$i, $af);
  $objPHPExcel->getActiveSheet()->setCellValue('AG'.$i, $ag);
 }


 // OUTPUT
 header('Content-Type: application/vnd.ms-excel');
 header('Content-Disposition: attachment;filename="output'.date("YmdHis").'.xls"');
 header('Cache-Control: max-age=0');
 
 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
 $objWriter->save('php://output');







實用筆記整理

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;
張貼者： 於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)