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

熱AI工具

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

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

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

AI Hentai Generator
免費產生 AI 無盡。

熱門文章

熱工具

禪工作室 13.0.1
強大的PHP整合開發環境

WebStorm Mac版
好用的JavaScript開發工具

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

SublimeText3 Linux新版
SublimeText3 Linux最新版

記事本++7.3.1
好用且免費的程式碼編輯器