PhpSpreadsheet - XLSX - Read charts in order

Question

I'm using library PHPOffice/PhpSpreadsheet to read charts.

Right now it seems that reading charts is in the order of pasted charts (doesn't matter if the later added chart is above previous charts).

I'd like to find way to mapping charts in correct order. Is it possible, to create order from top to bottom? So always the most top chart will be in the index '0'?

Also is there way to read the name of the chart - I mean the field in the Exel when you select a chart:

$reader = IOFactory::createReader('Xlsx');
$reader->setIncludeCharts(true);
$reader->setLoadAllSheets();
$spreadsheet = $reader->load('file.xlsx');

$loadedSheetNames = $spreadsheet->getSheetNames()

foreach ($loadedSheetNames as $sheetIndex => $loadedSheetName) {
  $spreadsheet->setActiveSheetIndexByName($loadedSheetName);
  $activeSheet = $spreadsheet->getActiveSheet();
  $chartNames = $activeSheet->getChartNames();
}

score 0 · Answer 1 · answered Jul 24 '23 at 16:02

You can get all the information about a chart via the ChartCollection. With $activeSheet-> getTopLeftCell() you can get the position of the chart in Excel and then sort it. The name is then used to get the index.

$realChartSorting contains the sorting as in the ExcelSheet.


foreach ($loadedSheetNames as $sheetIndex => $loadedSheetName) {
    $spreadsheet->setActiveSheetIndexByName($loadedSheetName);
    $activeSheet = $spreadsheet->getActiveSheet();
    $realChartSorting = $this->getRealChartOrdering($activeSheet);
    foreach ($realChartSorting as $item) {
        $activeSheet->getChartByName($realChartSorting['chartName']);
        $activeSheet->getChartByIndex($realChartSorting['chartIndex']);
    }
}


private function getRealChartOrdering(Worksheet $activeSheet): array {
    $chartData = [];
    $chartNames = $activeSheet->getChartNames();
    $chartCollection = $activeSheet->getChartCollection();
    foreach ($chartCollection as $item) {
        $chartData[] = [
            'name' => $item->getName(),
            'pos' => $item->getTopLeftCell()
        ];
    }

    // Sort by position
    array_multisort(
        array_column($chartData, 'pos'), SORT_ASC,
        $chartData
    );

    $realChartSorting = [];
    foreach ($chartData as $data) {
        $realChartSorting[] = [
            'chartIndex' => array_search($data['name'], $chartNames),
            'chartName' => $data['name']
        ];
    }

    return $realChartSorting;
}

PhpSpreadsheet - XLSX - Read charts in order

1 Answers1