PDA

View Full Version : سوال: مشکل با خروجی phpexcel


milad_d993
سه شنبه 11 آذر 1399, 07: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');
plague
سه شنبه 11 آذر 1399, 13:54 عصر
من کار نکردم باهاش ولی سرچ کن تو گوگل

phpexcel x-axis labels

ببین چجوری لیبل براش باید تعریف کنی
peyman32
یک شنبه 16 آذر 1399, 10:28 صبح
دوست نازنبنم
میلاد جان
به نظر میرسه شما باید قبلش این ویژگی رو true کنید:
$layout1->setShowVal(TRUE); // Initializing the data labels with Values


بلاک کامل کد:

$series = new PHPExcel_Chart_DataSeries(
PHPExcel_Chart_DataSeries::TYPE_BARCHART, // plotType
PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, // plotGrouping
array(0, 1), // plotOrder
$labels, // plotLabel
$categories, // plotCategory
$values // plotValues
);

$layout1 = new PHPExcel_Chart_Layout();
$layout1->setShowVal(TRUE); // Initializing the data labels with Values
$layout1->setShowPercent(TRUE); // Initializing the data labels with Percentages

از طرفی در گیتهاب اومده این کتاب خونه مرده!! DEAD و دیگه ساپورت نمیشه

پیج گیتهاب کتابخانه با مثال های کاربردی:
https://github.com/PHPOffice/PHPExcel

لینک به مشکل شما در استک:
https://stackoverflow.com/questions/12263366/phpexcel-piechart-labels-and-legends







سلام دوستان
با کد ها زیر خروجی اکسل گرفتم. فقط مشکلی که داره اینه که توی چارتی که میسازه در محور 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');
milad_d993
یک شنبه 16 آذر 1399, 13:49 عصر
ممنون از شما
کد ها رو ی مقدار تغییر دادم و نسخه بروزتر PHPExcel رو جایگزین کردم، نتیجه برای نمودار میله ای و خطی خوب شده؛ برای pie هم بهتر شده ولی هنوز مشخص نیست چی به چیه؛ مشکل مربوط به legend هست... :اشتباه:
152595
152596


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_para ms=[[[\"cf_759\",\"e\",\" 3374\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\" 3397\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\" 3417\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\" 4617\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"1\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"200\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"2196\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"2443\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"2732\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"2895\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"3417\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"3722\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"4\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"4014\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"4590\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"4617\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"5094\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"aaa\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"df\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"hiby_2\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"n\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"salam\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"salam2\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"cf_759\",\"e\",\"w\"]]]","index.php?module=HelpDesk&view=List&search_para ms=[[[\"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]);
$val = (is_array($data['values'][$i]))?$data['values'][$i][0]:$data['values'][$i];
array_push($values, $val);
}

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);
}

//$sheet->fromArray($exportAnswers, null, 'A1');

$dataseriesLabels = array(
new PHPExcel_Chart_DataSeriesValues('String', 'Data!$A$1', NULL, 1),
new PHPExcel_Chart_DataSeriesValues('String', 'Data!$B$1', NULL, 1)
);

$values = new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$'.($n+1), NULL,$n);
$categories = new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$'.($n+1), NULL,$n);

$charttype = PHPExcel_Chart_DataSeries::TYPE_BARCHART;
$plotDirection = PHPExcel_Chart_DataSeries::DIRECTION_VERTICAL;
$getChartType = strtolower($request->get('charttype'));
switch ($getChartType){
case 'piechart':
$charttype = PHPExcel_Chart_DataSeries::TYPE_PIECHART;
$plotDirection = PHPExcel_Chart_DataSeries::DIRECTION_COL;
break;
case 'linechart':
$charttype = PHPExcel_Chart_DataSeries::TYPE_LINECHART;
break;
case 'horizontalbarchart':
$plotDirection = PHPExcel_Chart_DataSeries::DIRECTION_HORIZONTAL;
break;
}

$series = new PHPExcel_Chart_DataSeries(
$charttype, // plotType
PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, // plotGrouping
range(0, count($values)-1), // plotOrder
$dataseriesLabels, // plotLabel
array($categories), // plotCategory
array($values) // plotValues
);
$series->setPlotDirection($plotDirection);

$layout = new PHPExcel_Chart_Layout();
$layout->setShowVal(TRUE); // Initializing the data labels with Values
$layout->setShowPercent(TRUE); // Initializing the data labels with Percentages
$plotarea = new PHPExcel_Chart_PlotArea($layout, array($series));
$legend = ($getChartType == 'piechart')?new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSIT ION_RIGHT, NULL, true):null;
$xTitle = new PHPExcel_Chart_Title('ستون');
$yTitle = new PHPExcel_Chart_Title('مقدار');
$title = new PHPExcel_Chart_Title($graphLabel);
$chart = new PHPExcel_Chart(
'sample',
$title,
$legend,
$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');