Data Source Schema

SpreadJS TableSheet gives the user the ability to provide a data source schema that defines the structure and properties of the tablesheet being added. The user can also use the data source schema to assign different dataType for columns or dataPattern/dataMap on how the data would be presented in the tablesheet.

The schema contains the following properties, Property Type Description type string Any of 'json', 'csv', 'xml', 'columnJson'. countPath string The data reader use this to get data source collection length. dataPath string The data reader use this to get data source collection after parse. columns GC.Data.IColumnCollection The columns definitions of the data source table. Its key is column name, and its value is column information, which contains dataType, dataPattern and dataMap. The dataType can be a "string", "number", "boolean", "object", "array" or "date", it is the actual data type of original value, and useful for a Date because a Date is a string in JSON data and need be converted. The dataPattern is a string for parsing strings to value, such as the formatter "dd/MM/yyyy" for a date string, true and false value pairs "Yes|No" for a boolean string, and decimal separator "," for a numeric string. The dataMap is a simple map to display the original value in a more meaningful way. Its key can be a number or string, and its value can be a number, string or Date. This is the sample code of type, dataPath and countPath. This is the sample code of dataType and dataMap. This is the sample code of dataPattern.
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 }); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; //init a data manager var tableName = "Order"; var baseApiUrl = getBaseApiUrl(); var apiUrl = baseApiUrl + "/" + tableName; var dataManager = spread.dataManager(); var myTable = dataManager.addTable("myTable", { remote: { read: { url: apiUrl } }, schema: { columns: { OrderDate: {dataType: "date"}, ShippedDate: {dataType: "date"}, ShipVia: {dataMap: {1: "Speedy Express", 2: "United Package", 3: "Federal Shipping"}} } } }); //init a table sheet var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet); sheet.options.allowAddNew = false; //hide new row //bind a view to the table sheet myTable.fetch().then(function() { var style = { formatter: 'MM/dd/yyyy' }; var style2 = { formatter: 'Ship To: {{=CONCAT(PROPERTY(@, "ShipAddress"), ", ", PROPERTY(@, "ShipCity"))}}' }; var view = myTable.addView("myView", [ { value: "Id", width: 60 }, { value: "CustomerId",caption:"Customer", width: 100 }, { value: "ShipName", width: 200 }, { value: "OrderDate", width: 120, style: style }, { value: "ShippedDate", width: 120, style: style }, { value: "ShipVia", width: 120 }, { value: "=[@]",caption:"Ship Address",style:style2, width: 350 }, ]); sheet.setDataView(view); }); spread.resumePaint(); } function getBaseApiUrl() { return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api'; }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill --> <script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <div id="optionContainer" class="optionContainer"> </div> </div> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; }