利用PHPExcel转Excel柱形图

这在附另一个转柱形图的效果及代码.

原PHP报表效果:

转成Excel后的效果:


<喎?http://www.2cto.com/kf/ware/vc/" target="_blank" rel="nofollow" class="keylink">vcD4KPHA+uL3Jz7T6wus6PC9wPgo8cD48cHJlIGNsYXNzPQ=="brush:java;"><?php/** * PHPExcel * * Copyright (C) 2006 - 2014 PHPExcel * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA * * @category PHPExcel * @package PHPExcel * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel) * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version 1.8.0, 2014-03-02 *//** Error reporting */error_reporting(E_ALL);ini_set("display_errors", TRUE);ini_set("display_startup_errors", TRUE);//date_default_timezone_set("Europe/London");date_default_timezone_set("Asia/Shanghai");if (PHP_SAPI == "cli") die("This example should only be run from a Web Browser");/** Include PHPExcel */require_once dirname(__FILE__) . "/libxls/Classes/PHPExcel.php";require_once dirname(__FILE__) ."/inc/xcl_conn.php";////////////////////////////////////////存放各状态汇总数量$status0 = 0;$status1 = 0;$status2 = 0;$status3 = 0;$status4 = 0;//////////////////////////////////////// Create new PHPExcel object$objPHPExcel = new PHPExcel();// Set document properties$objPHPExcel->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



郑重声明:本文版权包含图片归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们(delete@yzlfxy.com)修改或删除,多谢。

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。

留言与评论(共有 0 条评论)
昵称:
匿名发表
   
验证码: