SheetJS を使って、レコード編集時にテーブルを Excel でインポートしてまとめてレコード作成してみよう!

著者名: Fuji Business International (External link) Mamoru Fujinoki

目次

はじめに

今回のカスタマイズは、タイムカードアプリを作成して、編集画面で Excel ファイルをインポートし、レコード内のテーブルにデータを自動入力するカスタマイズです。
勤怠情報を毎日テーブルに 1 行ずつ追加しなくても一括で追加、編集できます。
一覧からインポートも可能ですが、レコードの編集時に実施することで今編集中のレコードだけが更新されるため、直感的にわかりやすく、データを保存する前にインポートする内容を確認できます。
今回は SheetJS ライブラリを使用します。

タイムカードアプリの作成

以下の画像を参考に「タイムカードアプリ」を作成します。

フィールドの種類 フィールド名 フィールドコード 備考
ユーザ選択 氏名 name
ドロップダウン 部署 department 選択肢:人事部、技術部、営業部、経理部
ドロップダウン month 選択肢:1、2、3、4、5、6、7、8、9、10、11、12
ドロップダウン year 選択肢:20023、2024、2025、2026、2027、2028、2029、2030
テーブル 勤怠 work_hours
日付 日付 date テーブル内フィールド
ドロップダウン ステータス status 選択肢:通常勤務、有給休暇、終日休、午前休、午後休
時刻 勤務開始 start_time テーブル内フィールド
時刻 勤務終了 end_time テーブル内フィールド
数値 休憩 break_time テーブル内フィールド
文字列(1 行) 備考 note テーブル内フィールド
スペース timeCardImport

JavaScript の作成

以下のコードを参考にしてコードを作成します。
テキストエディターで以下のコードをコピーして、適当なファイル名(sample.js 等)にて保存します。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
/*
 * import excel sample code
 * Copyright (c) 2023 Cybozu
 *
 * Licensed under the MIT License
 * https://opensource.org/license/mit/
 */

(() => {
  'use strict';
  // Luxon 日付フォーマット用のライブラリー
  const DateTime = luxon.DateTime;

  // Excel Dateから、JSへコンバート
  const excelDateToJSDate = (serial) => {
    const utc_days = Math.floor(serial - 25569);
    const utc_value = utc_days * 86400;
    const date_info = new Date(utc_value * 1000);

    const fractional_day = serial - Math.floor(serial) + 0.0000001;

    let total_seconds = Math.floor(86400 * fractional_day);

    const seconds = total_seconds % 60;

    total_seconds -= seconds;

    const hours = Math.floor(total_seconds / (60 * 60));
    const minutes = Math.floor(total_seconds / 60) % 60;

    return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
  };

  kintone.events.on(['app.record.edit.show'], (event) => {

    const spaceElement = kintone.app.record.getSpaceElement('timeCardImport'); // Spaceフィールドの要素を取得
    // 増殖バグ回避
    if (document.getElementById('upload_button') !== null || document.getElementById('file_upload') !== null) {
      return event;
    }

    // SpaceにFile inputを設置
    let selectedFile;
    const fileUpload = document.createElement('input');
    fileUpload.type = 'file';
    fileUpload.id = 'file_upload';
    fileUpload.accept = '.xls,.xlsx';
    fileUpload.className = 'button-simple-cybozu geo-search-btn';
    fileUpload.style = 'margin-top: 30px; margin-left: 10px;';
    fileUpload.addEventListener('change', (change) => {
      selectedFile = change.target.files[0];
    });
    spaceElement.appendChild(fileUpload);

    // Spaceにボタンを設置
    const importButton = document.createElement('button');
    importButton.id = 'upload_button';
    importButton.innerHTML = 'エクセルアップロードで入力';
    importButton.className = 'button-simple-cybozu geo-search-btn';
    importButton.addEventListener('click', () => {
      if (selectedFile) {
        const record = kintone.app.record.get(); // レコードの取得
        const fileReader = new FileReader();
        fileReader.readAsBinaryString(selectedFile);
        fileReader.onload = (onload)=>{
          const data = onload.target.result;
          const workbook = XLSX.read(data, {type: 'binary'});
          workbook.SheetNames.forEach((sheet) => {
            const rowObject = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheet]);
            const work_hours = [];
            rowObject.forEach((row) => {
              const work_hour = {
                value: {
                  date: {
                    type: 'DATE',
                    value: DateTime.fromJSDate(excelDateToJSDate(row['日付'])).toISODate()
                  },
                  status: {
                    type: 'DROP_DOWN',
                    value: row['ステータス']
                  },
                  start_time: {
                    type: 'TIME',
                    value: DateTime.fromJSDate(excelDateToJSDate(row['勤務開始'])).toLocaleString(DateTime.TIME_24_SIMPLE)
                  },
                  end_time: {
                    type: 'TIME',
                    value: DateTime.fromJSDate(excelDateToJSDate(row['勤務終了'])).toLocaleString(DateTime.TIME_24_SIMPLE)
                  },
                  break_time: {
                    type: 'NUMBER',
                    value: row['休憩']
                  },
                  note: {
                    type: 'SINGLE_LINE_TEXT',
                    value: row['備考']
                  }
                }
              };
              work_hours.push(work_hour);
            });
            record.record.work_hours.value = work_hours;
            kintone.app.record.set(record);
          });
        };
      }
    });
    spaceElement.appendChild(importButton);
    return event;
  });
})();

コードの解説

JS フォーマットの日付を String フォーマットに変換するライブラリを読み込んでいます。

11
12
// Luxon 日付フォーマット用のライブラリー
const DateTime = luxon.DateTime;

Excel の Date フォーマットを JS のフォーマットに変換する関数です。

14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
// Excel Dateから、JSへコンバート
const excelDateToJSDate = (serial) => {
  const utc_days = Math.floor(serial - 25569);
  const utc_value = utc_days * 86400;
  const date_info = new Date(utc_value * 1000);

  const fractional_day = serial - Math.floor(serial) + 0.0000001;

  let total_seconds = Math.floor(86400 * fractional_day);

  const seconds = total_seconds % 60;

  total_seconds -= seconds;

  const hours = Math.floor(total_seconds / (60 * 60));
  const minutes = Math.floor(total_seconds / 60) % 60;

  return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
};

編集画面のイベント内でボタン設置等の処理をします。

34
35
36
37
38
39
kintone.events.on(['app.record.edit.show'], (event) => {

  // 中略

  return event;
});

編集画面内のスペースフィールドの要素を取得した後、すでにボタンが存在していないかをチェックして、ページ更新によるボタンの増殖生成を防いでいます。
その後、ファイルアップロードの input エレメントを定義して、スペースに設置します。
ファイルのタイプは Excel の拡張子に限定し、エレメントの Change イベントにてファイルをアップロードした際にファイルの内容を取得します。

36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
const spaceElement = kintone.app.record.getSpaceElement('timeCardImport'); // Spaceフィールドの要素を取得
// 増殖バグ回避
if (document.getElementById('upload_button') !== null || document.getElementById('file_upload') !== null) {
  return event;
}

// SpaceにFile inputを設置
let selectedFile;
const fileUpload = document.createElement('input');
fileUpload.type = 'file';
fileUpload.id = 'file_upload';
fileUpload.accept = '.xls,.xlsx';
fileUpload.className = 'button-simple-cybozu geo-search-btn';
fileUpload.style = 'margin-top: 30px; margin-left: 10px;';
fileUpload.addEventListener('change', (change) => {
  selectedFile = change.target.files[0];
});
spaceElement.appendChild(fileUpload);

「エクセルアップロードで入力」ボタンをスペースに追加します。

55
56
57
58
59
60
61
62
63
// Spaceにボタンを設置
const importButton = document.createElement('button');
importButton.id = 'upload_button';
importButton.innerHTML = 'エクセルアップロードで入力';
importButton.className = 'button-simple-cybozu geo-search-btn';

// 中略

spaceElement.appendChild(importButton);

「エクセルアップロードで入力」ボタンをクリックしたタイミングで、Excel ファイル内のデータを読み込みます。
SheetJS ライブラリによって、Excel ファイル内のデータを読み込んでいます。
その後、kintone のテーブルの JSON 形式の配列を作成してテーブル内のレコードを更新します。

 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
importButton.addEventListener('click', () => {
  if (selectedFile) {
    const record = kintone.app.record.get(); // レコードの取得
    const fileReader = new FileReader();
    fileReader.readAsBinaryString(selectedFile);
    fileReader.onload = (onload)=>{
      const data = onload.target.result;
      const workbook = XLSX.read(data, {type: 'binary'});
      workbook.SheetNames.forEach((sheet) => {
        const rowObject = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheet]);
        const work_hours = [];
        rowObject.forEach((row) => {
          const work_hour = {
            value: {
              date: {
                type: 'DATE',
                value: DateTime.fromJSDate(excelDateToJSDate(row['日付'])).toISODate()
              },
              status: {
                type: 'DROP_DOWN',
                value: row['ステータス']
              },
              start_time: {
                type: 'TIME',
                value: DateTime.fromJSDate(excelDateToJSDate(row['勤務開始'])).toLocaleString(DateTime.TIME_24_SIMPLE)
              },
              end_time: {
                type: 'TIME',
                value: DateTime.fromJSDate(excelDateToJSDate(row['勤務終了'])).toLocaleString(DateTime.TIME_24_SIMPLE)
              },
              break_time: {
                type: 'NUMBER',
                value: row['休憩']
              },
              note: {
                type: 'SINGLE_LINE_TEXT',
                value: row['備考']
              }
            }
          };
          work_hours.push(work_hour);
        });
        record.record.work_hours.value = work_hours;
        kintone.app.record.set(record);
      });
    };
  }
});

アップロードしたファイルの Binary データを読み込んで、SheetJS ライブラリにて Excel のデータを読み込んでいます。
そして、データを配列形式に変換しています。

63
64
65
66
67
68
69
      const fileReader = new FileReader();
        fileReader.readAsBinaryString(selectedFile);
        fileReader.onload = (onload)=>{
          const data = onload.target.result;
          const workbook = XLSX.read(data, {type: 'binary'});
          workbook.SheetNames.forEach((sheet) => {
            const rowObject = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheet]);

Excel の退勤データを kintone のテーブルの JSON 形式に設定して、レコードを更新します。

 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
const work_hours = [];
rowObject.forEach((row) => {
  const work_hour = {
    value: {
      date: {
        type: 'DATE',
        value: DateTime.fromJSDate(excelDateToJSDate(row['日付'])).toISODate()
      },
      status: {
        type: 'DROP_DOWN',
        value: row['ステータス']
      },
      start_time: {
        type: 'TIME',
        value: DateTime.fromJSDate(excelDateToJSDate(row['勤務開始'])).toLocaleString(DateTime.TIME_24_SIMPLE)
      },
      end_time: {
        type: 'TIME',
        value: DateTime.fromJSDate(excelDateToJSDate(row['勤務終了'])).toLocaleString(DateTime.TIME_24_SIMPLE)
      },
      break_time: {
        type: 'NUMBER',
        value: row['休憩']
      },
      note: {
        type: 'SINGLE_LINE_TEXT',
        value: row['備考']
      }
    }
  };
  work_hours.push(work_hour);
});
record.record.work_hours.value = work_hours;
kintone.app.record.set(record);

Excel の日付を JS の日付フォーマットに変換しています。

1
excelDateToJSDate(row['日付']);

JS の日付フォーマットを String 形式に変換しています。

1
DateTime.fromJSDate(excelDateToJSDate(row['日付'])).toISODate();

動作確認

Luxon ライブラリ

JS フォーマットの日付を String フォーマットに変換するライブラリです。
以下のリンクよりダウンロードします。
https://moment.github.io/luxon/global/luxon.min.js (External link)
ダウンロードした luxon.min.js ファイルをアプリの設定でアップロードします。

SheetJS ライブラリ

SheetJS のライブラリを以下のリンクよりダウンロードします。
https://git.sheetjs.com/sheetjs/sheetjs/src/branch/master/dist/xlsx.full.min.js (External link)
ダウンロードした xlsx.full.min.js ファイルをアプリの設定でアップロードします。

「アプリを更新」して設定を確定します。
作成したタイムカードアプリでレコードを一件作成します。

作成後、レコードを編集する画面に入り、ファイルアップロード用のボタンと「エクセルアップロードで入力」ボタンが表示されていることを確認します。

画像を参考に勤怠のデータを入力した Excel ファイルを準備します。
ファイルを読み込む際には、読み込みたいフィールドのフィールド名と読み込むファイルの先頭行の項目名を一致させる必要があります。

作成した Excel ファイルを選択し、「エクセルアップロードで入力」ボタンをクリックします。

クリックするとアップロードしたデータが編集画面にて表示されるので、「保存」ボタンでレコードを保存します。

画像のように Excel からアップロードしたテーブルデータが保存されると成功です。

まとめ

今回は、一覧からではなく、レコードごとにテーブルへのデータをインポートするカスタマイズを紹介しました。
編集画面からインポートすることで、レコードを保存する前に内容が確認できるため、一覧から Excel データをインポートするより分かりやすく簡単にテーブルへのデータのインポートが可能になります。

また、Excel の特定のレコードへインポートするためのデータの調整が不要となり、間違って他のレコードを更新してしまう心配もなくなります。

information

この Tips は、2023 年 5 月版 kintone で動作を確認しています。