[SheetJS] WebアプリでExcelをデータベースとして使う

この記事について

ExcelファイルをJavaScriptで扱うためのライブラリ[SheetJS] についての個人的まとめです。主にExcelシートからJSONに変換する機能について書いています。

SheetJSについて

公式:https://github.com/SheetJS/js-xlsx

JavaScriptでExcelファイルを扱うためのライブラリです。CDNで提供されています。

WebアプリでExcelををデータベースとして使うメリット

SheetJSを使うとブラウザで直接Excelファイルを扱えます。

データの編集と管理をクライアント側で行わせてサーバ側にはファイルを送信させるというアプローチは昔からありますが、SheetJSでは解析と出力がブラウザで完結するので、サーバ側とExcelファイルを送受信することなく処理を行うことが可能です。

これにより、以下の効果を得ることができます。

  • テータベースの領域の削減、またはデータベースそのものが不要になる。
  • 機密性の高いデータを送信させなければセキュリティ対策が不要になる。
  • 従来VBAで構築していたロジックがHTML/CSS/JavaScriptに置き換わることによって、表現力が増すほかWebエンジニアが開発できるようになる。バージョン管理が楽になる。

参考資料の探し方

「SheetJS」で検索すると、Qiitaにも記事が見つかります。

JavaScriptでExcel(XLSX)を読み込む
https://qiita.com/tomgoodsun/items/bc5f2db3c28e1a6525d2

npmで配布されている「XLSX」というパッケージもSheetJSと同じものです。
ですので「Node.js + XLSX」で検索しても有用な情報が見つかります。

Node.jsでExcelファイルのread/write
https://qiita.com/Kazunori-Kimura/items/29038632361fba69de5e

サンプルコード

ファイルを読み込んでJSONに変換する最低限の動作のサンプル。

index.html
<!DOCTYPE html>
<html lang="ja">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>sheet.js サンプル</title>
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm"
        crossorigin="anonymous">
    <style>
        /* ファイル選択 */

        /* http://cccabinet.jpn.org/bootstrap4/javascript/forms/file-browser */

        .custom-file-input:lang(ja)~.custom-file-label::after {
            content: "参照";
        }

        .custom-file {
            overflow: hidden;
        }

        .custom-file-label {
            white-space: nowrap;
        }
    </style>
</head>

<body>
    <div class="container-fluid">
        <h2>sheet-js サンプル</h2>
        <div class="custom-file">
            <input type="file" class="custom-file-input" id="customFile">
            <label class="custom-file-label" for="customFile">Excelファイルを選択...</label>
        </div>
        <div>
            <h3>結果</h3>
            <pre id="result">ここにJSONを出力します</pre>
        </div>
    </div>
    <script src="https://code.jquery.com/jquery-3.3.1.min.js" integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8="
        crossorigin="anonymous"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.11.19/xlsx.full.min.js"></script>
    <script>
        var X = XLSX;

        // ファイル選択時のメイン処理
        function handleFile(e) {

            var files = e.target.files;
            var f = files[0];

            var reader = new FileReader();
            reader.onload = function (e) {
                var data = e.target.result;
                var wb;
                var arr = fixdata(data);
                wb = X.read(btoa(arr), {
                    type: 'base64',
                    cellDates: true,
                });

                var output = "";
                output = to_json(wb);
                console.log(output);

                $("pre#result").html(JSON.stringify(output, null, 2));

            };

            reader.readAsArrayBuffer(f);
        }

        // ファイルの読み込み
        function fixdata(data) {
            var o = "",
                l = 0,
                w = 10240;
            for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w,
                l * w + w)));
            o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
            return o;
        }

        // ワークブックのデータをjsonに変換
        function to_json(workbook) {
            var result = {};
            workbook.SheetNames.forEach(function (sheetName) {
                var roa = X.utils.sheet_to_json(
                    workbook.Sheets[sheetName],
                    {
                        raw: true,
                    });
                if (roa.length > 0) {
                    result[sheetName] = roa;
                }
            });
            return result;
        }

        // 画面初期化
        $(document).ready(function () {

            // ファイル選択欄 選択イベント
            // http://cccabinet.jpn.org/bootstrap4/javascript/forms/file-browser
            $('.custom-file-input').on('change', function (e) {
                handleFile(e);
                $(this).next('.custom-file-label').html($(this)[0].files[0].name);
            })
        });

    </script>
</body>

</html>

動くものはこちら。
https://okoppe8.github.io/sheetjs_sample/

使い方のコツ

設定

確実に理解して使おうとするとかなり時間がかかる。標準の設定を作って使いまわす方針が良い。おすすめ設定は以下の2つ

XSLX.read readfile で指定するオプション

wb = X.read(btoa(arr), {
    type: 'base64',
    cellDates: true,
});

XSLX.sheet_to_json で指定するオプション

var roa = X.utils.sheet_to_json(
    workbook.Sheets[sheetName],
    {
        raw: true,
    });

これでとりあえず日付時間セルについてはISO8601(UTC)形式の文字列となり、それ以外のセルについては書式(例:3桁カンマ区切り)の無いプレーンなデータが取れる。そこから必要な形式に再変換すればよい。

Excelファイルのデザイン

デフォルトの設定で読み込むと、空行と空列は無視され、データのある一行目がヘッダとして扱われる。そのためデザインのために空白行や空白列を作っても影響はない。
見出しや注釈を入れたければ、上と左に空行・空列を作り、そこに図形オブジェクトをつくるとよい。

テーブルヘッダーについて

SheetJSではExcelの表の見出し行(1行目)をJSONのフィールド名として使う。そのため普通の使い方なら読み取り時のJSONのフィールド名は日本語となる。

1行目に英語のフィールド名を入れて非表示にする等の細工も可能だが、ユーザーが誤って消してしまう事故もありえるおすすめしない。

JSONをそのまま使うことはあきらめて、読込直後のタイミングでフィールド名の変換と不要フィールドの削除を行うようにする。そうしたほうがユーザー側で列の入れ替えや計算列の追加が自由にできるためユーザビリティが良くなる。

Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.