PHPSpreadsheet生成带有图表的无效文件 [英] PHPSpreadsheet generates invalid file with charts

问题描述

我正在将现有/正在运行的应用程序从 PHPExcel ( phpoffice/phpexcel:1.8.0 )迁移到 PHPSpreadsheet ，生成包含一个或多个图表的Excel文件时出现的问题.

I am migrating the existing/working app from PHPExcel (phpoffice/phpexcel: 1.8.0) to PHPSpreadsheet and I have hit an issue when generating Excel file containing one or more charts.

没有任何 addChart 调用，生成的文件是有效的，但是一旦添加图表，生成的文件就会变得无效.Excel确实会尝试恢复数据，但成功但没有图表.

Without any addChart calls, the generated file is valid, but as soon as I add a chart, the resulting file becomes invalid. Excel does attempt to recover the data, which succeeds but without a chart.

Excel恢复工具显示:

Excel recovery tool shows:

Excel completed file level validation and repair. 
Some parts of this workbook may have been repaired or discarded.
Removed Part: /xl/drawings/drawing1.xml part.  (Drawing shape)

当前使用:PHP 7.1和 phpoffice/phpspreadsheet:"1.10.1"

Current using: PHP 7.1 and phpoffice/phpspreadsheet: "1.10.1"

我的代码与我要添加的PieChart示例非常相似.

My code is very similar to Example for PieChart, which I am attempting to add.

生成PieChart的部分代码:

Part of the code which generates PieChart:

• $ ageCounterRowIndex 的值为6
• $ expectedPointCount 的值为4
• 我的数据刻度值位于 D3:D6
• 我的值位于 E3:E6

• $ageCounterRowIndex has value 6
• $expectedPointCount has value 4
• My data tick values are located in D3:D6
• My values are located in E3:E6

$dataSeriesLabels = [];

$xAxisTickValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, "$quotedSheetTitle\$D\$3:\$D\$$ageCounterRowIndex", $expectedPointCount)
];

$dataSeriesValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, "$quotedSheetTitle\$E\$3:\$E\$$ageCounterRowIndex", $expectedPointCount),
];

$dataSeries = new DataSeries(
    DataSeries::TYPE_PIECHART,
    NULL, 
    range(0, count($dataSeriesValues) - 1), 
    $dataSeriesLabels, 
    $xAxisTickValues, 
    $dataSeriesValues 
);

$pieLayout = new Layout();
$pieLayout->setShowVal(TRUE)->setShowPercent(TRUE);

$plotArea = new PlotArea($pieLayout, [ $dataSeries ]);
$legend = new Legend(Legend::POSITION_RIGHT, NULL, FALSE);

$chart = new Chart(
    'piechart_' . ($sheetIndex - 1), 
    null,
    $legend, 
    $plotArea, 
    true, 
    0, 
    NULL, 
    NULL 
);

$chart->setTopLeftPosition('G2');
$chart->setBottomRightPosition('L15');
$sheet->addChart($chart);

推荐答案

将新图表"调用中的 displayBlanksAs 参数从 0 更改为'gap'为我解决了这个问题.

Changing the displayBlanksAs parameter in the New Chart call from 0 to 'gap' solved this issue for me.

我在 PhpSpreadsheet存储库

$chart = new Chart(
    'piechart_' . ($sheetIndex - 1), 
    null,
    $legend, 
    $plotArea, 
    true, 
    0, 
    NULL, 
    NULL 
);

成为

$chart = new Chart(
    'piechart_' . ($sheetIndex - 1), 
    null,
    $legend, 
    $plotArea, 
    true, 
    'gap', 
    NULL, 
    NULL 
);

这篇关于PHPSpreadsheet生成带有图表的无效文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！