Apply Free Header Area
TableSheet provides the applyFreeHeaderArea method to configure the free header area for the column header.
Generate Free Header Area JSON
If you want to refer to formulas with sheet names (like "TableSheet1![ColumnName]", "OtherSheet!A1" or "AVERAGE(TableSheet1[ColumnName])") when you generate the free header area JSON, you should enable this sheet option:
This is the sample code:
Formula Text Box in Header Area Support
You can double click cells in the header area to start editing, or press the "=" key to enter formula editing.
Then you can select TableSheet columns or any range in the free header area.
/*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.clearSheets();
spread.suspendPaint();
var dataManager = spread.dataManager();
var myTable = dataManager.addTable("myTable", {
data: [
{ id: 1, item: "Airfare", description: "Tickets", cost: 300, quantity: 1 },
{ id: 2, item: "Hotel", description: "Room", cost: 125, quantity: 3 },
{ id: 3, item: "Car rental", description: "Cost per day", cost: 52, quantity: 6 },
{ id: 4, item: "Gas", description: "Cost per gallon", cost: 1.74, quantity: 14 },
{ id: 5, item: "Entertainment", description: "Amount", cost: 130, quantity: 1 },
{ id: 6, item: "Gifts", description: "Amount", cost: 85, quantity: 1 },
{ id: 7, item: "Miscellaneous", description: "Amount", cost: 55, quantity: 1 },
{ id: 8, item: "Food", description: "Cost per day", cost: 48, quantity: 6 },
]
});
//init a table sheet
var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet);
sheet.applyTableTheme(GC.Spread.Sheets.Tables.TableThemes.professional7);
//bind a view to the table sheet
myTable.fetch().then(function() {
var Colors = {
lightGreen: "rgb(227,239,218)",
middleGreen: "rgb(115,150,61)",
darkGreen: "rgb(115,150,61)",
lightGray: "rgb(191,191,191)",
white: "rgb(255,255,255)",
gray: "rgb(225,225,225)",
black: "rgb(0,0,0)"
};
var formatter = "$#,##0.00_);($#,##0.00)";
var currencyFormatter = { formatter: formatter };
var headerStyle = {
backColor: Colors.darkGreen,
foreColor: Colors.white,
font: "bold 12pt Calibri",
hAlign: "left"
};
var dataBarRule1 = {
ruleType: "dataBarRule",
color: Colors.darkGreen,
gradient: true
};
var dataBarRule2 = {
ruleType: "dataBarRule",
color: Colors.lightGray,
gradient: true,
barDirection: "rightToLeft"
};
var view = myTable.addView("myView", [
{ value: "item", caption: "Item", width: 180, headerStyle },
{ value: "description", caption: "Description", width: 120, headerStyle },
{ value: "quantity", caption: "Qty", width: 90, headerStyle },
{ value: "cost", caption: "Cost", width: 100, style: currencyFormatter, headerStyle, conditionalFormats: [dataBarRule1] },
{ value: "=[@cost]*[@quantity]", caption: "Amount", width: 120, style: currencyFormatter, headerStyle, conditionalFormats: [dataBarRule2] }
]);
// create template sheet for free header area json
var templateSheet = new GC.Spread.Sheets.Worksheet();
templateSheet.options.keepUnknownFormulas = true;
var currencyFormatterStyle = new GC.Spread.Sheets.Style();
currencyFormatterStyle.formatter = formatter;
currencyFormatterStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
templateSheet.setRowCount(6);
var freeHeaderAreaStyle = new GC.Spread.Sheets.Style();
freeHeaderAreaStyle.backColor = Colors.white;
templateSheet.setValue(0, 0, "Business Trip Budget");
templateSheet.getCell(0, 0).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 30px \"Microsoft Sans Serif\"");
templateSheet.addSpan(0, 0, 1, 5);
var titleStyle = new GC.Spread.Sheets.Style();
titleStyle.backColor = Colors.lightGreen;
titleStyle.foreColor = Colors.black;
templateSheet.setStyle(0, -1, titleStyle);
templateSheet.setRowHeight(0, 80);
templateSheet.getCell(1, 0)
.value("Enter Target trip budget below. Total trip cost and under or over budget are automatically calculated.")
.font("italic 12px \"Microsoft Sans Serif\"")
.foreColor("rgb(120,120,120)")
.textIndent(2);
templateSheet.setStyle(1, -1, titleStyle);
templateSheet.addSpan(1, 0, 1, 5);
var headerDescriptionStyle = new GC.Spread.Sheets.Style();
headerDescriptionStyle.backColor = Colors.lightGreen;
headerDescriptionStyle.borderBottom = new GC.Spread.Sheets.LineBorder(Colors.middleGreen, GC.Spread.Sheets.LineStyle.thick);
templateSheet.setStyle(2, -1, headerDescriptionStyle);
templateSheet.addSpan(2, 0, 1, 5);
templateSheet.setRowHeight(2, 16);
templateSheet.setValue(3, 0, "Target trip budget");
templateSheet.getCell(3, 0).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Trebuchet MS\"").foreColor(Colors.darkGreen);
templateSheet.addSpan(3, 0, 1, 2);
templateSheet.setValue(3, 2, 1000);
templateSheet.getCell(3, 2).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Trebuchet MS\"").foreColor(Colors.darkGreen).formatter(formatter);
templateSheet.setFormula(3, 3, '=HBARSPARKLINE(ROUND(C4/MAX(C4,C5),2),"'+ Colors.darkGreen +'",false)');
templateSheet.setStyle(3, 3, new GC.Spread.Sheets.Style(undefined, Colors.black));
templateSheet.addSpan(3, 3, 1, 2);
templateSheet.setStyle(3, -1, freeHeaderAreaStyle);
templateSheet.setValue(4, 0, "Total cost of the trip");
templateSheet.getCell(4, 0).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Trebuchet MS\"").foreColor(Colors.lightGray);
templateSheet.addSpan(4, 0, 1, 2);
templateSheet.setFormula(4, 2, '=SUM(TableSheet1[Amount])');
templateSheet.getCell(4, 2).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Trebuchet MS\"").foreColor(Colors.lightGray).formatter(formatter);
var contentBorderStyle = new GC.Spread.Sheets.Style();
contentBorderStyle.backColor = Colors.white;
contentBorderStyle.borderBottom = new GC.Spread.Sheets.LineBorder(Colors.lightGray, GC.Spread.Sheets.LineStyle.thick);
templateSheet.setStyle(4, -1, contentBorderStyle);
templateSheet.setFormula(4, 3, '=HBARSPARKLINE(ROUND(C5/MAX(C4,C5),2),"'+ Colors.lightGray +'",false)');
templateSheet.setStyle(4, 3, new GC.Spread.Sheets.Style(undefined, Colors.black));
templateSheet.addSpan(4, 3, 1, 2);
templateSheet.setFormula(5, 0, '=IF(C4>C5,"You\'re under budget by","You\'re over budget by")');
templateSheet.getCell(5, 0).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Trebuchet MS\"").foreColor(Colors.black);
templateSheet.addSpan(5, 0, 1, 2);
templateSheet.setFormula(5, 2, '=C4-C5');
templateSheet.getCell(5, 2).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Trebuchet MS\"").formatter(formatter).foreColor(Colors.black);
templateSheet.setStyle(5, -1, freeHeaderAreaStyle);
templateSheet.addSpan(5, 2, 1, 3);
let template = templateSheet.toJSON();
sheet.applyFreeHeaderArea(template);
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, 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;
}
.gc-columnHeader-selected, .gc-columnHeader-highlight, .gc-columnHeader-hover {
background-color: rgba(115,150,61,0.8);
color: white;
border-bottom-color: white !important;
}
