Convert PHPExcel to Excel column chart

This is the effect and code of another column chart.

Results of the original PHP report:

Effect after converting to Excel:

<喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KPHA + uL3Jz7T6wus6PC9wPgo8cD48cHJlIGNsYXNzPQ = "brush: java;"> GetProperties ()-> setCreator ("timer")-> setLastModifiedBy ("XiongChuanLiang")-> setTitle ("Schedule"); $ 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-> g EtRowDimension (2)-> setRowHeight (16); $ objActSheet-> getRowDimension (3)-> setRowHeight (16); $ objActSheet-> mergeCells ('a1: K1 '); $ objActSheet-> mergeCells ('a2: k2'); $ objActSheet-> mergeCells ('a3: k3 '); // set center aligment $ objActSheet-> getStyle ('a1')-> getAlignment ()-> setHorizontal (Region: HORIZONTAL_CENTER); $ objActSheet-> getStyle ('a2 ') -> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZ ONTAL_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 ...... "; $ SQL = mysql_query ($ sqlSelect); $ inf O = mysql_fetch_array ($ SQL); $ objActSheet-> setCellValue ('a1', 'schedule '); if (strlen (trim ($ pai_time_begin)> 0 & strlen (trim ($ pai_time_end)> 0) {$ objActSheet-> setCellValue ('a2 ',"(". $ pai_time_begin. "~ ". $ Pai_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 ['... ']); $ objActSh Eet-> setCellValue ('F '. $ row, $ info ['... ']); $ objActSheet-> setCellValue ('G '. $ row, $ info ['... ']); $ objActSheet-> setCellValue ('H '. $ row, $ info ['... ']); $ objActSheet-> setCellValue (' I '. $ row, $ info ['... ']); $ objActSheet-> setCellValue ('J '. $ row, $ info ['... ']); // accumulate $ 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 ++) {// set the border $ objActSheet-> getStyle ('A '. $ currrow)-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN); $ objActSheet-> getStyle ('A '. $ currrow)-> getBor Ders ()-> 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_ B Order: 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_Borde R: 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 '. $ cur Rrow)-> 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 (PHP Excel_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-> get Style ('J '. $ currrow)-> getBorders ()-> getBottom ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );} //////////////////////////////////////// //// bar ////////////////////////////////// /// // $ row + = 3; $ tabInitRow = $ row; $ objActSheet-> setCellValue ('A '. $ row, 'status'); $ objActSheet-> setCellValue ('B '. $ row, 'Total'); $ 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-> setCellValu E ('B '. $ row, $ status4); $ tabLastRow = $ row; for ($ currrow = $ tabInitRow; $ currrow <= $ tabLastRow; $ currrow ++) {// set the border $ 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 val Ues // 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 tables (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 ('State summary'); $ yAxisLabel = new PHPExcel_Chart_Title ('Total '); // 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 shoshould 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 = 'schedule _'. 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 ///////////////////////////////////// //// handle Chinese file name garbled problem $ 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.1 header ('pragma: public '); // HTTP/1.0 $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel2007 '); $ objWriter-> setmediadecharts (TRUE); $ objWriter-> save ('php: // output'); exit;

MAIL: xcl_168@aliyun.com

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