TalendでExcelファイルの読み込み

# はじめに
エクセルファイルをTalendで読み込んで集計しようとしたところ
日付形式のデータがエクセル側で設定したフォーマットとは異なっていたので
備忘録として残しておきます。

# 環境
| 使用環境 |バージョン |
|---|---|
| OS | Windows10 |
| TalendOpenStudio | 7.2.1 |
| OpenJDK | 11.0.4 |
| Office365 | 1902 |

# 準備したエクセルファイル
ExcelTestData.xlsx
※拡張子「xlsx」ファイル
![excel_input_02.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/165430/5e9716dd-7106-960d-b22c-831ca2ee74ce.jpeg)
※作業時間：「End」-「Start」、 セルの書式設定：ユーザ定義（h:mm）
  累積時間：「1行前の作業時間」＋「作業時間」、セルの書式設定：ユーザ定義（[h]:mm）

# Talendでの読み込み
![excel_input_01.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/165430/95e337c5-65fa-52a8-226d-4aa515723186.jpeg)

* tFileListコンポーネントで、ExcelTestData.xlsxを置いた任意の場所を指定します。
* tFileInputExcelコンポーネントで、Excelファイルを読み込みます。
 - 「Excel2007のファイル形式(xlsx)を読み取る」にチェックを付けます。
 - スキーマにカラムを5個設定します。（タイプは全てString）
* tLogRowコンポーネントで、標準出力します。
 - モードをテーブルにします。

# 実行
TalaendStudioの実行ジョブで

``` TalendOpenStudio
ジョブ readExcel を 11:35 05/10/2019 に開始しました。
[statistics] connecting to socket on port 3421
[statistics] connected
.---------+----------------------------+----------------------------+----------------------------+----------------------------.
|                                                          tLogRow_1                                                          |
|=--------+----------------------------+----------------------------+----------------------------+---------------------------=|
|newColumn|newColumn1                  |newColumn2                  |newColumn3                  |newColumn4                  |
|=--------+----------------------------+----------------------------+----------------------------+---------------------------=|
|1        |Tue Oct 01 09:30:00 JST 2019|Tue Oct 01 18:30:00 JST 2019|Sun Dec 31 09:00:00 JST 1899|Sun Dec 31 09:00:00 JST 1899|
|2        |Tue Oct 01 09:00:00 JST 2019|Tue Oct 01 19:00:00 JST 2019|Sun Dec 31 10:00:00 JST 1899|Sun Dec 31 19:00:00 JST 1899|
|3        |Tue Oct 01 09:00:00 JST 2019|Tue Oct 01 18:00:00 JST 2019|Sun Dec 31 09:00:00 JST 1899|Mon Jan 01 04:00:00 JST 1900|
|4        |Tue Oct 01 09:30:00 JST 2019|Tue Oct 01 19:30:00 JST 2019|Sun Dec 31 10:00:00 JST 1899|Mon Jan 01 14:00:00 JST 1900|
|5        |Tue Oct 01 13:30:00 JST 2019|Tue Oct 01 21:30:00 JST 2019|Sun Dec 31 08:00:00 JST 1899|Mon Jan 01 22:00:00 JST 1900|
|6        |Tue Oct 01 09:30:00 JST 2019|Tue Oct 01 18:30:00 JST 2019|Sun Dec 31 09:00:00 JST 1899|Tue Jan 02 07:00:00 JST 1900|
'---------+----------------------------+----------------------------+----------------------------+----------------------------'

[statistics] disconnected

ジョブ readExcel が 11:35 05/10/2019 に終了しました。 [終了コード=0]
```

# 日付の形式について
Excel2007の日付型をString型で読むと
「Tue Oct 01 09:30:00 JST 2019」
という形になりました。
※日付表示が文字になっているので、Localeを指定しないと
　文字列⇒日付の変換ができません。
　スキーマでは、パターンの指定しかできないので
　tFileInputExcelで直に日付型では読み込めないと思われます。
※「Excelは、日付情報をファイル内部では数値型として値を保持しており、値が0の場合は基準日として、通常は 「1899年12月31日（Excel表記上は1900年1月0日）」 を表します。」とのことです。
参考） [ファイル内部の日付の基準日の設定](https://mygreen.github.io/excel-cellformatter/sphinx/format_other_startdate.html)

# tJavaRowで変換
tFileInputExcelコンポーネントの後ろにtJavaRowコンポーネントを追加して
文字列を日付に変換します。
![excel_input_03.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/165430/b358b642-8ee9-79b4-a81e-22456f52a689.jpeg)


* 基本設定

```
SimpleDateFormat sdfi = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy", Locale.US);
SimpleDateFormat sdfo = new SimpleDateFormat("yyyy/MM-dd HH:mm:ss");
// エクセル基準日
String zeroDate="Sun Dec 31 00:00:00 JST 1899";
long dateTimeZero = sdfi.parse(zeroDate).getTime();

output_row.newColumn = input_row.newColumn;
output_row.newColumn1 = sdfo.format( sdfi.parse( input_row.newColumn1 ) );
output_row.newColumn2 = sdfo.format( sdfi.parse( input_row.newColumn2 ) );

// 時間差
long dateTimeDiff = sdfi.parse( input_row.newColumn3 ).getTime();
long dateTimeAccm = sdfi.parse( input_row.newColumn4 ).getTime();

output_row.newColumn3 = String.valueOf((dateTimeDiff - dateTimeZero )/ (1000 * 60 * 60 ) );
output_row.newColumn4 = String.valueOf((dateTimeAccm - dateTimeZero )/ (1000 * 60 * 60 ) );
```
※エクセル基準日（1899-12-31 00:00:00）から、時刻差のミリ秒をlongでとり
　(1000 * 60 * 60 )で割ることで時間に変換します。　

* 詳細設定

```
import java.util.Locale;
```

# 結果
tJavaRowに上記を設定して実行すると、下記の出力になりました。

```
ジョブ readExcel を 21:44 05/10/2019 に開始しました。
[statistics] connecting to socket on port 3829
[statistics] connected
.---------+-------------------+-------------------+----------+----------.
|                               tLogRow_1                               |
|=--------+-------------------+-------------------+----------+---------=|
|newColumn|newColumn1         |newColumn2         |newColumn3|newColumn4|
|=--------+-------------------+-------------------+----------+---------=|
|1        |2019/10-01 09:30:00|2019/10-01 18:30:00|9         |9         |
|2        |2019/10-01 09:00:00|2019/10-01 19:00:00|10        |19        |
|3        |2019/10-01 09:00:00|2019/10-01 18:00:00|9         |28        |
|4        |2019/10-01 09:30:00|2019/10-01 19:30:00|10        |38        |
|5        |2019/10-01 13:30:00|2019/10-01 21:30:00|8         |46        |
|6        |2019/10-01 09:30:00|2019/10-01 18:30:00|9         |55        |
'---------+-------------------+-------------------+----------+----------'

[statistics] disconnected

ジョブ readExcel が 21:44 05/10/2019 に終了しました。 [終了コード=0]
```

# xlsファイルの場合
拡張子xlsファイルを対象として、同様の書式にして読み込んだ場合は、
下記の様になりました。
※tFileInputExcelコンポーネントの「Excel2007のファイル形式(xlsx)を読み取る」からチェックを外しました。

```
ジョブ readExcel_Copy を 22:12 05/10/2019 に開始しました。
[statistics] connecting to socket on port 3940
[statistics] connected
.---------+----------+----------+----------+----------.
|                      tLogRow_1                      |
|=--------+----------+----------+----------+---------=|
|newColumn|newColumn1|newColumn2|newColumn3|newColumn4|
|=--------+----------+----------+----------+---------=|
|1        |9:30      |6:30      |9:00      |:00       |
|2        |9:00      |7:00      |10:00     |:00       |
|3        |9:00      |6:00      |9:00      |:00       |
|4        |9:30      |7:30      |10:00     |:00       |
|5        |1:30      |9:30      |8:00      |:00       |
|6        |9:30      |6:30      |9:00      |:00       |
|null     |null      |null      |null      |null      |
'---------+----------+----------+----------+----------'

[statistics] disconnected

ジョブ readExcel_Copy が 22:12 05/10/2019 に終了しました。 [終了コード=0]
```
標準的な時刻の書式は、表示と同じ書式で読み込んでもらえるようですが、
newColumn4のカラムは、ファイルの書式が、ユーザ定義（[h]:mm）であったため
時間が読み込めなかったと思われます。
※エクセルの表示通りに読み込んでもらえるのは楽ですが、
　読み込めない場合があるのは、書式がいじれないファイルを扱う場合にはつらいですね。