php 操作excel

標籤：水平   pen   create   ext   路徑   串連資料庫   dimens   fc14   位置   

<?php $dir=dirname(__FILE__);//尋找當前指令碼所在路徑require $dir."/db.php";//引入mysql操作類檔案require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel$db=new db($phpexcel);//執行個體化db類 串連資料庫$objPHPExcel=new PHPExcel();//執行個體化PHPExcel類， 等同於在案頭上建立一個excel$objSheet=$objPHPExcel->getActiveSheet();//獲得當前作用儲存格//開始本節課代碼編寫$objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//設定excel檔案預設水平垂直方向置中$objSheet->getDefaultStyle()->getFont()->setSize(14)->setName("微軟雅黑");//設定預設字型大小和格式$objSheet->getStyle("A2:Z2")->getFont()->setSize(20)->setBold(true);//設定第二行字型大小和加粗$objSheet->getStyle("A3:Z3")->getFont()->setSize(16)->setBold(true);//設定第三行字型大小和加粗$objSheet->getDefaultRowDimension()->setRowHeight(30);//設定預設行高$objSheet->getRowDimension(2)->setRowHeight(50);//設定第二行行高$objSheet->getRowDimension(3)->setRowHeight(40);//設定第三行行高$gradeInfo=$db->getAllGrade();//查詢所有的年級$index=0;foreach($gradeInfo as $g_k=>$g_v){$gradeIndex=getCells($index*2);//擷取年級資訊所在列$objSheet->setCellValue($gradeIndex."2","高".$g_v[‘grade‘]);$classInfo=$db->getClassByGrade($g_v[‘grade‘]);//查詢每個年級所有的班級foreach($classInfo as $c_k=>$c_v){$nameIndex=getCells($index*2);//獲得每個班級學生姓名所在列位置$scoreIndex=getCells($index*2+1);//獲得每個班級學生分數所在列位置$objSheet->mergeCells($nameIndex."3:".$scoreIndex."3");//合并每個班級的儲存格$objSheet->getStyle($nameIndex."3:".$scoreIndex."3")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB(‘6fc144‘);//填充班級背景顏色$classBorder=getBorderStyle("445cc1");//擷取班級邊框樣式代碼$objSheet->getStyle($nameIndex."3:".$scoreIndex."3")->applyFromArray($classBorder);//設定每個班級的邊框$info=$db->getDataByClassGrade($c_v[‘class‘],$g_v[‘grade‘]);//查詢每個班級的學生資訊$objSheet->setCellValue($nameIndex."3",$c_v[‘class‘]."班");//填充班級資訊$objSheet->getStyle($nameIndex)->getAlignment()->setWrapText(true);//設定文字自動換行$objSheet->setCellValue($nameIndex."4","姓名\n換行")->setCellValue($scoreIndex."4","分數");$objSheet->getStyle($scoreIndex)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//設定某列儲存格格式為文字格式設定$j=5;foreach($info as $key=>$val){$objSheet->setCellValue($nameIndex.$j,$val[‘username‘])->setCellValue($scoreIndex.$j,$val[‘score‘]."21312321321321321321");//填充學生資訊//$objSheet->setCellValue($nameIndex.$j,$val[‘username‘])->setCellValueExplicit($scoreIndex.$j,$val[‘score‘]."12321321321321312",PHPExcel_Cell_DataType::TYPE_STRING);//填充學生資訊$j++;}$index++;}$endGradeIndex=getCells($index*2-1);//獲得每個年級的終止儲存格$objSheet->mergeCells($gradeIndex."2:".$endGradeIndex."2");//合并每個年級的儲存格$objSheet->getStyle($gradeIndex."2:".$endGradeIndex."2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB(‘c1b644‘);//填充年級背景顏色$gradeBorder=getBorderStyle("c144b1");//擷取年級邊框樣式代碼$objSheet->getStyle($gradeIndex."2:".$endGradeIndex."2")->applyFromArray($gradeBorder);//設定每個年級的邊框}$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,‘Excel5‘);//產生excel檔案//$objWriter->save($dir."/export_1.xls");//儲存檔案browser_export(‘Excel5‘,‘browser_excel03.xls‘);//輸出到瀏覽器$objWriter->save("php://output");function browser_export($type,$filename){if($type=="Excel5"){header(‘Content-Type: application/vnd.ms-excel‘);//告訴瀏覽器將要輸出excel03檔案}else{header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet‘);//告訴瀏覽器資料excel07檔案}header(‘Content-Disposition: attachment;filename="‘.$filename.‘"‘);//告訴瀏覽器將輸出檔案的名稱header(‘Cache-Control: max-age=0‘);//禁止緩衝}/****根據下標獲得儲存格所在列位置**/function getCells($index){$arr=range(‘A‘,‘Z‘);//$arr=array(A,B,C,D,E,F,G,H,I,J,K,L,M,N,....Z);return $arr[$index];}/****擷取邊框樣式代碼**/function getBorderStyle($color){$styleArray = array(‘borders‘ => array(‘outline‘ => array(‘style‘ => PHPExcel_Style_Border::BORDER_THICK,‘color‘ => array(‘rgb‘ => $color),),),);return $styleArray;}?>

　　

php 操作excel