Oracle APEX 低碼平台如何內建 SpreadJS

SpreadJS 是純 javascript 實作的基于 Excel 操作習慣的富表格控件，也可以直接導入和操作Excel，功能豐富，可以說明國産前端軟體的佼佼者了。

SpreadJS的工作模式是能夠讓業務人員設計樣例，開發人員将樣例模版化(參數化)，然後運作時再通過資料和參數的綁定，動态加載資料庫中的資料的。這給一些習慣于Excel操作的業務人員而言，可以滿足他們熟練的Excel操作習慣和技巧。

樣例和模闆的設計可以使用SpreadJS Designer，這個可以是線上的，也可以是本地安裝的exe。這個Designer也可以內建到自己的Web應用中，使應用擁有線上編輯Excel的功能。

線上編輯Excel這個功能實際應用價值有限，因為現在共享sheet很多了，如google sheet，各廠家的文檔雲，等。是以，這裡不啰嗦這個。這裡主要介紹如何将SpreadJS運作引擎內建到APEX。

1. 引入js和css (路徑依自己環境不同)

//  (Page x -> Javascript -> File URLs)
https://www.grapecity.com/spreadjs/demos/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js
https://www.grapecity.com/spreadjs/demos/spread/source/js/FileSaver.js
https://www.grapecity.com/spreadjs/demos/en/purejs/node_modules/@grapecity/spread-excelio/dist/gc.spread.excelio.min.js
https://www.grapecity.com/spreadjs/demos/en/purejs/node_modules/@grapecity/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js           

// (Page x -> Javascript -> File URLs)
https://www.grapecity.com/spreadjs/demos/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css           

1. 定義公共變量 (Page x -> Javascript -> Function and Global Variable Declaration)

GC.Spread.Sheets.LicenseKey = "Oracle,E781614243588279#B1NQ5x6KRJEOykmawNH...";
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss").getElementsByClassName("t-Region-body")[0], {calcOnDemand: true});
var excelIo = new GC.Spread.Excel.IO();
var filename = "file";  // 友善起見，在頁面上直接定義了一個全局的檔案名變量，實際上不一定需要           

1. 上面代碼中引用到了一個ID "ss"，那麼在頁面上建立一個靜态區域，把ID設為ss.

1. 定義一個檔案上傳框

1. 定義上傳按鈕

var excelFile = document.getElementById("UPLOAD_FILE").files[0];
filename = excelFile.name;
var password = document.getElementById('PASSWORD').value;
// here is excel IO API
excelIo.open(excelFile, function (json) {
        console.log("excelFile: " + filename, excelFile);
        var workbookObj = json;
        console.log("workbookobj", workbookObj);
        spread.fromJSON(workbookObj);
    }
, function (e) {
    // process error
    alert(e.errorMessage);
});           

1. 定義儲存按鈕

var json = spread.toJSON();
excelIo.save(json, function (blob) {
    apex.jQuery.ajax({
        url: 'https://apex.oracle.com/pls/apex/hyspoc/excel/action/?f=' + filename,
        method: "POST",
        data: {"data" : JSON.stringify(json)}
    }).done(function(msg){
        console.log("*** Saved successfully");
        location.reload();
    });
    // saveAs(blob, fileName); //Export to local dir
}, function (e) {
    console.log(e);
});           

最後，定義一個page load事件，啟動頁面時，将上次儲存的檔案自動從資料庫表中加載進來 （資料庫表的建立略，儲存和加載的REST API略）

apex.jQuery.ajax({
    url: "https://apex.oracle.com/pls/apex/hyspoc/excel/action/",
    success: function(result){
        filename = result.name;
        var db_content = result.items[0].content.replace("data=","").replaceAll("+", " ");
        var db_json_str = decodeURIComponent(db_content);
        var db_json = JSON.parse(db_json_str);
        spread.fromJSON(db_json);
    }
});           

以上就将SpreadJS內建到了APEX。話語不多，需要熟悉APEX才行。

=========================================

順便提一下SpreadJS Designer內建到 APEX。

第一步，引入js/css

https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.all.16.0.4.min.js
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.charts.16.0.4.min.js
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.shapes.16.0.4.min.js
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.slicers.16.0.4.min.js

https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.print.16.0.4.min.js
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.barcode.16.0.4.min.js
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.pdf.16.0.4.min.js
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.pivot.pivottables.16.0.4.min.js
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.tablesheet.16.0.4.min.js
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.io.16.0.4.min.js
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.excelio.16.0.4.min.js
	
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.designer.resource.en.16.0.4.min.js
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.designer.all.16.0.4.min.js           

https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.excel2013white.16.0.4.css
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/gc.spread.sheets.designer.16.0.4.min.css
https://apex.oracle.com/pls/apex/r/hyspoc/182232/files/static/v21/custom.css           

第二步，初始化 (Page x -> JavaScript -> Execute when Page Loads

//Apply License
GC.Spread.Sheets.LicenseKey = 'Oracle,E781614243588279#B1NQ5x6KRJ....';
GC.Spread.Sheets.Designer.LicenseKey = 'Oracle,E662212685567464#B11d....';

var config = GC.Spread.Sheets.Designer.DefaultConfig;
config.commandMap = {
    Welcome: {
        title: "Welcome",
        text: "Welcome",
        iconClass: "ribbon-button-welcome",
        bigButton: "true",
        commandName: "Welcome",
        execute: function (context, propertyName, fontItalicChecked) {
            alert('Welcome to new designer.');
        }
    }
}
config.ribbon[0].buttonGroups.unshift({
    "label": "NewDesigner",
    "thumbnailClass": "welcome",
    "commandGroup": {
        "children": [
            {
                "direction": "vertical",
                "commands": [
                    "Welcome"
                ]
            }
        ]
    }
});
var designer = new GC.Spread.Sheets.Designer.Designer(document.getElementById("gc-designer-container"), config);           

第三步，建個區域用來放設計器：

完成，效果如下：