Export Data into Separate Columns using PHPSpreadsheet

I am working on a project to convert payment methods into tokens. To do this, I need to generate Method IDs using the customer numbers from my database. I have created a simple HTML form where I can upload an excel sheet with the customer numbers and receive a new excel sheet with the updated information (when submitting the form), including the Method IDs. However, all the new data is appearing in a single column. Can you show me how to separate the data into separate columns in the output? Here is an example of the current output.

Excel export

Here is my code

<?php
ob_start();

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

if ($_SERVER["REQUEST_METHOD"] === "POST")
{
    $wsdl = "https://sandbox.usaepay.com/soap/gate/43R1QPKU/usaepay.wsdl";
    $sourceKey = "_g6BALVW9vpPZ3jEqf5kwe4pIrqyvabY";
    $pin = "1234";

    function getClient($wsdl)
    {
        return new SoapClient($wsdl, array(
            'trace' => 1,
            'exceptions' => 1,
            'stream_context' => stream_context_create(array(
                'ssl' => array(
                    'verify_peer' => false,
                    'verify_peer_name' => false,
                    'allow_self_signed' => true
                )
            ))
        ));
    }

    function getToken($sourceKey, $pin)
    {
        $seed = time() . rand();

        return array(
            'SourceKey' => $sourceKey,
            'PinHash' => array(
                'Type' => 'sha1',
                'Seed' => $seed,
                'HashValue' => sha1($sourceKey . $seed . $pin)
            ) ,
            'ClientIP' => $_SERVER['REMOTE_ADDR']
        );
    }

    $client = getClient($wsdl);
    $token = getToken($sourceKey, $pin);

    // Load the customer numbers from the uploaded Excel file
    try
    {
        // Load the customer numbers from the uploaded Excel file
        $file = $_FILES['file'];
        if (!$file)
        {
            throw new Exception('File not uploaded');
        }
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file['tmp_name']);
        $worksheet = $spreadsheet->getActiveSheet();
        $customer_numbers = $worksheet->toArray();

        // Generate the method ID for each customer number
        foreach ($customer_numbers as $customer_number)
        {
            try
            {
                print_r($client->getCustomer($token, $customer_number[0]));
            }
            catch(soapFault $e)
            {
                // Code to handle the exception
                echo "An error occurred for customer number: " . $customer_number[0] . " - " . $e->getMessage() . "\n";
            }

        }

        // Save the new Excel sheet with the information and Method IDs
        $outputFileName = 'output.csv';
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Csv');
        $writer->save($outputFileName);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=output.csv');

    }
    catch(Exception $e)
    {
        echo "An error occurred: " . $e->getMessage() . "\n";
    }

}

ob_end_flush();
?>

HTMl

<!DOCTYPE html>
<html>
  <head>
    <title>Method ID Generator</title>
  </head>
  <body>
    <h1>Method ID Generator</h1>
    <form action="getmethodID.php" method="post" enctype="multipart/form-data">
      <label for="file">Upload Excel File:</label>
      <input type="file" name="file" id="file">
      <br><br>
      <input type="submit" value="Submit">
    </form>
  </body>
</html>

I have already spent over 2 hours figuring it out can anyone help me here?

Solution

You are using print_r which will send the output of your function back to the browser, and setting the headers will instruct the browser to offer the file for download. This is the file you have showed in your screenshot which contains the output of print_r.

At the same time you are exporting a CSV file from php spreadsheet which will save the file on your web server in the same directory as the php script (assuming it has permission). You have not shown us the content of this file but it will be the same as your input file as your script never writes to the $spreadsheet object.

You need to write the content from your function into the $spreadsheet object.

$sheet = $spreadsheet->getActiveSheet();
// export headers
$sheet->setCellValue('A1','Ammount');
$sheet->setCellValue('B1','City');
// more headers ...

$spreadsheetrow = 2; // set a row counter
foreach ($customer_numbers as $customer_number){
    $data = $client->getCustomer($token, $customer_number[0]);
    // export data
    $sheet->setCellValue('A'.$spreadsheetrow,$data['Ammount']);
    $sheet->setCellValue('B'.$spreadsheetrow,$data['Billing']['City']);
    // etc..
    $spreadsheetrow++;  // increment row counter
}

You can then save the file inline back to the browser:

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=output.csv');
$writer->save('php://output');   // Exports inline to browser, rather than saving to file

Alternativly for CSV you can use built in PHP functions:

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment;filename=output.csv');
$output = fopen('php://output', 'w');
fputcsv($output, ['Ammount', 'City']);
foreach ($customer_numbers as $customer_number){
    $data = $client->getCustomer($token, $customer_number[0]);
    fputcsv($data['Ammount'], $data['Billing']['City']);
}