エクセルで長い数字の表示

私のあるプロジェクトにおいて、Laravelからエクセルを生成するプログラムがあります。DBからの値を整形してエクセルに流し込み、生成されたエクセルをウェブからダウンロードできるプログラムです。記録として残すため、あるいはそれを他のプログラムにインポートするために、エクセルを生成する必要があるわけなのですが、ちょっとしたややこしい問題がありました。今回はその解決方法を共有です。

エクセルの作成

エクセルを作成するプログラムは、有名なphpspreadsheetのライブラリを使用して作成します。

まず、ライブラリ使用のために、以下を実行してプロジェクトに取り込みます。

$ composer require phpoffice/phpspreadsheet

次にエクセル作成のコマンドプログラムの作成です。

$ php artisan make:command ExcelCommand

作成されたコマンドのファイルを以下のように編集します。phpspreadsheetからの例から取ってきました。実行するとファイルは、storage/app/Hello world.xlsxとして作成されます。

namespace App\Console\Commands;

use Illuminate\Console\Command;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExcelCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:excel';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Generate an Excel file';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Hello World !');

        $writer = new Xlsx($spreadsheet);
        $writer->save(storage_path('app/Hello world.xlsx'));
    }
}

セル値に長い番号があるときの表示

さて、上のプログラムを編集して、長い番号を表示してみます。

...
   public function handle()
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->getDefaultColumnDimension()->setWidth(20); //列の幅を広げる

        $sheet->setCellValue('A1', '12345678901');
        $sheet->setCellValue('A2', '123456789012'); // 12桁の数
        $sheet->setCellValue('A3', '123456789012345');　// 15桁の数

        $writer = new Xlsx($spreadsheet);
        $writer->save(storage_path('app/numbers.xlsx'));
    }
...

生成されたファイルをエクセルでオープンすると、以下のように表示されます。

見ての通り、番号が12桁以上なら、指数として表示されます。
さらに、15桁以上となると、セル値そのものが変わって15桁からの数字が0に変わってしまいます。

この解決方法として一番簡単なのは、数字の前に文字、例えば半角スペースを入れることです。

...
        $sheet->setCellValue('A1', '12345678901');
        $sheet->setCellValue('A2', ' 123456789012'); // スペース＋12桁の数
        $sheet->setCellValue('A3', ' 123456789012345');　// スペース＋15桁の数
...

数字すべて表示されるようになりましたね。しかし、数字の前に半角スペースがあるのが気に入らない、あるいは他のプログラムにインポートするときに、いちいちスペースをトリムする必要があり問題となるかもしれません。

最終の解決方法

数字を指数として表示せずに、また値に半角を入れずにオリジナルの数値をキープ方法というのは、数字とでなくテキストとして値を扱うことですが、以下のように違う関数、setCEllValueExplicity()の使用となります。

...
        $sheet->setCellValueExplicit('A1', '123456789012345',
            \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
        );
...

結果は、以下のような表示となり、値もオリジナルと同じとなりました。

今回役だった情報は以下のphpspreadsheetのgithubから来ました：

https://github.com/PHPOffice/PHPExcel/issues/1148
https://github.com/PHPOffice/PhpSpreadsheet/issues/357