我目前正在使用 PHPExcel (v1.8.0) 生成一个包含数据和图形的 xlxs 文件。
当我在 LibreOffice 上打开文件时,它运行良好,但在 Excel 中它会抛出并出错:"Excel 在 .."
当我在Excel中"修复"文件时,我有我的数据,但没有图表,并且此消息:"已删除部件:/xl/drawings/drawing1.xml部件。 (绘图形状)"
奇怪的事实:当我从LibreOffice另存为.xls时,我可以在Excel中读取带有图表的文件。
您可以在我的 Dropbox 上下载示例文件:在此处下载
这是生成我的 *.xlsx 文件的完整代码:
<?php
require_once('PHPExcel.php');
/* increments $nb times a column letter */
function getCol($col, $nb) {
for ($i = 0; $i < $nb; $i++) {
$col++;
}
return $col;
}
$objPHPExcel = new PHPExcel();
$objWorksheet = $objPHPExcel->getActiveSheet();
$sheetlabels = array("Groupe d'activité", "Données réelles", "Données simulées");
$sheetdatas = array();
$sheetdatas[] = array_map("utf8_encode", $sheetlabels);
foreach ($groups as $k => $group) {
$sheetdatas[] = array($group, $data_real[$k]*1, $data_simulated[$k]*1);
}
$sheetdatas[] = array("Total", $total_real*1, $total_simulated*1);
$nbdatas = count($groups);
$startCol = 'B';
$startLine = 25;
$objWorksheet->fromArray($sheetdatas, null, ($startCol . $startLine));
$endCol = getCol($startCol, count($sheetlabels)-1);
foreach(range($startCol, $endCol) as $columnID) {
$objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
}
$currentLine = $startLine + count($groups) +1;
$objWorksheet->getStyle($startCol . $currentLine . ':' . $endCol . $currentLine)->getFont()->setBold(true);
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
),
);
$objWorksheet->getStyle($startCol . $startLine . ':' . $endCol . $currentLine)->applyFromArray($styleArray);
$currentCol = getCol($startCol, 1);
$currentLine = $startLine + 1;
$endLine = $startLine + count($groups) + 1;
$objWorksheet->getStyle($currentCol . $currentLine . ':' . $endCol . $endLine)->getNumberFormat()->setFormatCode('# ##0.00');
$currentCol = $startCol;
$dataSeriesLabels = array(
new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$' . (++$currentCol) . '$' . $startLine, NULL, 1), // Données réelles
new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$' . (++$currentCol) . '$' . $startLine, NULL, 1) // Données simulées
);
$currentLine = $startLine + 1;
$xAxisTickValues = array(
new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$' . $startCol . '$' . $currentLine . ':$' . $startCol . '$' . ($currentLine + $nbdatas - 1), NULL, $nbdatas) // Tous les groups
);
$dataSeriesValues = array();
$currentCol = $startCol;
$currentLine = $startLine + 1;
foreach ($sheetlabels as $sheetlabel) {
if ($sheetlabel) {
$currentCol++;
$dataSeriesValues[] = new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$' . $currentCol . '$' . $currentLine . ':$' . $currentCol . '$' . ($currentLine + $nbdatas - 1), NULL, $nbdatas);
}
}
// 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
);
$plotArea = new PHPExcel_Chart_PlotArea(NULL, array($series));
$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
$title = new PHPExcel_Chart_Title('Consolidation ' . utf8_encode($label));
$chart = new PHPExcel_Chart(
'chart1', // name
$title, // title
$legend, // legend
$plotArea, // plotArea
true, // plotVisibleOnly
0, // displayBlanksAs
NULL, // xAxisLabel
$yAxisLabel // yAxisLabel
);
$chart->setTopLeftPosition('B2');
$chart->setBottomRightPosition('L22');
$objWorksheet->addChart($chart);
$filename = "export_" . $indicator_id . "_" . $date . ".xlsx";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setIncludeCharts(true);
$objWriter->save($dir . '/' . $filename);
?>
我不知道我在哪里犯了错误。
分辨率
好吧,我想出了问题所在。
似乎您必须指定绘图方向才能在Excel中出现问题
$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
);
$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_VERTICAL);
我向 GithHub 项目发出了拉取请求,以设置 plotDirection 默认值。
分辨率
好吧,我想出了问题所在。
似乎您必须指定绘图方向才能在Excel中出现问题
$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
);
$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_VERTICAL);
我向 GithHub 项目发出了拉取请求,以设置 plotDirection 默认值。