利用PHPExcel轉Excel餅圖

來源:互聯網
上載者:User

PHPExcel處理Excel檔真是個強大的工具,我有張報表,帶餅圖,需要轉成Excel, PHPExcel有一個相關的例子,參考並修改後實現了這個效果,

可以讓使用者在點擊下載過程中產生和下載Excel檔,並在Excel中檔產生餅圖。

對其例子主要做了兩方面的修改:

1. 改成從MySQL資料庫取資料

2. 加上了中文檔案名稱在部份瀏覽器,如IE下,下載時名字亂碼的解決方案.

PHP報表如下:

轉成xls的效果圖:


<喎?http://www.2cto.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 < $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 ); }////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 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 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; 另要注意的地方是,Excel的餅圖,通過指定其標籤,值所對應的單元格範圍,自動產生,所以主要是在代碼中計算好。另在非Windows伺服器,產生會失敗。


MAIL: xcl_168@aliyun.com

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




相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。