如何使用PHPSpreadsheet将导出数据拆分至独立列

我正在做一个支付方式转token的项目，需要通过数据库客户号生成Method ID。已经完成一个HTML表单，可上传含客户号的Excel文件，提交后获取带Method ID的新文件，但所有新数据都挤在同一列里。

问题分析 #

当前代码存在几个核心问题：

• 未将API返回的Method ID写入工作表的新列，仅用print_r输出结果，最终保存的还是原上传文件内容
• 导出CSV时设置了错误的Content-Type（误用Excel的MIME类型），可能导致解析异常
• 未构建正确的多列数据结构，导致导出后内容混在一列

解决方案 #

以下是修改后的代码，核心调整包括：

1. 从API返回结果中提取Method ID并写入原工作表的相邻列（B列）
2. 修正CSV导出的HTTP头信息
3. 处理行号对应关系（PhpSpreadsheet行索引从1开始）

修改后的PHP代码 #

<?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);

    try
    {
        $file = $_FILES['file'];
        if (!$file || $file['error'] !== UPLOAD_ERR_OK)
        {
            throw new Exception('File upload failed');
        }
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file['tmp_name']);
        $worksheet = $spreadsheet->getActiveSheet();
        
        $highestRow = $worksheet->getHighestRow();
        
        // 写入Method ID表头
        $worksheet->setCellValue('B1', 'Method ID');

        // 从第2行开始处理客户号（假设第1行是表头）
        for ($row = 2; $row <= $highestRow; $row++) {
            $customer_number = $worksheet->getCell('A' . $row)->getValue();
            
            if (empty($customer_number)) continue;
            
            try
            {
                $customerData = $client->getCustomer($token, $customer_number);
                // 请根据API实际返回结构调整Method ID提取逻辑
                $methodId = $customerData->methodId ?? 'N/A';
                $worksheet->setCellValue('B' . $row, $methodId);
            }
            catch(SoapFault $e)
            {
                $errorMsg = "Error: " . $e->getMessage();
                $worksheet->setCellValue('B' . $row, $errorMsg);
                error_log($errorMsg . " for customer: " . $customer_number);
            }
        }

        // 导出CSV
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment;filename=output.csv');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Csv');
        $writer->setDelimiter(',');
        $writer->setEnclosure('"');
        $writer->save('php://output');
        exit;

    }
    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>

关键说明 #

• 请根据getCustomer API的实际返回结构，调整$methodId的提取逻辑（若返回为数组则用$customerData['methodId']）
• 设置CSV分隔符为逗号并添加引号包裹，确保Excel打开时能正确识别列
• 处理空客户号情况，避免无效API请求
• 将错误信息写入对应列，方便排查问题

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