エクセルの日付フォーマット変換を検証したよ

かなり時間が空いてしまいました。

おひさしぶりです。
自称）BizRobo!エンジニアのもりりんです。

かなりバタバタしており、ブログの更新が追いついていませんでした。（理由はすぐ下にw）

お盆を前にRPAブログもとい、弊社コーポレートサイトをリニューアルしました。（今後も定期的にコンテンツを追加する予定です）
リニューアルに合わせて、RPAブログは会社ブログの中の１コンテンツとして展開する運びとなりました。
今までは見にくかったブログもResponsive対応しましたので、スマホからも見ていただけると幸いです。

さて、今回のテーマは「エクセルのバラバラで来る日付フォーマットを変換する」です。

とあるユーザ様にて、エクセル内のとあるセルの日付フォーマットが変わってしまい、システムが想定する入力フォーマットに一致せずエラーになったという事例がございました。
その時に対応した内容となります。
つい最近（ブログを執筆している日からだと、昨日）の話です。

前提

今回問題となったエクセルは、人とロボットが相互で操作するものとなっています。

そのため、人がデータをコピーする際に元データのフォーマットが変更されていると、その状態が反映されます。
その状態で抽出された日付を、例えば「yyyy/MM/dd」形式を想定しているシステムに「MM/dd/yy」形式で入力すると、バリデーションで引っかかりますよね。

今回この状態が発生し、システム登録が止まった状況となりました。
念の為、前回に取り込まれたデータを確認しましたが、「yyyy/MM/dd」形式だったため問題なかったです。

ここで、何個か日付フォーマットを持たせたエクセルをBizRobo!がどう認識するか見てみましょう。
下記は、「2023/8/17 00:00」のUNIXタイムをベースとし、計算したエクセル用シリアル値を指定されたフォーマットで表示しています。

今回問題となったフォーマットは、D列の「*yyyy/M/d」です。
Office上では、他フォーマットを含めて特に問題ないように見えます。

では、BizRobo!のDSで見るとどうなるか確認しましょう。
※確認バージョンは、11.1.0.6です

BizRobo!のDSで正常に認識されないフォーマットは、「*yyyy/M/d」「GGGE年M月d日」「GE/M/d」 の３つでした。

アスタリスクが付く付かないでの差異は、こちらのブログにて説明されていました。

「*2001/3/14」と「2001/3/14」の違いって何？:Excel(エクセル)の使い方-セルの書式設定/日付・時間の表示…

ブログの説明を抜粋すると

「*」つきの書式設定をすると、Windowsの地域設定の影響を受け、Excelファイルを開いたPCによって書式が変化する可能性があるということです。

しかし、ロボット設定の言語も日本語に変更しても改善されませんでした。
DSそのものの言語設定を設定ファイルから変更する必要があるのかもしれませんね。
今回は、そこまで対応する予定はなかったため調査はしておりません。

「GGGE年M月d日」「GE/M/d」は元々ある和暦フォーマットを少し弄ったものとなりますが、こちらはシリアル値が表示される仕様となっています。
シリアル値から復元するステップを組み込む必要が出てくるかもしれませんね。
もしくは、UNIXタイムに戻して再変換する方が楽かも知れません。

今回の対応

JavaScriptで日付変換する
BizRobo!の日付抽出機能を利用する

JavaScriptで日付変換する

シンプルにできるかと思いましたが、「M/d/yy」形式だと年変換を工夫しないといけない、シリアル値の変換処理も別途作成する必要があります。

また、Webロボットだと言語を日本語に変更しても、国フォーマットを指定しても、日本形式で出力されないため別途作る必要もありますね。
ロボット設定のポリフィルがJavaScript単体実行でも効けばプログラムを書かなくてもいいのに。。。とは思いましたが、ブラウザ上でしか効かないため仕方なしです。

以前DAでJavaScriptを操作する記事を公開しているので、参考に作っていただければと思います。

DAのJavaScriptで日付を取得しよう

ただし、BizRobo!（10.7/11.1の場合）が採用しているJavaScriptはECMAScript5準拠とかなり古いため、使えないクラス、関数等々の機能が多々存在します。
例えば、DSエクスプレッションに存在する文字を一括置換できる「replaceAll」関数も使えません。
そのため、既存関数を工夫して利用しましょう。（今回は、replace+正規表現で対応しました）

なお、DA（Chromium）のJavaScriptの方が新しい基準に準拠しており、機能が異なるためご注意ください。

今回、別途対応することは、

「MM/dd/yy」「MM-dd-yy」のような年が2桁の場合、4桁に加工する

2桁の場合に1900/2000年をどう判別するかですが、生年月日でない限り1900年代に変換することはないかと思うので、20を決め打ちし4桁に加工しましょう。

マイクロソフトも表示形式の記事を出しているので確認しておきましょう。

表示形式の書式記号 – Microsoft サポート

function convert(value) {
  const dt = new Date(value);
  const y = dt.getFullYear().toString()

  // 1900年代だったら、2000年代へ変更
  // BizRobo!がES5のため、String.startsWithが使えないことに注意
  if (y.lastIndexOf('19', 0) === 0) {
    dt.setFullYear('20' + y.substr(y.length > 2 ? y.length-2 : 0, 2));
  }

  return dt.toString();
}

上記処理を開発者ツールで簡単に確認します。

開発者ツールは、ブラウザ標準で提供されているデバッグツールです。
WebサイトのHTML構成や表示スピード、通信状況などを確認できます。
今回は、コンソールと呼ばれる機能でJavaScriptを直接呼び出し動作確認しています。

コンソールで実行での実行結果は以下です。

シリアル値に対応する

シリアル値に関しては私も詳しくはないのですが、説明を見た感じだと閏年や1900/1/0というあり得ない日付がカウントされている等々の独自仕様があるので、考慮する必要があります。

JavaScriptのDate関数はUnixタイムを受け付けてくれるため、シリアル値→Unixタイムへ変換しDateの引数へ渡します。

// 定数
var ONE_DAY_SECOND = 24 * 60 * 60;
var UNIX_OFFSET = 70 * 365 + 17 + 1 + 1;
var JST_TIME_SECOND = 9 * 60 * 60;

// シリアル値→Unixタイム
function convertSerial2Unix(value) {
  // Unixタイムはミリ秒のため1000倍に拡大
  return ((value - UNIX_OFFSET) * ONE_DAY_SECOND - JST_TIME_SECOND) * 1000;
}

// 日付変換
function getConvertDate(value) {
  return new Date(convertSerial2Unix(value));
}

コンソールで実行での実行結果は以下です。

動作確認は取れたので、こちらを追加しておくとシリアル値データも対応できそうです。

最終的には、以下のようにすればいいかなと思います。

今回提示した以外のフォーマットは考慮していません。
「yyyyMMdd」形式の初期化はエラーとなるため、必要であれば考えてみてください。

var ONE_DAY_SECOND = 24 * 60 * 60;
var UNIX_OFFSET = 70 * 365 + 17 + 1 + 1;
var JST_TIME_SECOND = 9 * 60 * 60;

function dateFromat(value) {
  var dt;
  // シリアル値の場合は数値変換が成功するが、日付の場合「/-」が記号のため失敗する仕様を利用する
  if (!isNaN(Number(value))) {
    // シリアル
    dt = getConvertDate(value);
  } else {
    // 日付
    // ハイフンを変換する
    value = value.replace(/-/g, '/');
    
    dt = new Date(value);
    const y = dt.getFullYear().toString();

    // 1900年代だったら、2000年代へ変更
    // BizRobo!がES5のため、String.startsWithが使えないことに注意
    if (y.lastIndexOf('19', 0) === 0) {
      dt.setFullYear('20' + y.substr(y.length > 2 ? y.length-2 : 0, 2));
    }
  }

  // フォーマット変換(yyyy/M/d)
  // Date.getMonth()は、0-11で取得されるため +1 しておく
  return dt.getFullYear() + '/' + (dt.getMonth()+1) + '/' + dt.getDate();
}

// シリアル値→Unixタイム
function convertSerial2Unix(value) {
  // Unixタイムはミリ秒のため1000倍に拡大
  return ((value - UNIX_OFFSET) * ONE_DAY_SECOND - JST_TIME_SECOND) * 1000;
}

// 日付変換
function getConvertDate(value) {
  return new Date(convertSerial2Unix(value));
}

// 入出力
OUTPUT = dateFromat(INPUT);

先ほどのエクセルを列ループし、フォーマットごとに変換し、ログ出力でチェックします。