Jspreadsheet CE

Jspreadsheet CEはテーブル表示のライブラリです。
Jspreadsheet CEはMIT ライセンス(無償)、Jspreadsheet Proは有償になります。

テーブル表示
スタイル
読み込み専用
マージセル
入力
列固定・遅延読み込み
ページネーション・検索
データゲット、セット、戻す
イベント
ワークシート
初期設定
ライブラリ取得

Version 5でworksheets対応が行われ、jspreadsheet定義にworksheetsが必須になりました。

テーブル表示


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <script>
        var data = [
            ['text1', 'aaa'],
            ['text2', 'bbb'],
        ];

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            worksheets: [{
                data: data,
                columns: [
                    {
                        type: 'text',
                        title: 'title1',
                    },
                    {
                        type: 'dropdown',
                        title: 'title2',
                        source: [
                            "aaa",
                            "bbb",
                            "ccc",
                        ]
                    },
                ]
            }]
        });

    </script>
</body>

</html>

実行結果

スタイル


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <script>
        var data = [
            ['text1', 'aaa'],
            ['text2', 'bbb'],
        ];

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            worksheets: [{
                data: data,
                columns: [
                    {
                        type: 'text',
                        title: 'title1',
                        width: '100px',
                    },
                    {
                        type: 'text',
                        title: 'title2',
                    }
                ],
                style: {
                    A1: 'background-color: yellow;text-align: left;'
                },
            }]
        });

        table[0].setStyle('A2', 'background-color', 'green');
        table[0].getCell(1, 0).style.backgroundColor = 'orange'; 
    </script>
</body>

</html>

スタイルの行、列指定はJspreadsheet CEではできません。Jspreadsheet Proならできます。

実行結果

読み込み専用


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <script>
        var data = [
            ['text1', 'aaa'],
            ['text2', 'bbb'],
        ];

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            worksheets: [{
                data: data,
                columns: [
                    {
                        type: 'text',
                        title: 'title1',
                    },
                    {
                        type: 'text',
                        title: 'title2',
                        readOnly: true,
                    }
                ],
            }]
        });

    </script>
</body>

</html>

実行結果

マージセル


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <script>
        var data = [
            ['text1', 'aaa'],
            ['text2', 'bbb'],
        ];

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            worksheets: [{
                data: data,
                columns: [
                    {
                        type: 'text',
                        title: 'title1',
                    },
                    {
                        type: 'text',
                        title: 'title2',
                    },
                ],
                mergeCells: {
                    A1: [2, 1]
                },
            }]
        });

        table[0].setMerge('A2', 2, 1);
    </script>
</body>

</html>

実行結果

入力


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <script>
        var data = [
            ['text1', 'aaa'],
            ['text2', 'bbb'],
        ];

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            worksheets: [{
                data: data,
                columns: [
                    { type: 'text', title: 'text' },
                    { type: 'dropdown', title: 'dropdown', width: 100, source: ["abc", "bcd", "cde"] },
                    { type: 'dropdown', title: 'autocomplete', width: 120, source: ["abc", "bcd", "cde"], autocomplete: true },
                    { type: 'dropdown', title: 'multiple', width: 100, source: ["abc", "bcd", "cde"], multiple: true },
                    { type: 'calendar', title: 'calendar', width: 100, options: { format: 'YYYY/MM/DD' } },
                    { type: 'checkbox', title: 'checkbox', width: 100, },
                    { type: 'numeric', title: 'numeric', width: 100, mask: '#,##', },
                ],
            }]
        });

    </script>
</body>

</html>

実行結果

列固定

遅延読み込み


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <script>
        let columns = [];
        for (let x = 0; x < 20; x++) {
            columns.push({
                type: 'text',
                title: 'title' + x,
                width: '100px',
            });
        }

        var datas = [];
        for (let y = 0; y < 500; y++) {
            var data = [];
            for (let x = 0; x < columns.length; x++) {
                data.push("data_" + y + "_" + x);
            }
            datas.push(data);
        }

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            worksheets: [{
                data: datas,
                columns: columns,
                tableOverflow: true,
                lazyLoading: true,
                tableWidth: '400px',
                tableHeight: '400px',
                freezeColumns: 2,
            }]
        });

    </script>
</body>

</html>

実行結果

行固定はJspreadsheet CEでは出来ませんでした。Jspreadsheet Proではできるみたいです。
(2023/09/19 時点)

ページネーション

検索


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <script>
        let columns = [];
        for (let x = 0; x < 20; x++) {
            columns.push({
                type: 'text',
                title: 'title' + x,
                width: '100px',
            });
        }

        var datas = [];
        for (let y = 0; y < 500; y++) {
            var data = [];
            for (let x = 0; x < columns.length; x++) {
                data.push("data_" + y + "_" + x);
            }
            datas.push(data);
        }

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            worksheets: [{
                data: datas,
                columns: columns,
                search: true,
                pagination: 10,
            }]
        });

    </script>
</body>

</html>

実行結果

データゲット、セット、戻す


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <br />
    <button onclick="getData()">getData</button>
    <button onclick="getRowData()">getRowData</button>
    <button onclick="getColumnData()">getColumnData</button>
    <button onclick="getValue()">getValue</button>
    <button onclick="getValueFromCoords()">getValueFromCoords</button>
    <br />
    <button onclick="setData()">setData</button>
    <button onclick="setRowData()">setRowData</button>
    <button onclick="setColumnData()">setColumnData</button>
    <button onclick="setValue()">setValue</button>
    <button onclick="setValueFromCoords()">setValueFromCoords</button>
    <br />
    <button onclick="undo()">undo</button>
    <button onclick="redo()">redo</button>
    <script>
        var data = [
            ['text1', 'aaa'],
            ['text2', 'bbb'],
        ];

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            worksheets: [{
                data: data,
                columns: [
                    {
                        type: 'text',
                        title: 'title1',
                    },
                    {
                        type: 'text',
                        title: 'title2',
                    },
                ]
            }]
        });

        function getData() {

            table[0].updateSelectionFromCoords(0, 0, 1, 0);
            alert(table[0].getData(true));

            alert(table[0].getData(false));

            alert(JSON.stringify(table[0].getData({ asJson: true })));
        }
        function getRowData() {
            alert(table[0].getRowData(0));
        }
        function getColumnData() {
            alert(table[0].getColumnData(0));
        }
        function getValue() {
            alert(table[0].getValue("A1"));
        }
        function getValueFromCoords() {
            alert(table[0].getValueFromCoords(0, 0));
        }

        function setData() {
            table[0].setData([["a", "b"], ["c", "d"]]);
        }
        function setRowData() {
            table[0].setRowData(0, ["e", "f"]);
        }
        function setColumnData() {
            table[0].setColumnData(0, ["g", "h"]);
        }
        function setValue() {
            table[0].setValue("A1", "i", true);
        }
        function setValueFromCoords() {
            table[0].setValueFromCoords(0, 0, "j", true);
        }
        function undo() {
            table[0].undo();
        }
        function redo() {
            table[0].redo();
        }

    </script>
</body>

</html>

実行結果

イベント


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <select id="sel1" size="10" multiple></select>
    <script>
        var data = [
            ['text1', 'aaa'],
            ['text2', 'bbb'],
        ];

        function debug(val) {
            var option = document.createElement("option");
            option.text = val;
            var obj = document.getElementById("sel1");
            obj.add(option, 0);
        }

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            worksheets: [{
                data: data,
                columns: [
                    {
                        type: 'text',
                        title: 'title1',
                    },
                    {
                        type: 'dropdown',
                        title: 'title2',
                        source: [
                            "aaa",
                            "bbb",
                            "ccc",
                        ]
                    },
                ]
            }],
            onchange: function (el, cell, x, y, newValue, oldValue) {
                debug("onchange:" + x + ":" + y + ":" + oldValue + "-" + newValue);

                cell.style.backgroundColor = 'orange';
                // こちらでも動作します
                // table[0].getCell(Number(x), Number(y)).style.backgroundColor = 'blue';

			},
            onselection: function (el, px, py, ux, uy, origin) {
                debug("onselection:" + px + ":" + py + ":" + ux + ":" + uy);
            },
            onblur: function (el) {
                debug("onblur");
            },
            onbeforepaste: function (el, data, x, y) {

                debug("onbeforepaste:" + x + ":" + y + ":" + data.length);

                if (data.length != 1) {
                    // return falseで貼り付けの取り消し
                    return false;
                }
            }
        });

    </script>
</body>

</html>

上記例はイベントの一部です。他のイベントは公式ページを参照して下さい。

実行結果

ワークシート


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <input type="button" value="Add" id="btn1" />
    <script>
        var data = [
            ['text1', 'aaa'],
            ['text2', 'bbb'],
        ];

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            tabs: true,
            worksheets: [{
                data: data,
                worksheetName: 'default',
                columns: [
                    {
                        type: 'text',
                        title: 'title1',
                    },
                    {
                        type: 'text',
                        title: 'title2',
                    },
                ]
            }]
        });

        document.getElementById("btn1").onclick = function () {

            table[0].createWorksheet({

                minDimensions: [2, 2],
                worksheetName: 'from Button',
            });
        }

    </script>
</body>

</html>

実行結果

初期設定


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Jspreadsheet CE のテストページ</title>
    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />
</head>

<body>
    <div id="spreadsheet"></div>
    <script>
        var data = [
            ['text1', 'aaa'],
            ['text2', 'bbb'],
        ];

        var table = jspreadsheet(document.getElementById('spreadsheet'), {
            // 右クリックメニューを表示させない
            contextMenu: function () {
                return false;
            },
            worksheets: [{
                // 行を追加させない
                allowInsertRow: false,
                // 列を追加させない
                allowInsertColumn: false,
                // 編集させない
                //editable: false,
                data: data,
                columns: [
                    {
                        type: 'text',
                        title: 'title1',
                    },
                    {
                        type: 'text',
                        title: 'title2',
                    },
                ]
            }]
        });

    </script>
</body>

</html>

上記例は初期設定の一部です。他の初期設定は公式ページ(v4)を参照して下さい。

実行結果

ライブラリ取得

URLで指定

Jspreadsheet CEを使用する場合、以下を指定します。


    <script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css"
        type="text/css" />
    <script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.css" type="text/css" />

入手したライブラリを指定

ライブラリをローカル環境等に置いて動作させる場合は以下を取得します。

jsuites.js
index.js
jspreadsheet.css
jsuites.css

https://github.com/jspreadsheet/ceにアクセスして、以下をダウンロードします。

解凍してdistフォルダに対象ファイルが入っています。
https://github.com/jsuites/jsuitesからも同じようにダウンロードします。

ファイルを環境に置いた後、URLを以下のように変更します。


<script src="../jspreadsheet-ce/index.js"></script>
<script src="../jspreadsheet-ce/jsuites.js"></script>
<link rel="stylesheet" href="../jspreadsheet-ce/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="../jspreadsheet-ce/jsuites.css" type="text/css" />

ページのトップへ戻る