Aska

Excelのカラムの時間を取得したい

クライアントエンジニアもサーバ側のCMSの開発も行っている山中です。

「Xlsmファイルの特定シートの値を取得したい」 でExcelファイルをインポートについて話しましたが、 自作のxlsx形式のインポート処理にて 2021-11-24 10:00:00 のデータを入れようとしましたら、 44524.416666666664 と予想外の値がインポートされました。

zipファイルとして展開してsheet1.xmlを開くと

 <c r="E7" s="11">
<v>44524.416666666664</v>
</c>

下記の参考URLによるとこの数値のことを「日付シリアル値」と呼びます。 西暦1900年1月1日午前0時ちょうどを1として、24時間を１として表します。

参考URL:https://www.forguncy.com/blog/20170622_datetime

このs="11"というのがおそらく日時を表しているのかと思いましたがそう単純では無さそうです。

2021-11-24 10:00:00の表示形式を新規Excelで

カラム：A1　分類：日時　種類：2001/3/14 1:30 PM
カラム：A2　分類：日時　種類：2001/3/14 13:30
カラム：A3　分類：時刻　種類：2001/3/14 1:30 PM
カラム：A4　分類：時刻　種類：2001/3/14 13:30
カラム：A5　分類：ユーザー定期　種類：G/標準
カラム：A6　分類：ユーザー定期　種類：yyyy/m/d h:mm
カラム：A7　分類：ユーザー定期　種類：[$-409]yyyy/m/d h:mm AM/PM;@
カラム：A8　分類：ユーザー定期　種類：yyyy/m/d h:mm;@

を作成してsheet1.xmlを確認したら

<row r="1" spans="1:1"><c r="A1" s="2"><v>44524.416666666664</v></c></row>
<row r="2" spans="1:1"><c r="A2" s="3"><v>44524.416666666664</v></c></row>
<row r="3" spans="1:1"><c r="A3" s="2"><v>44524.416666608799</v></c></row>
<row r="4" spans="1:1"><c r="A4" s="3"><v>44524.416666608799</v></c></row>
<row r="5" spans="1:1"><c r="A5" s="4"><v>44524.416666608799</v></c></row>
<row r="6" spans="1:1"><c r="A6" s="1"><v>44524.416666608799</v></c></row>
<row r="7" spans="1:1"><c r="A7" s="2"><v>44524.416666608799</v></c></row>
<row r="8" spans="1:1"><c r="A8" s="3"><v>44524.416666608799</v></c></row>

となるのでs="11"ならば日時というわけでは無さそうです。

s=の番号はstyles.xmlのcellXfs配下のxfの番号を表していました。（0始まり） xfの中でnumFmtIdが表示形式を表しています。

元のエクセルに戻りまして、 2021-11-24 10:00:00は定義された表示形式ではないので、同じstyles.xmlにnumFmtsで定義されています。

<numFmts count="1">
<numFmt numFmtId="176" formatCode="yyyy\-mm\-dd\ hh:mm:ss"/>
</numFmts>

今回の表示形式のみ取得する場合は以下の処理で取得できました。

        // 型が保存されたファイル
        $stylePath = 'xl/styles.xml';
        // ファイルを取りだす
        exec("unzip -p ".$excelPath ." ". $stylePath , $res);
        $data = simplexml_load_string($res[1]);
        $numFmt = $data->numFmts;
        $styleIsTime = [];
        $index = 0;
        foreach ($data->cellXfs->xf as $value) {
            $styleIsTime[$index] = false;
            if(intval($value["numFmtId"]) >= 176){
                foreach ($numFmt->numFmt as $fmt){
                    if(intval($fmt["numFmtId"]) == intval($value["numFmtId"]) && strval($fmt["formatCode"]) == 'yyyy\-mm\-dd\ hh:mm:ss'){
                        $styleIsTime[$index] = true;
                        continue;
                    }
                }
            }
            $index++;
        }

定義された表示形式ならnumFmtIdの数値で判断を行えば問題ないでしょう。 コレで時間だと判断したら、日付シリアル値をタイムスタンプに変換します。 参考URL:https://blog.taka.at/archives/53196027.html

        $get_time = date('Y-m-d H:i:s', ($data-25569.375)*86400);

しかし、2021-11-24 09:59:59と時間が1秒ほどのズレが．．．

2021-11-24 10:00:00 をExcel上で標準に変えれば44524.4166666667 sheet1.xmlでは 44524.416666666664 後ろの数値が異なります。

取り敢えず少し数値を足して切り上げて対応しました。

        $ceil_round_up = 0.5 * pow(0.1, 11);
        $get_time = date('Y-m-d H:i:s', (round($data+$ceil_round_up,12)-25569.375)*86400);

以上 山中がお伝えしました。またお会いしましょう！