利用PHPExcel轉Excel直條圖_PHP教程

這在附另一個轉直條圖的效果及代碼.

原PHP報表效果:

轉成Excel後的效果:

<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+uL3Jz7T6wus6PC9wPgo8cD48cHJlIGNsYXNzPQ=="brush:java;">getProperties()->setCreator("XiongChuanLiang") ->setLastModifiedBy("XiongChuanLiang") ->setTitle("明細表");$objActSheet = $objPHPExcel->getActiveSheet(); $objActSheet->getColumnDimension('A')->setWidth(12); $objActSheet->getColumnDimension('B')->setWidth(20); $objActSheet->getColumnDimension('C')->setWidth(12); $objActSheet->getColumnDimension('D')->setWidth(20); $objActSheet->getColumnDimension('E')->setWidth(20); $objActSheet->getColumnDimension('F')->setWidth(12); $objActSheet->getColumnDimension('G')->setWidth(20); $objActSheet->getColumnDimension('H')->setWidth(18); $objActSheet->getColumnDimension('I')->setWidth(18); $objActSheet->getColumnDimension('J')->setWidth(30); $objActSheet->getColumnDimension('K')->setWidth(20); $objActSheet->getRowDimension(1)->setRowHeight(30); $objActSheet->getRowDimension(2)->setRowHeight(16); $objActSheet->getRowDimension(3)->setRowHeight(16); $objActSheet->mergeCells('A1:K1'); $objActSheet->mergeCells('A2:K2'); $objActSheet->mergeCells('A3:K3'); //設定置中對齊 $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objFontA1 = $objActSheet->getStyle('A1')->getFont(); $objFontA1->setSize(18); $objFontA1->setBold(true); ///////////////////////////////////////////////////////////////////////// $sqlSelect="SELECT ...... FROM ( ...... ) k order by ...... "; $sql = mysql_query($sqlSelect); $info = mysql_fetch_array($sql); $objActSheet->setCellValue('A1', '明細表'); if(strlen( trim( $rent_time_begin)) > 0 && strlen( trim( $rent_time_end)) > 0 ) { $objActSheet->setCellValue('A2',"(".$rent_time_begin." ~ ".$rent_time_end.")"); } $objActSheet->setCellValue('A3', "(demo)"); $row=4;$objActSheet->setCellValue('A'.$row, '...'); $objActSheet->setCellValue('B'.$row, '...'); $objActSheet->setCellValue('C'.$row, '...'); $objActSheet->setCellValue('D'.$row,'...');$objActSheet->setCellValue('E'.$row, '...');$objActSheet->setCellValue('F'.$row, '...');$objActSheet->setCellValue('G'.$row, '...');$objActSheet->setCellValue('H'.$row, '...');$objActSheet->setCellValue('I'.$row, '...');$objActSheet->setCellValue('J'.$row, '...'); $row=5; do{ $objActSheet->setCellValue('A'.$row, $info['...']); $objActSheet->setCellValue('B'.$row, $info['...']); $objActSheet->setCellValue('C'.$row, $info['...']); $objActSheet->setCellValue('D'.$row, $info['...']); $objActSheet->setCellValue('E'.$row, $info['...']); $objActSheet->setCellValue('F'.$row, $info['...']); $objActSheet->setCellValue('G'.$row, $info['...']); $objActSheet->setCellValue('H'.$row, $info['...']); $objActSheet->setCellValue('I'.$row, $info['...']); $objActSheet->setCellValue('J'.$row, $info['...']); //分別累加 $tmpstatus = $info['...']; if($tmpstatus == 'A'){ $status1++; }else if($tmpstatus == 'B'){ $status2++; }else if($tmpstatus == 'C'){ $status3++; }else if($tmpstatus == 'D'){ $status4++; }else{ $status0 ++; } $row++; }while($info=mysql_fetch_array($sql));/////////////////////////////////////////////////////////////////////////for ($currrow = 4; $currrow < $row; $currrow++) { //設定邊框 $objActSheet->getStyle('A'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('A'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('A'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('A'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('C'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('C'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('C'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('C'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('D'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('D'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('D'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('D'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('E'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('E'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('E'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('E'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('F'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('F'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('F'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('F'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('G'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('G'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('G'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('G'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('H'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('H'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('H'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('H'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('I'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('I'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('I'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('I'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('J'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('J'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('J'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('J'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); }///////////////////////////////////////////// bar/////////////////////////////////////////////// $row += 3; $tabInitRow = $row; $objActSheet->setCellValue('A'.$row, '狀態'); $objActSheet->setCellValue('B'.$row, '總數'); $row++; $objActSheet->setCellValue('A'.$row, '...'); $objActSheet->setCellValue('B'.$row, $status0); $row++; $objActSheet->setCellValue('A'.$row, '...'); $objActSheet->setCellValue('B'.$row, $status1); $row++; $objActSheet->setCellValue('A'.$row, '...'); $objActSheet->setCellValue('B'.$row, $status2); $row++; $objActSheet->setCellValue('A'.$row, '...'); $objActSheet->setCellValue('B'.$row, $status3); $row++; $objActSheet->setCellValue('A'.$row, '...'); $objActSheet->setCellValue('B'.$row, $status4); $tabLastRow = $row; for ($currrow = $tabInitRow; $currrow <= $tabLastRow; $currrow++) { //設定邊框 $objActSheet->getStyle('A'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('A'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('A'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('A'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); }// Set the Labels for each data series we want to plot// Datatype// Cell reference for data// Format Code// Number of datapoints in series// Data values// Data Marker$dataseriesLabels = array( new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$'.$tabInitRow , NULL, 1), // 2010 ); //new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', NULL, 1), // 2011 //new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', NULL, 1), // 2012// Set the X-Axis Labels// Datatype// Cell reference for data// Format Code// Number of datapoints in series// Data values// Data Marker$tabInitRow ++;$xAxisTickValues = array( new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$'.$tabInitRow.':$A$'.$tabLastRow , NULL, 4), // Q1 to Q4);// Set the Data values for each data series we want to plot// Datatype// Cell reference for data// Format Code// Number of datapoints in series// Data values// Data Marker$dataSeriesValues = array( new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$'.$tabInitRow.':$B$'.$tabLastRow, NULL, 4),);// Build the dataseries$series = new PHPExcel_Chart_DataSeries( PHPExcel_Chart_DataSeries::TYPE_BARCHART, // plotType PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, // plotGrouping range(0, count($dataSeriesValues)-1), // plotOrder $dataseriesLabels, // plotLabel $xAxisTickValues, // plotCategory $dataSeriesValues // plotValues);// Set additional dataseries parameters// Make it a horizontal bar rather than a vertical column graph$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_BAR);// Set the series in the plot area$plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series));// Set the chart legend$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);$title = new PHPExcel_Chart_Title('狀態匯總');$yAxisLabel = new PHPExcel_Chart_Title('總數');// Create the chart$chart = new PHPExcel_Chart( 'chart1', // name $title, // title $legend, // legend $plotarea, // plotArea true, // plotVisibleOnly 0, // displayBlanksAs NULL, // xAxisLabel $yAxisLabel // yAxisLabel);// Set the position where the chart should appear in the worksheet $tabLastRow += 2;$chart->setTopLeftPosition('A'.$tabLastRow );$tabLastRow += 15;$chart->setBottomRightPosition('F'.$tabLastRow );// Add the chart to the worksheet$objActSheet->addChart($chart);//////////////////////////////////////////////////////////////////////////////////////// Set active sheet index to the first sheet, so Excel opens this as the first sheet$objPHPExcel->setActiveSheetIndex(0);$filename = '明細表_'.date("Y_m_d").".xlsx";// Redirect output to a client’s web browser (Excel2007)header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//header('Content-Disposition: attachment;filename="'.$filename.'"'); //devrent.xlsx//////////////////////////////////////////處理中文檔案名稱亂碼問題$ua = $_SERVER["HTTP_USER_AGENT"]; $encoded_filename = urlencode($filename);$encoded_filename = str_replace("+", "%20",$encoded_filename);header('Content-Type: application/octet-stream');if (preg_match("/MSIE/", $ua)) { header('Content-Disposition: attachment;filename="' . $encoded_filename . '"');}else if (preg_match("/Firefox/", $ua)){ header('Content-Disposition: attachment; filename*="utf8\'\'' . $filename . '"');}else { header('Content-Disposition: attachment; filename="' . $filename . '"');}////////////////////////////////////////header('Cache-Control: max-age=0');// If you're serving to IE 9, then the following may be neededheader('Cache-Control: max-age=1');// If you're serving to IE over SSL, then the following may be neededheader ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the pastheader ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modifiedheader ('Cache-Control: cache, must-revalidate'); // HTTP/1.1header ('Pragma: public'); // HTTP/1.0$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');$objWriter->setIncludeCharts(TRUE);$objWriter->save('php://output');exit;

MAIL: xcl_168@aliyun.com

BLOG: http://blog.csdn.net/xcl168

http://www.bkjia.com/PHPjc/766058.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/766058.htmlTechArticle這在附另一個轉直條圖的效果及代碼. 原PHP報表效果: 轉成Excel後的效果: vcD4KPHA+uL3Jz7T6wus6PC9wPgo8cD48cHJlIGNsYXNzPQ=="brush:java;"> getProperties()->setC...

