0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

[PHP]エクセルファイル読み書きとファイル定義

Posted at

[php]CSVファイル定義のconf化のエクセル版で、内容はほぼ同じです。

目的

  • 入出力ファイルの定義をconfでやりたい
  • メモリ節約しながらエクセル作りたい

対応

メモリ節約にはopenspoutがいい感じだった

csv版をエクセル用に以下を変更

  • 文字コード系設定削除
  • sheetの扱い追加
composer require openspout/openspout
config/file/kyaku.php
<?php
return [
    'user' => [
        'type'            => 'xlsx',
        'formatName'      => '契約者情報',
        'sheetName'       => 'Sheet2',
        'headerExist'     => true,
        'outputHeaderFlg' => true,
        'fixedValue' => [ // keyをvalueで記載
            'other' => '固定値',
        ],
        'guard' => [ // keyのcontent読込不要
            'other',
        ],
        'content' => [ // header => key名
            '契約番号' => 'contract_id',
            '契約者名' => 'user_name',
            '性別'    => 'gender',
            'その他'  => 'other',
        ],
    ],
];
作成処理
public function createXlsx($data, $fileName, $config): string
{
    $writer = new \OpenSpout\Writer\XLSX\Writer();
    $writer->openToFile(storage_path($fileName));
    $writer->getCurrentSheet()->setName($config['sheetName']);
    // ヘッダ有無判定し、中身を書き込む
    if($config['outputHeaderFlg']){
        $writer->addRow(\OpenSpout\Common\Entity\Row::fromValues(array_keys($config['content'])));
    }
    // 出力項目のみに絞り、出力の順番に並び替えて配列に変換
    $contentConfig = array_flip(array_values($config['content']));
    foreach ($data as $row) {
        // 出力項目に絞り、不足項目を空文字で埋め、固定値で上書き、出力順に並び替え
        $row = collect($row)->intersectByKeys($contentConfig)
            ->merge(array_fill_keys(array_flip(array_diff_key($contentConfig, $row)), ''))
            ->merge($config['fixedValue'])
            ->sortKeysUsing(fn($a,$b)=>$contentConfig[$a] <=> $contentConfig[$b])
            ->toArray();
        $writer->addRow(\OpenSpout\Common\Entity\Row::fromValues($row));
    }
    $writer->close();
    return $fileName;
}
読込処理
public function readXlsx($filePath, $config): Collection
{
    $reader = new \OpenSpout\Reader\XLSX\Reader;
    $reader->open($filePath);
    $response = collect();
    foreach ($reader->getSheetIterator() as $sheet) {
        if (!in_array($config['sheetName'], ['', $sheet->getName()])) {
            continue;
        }
        foreach ($sheet->getRowIterator() as $row) {
            $row = array_diff_key(array_combine($config['content'], $row->toArray()), array_flip($config['guard']));
            $response->push($row);
        }
        break;
    }
    $reader->close();
    // ヘッダ削除
    if($config['headerExist']){
        $response->shift();
    }
    return $response;
}
呼び出し
public function hoge(): void
{
    // ファイルを作成
    $data = [
        ['user_name'=>'佐々木','contract_id'=>1,'gender'=>'男','bug'=>'不要'],
        ['contract_id'=>2,'user_name'=>'佐藤','gender'=>'男',],
        ['contract_id'=>3,'user_name'=>'藤原','gender'=>'女',],
    ];
    $this->createXlsx($data, 'fuga.xlsx',config('file.kyaku.user'));        
    $data = $this->readXlsx(storage_path('fuga.xlsx'), config('file.kyaku.user'));
    dd($data);
}

結果

書込
スクリーンショット 2024-12-20 18.47.24.png

読込
Illuminate\Support\Collection^ {#857
  #items: array:3 [
    0 => array:3 [
      "contract_id" => 1
      "user_name" => "佐々木"
      "gender" => "男"
    ]
    1 => array:3 [
      "contract_id" => 2
      "user_name" => "佐藤"
      "gender" => "男"
    ]
    2 => array:3 [
      "contract_id" => 3
      "user_name" => "藤原"
      "gender" => "女"
    ]
  ]
  #escapeWhenCastingToString: false
}

おわり。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?