Go to Qiita Advent Calendar 2024 Top
LoginSignup
59
74

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@okoppe8

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

Posted at

##この記事について

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/

##使い方のコツ

###設定

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

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の表の見出し行（１行目）をJSONのフィールド名として使う。そのため普通の使い方なら読み取り時のJSONのフィールド名は日本語となる。

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

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

59
74
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
59
74

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?