Tips

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://js.cybozu.com/luxon/3.4.4/luxon.min.js (External link)
ダウンロードしたluxon.min.jsファイルをアプリの設定でアップロードします。

SheetJSライブラリ

固定リンクがコピーされました

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

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

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

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

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

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

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

まとめ

固定リンクがコピーされました

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

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

information

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