搜尋
首頁php教程php手册利用PHPExcel转Excel饼图

PHPExcel处理Excel档真是个强大的工具,我有张报表,带饼图,需要转成Excel, PHPExcel有一个相关的例子,参考并修改后实现了这个效果,

可以让用户在点击下载过程中生成和下载Excel档,并在Excel中档生成饼图。

对其例子主要做了两方面的修改:

1. 改成从MySQL数据库取资料

2. 加上了中文文件名在部份浏览器,如IE下,下载时名字乱码的解决方法.

PHP报表如下:

转成xls的效果图:


喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+tPrC68jnz8I6PC9wPgo8cD4gPHByZSBjbGFzcz0="brush:java;">getProperties()->setCreator("XiongChuanLiang") ->setLastModifiedBy("XiongChuanLiang") ->setTitle("汇总表"); $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'); $objActSheet->mergeCells('A2:C2'); //设置居中对齐 $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::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', '汇总表'); if(strlen( trim( $sdev_model)) > 0 ) { $objActSheet->setCellValue('A2',"型号:xxxxxx"); } $row=3; $objActSheet->setCellValue('A'.$row,'状态'); $objActSheet->setCellValue('B'.$row, '总数量'); $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 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 $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 PHPExcel_Chart_DataSeries( PHPExcel_Chart_DataSeries::TYPE_PIECHART, // plotType PHPExcel_Chart_DataSeries::GROUPING_STANDARD, // plotGrouping range(0, count($dataSeriesValues1)-1), // plotOrder $dataseriesLabels1, // plotLabel $xAxisTickValues1, // 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(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $title1 = new PHPExcel_Chart_Title('汇总表'); // 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 should 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 = '汇总表_'.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; 另要注意的地方是,Excel的饼图,通过指定其标签,值所对应的单元格范围,自动生成,所以主要是在代码中计算好。另在非Windows服务器,生成会失败。


MAIL: xcl_168@aliyun.com

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


陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
3 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
3 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您聽不到任何人,如何修復音頻
3 週前By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25:如何解鎖Myrise中的所有內容
4 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

WebStorm Mac版

WebStorm Mac版

好用的JavaScript開發工具

MantisBT

MantisBT

Mantis是一個易於部署的基於Web的缺陷追蹤工具,用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。

SublimeText3 Linux新版

SublimeText3 Linux新版

SublimeText3 Linux最新版

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器