Customize Column Cells

Theme:

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:

//add table
dataManager.addTable("myTable", {
    remote: {
        read: {
            url: "https://demodata.mescius.io/northwind/api/v1/Orders"
        }
    }
});
//add custom column cell style
var addressStyle = {
    formatter: 'The detailed ship address: {{=CONCAT(@.shipAddress, ", ", @.shipCity)}}'
};
//The formatter in the style can be a formula or a template with the formula:
// 1. formula: =[@employee.FirstName] & "  " & [@employee.LastName]
// 2. template:  {{=[@employee.FirstName] & "  " & [@employee.LastName]}}
//The formula can extract the properties from the full row.
var employeeStyle = {
    formatter: '{{=[@employee.FirstName] & "  " & [@employee.LastName]}} ({{=[@shipRegion]}})'
};
//The formatter in the style can be a formula or a template with the formula:
// 1. formula: =[@employee.FirstName] & "  " & [@employee.LastName]
// 2. template:  {{=[@employee.FirstName] & "  " & [@employee.LastName]}}
//The formula can extract the properties from the full row.
var employeeStyle = {
    formatter: 'The employee: {{=[@employee.FirstName] & "  " & [@employee.LastName]}}'
};
var formulaRule = {
    ruleType: "formulaRule",
    formula: "@>50",
    style: {
        backColor: "pink"
    }
};
//add custom view
var myView = orderTable.addView("myView", [
    { value: "orderId", width: 100}, //set column width 100px
    { value: "orderDate", width: 200},
    { value: "freight", width: 100, conditionalFormats: [formulaRule], style: {hAlign: "center"}},
    { caption: "CompanyName", value: "customer.companyName", width: "2*"}, //set column width with star size, which will calculate actual column width by the rest size of viewport
    { value: "customer.contactName", width: "*"},
    { value: "customer.contactTitle", width: "*"},
    { value: "=[@]", caption: "Address", style: addressStyle, width: "3*"},
    { value: "employee", caption: "Employee", style: employeeStyle, width: "*"},
]);
// Set custom view into tableSheet.
tableSheet.setDataView(myView);

Feedback

Submit and view feedback for

This Product This Page

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;
}