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 a different dataType for columns or a 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 uses this to get the data source collection length. dataPath string The data reader uses this to get the 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 the column information, which contains dataType, dataPattern, dataMap and dataName. The dataType can be a "string", "number", "boolean", "object", "array", "date", "rowOrder" or "formula". It is the actual data type of original value, and useful for a Date because a Date is a string in JSON data and needs 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. The dataName indicates the name of the original data field that be from data source, using this property can map the original field name to the new field name in the data manager. This is the sample code of type, dataPath and countPath. This is the sample code for dataType and dataMap. This is the sample code for dataPattern. This is the sample code of dataName. the keys of the property columns indicate the field names in the data manager, and the dataName indicates the original data source field name.
/*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: { OrderId: {dataName: "Id"}, OrderDate: {dataType: "date"}, RequiredDate: {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(@."ShipAddress", ", ", @."ShipCity")}}' }; var view = myTable.addView("myView", [ { value: "OrderId", 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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Promise Polyfill for IE, --> <script src=""></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/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> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; margin: 0; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; }