milad_d993
سه شنبه 11 آذر 1399, 08:57 صبح
سلام دوستان
با کد ها زیر خروجی اکسل گرفتم. فقط مشکلی که داره اینه که توی چارتی که میسازه در محور x بجای نام ستون از عدد استفاده میکنه:
152560
152573
require_once("libraries/PHPExcel/PHPExcel.php");
$phpexcel = new PHPExcel();
$json = '{"labels":[" "," 3374 "," 3397 "," 3417 "," 4617 ","1 ","200 ","2196 ","2443 ","2732 ","2895 ","3417 ","3722 ","4 ","4014 ","4590 ","4617 ","5094 ","aaa ","df ","hiby_2 ","n ","salam ","salam2 ","w ","\u0633\u0644\u0627\u0645 "],"values":[11,1,1,1,3,2,1,3,2,1,1,1,2,1,3,5,9,1,3,3,1,1,1,1,1 ,1],"links":["index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"empty\",\"\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\" 3374\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\" 3397\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\" 3417\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\" 4617\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"1\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"200\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"2196\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"2443\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"2732\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"2895\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"3417\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"3722\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"4\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"4014\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"4590\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"4617\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"5094\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"aaa\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"df\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"hiby_2\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"n\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"salam\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"salam2\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"w\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"\\u0633\\u0644\\u0627\\u0645\"]]]"],"graph_label":"123"}';
$data = json_decode($json, true);
$labels = ['ستون'];
$values = ['مقدار'];
$n = count($data['labels']);
for($i = 0; $i < $n; $i++){
array_push($labels, $data['labels'][$i]);
array_push($values, $data['values'][$i]);
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: max-age=0');
$phpexcel->setActiveSheetIndex(0);
$sheet = $phpexcel->getActiveSheet();
$data = $values;
$row = 1;
foreach($data as $point) {
$sheet->setCellValueByColumnAndRow(1, $row++, $point);
}
$data = $labels;
$row = 1;
foreach($data as $point) {
$sheet->setCellValueByColumnAndRow(0, $row++, $point);
}
$values = new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$'.($n+1));
$categories = new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$'.($n+1));
$charttype = PHPExcel_Chart_DataSeries::TYPE_BARCHART;
switch ($request->get('charttype')) {
case 'pieChart':
$charttype = PHPExcel_Chart_DataSeries::TYPE_PIECHART;
break;
case 'lineChart':
$charttype = PHPExcel_Chart_DataSeries::TYPE_LINECHART;
break;
}
$plotDirection = ($request->get('charttype') == 'horizontalbarChart')?PHPExcel_Chart_DataSeries::D IRECTION_HORIZONTAL:PHPExcel_Chart_DataSeries::DIR ECTION_VERTICAL;
$series = new PHPExcel_Chart_DataSeries(
$charttype, // plotType
PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, // plotGrouping
array(0), // plotOrder
array(), // plotLabel
array($categories), // plotCategory
array($values) // plotValues
);
$series->setPlotDirection($plotDirection);
$layout = new PHPExcel_Chart_Layout();
$plotarea = new PHPExcel_Chart_PlotArea($layout, array($series));
$xTitle = new PHPExcel_Chart_Title('ستون');
$yTitle = new PHPExcel_Chart_Title('مقدار');
$chart = new PHPExcel_Chart('sample', null, null, $plotarea, true,0,$xTitle,$yTitle);
$chart->setTopLeftPosition('D2');
$chart->setBottomRightPosition('S30');
$sheet->addChart($chart);
$writer = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel2007');
$writer->setIncludeCharts(TRUE);
$writer->save('php://output');
با کد ها زیر خروجی اکسل گرفتم. فقط مشکلی که داره اینه که توی چارتی که میسازه در محور x بجای نام ستون از عدد استفاده میکنه:
152560
152573
require_once("libraries/PHPExcel/PHPExcel.php");
$phpexcel = new PHPExcel();
$json = '{"labels":[" "," 3374 "," 3397 "," 3417 "," 4617 ","1 ","200 ","2196 ","2443 ","2732 ","2895 ","3417 ","3722 ","4 ","4014 ","4590 ","4617 ","5094 ","aaa ","df ","hiby_2 ","n ","salam ","salam2 ","w ","\u0633\u0644\u0627\u0645 "],"values":[11,1,1,1,3,2,1,3,2,1,1,1,2,1,3,5,9,1,3,3,1,1,1,1,1 ,1],"links":["index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"empty\",\"\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\" 3374\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\" 3397\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\" 3417\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\" 4617\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"1\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"200\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"2196\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"2443\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"2732\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"2895\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"3417\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"3722\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"4\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"4014\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"4590\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"4617\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"5094\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"aaa\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"df\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"hiby_2\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"n\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"salam\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"salam2\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"w\"]]]","index.php?module=HelpDesk&view=List&search_params=[[[\"cf_759\",\"e\",\"\\u0633\\u0644\\u0627\\u0645\"]]]"],"graph_label":"123"}';
$data = json_decode($json, true);
$labels = ['ستون'];
$values = ['مقدار'];
$n = count($data['labels']);
for($i = 0; $i < $n; $i++){
array_push($labels, $data['labels'][$i]);
array_push($values, $data['values'][$i]);
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: max-age=0');
$phpexcel->setActiveSheetIndex(0);
$sheet = $phpexcel->getActiveSheet();
$data = $values;
$row = 1;
foreach($data as $point) {
$sheet->setCellValueByColumnAndRow(1, $row++, $point);
}
$data = $labels;
$row = 1;
foreach($data as $point) {
$sheet->setCellValueByColumnAndRow(0, $row++, $point);
}
$values = new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$'.($n+1));
$categories = new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$'.($n+1));
$charttype = PHPExcel_Chart_DataSeries::TYPE_BARCHART;
switch ($request->get('charttype')) {
case 'pieChart':
$charttype = PHPExcel_Chart_DataSeries::TYPE_PIECHART;
break;
case 'lineChart':
$charttype = PHPExcel_Chart_DataSeries::TYPE_LINECHART;
break;
}
$plotDirection = ($request->get('charttype') == 'horizontalbarChart')?PHPExcel_Chart_DataSeries::D IRECTION_HORIZONTAL:PHPExcel_Chart_DataSeries::DIR ECTION_VERTICAL;
$series = new PHPExcel_Chart_DataSeries(
$charttype, // plotType
PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, // plotGrouping
array(0), // plotOrder
array(), // plotLabel
array($categories), // plotCategory
array($values) // plotValues
);
$series->setPlotDirection($plotDirection);
$layout = new PHPExcel_Chart_Layout();
$plotarea = new PHPExcel_Chart_PlotArea($layout, array($series));
$xTitle = new PHPExcel_Chart_Title('ستون');
$yTitle = new PHPExcel_Chart_Title('مقدار');
$chart = new PHPExcel_Chart('sample', null, null, $plotarea, true,0,$xTitle,$yTitle);
$chart->setTopLeftPosition('D2');
$chart->setBottomRightPosition('S30');
$sheet->addChart($chart);
$writer = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel2007');
$writer->setIncludeCharts(TRUE);
$writer->save('php://output');