利用PHPExcel转Excel柱形图_PHP教程
这在附另一个转柱形图的效果及代码.
原PHP报表效果:
转成Excel后的效果:
喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+uL3Jz7T6wus6PC9wPgo8cD48cHJlIGNsYXNzPQ=="brush:java;">getProperties()->setCreator("XiongChuanLiang")
->setLastModifiedBy("XiongChuanLiang")
->setTitle("明细表");
$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->getRowDimension(2)->setRowHeight(16);
$objActSheet->getRowDimension(3)->setRowHeight(16);
$objActSheet->mergeCells('A1:K1');
$objActSheet->mergeCells('A2:K2');
$objActSheet->mergeCells('A3:K3');
//设置居中对齐
$objActSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_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 by ...... ";
$sql = mysql_query($sqlSelect);
$info = mysql_fetch_array($sql);
$objActSheet->setCellValue('A1', '明细表');
if(strlen( trim( $rent_time_begin)) > 0 && strlen( trim( $rent_time_end)) > 0 )
{
$objActSheet->setCellValue('A2',"(".$rent_time_begin." ~ ".$rent_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['...']);
$objActSheet->setCellValue('F'.$row, $info['...']);
$objActSheet->setCellValue('G'.$row, $info['...']);
$objActSheet->setCellValue('H'.$row, $info['...']);
$objActSheet->setCellValue('I'.$row, $info['...']);
$objActSheet->setCellValue('J'.$row, $info['...']);
//分别累加
$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 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 );
$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_Border::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'.$currrow)->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(PHPExcel_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->getStyle('J'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );
}
//////////////////////////////////////////
/// bar
///////////////////////////////////////////////
$row += 3;
$tabInitRow = $row;
$objActSheet->setCellValue('A'.$row, '状态');
$objActSheet->setCellValue('B'.$row, '总数');
$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->setCellValue('B'.$row, $status4);
$tabLastRow = $row;
for ($currrow = $tabInitRow; $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
$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 PHPExcel_Chart_DataSeries(
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('状态汇总');
$yAxisLabel = new PHPExcel_Chart_Title('总数');
// 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 should 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 = '明细表_'.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;
MAIL: xcl_168@aliyun.com
BLOG: http://blog.csdn.net/xcl168
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
熱門話題
在PHP API中說明JSON Web令牌（JWT）及其用例。
Apr 05, 2025 am 12:04 AM
JWT是一種基於JSON的開放標準，用於在各方之間安全地傳輸信息，主要用於身份驗證和信息交換。 1.JWT由Header、Payload和Signature三部分組成。 2.JWT的工作原理包括生成JWT、驗證JWT和解析Payload三個步驟。 3.在PHP中使用JWT進行身份驗證時，可以生成和驗證JWT，並在高級用法中包含用戶角色和權限信息。 4.常見錯誤包括簽名驗證失敗、令牌過期和Payload過大，調試技巧包括使用調試工具和日誌記錄。 5.性能優化和最佳實踐包括使用合適的簽名算法、合理設置有效期、
描述紮實的原則及其如何應用於PHP的開發。
Apr 03, 2025 am 12:04 AM
SOLID原則在PHP開發中的應用包括：1.單一職責原則（SRP）：每個類只負責一個功能。 2.開閉原則（OCP）：通過擴展而非修改實現變化。 3.里氏替換原則（LSP）：子類可替換基類而不影響程序正確性。 4.接口隔離原則（ISP）：使用細粒度接口避免依賴不使用的方法。 5.依賴倒置原則（DIP）：高低層次模塊都依賴於抽象，通過依賴注入實現。
如何在系統重啟後自動設置unixsocket的權限？
Mar 31, 2025 pm 11:54 PM
如何在系統重啟後自動設置unixsocket的權限每次系統重啟後，我們都需要執行以下命令來修改unixsocket的權限：sudo...
解釋PHP中晚期靜態結合的概念。
Mar 21, 2025 pm 01:33 PM
文章討論了PHP 5.3中介紹的PHP中的晚期靜態結合（LSB），允許靜態方法的運行時間分辨率調用以更靈活的繼承。 LSB的實用應用和潛在的觸摸
如何用PHP的cURL庫發送包含JSON數據的POST請求？
Apr 01, 2025 pm 03:12 PM
使用PHP的cURL庫發送JSON數據在PHP開發中，經常需要與外部API進行交互，其中一種常見的方式是使用cURL庫發送POST�...
