PhpSpreadsheetで複数シートに書き込まれるデータの順番が狂ってしまう

Question

複数の注文データをエクセルに出力してダウンロードする機能を実装しているのですが、複数のシートに書き込まれる注文データの順番が狂ってしまいます。

エクセル生成部分のコードは下記の通りです。
処理の順番としては、注文一覧画面で複数の注文データにチェックを付けて、ajaxでpost送信→下記の処理でエクセルを生成し、base64_encodeしてjsonを返す、という流れにしています。

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx;
        $reader->setReadDataOnly(false);
        $spreadsheet = $reader->load(storage_path('excel/order_template.xlsx'));
        
        // $order_number_arrayは注文一覧でチェックを入れてpost送信した複数の注文番号の配列
        $order_number_array = $request->post('order_number');
        
        foreach($order_number_array as $key => $order_number) {
            //  ループの2回目以降はシートを複製
            if ($key > 0) {
                $sheet_num = $key + 1;
                $clone_spreadsheet = clone $spreadsheet->getSheetByName('Table 1');
                $clone_spreadsheet->setTitle('Table '.$sheet_num);
                $spreadsheet->addSheet($clone_spreadsheet);
            }
            $sheet = $spreadsheet->getActiveSheet();
            
            $orders = Order::where('order_number', $order_number)->get();
            
            // 注文情報をエクセルへ埋め込み
            foreach ($orders as $key1 => $order) {
                // 注文情報
                $sheet->setCellValue('D5', $order->order_number);
                $sheet->setCellValue('D6', $order->purchase_order_number);
                $sheet->setCellValue('D8', $order->customer_name);
                $sheet->setCellValue('D9', $order->customer_address);
                
                // 商品情報
                $row_num = 12 + $key1;
                $sheet->setCellValue('A'.$row_num, $order->jan_code);
                $sheet->setCellValue('D'.$row_num, $order->product_name);
                $sheet->setCellValue('N'.$row_num, $order->order_amount);
            }
        }
        
        ob_start();
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        $xlsxData = ob_get_contents();
        ob_end_clean();
        
        return response()->json([
            'status' => \Illuminate\Http\Response::HTTP_OK,
            'file' => 'data:application/vnd.ms-excel;base64,' . base64_encode($xlsxData),
            'datetime' => date('mdHis')
        ]);

この結果、エクセルには「Table 1」「Table 2」…のシートができていますが、例えば、上記の$order_number_arrayが

$order_number_array = [
  [0] => 'order_number1',
  [1] => 'order_number2',
  [2] => 'order_number3',
  [3] => 'order_number4',
  [4] => 'order_number5',
];

だとすると、

Table1 にorder_number5のデータ
Table2 にorder_number1のデータ
Table3 にorder_number2のデータ
Table4 にorder_number3のデータ
Table5 にorder_number4のデータ

が入ってしまいます。

foreach($order_number_array as $key => $order_number) {

}

の最初のループ時に

var_dump($sheet);
var_dump($orders);

を確認すると、$sheetの出力内容には「Table1」という文字列があり、$ordersの出力内容は、最初にチェックを入れた注文データになっているのですが、なぜか、そのデータは「Table 2」に入ってしまい、「Table 1」には最後にチェックを入れたデータが入ってしまいます。

ちょっと原因が分からず、ご教示いただければ幸いです。

バージョンについては、PHP7.2、Laravel5.6、エクセル出力にはPhpSpreadsheetを使っています。

よろしくお願いいたします。

Answer 1

            //  ループの2回目以降はシートを複製
            if ($key > 0) {
                $sheet_num = $key + 1;
                $clone_spreadsheet = clone $spreadsheet->getSheetByName('Table 1');
                $clone_spreadsheet->setTitle('Table '.$sheet_num);
                $spreadsheet->addSheet($clone_spreadsheet);
            }
            $sheet = $spreadsheet->getActiveSheet();

$key > 0 のとき、新しいシートを追加していますが、addSheet したシートが ActiveSheet になる、なんて挙動は明示されてないはずです。ですから次の getActiveSheet() の結果が意図したとおりになるとは限らないのでは？

            if ($key > 0) {
                $sheet_num = $key + 1;
                $clone_spreadsheet = clone $spreadsheet->getSheetByName('Table 1');
                $clone_spreadsheet->setTitle('Table '.$sheet_num);
                $spreadsheet->addSheet($clone_spreadsheet);
            }
            $sheet = $spreadsheet->getSheetByName('Table '.$key + 1);

としたらどうでしょうか。

Comments

[avantgarden]

ありがとうございます。
たしかに、追加したシートをActiveSheetにする所がないですね。。
ご指摘の内容を元に、

if ($key > 0) {
$sheet_num = $key + 1;
$clone_spreadsheet = clone $spreadsheet->getSheetByName('Table 1');
$clone_spreadsheet->setTitle('Table '.$sheet_num);
$spreadsheet->addSheet($clone_spreadsheet);
} else {
$sheet_num = 1;
}

$sheet = $spreadsheet->getSheetByName('Table '.$sheet_num);

としたところ、順番通りにシートが出力されました！

ありがとうございました！