如何为帕累托图添加次Y轴？代码实现未达预期求助

问题 #

尝试绘制带次Y轴的帕累托图（柱状图+右侧次Y轴折线图），但当前生成的图表中折线图与柱状图共用左侧Y轴，未正确启用次Y轴，达不到预期效果。已查阅官方文档及GitHub示例未获有效帮助。

现有代码 #

$dataSeriesLabels = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'ErrorFrequency!$C$1', null, 1),
];
$xAxisTickValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'ErrorFrequency!$B$2:$B$'.($this->rowIndex-1), null, $totalRowsCount),

];
$dataSeriesValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'ErrorFrequency!$C$2:$C$'.($this->rowIndex-1), null, $totalRowsCount),
];
$series = new DataSeries(
    DataSeries::TYPE_BARCHART,
    DataSeries::GROUPING_CLUSTERED,
    range(0, count($dataSeriesValues) - 1),
    $dataSeriesLabels,
    $xAxisTickValues,
    $dataSeriesValues
);

// second axis
$dataSeriesLabels2 = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'ErrorFrequency!$E$1', null, 1),
];
$dataSeriesValues2 = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'ErrorFrequency!$E$2:$E$'.($this->rowIndex-1), null, $totalRowsCount),
];

// Build the dataseries
$series2 = new DataSeries(
    DataSeries::TYPE_LINECHART,
    DataSeries::GROUPING_STANDARD ,
    range(0, count($dataSeriesValues2) - 1),
    $dataSeriesLabels2,
    [],
    $dataSeriesValues2
);

$series2->setPlotDirection(DataSeries::DIRECTION_VERTICAL);


$plotArea = new PlotArea(null, [$series,$series2]);
$legend = new Legend(Legend::POSITION_RIGHT, null, false);

$title = new Title('Pareto Chart - Error Types (20% of errors cause 80% of issues)');
$yAxisLabel = new Title('Error Counts');
$secondaryYAxisLabel = new Title('Secondary Axis Label');  


$yaxis = new Axis();
$xaxis = new Axis();
$yaxis->setAxisOptionsProperties('low', null, null, null, null, null, -20, 20, null, null);

$xaxis->setAxisOptionsProperties('low', null, null, null, null, null, 0, 0, null, null);

// Create the chart
$chart = new Chart(
    'chart',
    $title,
    $legend,
    $plotArea,
    true,
    DataSeries::EMPTY_AS_GAP,
    null, // xAxisLabel
    $yAxisLabel,  // yAxisLabel
    null, // xAxis
    null,  // yAxis
    null,  // majorGridlines
    null,  //minor Gridlines
    $secondaryYAxisLabel  
);
$chart->setTopLeftPosition('I3');
$chart->setBottomRightPosition('Z25');
$worksheet = $this->spreadsheet->getActiveSheet();
$worksheet->addChart($chart);

解决方法 #

核心是给折线图系列指定使用次Y轴，并正确初始化次Y轴实例传入图表：

1. 标记折线图使用次Y轴
   在创建$series2后添加：

   $series2->setPlotAxis(DataSeries::AXIS_SECONDARY);
   

2. 初始化并配置次Y轴
   替换单纯的标签，创建完整的次Y轴实例（帕累托的累计百分比通常范围是0-100）：

   // 替换原$secondaryYAxisLabel的定义
   $secondaryYAxisLabel = new Title('Cumulative Percentage');
   $secondaryYAxis = new Axis();
   $secondaryYAxis->setAxisOptionsProperties('low', null, null, null, null, null, 0, 100, null, null);
   $secondaryYAxis->setTitle($secondaryYAxisLabel);
   

3. 修改图表初始化参数
   将图表构造函数的最后一个参数从$secondaryYAxisLabel改为$secondaryYAxis，同时传入主Y轴实例$yaxis：

   $chart = new Chart(
       'chart',
       $title,
       $legend,
       $plotArea,
       true,
       DataSeries::EMPTY_AS_GAP,
       null, // xAxisLabel
       null,  // 这里不再传yAxisLabel，因为已在Axis实例中设置
       $xaxis, // 传入定义好的X轴实例
       $yaxis,  // 传入主Y轴实例
       null,  // majorGridlines
       null,  //minor Gridlines
       $secondaryYAxis  // 传入次Y轴实例
   );
   

4. 额外优化
   帕累托图要求柱状图按数值降序排列，确保数据源ErrorFrequency!$B$2:$C$N已按错误频率从高到低排序，否则图表不符合帕累托分析逻辑。

修改后的完整代码 #

$dataSeriesLabels = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'ErrorFrequency!$C$1', null, 1),
];
$xAxisTickValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'ErrorFrequency!$B$2:$B$'.($this->rowIndex-1), null, $totalRowsCount),
];
$dataSeriesValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'ErrorFrequency!$C$2:$C$'.($this->rowIndex-1), null, $totalRowsCount),
];
$series = new DataSeries(
    DataSeries::TYPE_BARCHART,
    DataSeries::GROUPING_CLUSTERED,
    range(0, count($dataSeriesValues) - 1),
    $dataSeriesLabels,
    $xAxisTickValues,
    $dataSeriesValues
);

// 次轴系列配置
$dataSeriesLabels2 = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'ErrorFrequency!$E$1', null, 1),
];
$dataSeriesValues2 = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'ErrorFrequency!$E$2:$E$'.($this->rowIndex-1), null, $totalRowsCount),
];

$series2 = new DataSeries(
    DataSeries::TYPE_LINECHART,
    DataSeries::GROUPING_STANDARD ,
    range(0, count($dataSeriesValues2) - 1),
    $dataSeriesLabels2,
    [],
    $dataSeriesValues2
);
$series2->setPlotDirection(DataSeries::DIRECTION_VERTICAL);
// 关键：指定次Y轴
$series2->setPlotAxis(DataSeries::AXIS_SECONDARY);

$plotArea = new PlotArea(null, [$series,$series2]);
$legend = new Legend(Legend::POSITION_RIGHT, null, false);

$title = new Title('Pareto Chart - Error Types (20% of errors cause 80% of issues)');
$yAxisLabel = new Title('Error Counts');

// 初始化主Y轴
$yaxis = new Axis();
$yaxis->setAxisOptionsProperties('low', null, null, null, null, null, 0, null, null, null); // 调整主Y轴范围为0到自动最大值
$yaxis->setTitle($yAxisLabel);

// 初始化X轴
$xaxis = new Axis();

// 初始化次Y轴
$secondaryYAxisLabel = new Title('Cumulative Percentage');
$secondaryYAxis = new Axis();
$secondaryYAxis->setAxisOptionsProperties('low', null, null, null, null, null, 0, 100, null, null);
$secondaryYAxis->setTitle($secondaryYAxisLabel);

// 创建图表
$chart = new Chart(
    'chart',
    $title,
    $legend,
    $plotArea,
    true,
    DataSeries::EMPTY_AS_GAP,
    null, // xAxisLabel
    null,  // 这里不再传yAxisLabel，因为已经在Axis实例中设置
    $xaxis, // xAxis
    $yaxis,  // 主Y轴
    null,  // majorGridlines
    null,  //minor Gridlines
    $secondaryYAxis  // 次Y轴实例
);
$chart->setTopLeftPosition('I3');
$chart->setBottomRightPosition('Z25');
$worksheet = $this->spreadsheet->getActiveSheet();
$worksheet->addChart($chart);

内容的提问来源于stack exchange，提问作者Rehamjf