利用PHPExcel转Excel柱形图

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

原PHP报表效果:

转成Excel后的效果:

附上代码:

<?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 needed
header(‘Cache-Control: max-age=1‘);

// If you‘re serving to IE over SSL, then the following may be needed
header (‘Expires: Mon, 26 Jul 1997 05:00:00 GMT‘); // Date in the past
header (‘Last-Modified: ‘.gmdate(‘D, d M Y H:i:s‘).‘ GMT‘); // always modified
header (‘Cache-Control: cache, must-revalidate‘); // HTTP/1.1
header (‘Pragma: public‘); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007‘);
$objWriter->setIncludeCharts(TRUE);
$objWriter->save(‘php://output‘);
exit;

MAIL: [email protected]

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

利用PHPExcel转Excel柱形图,古老的榕树,5-wow.com