PhpOffice convert excel to json

Question

I am trying to convert an excel file to a specific json output, using PhpOffice (PhpSpreadsheet) library. The excel sheet looks like this:

and i am looking to achieve this json_encode:

{
"As24 sheet": [
    {
        "Contract": "656203",
        "Vehicle card": "0130",
        "Driver card": "",
        "Product": "03",
        "Fuel type": "DIESEL",
        "Volume": "92.00",
        "Date": "20210316",
        "Hour": "0355"
    },
    {
        "Contract": "656203",
        "Vehicle card": "0211",
        "Driver card": "",
        "Product": "03",
        "Fuel type": "DIESEL",
        "Volume": "74.30",
        "Date": "20210316",
        "Hour": "0415"
    },
    {
        "Contract": "656203",
        "Vehicle card": "0197",
        "Driver card": "",
        "Product": "03",
        "Fuel type": "DIESEL",
        "Volume": "33.70",
        "Date": "20210316",
        "Hour": "0522"
    },
    {
        "Contract": "656203",
        "Vehicle card": "0104",
        "Driver card": "",
        "Product": "03",
        "Fuel type": "DIESEL",
        "Volume": "55.13",
        "Date": "20210316",
        "Hour": "0553"
    },
    {
        "Contract": "656203",
        "Vehicle card": "0066",
        "Driver card": "",
        "Product": "03",
        "Fuel type": "DIESEL",
        "Volume": "105.63",
        "Date": "20210316",
        "Hour": "0756"
    },
    {
        "Contract": "656203",
        "Vehicle card": "0167",
        "Driver card": "",
        "Product": "03",
        "Fuel type": "DIESEL",
        "Volume": "54.88",
        "Date": "20210316",
        "Hour": "0757"
    }
]

}

This is my current code, but i am not able to group the first row which is the header, with each column, like in the json example above. Any help would be apreciated.

<?php 
require_once("vendor/autoload.php"); 

/* Start to develop here. Best regards https://php-download.com/ */



$inputFileType = 'Xlsx';
$inputFileName = 'motorina.xlsx';

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Advise the Reader that we only want to load cell data  **/
$reader->setReadDataOnly(true);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
$worksheet = $spreadsheet->getSheet(0);//
// Get the highest row and column numbers referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

$data = array();


for ($row = 2; $row <= $highestRow; ++$row) {
    for ($col = 1; $col <= $highestColumnIndex; ++$col) {
    
        $data[] = array(
        $worksheet->getCellByColumnAndRow($col, 1)->getValue() => $worksheet->getCellByColumnAndRow($col, $row)->getValue()
        );
    }

}    

echo json_encode($data);

Answer 1

You're assembling your array wrong, adding each time a single pair ( label => value ). You want a whole row in a single dictionary, and that dictionary to be added to your $data.

$data = array();

for ($row = 1; $row <= $highestRow; $row++) {
    $riga = array();
    for ($col = 1; $col <= $highestColumnIndex; $col++) {
        $riga[] = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
    }
    if (1 === $row) {
        // Header row. Save it in "$keys".
        $keys = $riga;
        continue;
    }
    // This is not the first row; so it is a data row.
    // Transform $riga into a dictionary and add it to $data.
    $data[] = array_combine($keys, $riga);
}