Customize Column Cells

Users can customize the column cells defaultValue, validators, conditional formatting and styles.

In addition to customizing header style and caption, users can also customize style, conditional formats and validator of column cells in tableSheet view:
/*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; spread.bind(GC.Spread.Sheets.Events.EditStarting, function (e, args) { if (args.sheet.getValue(0, args.col, GC.Spread.Sheets.SheetArea.colHeader) === 'Customer'){ args.cancel = true; } }); //init a data manager var baseApiUrl = getBaseApiUrl(); var dataManager = spread.dataManager(); //add order table var orderTable = dataManager.addTable("orderTable", { remote: { read: { url: baseApiUrl + "/Order" } }, schema: { columns: { CustomerId: { lookup: { name: 'customer', columns: [ { value: "Id", width: 60 }, { value: "CompanyName", width: 200 }, { value: "ContactName", width: 140 }, { value: "ContactTitle", width: 140 } ] } } } } }); //add customer table var customerTable = dataManager.addTable("customerTable", { remote: { read: { url: baseApiUrl + "/Customer" } } }); //add employee table var employeeTable = dataManager.addTable("employeeTable", { remote: { read: { url: baseApiUrl + "/Employee" } } }); //add relationship between order table, customer table and employee table dataManager.addRelationship(orderTable, "CustomerId", "customer", customerTable, "Id", "orders"); dataManager.addRelationship(orderTable, "EmployeeId", "employee", employeeTable, "Id", "orders"); //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 var addressStyle = { formatter: 'Ship To: {{=CONCAT(@.ShipAddress, ", ", @.ShipCity)}}' }; var formulaRule = { ruleType: "formulaRule", formula: "@>50", style: { backColor: "pink" } }; var multiSelectStyle = { formatter: '{{=CONCAT([@customer.CompanyName], ", ", [@customer.ContactName])}}', // convert the object to string }; var myView = orderTable.addView("myView", [ { value: "Id", width: 60}, //set column width 100px { value: "OrderDate", width: 120, style: { formatter: "MM/dd/yyyy", hAlign: "center", cellButtons: [{ command: "openDateTimePicker", imageType: "dropdown", useButtonStyle: true, }]}}, { value: "Freight", width: 100, conditionalFormats: [formulaRule], style: {backColor: "white"}}, /** * customer.companyName - this is a related field from the customer table * update the customerId in orderTable by select from customer table. */ { caption: "Customer", value: "CustomerId", width: 350, style: multiSelectStyle}, /** * [=@] this column will contain the full row as a value, the formatter formula will extract the shipping address from the full row. * Format functions can be used to customize column display value * use star-size "2*" to set column width relative to the viewport width */ { value: "=[@]", caption: "Address", style: addressStyle, width: 450 }, /** * The formatter formula will extract the properties from the full row. * Format functions can be used to customize column display value. */ { caption: "Employee", value: "employee", width: 250 , style: { formatter: '{{=[@employee.FirstName] & " " & [@employee.LastName]}} ({{=[@ShipRegion]}})'} }, ]); myView.fetch().then(function() { sheet.setDataView(myView); }); 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, 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/@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> </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; }