LoginSignup
1
3

More than 3 years have passed since last update.

@naozo-se

jExcelを使ってブラウザ上でスプレッドシートを実現（売上管理サンプル）

Last updated at Posted at 2020-12-07

初めに

JavaScriptでほぼエクセルな画面が作成可能
表形式で入出力させたい場合に良いかも

見た目

jexcel01.png

こんな感じで作っています

色がついているところは編集はできず、自動で計算(セル単体の編集時のみ)
行はいくらでも増やせる（増やした行も自動計算）
列は増やさせない

HTML（使い方）

index.html
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>売上管理サンプル</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <script src="https://bossanova.uk/jexcel/v4/jexcel.js"></script>
    <link rel="stylesheet" href="https://bossanova.uk/jexcel/v4/jexcel.css" type="text/css" /> 
    <script src="https://bossanova.uk/jsuites/v3/jsuites.js"></script>
    <link rel="stylesheet" href="https://bossanova.uk/jsuites/v3/jsuites.css" type="text/css" />
    <style>
        /* 表全体 */
        #spreadsheet{
            font-size:12px;
        }

        /* 指定列の色変更（jsとかでちゃんと定義して処理したほうが良いかも） */
        #spreadsheet tbody.draggable td[data-X="5"],
        #spreadsheet tbody.draggable td[data-X="6"],
        #spreadsheet tbody.draggable td[data-X="8"],
        #spreadsheet tbody.draggable td[data-X="9"],
        #spreadsheet tbody.draggable td[data-X="10"]  {
            color:#000;
            background-color: #ff9;
        }
    </style>

    <script>
        // jExcelオブジェクト変数
        var jExcelsheetObj = null;

        // 取引先データ
        var customerList=[
            {"id": "10000001", "name": "取引先１"},
            {"id": "10000002", "name": "取引先２"},
            {"id": "10000003", "name": "取引先３"},
            {"id": "10000004", "name": "取引先４"},
            {"id": "10000005", "name": "取引先５"},
            {"id": "10000006", "name": "取引先６"}
        ];

        // 商品データ
        var productList=[
            {"id": "00000001", "name": "商品Ａ"},
            {"id": "00000002", "name": "商品Ｂ"},
            {"id": "00000003", "name": "商品Ｃ"},
            {"id": "00000004", "name": "商品Ｄ"},
            {"id": "00000005", "name": "商品Ｅ"},
            {"id": "00000006", "name": "商品Ｆ"}
        ];

        // 表示データ
        var sheetData = [
          { 
            "selesDate": "2020-12-08"
            , "customer": "10000001"
            , "product": "00000001"
            , "amount": "4"
            , "salesUnitPrice": "2000"
            , "salesSumPrice": "0"
            , "tax": "0"
            , "purchaseUnitPrice": "1600"
            , "purchaseSumPrice": "0"
            , "profit":"0"
            , "profitRate":"0"
            , "remarks": ""
          } //１行目
          ,{ 
            "selesDate": "2020-12-19"
            , "customer": "10000003"
            , "product": "00000003"
            , "amount": "1"
            , "salesUnitPrice": "3000"
            , "salesSumPrice": "0"
            , "tax": "0"
            , "purchaseUnitPrice": "2000"
            , "purchaseSumPrice": "0"
            , "profit":"0"
            , "profitRate":"0"
            , "remarks": ""
          } //２行目
        ];

        // ページ読み込み時
        window.addEventListener("load", function(){

            /** 
            * セルの値が変更された場合
            * @param instance:編集されたタグのインスタンス(使用していない)
            * @param cell:編集されたタグの情報(使用していない)
            * @param x:列のインデックス
            * @param y:行のインデックス 
            * @param value:編集されたタグの内容(使用していない)
            */
            const cellChanged = function(instance, cell, x, y, value) {

                // 合計処理を実行するカラムインデックス
                const executeCalcSumDataColomIdx = ["3","4","7"];

                beforeChangeExcute();
                // 合計処理を実行するカラム番号の場合
                if(executeCalcSumDataColomIdx.indexOf(x) >= 0){

                    // 計算データ取得
                    let calcData = calcSumData(y, x);

                    if(calcData){
                        // 計算データ設定
                        jExcelsheetObj.setRowData(y, calcData);
                    }
                }
                afterChangeExcute();
            }

            /** 
            * 合計計算ロジック(指定行の合計した数値を計算する)
            * @param rowIdx:行のインデックス 
            * @param colomIdx:列のインデックス(デフォルト-1のときは、処理実行)
            */
            const calcSumData = function(rowIdx){

                // 余りの行は、処理しない
                if(rowIdx >= jExcelsheetObj.rows.length - jExcelsheetObj.options.minSpareRows) return null;

                // 行のデータを取得
                rowData = jExcelsheetObj.getRowData(rowIdx);

                try{

                    /** 設定している値で計算(インデックスはリテラルじゃなくて、ちゃんと定義したほうが良い) */
                    // 売上金額 = 数量 × 売上単価
                    rowData[5] = chgStrToInt(rowData[3]) * chgStrToInt(rowData[4]);
                    // 消費税 = 売上金額 × 税率（定義してね）
                    rowData[6] = rowData[5] * 0.1;
                    // 仕入金額 = 数量 × 仕入単価
                    rowData[8] = chgStrToInt(rowData[3]) * chgStrToInt(rowData[7]);
                    // 利益 = 売上金額 ー 仕入金額
                    rowData[9] = rowData[5] - rowData[8];
                    // 利益率 = 利益 ÷ 売上金額
                    rowData[10] = Math.round(rowData[9] / rowData[5] * 100) + "%";

                }catch(e){
                    console.log(e);
                    // エラーが出る前のものまで返す
                    return rowData;
                }
                return rowData;
            }

            /** 
            * セル変更処理実行処理前ロジック
            */
            const beforeChangeExcute = function(){

                // 変更ロジック無効
                jExcelsheetObj.options.onchange = null;
                // 読み込み専用無効(無効にしないと、プログラムでも編集できない)
                jExcelsheetObj.options.columns[5].readOnly = false;
                jExcelsheetObj.options.columns[6].readOnly = false;
                jExcelsheetObj.options.columns[8].readOnly = false;
                jExcelsheetObj.options.columns[9].readOnly = false;
                jExcelsheetObj.options.columns[10].readOnly = false;
                // 読み込み専用の反映
                jExcelsheetObj.refresh();
            }
            /** 
            * 処理変更処理実行処理後ロジック
            */
            const afterChangeExcute = function(){
                // 変更ロジック有効
                jExcelsheetObj.options.onchange = cellChanged;
                // 読み込み専用有効
                jExcelsheetObj.options.columns[5].readOnly = true;
                jExcelsheetObj.options.columns[6].readOnly = true;
                jExcelsheetObj.options.columns[8].readOnly = true;
                jExcelsheetObj.options.columns[9].readOnly = true;
                jExcelsheetObj.options.columns[10].readOnly = true;
                // 読み込み専用の反映
                jExcelsheetObj.refresh();
            }

            // 変換メソッド（もう少し良いやり方が、、
            const chgStrToInt = function(strNum){
                return parseInt(strNum.replace(/,/, ''));
            }

            /** 初期処理開始 */
            // シートの領域取得
            var spArea = document.getElementById('spreadsheet');

            // jExcelオブジェクト生成
            jExcelsheetObj = jexcel(spArea, {
                data: sheetData, //設定データ
                minSpareRows: 1, //余り行
                columns: [
                    { type: "calendar", title:"年月日", width:80, options: { format:"YYYY-MM-DD" }},
                    { type: "dropdown", title:"取引先", width:200, align: "left", source:customerList },
                    { type: "autocomplete", title:"商品名", width:160 , align: "left", source: productList, multiple:false },
                    { type: "numeric", title:"数量", width:80, align: "right" ,mask:"#,##" },
                    { type: "numeric", title:"売上単価", width:80, align: "right" ,mask:"#,##" },
                    { type: "numeric", title:"売上金額", width:80, align: "right" ,mask:"#,##", readOnly:true},
                    { type: "numeric", title:"消費税", width:80, align: "right" ,mask:"#,##", readOnly:true},
                    { type: "numeric", title:"仕入単価", width:80, align: "right" ,mask:"#,##" },
                    { type: "numeric", title:"仕入金額", width:80, align: "right" ,mask:"#,##", readOnly:true},
                    { type: "numeric", title:"利益", width:80 , align: "right" ,mask:"#,##", readOnly:true},
                    { type: "numeric", title:"利益率", width:80, align: "right" ,readOnly:true},
                    { type: "text", title:"備考", width:120, align: "left" },
                ], //列定義
                tableOverflow:true,    // trueの場合は、領域以上になるとスクロールを表示
                tableHeight:'200px',   // 高さ
                tableWidth:'98vw',     // 幅
                onchange: cellChanged, // 変更時のロジック
                allowDeleteColumn: false,       // 列削除ＮＧ
                allowInsertColumn: false,       // 列追加ＮＧ
                allowManualInsertColumn: false, // 列追加ＮＧ
                allowRenameColumn: false,       // 列名変更
            });

            /** 初回データの計算ロジック */
            // 変更前メソッドを実行
            beforeChangeExcute();
            // データ(行)数取得
            let allDataRows = jExcelsheetObj.rows.length;
            // データ(行)数ループ
            for(let idx=0; idx<allDataRows;idx++){
                // 計算データ取得
                let calcData = calcSumData(idx);

                if(calcData){
                    // 計算データ設定
                    jExcelsheetObj.setRowData(idx, calcData);
                }

            }
            // 変更後メソッドを有効にする
            afterChangeExcute();

            // ヘッダーの中央寄せ
            var spHeaders = document.querySelectorAll("#spreadsheet thead.resizable td");
            for(let idx = 0; idx<spHeaders.length;idx++){
                spHeaders[idx].style.textAlign="center";
            }

            // 登録ボタン
            document.getElementById("regist").addEventListener("click", function(){
                // 全データ取得
                var allData = jExcelsheetObj.getData();

                // 取得したデータでajaxで指定URLにpostとかする
                // commonAjax(url, allData);
            });

        });
    </script>
</head>
<body>
<h1>売上管理</h1>
<div id="spreadsheet"></div>
<div><input type="button" value="登録" id="regist"></div>
</body>
</html>

あとがき

合計行も入れたかったけど、折れました＞＜；
色々カラム情報を定義すれば、もうちょい良いものができるはず！
にしても、英語のドキュメントのわかりにくいことわかりにくいこと、、、（涙）
onloadにnull突っ込むやり方については、もう少し良いやり方あるかも、、、
※永久ループが発生する

参考

基本処理：https://qiita.com/t-iguchi/items/689b85ff163e0a321f1d
参考エクセル：https://www.template-sozai.com/keyword/%E5%A3%B2%E4%B8%8A%E7%AE%A1%E7%90%86

プラグインのファイルをダウンロード

　・jExcel：https://github.com/jspreadsheet/jexcel
　・jSuites：https://github.com/jsuites/jsuites
　からダウンロード

1
3
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
1
3