Posted at

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

More than 1 year has passed since last update.


この記事について

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