Convert a pie chart from PHPExcel to Excel

PHPExcel is a powerful tool for processing Excel files. I have a report with a pie chart, which needs to be converted to Excel. PHPExcel has a related example. after referring to and modifying it, this effect is achieved, allows you to generate and download an Excel file during Click download, and

PHPExcel is a powerful tool for processing Excel files. I have a report with a pie chart, which needs to be converted to Excel. PHPExcel has a related example. after referring to and modifying it, this effect is achieved,

Allows you to generate and download an Excel file during Click download, and generate a pie chart in the Excel file.

In this example, we made two major changes:

1. retrieve data from the MySQL database.

2. added the Chinese file name in some browsers, such as IE, to solve the problem of garbled names during download.

The PHP report is as follows:

Convert to xls:

VcD4KPHA + tPrC68jnz8I6PC9wPgo8cD4gPHByZSBjbGFzcz0 = "brush: java;"> GetProperties ()-> setCreator ("creator")-> setLastModifiedBy ("XiongChuanLiang")-> setTitle ("summary table"); $ objActSheet = $ objPHPExcel-> getActiveSheet (); $ objActSheet-> getColumnDimension ('A')-> setWidth (50); $ objActSheet-> getColumnDimension ('B')-> setWidth (50 ); $ objActSheet-> getRowDimension (1)-> setRowHeight (30); $ objActSheet-> getRowDimension (2)-> setRowHeight (16); $ objActSheet-> mergeCells ('A1: c1 '); $ objA CtSheet-> mergeCells ('A2: c2'); // you can specify center aligment. $ objActSheet-> getStyle ('A1')-> getAlignment ()-> setHorizontal :: HORIZONTAL_CENTER); $ objActSheet-> getStyle ('A2 ')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER ); $ objFontA1 = $ objActSheet-> getStyle ('A1')-> getFont (); $ objFontA1-> setSize (18); $ objFontA1-> setBold (true ); //////////////////////////////////////// /// /// // $ SQL = mysql_query ("SELECT * AS state_name, count (*) AS stat_count FROM (......) k group by status order by status "); $ info = mysql_fetch_array ($ SQL); $ objActSheet-> setCellValue ('A1', 'summary '); if (strlen (trim ($ sdev_model)> 0) {$ objActSheet-> setCellValue ('A2 ', "model: xxxxxx");} $ row = 3; $ objActSheet-> setCellValue ('A '. $ row, 'status'); $ objActSheet-> setCellValue ('B '. $ row, 'Total number'); $ row = 4; do {$ objActSheet-> setCellValueExplicit ('A '. $ row, $ info ['state _ name'], PHPExcel_Cell_DataType: TYPE_STRING); $ objActSheet-> setCellValueExplicit ('B '. $ row, $ info ['stat _ count'], PHPExcel_Cell_DataType: TYPE_NUMERIC); $ objActSheet-> setCellValue ('A '. $ row, $ info ['state _ name']); $ objActSheet-> setCellValue ('B '. $ row, $ info ['stat _ count']); $ row ++;} while ($ info = mysql_fetch_array ($ SQL )); ///////// //////////////////////////////////////// /// // For ($ currrow = 3; $ currrow <$ row; $ 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)-> getBorder S ()-> 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_Bord Er: 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 data // Format Code // Number of datapoints in series // Data values // Data Marker $ dataseriesLabels1 = array (new PHPExcel_Chart_DataSeriesValues ('string ', 'worksheet! $ B $3 ', NULL, 1 ),); // Set the X-Axis Labels // Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values // Data Marker $ xAxisTickValues1 = array (new PHPExcel_Chart_DataSeriesValues ('string ', 'worksheet! $ A $4: $ A $ '. $ row, NULL, 4 ),); // 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 $ dataSeriesValues1 = array (new PHPExcel_Chart_DataSeriesValues ('number ', 'worksheet! $ B $4: $ B $ '. $ row, NULL, 4),); // Build the dataseries $ series1 = new partition (PHPExcel_Chart_DataSeries: TYPE_PIECHART, // plotType PHPExcel_Chart_DataSeries: GROUPING_STANDARD, // plotGrouping range (0, count ($ dataSeriesValues1)-1), // plotOrder $ dataseriesLabels1, // plotLabel $ tags, // plotCategory $ dataSeriesValues1 // plotValues ); // Set up a layout object for the Pie chart $ layout1 = new PHPExcel_Chart_Layout (); $ layout1-> setShowVal (TRUE); $ layout1-> setShowPercent (TRUE ); // Set the series in the plot area $ plotarea1 = new PHPExcel_Chart_PlotArea ($ layout1, array ($ series1); // Set the chart legend $ legend1 = new PHPExcel_Chart_Legend (subject :: POSITION_RIGHT, NULL, false); $ title1 = new PHPExcel_Chart_Title (' terization'); // Create the chart $ chart1 = new PHPExcel_Chart ('chart1', // name $ title1, // title $ legend1, // legend $ plotarea1, // plotArea true, // plotVisibleOnly 0, // displayBlanksAs NULL, // xAxisLabel NULL // yAxisLabel-Pie charts don't have a Y-Axis); // Set the position where the chart shoshould appear in the worksheet $ row + = 2; $ chart1-> setTopLeftPosition ('A '. $ row); $ row + = 10; $ chart1-> setBottomRightPosition ('C '. $ row); // Add the chart to the worksheet $ objPHPExcel-> getActiveSheet ()-> addChart ($ chart1 ); //////////////////////////////////////// //////////////////////////////////////// ////////// Set active sheet index to the first sheet, so Excel opens this as the first sheet $ objPHPExcel-> setActiveSheetIndex (0); $ filename = 'summary table _'. 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;In addition, the Excel pie chart is automatically generated by specifying its label and the range of cells corresponding to the value. Therefore, it is mainly calculated in the code. On a non-Windows Server, generation fails.

MAIL: xcl_168@aliyun.com

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