phpspreadsheetでシートコピーすると、出来上がったファイルが破損している

Question

前提・実現したいこと

ひな形となるシートをコピーし、新しくコピーしたシート側に各種データを埋め込む機能を実装中に以下のエラーメッセージが発生しました。

生成されたExcelファイルが破損してしまいます。


何が問題なのかが理解できず困っております
ご教示いただけますでしょうか、よろしくお願いします


【環境】

Linux側	Windows側
CentOS7 ＋ PHP7.3.19 ＋ phpspreadsheet(1.13.0)	Windows10 + Excel2019

発生している問題・エラーメッセージ

phpで生成されたExcelファイルを開くと下記のメッセージが表示されます。

該当のソースコード

<?php
require '../phpLibs/PhpSpreadsheet/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
use PhpOffice\PhpSpreadsheet\Reader\Xls as XlsReader;
use PhpOffice\PhpSpreadsheet\Writer\Xls as XlsWriter;
use PhpOffice\PhpSpreadsheet\Style\Style;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Conditional;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Document\Properties;

require '../phpLibs/Ginq/src/Ginq.php';
require '../config.php';

$reader = new XlsxReader();
$spreadsheet = $reader->load('../tmp/新規 Microsoft Excel ワークシート.xlsx');
$spreadsheet->setActiveSheetIndex(0);

$mastersheet = $spreadsheet->getSheetByName('Sheet1');

$worksheet = clone $mastersheet;
$worksheet->setTitle('シート１');
$spreadsheet->addSheet($worksheet);
        
$worksheet->setCellValue('A1','かきこみ');

$writer = new XlsxWriter($spreadsheet);
$writer->save('../tmp/TEST_generate.xlsx');

試したこと

• Windows10で右クリックして「新規作成」→「Microsoft Excel ワークシート」の操作を行って

新規 Microsoft Excel ワークシート.xlsx を作成し、何も変更していないファイルを
テンプレートとして使用し、上記プログラムを実行。
⇒ファイル破損のメッセージは表示されない。

• Windows10で右クリックして「新規作成」→「Microsoft Excel ワークシート」の操作を行って

新規 Microsoft Excel ワークシート.xlsx を作成し、適当なセルに値を入力して保存。
保存したファイルをテンプレートとして使用し、上記プログラムを実行。
⇒ファイル破損のメッセージが表示される。

• .xlsxではなく、.xlsファイルをテンプレートとして使用、上記プログラムを実行。

⇒ファイル破損のメッセージが表示される。

• テンプレートとなるファイル名を TEST.xlsxに変更して、上記プログラムを実行。

⇒ファイル破損のメッセージが表示される。

• セルに値を書き込む箇所をコメントにし、シートをコピーするだけでも破損していました。

// $worksheet->setCellValue('A1','かきこみ');
⇒ファイル破損のメッセージが表示される。

補足情報（FW/ツールのバージョンなど）

Comments

[m.ts10806]

>の一ぐの内容に問題が見つかりました。

コピペできないメッセージでしたら、画面キャプチャで良いですよ。

[m.ts10806]

>$spreadsheet = $reader->load('../tmp/新規 Microsoft Excel ワークシート.xlsx');

スペースや全角文字なしで半角英数のみのファイル名にした場合はどうでしょうか。

[makotokw]

> コピペできないメッセージでしたら、画面キャプチャで良いですよ。
誤字ありました。画面キャプチャでいいんですね。
承知しました。

> スペースや全角文字なしで半角英数のみのファイル名にした場合はどうでしょうか
入力元となるテンプレートファイルを,TEST.xlsxにして実行しても状況は変わらなかったです。

[makotokw]

セルに値を書き込む箇所をコメントにし、シートをコピーするだけでも破損していました。
// $worksheet->setCellValue('A1','かきこみ');

[m.ts10806]

んー
コード自体におかしいところはなさそうな気はしますが・・・
https://ja.coder.work/so/excel/1619291
試してみてください

[makotokw]

IOFactoryを使用するように修正して実行したのですが、変化なしです。
いろいろと調べて下さってうれしいです。

//$spreadsheet = $reader->load('../tmp/TEST.xlsx');
$spreadsheet = IOFactory::load('../tmp/TEST.xlsx');
：
：
//$writer = new XlsxWriter($spreadsheet);
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

$writer->save('../tmp/TEST_generate.xlsx');

[hentaiman]

例外が出るパターンは調査済みですか？
処理開始の行の手前からtry~catchで囲って何かしらエラーをキャッチしているならそれが原因です。
エラーを元に原因個所を修正して解消しましょう。
https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-files/#error-handling

[m.ts10806]

一応、die()（またはexit()）も試してみてください。
私もだいぶ昔ですが、exit()で締めることにより解決した経験はあります。

[m.ts10806]

あ、hentaimanさんの提示されているエラーハンドリングは必須ですね。
ファイル操作なので。（JavaだとIOException書かないと怒られます）

[makotokw]

try ～ catch で囲って実行したのですが、何も例外は発生しませんでした。

> 一応、die()（またはexit()）も試してみてください。
ブラウザに直接バイナリをレスポンスとして返すなら、exitを指定した方がいいとの記載も見つけて
既にやっていたのですが、状況は同じでした。

本当に原因不明で困りもんです...

[hentaiman]

そうなると後はテキストエディタで開いて余計な文字やエラーが出力されてないかを確認するしかないな
xlsxはxmlなのでテキストエディタで開けるので

[m.ts10806]

CentOS上でなかったらどうなんだろうと気にはなっています。
つまり、きちんとExcelが入っている環境だとどうか。

[makotokw]

作成されたxlsxをバイナリで見たところ、先頭がPKで始まっていてZIPファイル圧縮されている感じだったので、TEST_generate.xlsx.zip にリネームし、ZIPファイルを解凍してみたけど、余分な文字とかエラは出力されていませんでした。

[makotokw]

Linux上のphpspreadsheetフォルダと、テストプログラムをWindows上に持ってきて、同様のテストを
行ったところ、破損のエラーが発生しませんでした。

[hentaiman]

ということはもしかして
$worksheet->setTitle('シート１');
を
$worksheet->setTitle('sheet999');
にしたら破損しなくならないか？

[makotokw]

$worksheet->setTitle('シート１');
　　　　　　　　　　　　↓
$worksheet->setTitle('sheet999');

試してみましたが、ダメでした...

[hentaiman]

$worksheet->setCellValue('A1','かきこみ')
の処理無しでもダメでしたか？

[makotokw]

原因追及までしていませんが、なんとなく状況は把握できました。
phpspreadsheet＋Excel2019 の組み合わせが悪いみたいです．．．

Answer 1

詳細な状況がわかってきました。
テンプレートとなるExcelファイルのシートにプリンタ設定情報が含まている場合、
そのシートをコピーすると、コピーされた新しいシートにも同じプリンタ設定情報がコピーされます。

phpspreadsheetで、コピーしたシートのプリンタ設定情報の扱いがバグっている感じです。

【確認方法】
1．生成後エラーが出るxlsxファイルの拡張子をzipに変更。
2．エクスプローラーでzipファイルをダブルクリック。
3．フォルダ移動。　xl\printerSettings
※ここのフォルダに同じ名前の printerSettings1.bin がコピーしたシート数分あります。


phpspreadsheet自体を修正する技術量はないので、暫定対応で回避する。

【暫定対応】
1．phpspreadsheetで生成されたxlsxファイルをunzipで解凍し、重複ファイルを１つにする。
2．解凍されたファイルをzipで再圧縮する。　※拡張子は .xlsx で圧縮

require '../phpLibs/PhpSpreadsheet/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
use PhpOffice\PhpSpreadsheet\IOFactory;


// Excel2016の環境で、右クリック⇒新規作成⇒Microsoft Excel ワークシート
copyTemplateSheet('../tmp/Excel2016_Rclick_NEW.xlsx', 
                  '../tmp/Excel2016_Rclick_NEW_generate.xlsx');

// Excel2016の環境で、右クリック⇒新規作成⇒Microsoft Excel ワークシート ※新規作成後、ファイルを開いて内容を更新して保存
copyTemplateSheet('../tmp/Excel2016_Rclick_NEW_Save.xlsx', 
                  '../tmp/Excel2016_Rclick_NEW_Save_generate.xlsx');

// Excel2016の環境で、右クリック⇒新規作成⇒Microsoft Excel ワークシート ※新規作成後、ファイルを開いて内容を更新して保存（プリンタ設定を変更）
copyTemplateSheet('../tmp/Excel2016_Rclick_NEW_Save2.xlsx', 
                  '../tmp/Excel2016_Rclick_NEW_Save2_generate.xlsx');

// Excel2019の環境で、右クリック⇒新規作成⇒Microsoft Excel ワークシート
copyTemplateSheet('../tmp/Excel2019_Rclick_NEW.xlsx', 
                  '../tmp/Excel2019_Rclick_NEW_generate.xlsx');

// Excel2019の環境で、右クリック⇒新規作成⇒Microsoft Excel ワークシート ※新規作成後、ファイルを開いて内容を更新して保存
copyTemplateSheet('../tmp/Excel2019_Rclick_NEW_Save.xlsx', 
                  '../tmp/Excel2019_Rclick_NEW_Save_generate.xlsx');

exit;

function copyTemplateSheet($load_filename, $save_filename) {
    $save_folder = realpath(pathinfo($save_filename, PATHINFO_DIRNAME));
    $save_basename = pathinfo($save_filename, PATHINFO_BASENAME );
    
    try {
        $reader = new XlsxReader();
        $spreadsheet = $reader->load($load_filename);
        $spreadsheet->setActiveSheetIndex(0);
        
        $mastersheet = $spreadsheet->getSheetByName('Sheet1');
        
        $worksheet = clone $mastersheet;
        $worksheet->setTitle('シート１');
        $spreadsheet->addSheet($worksheet);
                
        $worksheet->setCellValue('A1','かきこみ');
        $writer = new XlsxWriter($spreadsheet);
        $writer->save($save_filename);
        
        // 解凍先のtmpフォルダ作成
        if(file_exists($save_filename.'.tmp')) {
            removeDirectory($save_filename.'.tmp');
        }
        mkdir($save_filename.'.tmp', 0777, true);

        //tmpフォルダにxlsxファイルを解凍　※xlsxファイルはzip圧縮されている？
        //この段階で、重複しているファイルが１つにまとめられる
        shell_exec('unzip -o '.$save_filename.' -d '.$save_filename.'.tmp');

        // 生成されたxlsxファイルを削除
        unlink($save_filename);
        
        // tmpフォルダの内容で再圧縮し、xlsxファイルを作成する
        $cwd = getcwd();
        chdir($save_filename.'.tmp');
        shell_exec('zip -r '.$save_folder.'/'.$save_basename.' *');
        chdir($cwd);

        // tmpフォルダを削除
        removeDirectory($save_filename.'.tmp');

    } catch(Exception $e) {
        die('Error: '.$e->getMessage());
    }
}

function removeDirectory($directory) {
    $result = true;
    foreach(new \RecursiveIteratorIterator(
        new \RecursiveDirectoryIterator($directory, \RecursiveDirectoryIterator::SKIP_DOTS),
        \RecursiveIteratorIterator::CHILD_FIRST
    ) as $file) {
        if ($file->isDir()) {
            $result &= @rmdir($file->getPathname());
        } else {
            $result &= @unlink($file->getPathname());
        }
    }
    return $result && @rmdir($directory);
}

【根本的な原因】
phpspreadsheetの内部で使用している、zipstream-phpというライブラリにバグがある。
https://github.com/maennchen/ZipStream-PHP/issues/154

xlsxファイルを解凍後に再圧縮するのではなく、phpspreadsheetを暫定修正する方法も考えてみた。

【phpspreadsheetを暫定的に修正】
既にZipファイルに追加されているファイルは複数回追加されないようにチェック。
vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx.php

public function save($pFilename)
　　　　　：
　　　　　：

《280行目付近》

            // Add worksheet relationships (drawings, ...)
            $added_files = array(); // ※追加
            for ($i = 0; $i < $this->spreadSheet->getSheetCount(); ++$i) {
                // Add relationships
                $zip->addFile('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->spreadSheet->getSheet($i), ($i + 1), $this->includeCharts));

                // Add unparsedLoadedData
                $sheetCodeName = $this->spreadSheet->getSheet($i)->getCodeName();
                $unparsedLoadedData = $this->spreadSheet->getUnparsedLoadedData();
                if (isset($unparsedLoadedData['sheets'][$sheetCodeName]['ctrlProps'])) {
                    foreach ($unparsedLoadedData['sheets'][$sheetCodeName]['ctrlProps'] as $ctrlProp) {
                        $zip->addFile($ctrlProp['filePath'], $ctrlProp['content']);
                    }
                }
                if (isset($unparsedLoadedData['sheets'][$sheetCodeName]['printerSettings'])) {
                    foreach ($unparsedLoadedData['sheets'][$sheetCodeName]['printerSettings'] as $ctrlProp) {
                        if(!isset($added_files[$ctrlProp['filePath']])) {    // ※追加
                            $zip->addFile($ctrlProp['filePath'], $ctrlProp['content']); 
                            $added_files[$ctrlProp['filePath']] = $ctrlProp['filePath'];     // ※追加
                        }    // ※追加
                    }
                }

Comments

[m.ts10806]

365シリーズだとどうなるか見ものですね。
ただやはり、PHPはファイル操作には不向きな言語と言えそうです。

[Daregada]

解決してないけど、公式のIssuesにも同じ結論を出している人がいた。

> After additional testing if I created my template entirely in PHP using the library it works correctly. The error only happens if I build a template in Excel (2019) and then use that file.

https://github.com/PHPOffice/PhpSpreadsheet/issues/1022#issuecomment-508284925

[hentaiman]

なるほどーこれは他者にも有益な自己解決だ

[makotokw]

Excelのバージョンは関係なく、テンプレートとなるExcelファイルにプリンタ設定情報が格納されているかどうかで動きが変わるかんじです。

プログラムで暫定対応して回避しました。