自定义表头区域

概述本 Demo 展示了如何在集算表中创建自定义表头区域。通过 applyFreeHeaderArea 方法,在商务旅行预算表的上方添加了标题、说明、预算目标和实际花费统计,并使用公式自动计算差额和迷你图显示进度。实现思路创建数据源,包含旅行费用明细数据创建 TableSheet 并应用表格主题配置视图,设置列定义、样式和条件格式创建模板工作表用于自定义表头区域启用 keepUnknownFormulas 选项,允许引用 TableSheet 列数据在模板表中设置标题、说明、公式、样式和合并单元格将模板表转换为 JSON 并应用到 TableSheet 代码解析创建数据源和视图这段代码创建了数据管理器和数据表,并定义了视图。最后一列使用公式 =[@cost]*[@quantity] 计算金额。创建模板工作表创建一个普通工作表作为模板,并启用 keepUnknownFormulas 选项。这个选项允许模板中的公式引用 TableSheet 的列数据(如 TableSheet1[Amount])。设置标题和说明使用 setValue 设置单元格文本,getCell 获取单元格对象并设置样式,addSpan 合并单元格。引用 TableSheet 数据的公式这个公式引用了 TableSheet 中名为 Amount 的列,计算总花费。因为启用了 keepUnknownFormulas 选项,这个公式会在应用后正确解析。使用迷你图显示进度使用 HBARSPARKLINE 函数创建水平条形迷你图。参数说明: ROUND(C4/MAX(C4,C5),2) - 计算预算占比(0-1之间的值) Colors.darkGreen - 条形颜色 false - 不显示轴线应用自定义表头区域将模板工作表转换为 JSON,然后应用到 TableSheet。applyFreeHeaderArea 方法将模板中的内容、样式和公式应用到表头区域。运行效果表格顶部显示自定义的表头区域,包含标题 "Business Trip Budget" 表头区域显示说明文字,提示用户输入目标预算目标预算单元格可编辑,默认值为 $1,000 实际花费总额通过公式自动计算,引用表格中的 Amount 列表头区域显示预算对比结果(超出或节省预算的金额) 使用水平条形迷你图直观显示预算执行进度表格数据使用数据条条件格式显示每项费用的相对大小 API 参考 applyFreeHeaderArea 方法将工作表 JSON 应用到 TableSheet 列标题自由布局区域。 sheetJson - 可选,工作表 JSON 对象,可通过 Worksheet.toJSON() 方法获取 keepUnknownFormulas 选项布尔值,指示未知公式是否可以包含在工作表 JSON 数据中。默认为 false。当需要在自定义表头区域中引用 TableSheet 列或其他工作表数据时,需要设置为 true。 HBARSPARKLINE 函数创建水平条形迷你图。 value - 条形长度值,应在 1(-100%)到 1(100%)之间 colorScheme - 可选,条形颜色,默认为灰色 axisVisible - 可选,是否显示轴线,默认为 true barHeight - 可选,条高度与单元格高度的百分比(0-1)

/*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\/SpreadJSTutorial\//)[0] + 'server/api';
}

<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="zh-cn" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/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$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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;
}

SpreadJS 在线示例