纯前端表格控件SpreadJS示例教程：费用预算示例

下面的示例展示了如何用SpreadJS来创建应用，比如费用预算。这个示例主要使用了表格、公式、条件格式。使用IconSetRule 和 DataBarRule可以更好地呈现数据。

下面的示例展示了如何用SpreadJS来创建应用，比如费用预算。这个示例主要使用了表格、公式、条件格式。使用IconSetRule 和 DataBarRule可以更好地呈现数据。

app.js:

        var spreadNS = GC.Spread.Sheets;        window.onload = function () {            var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });            initSpread(spread);        };        function initSpread(spread) {            var sheet = spread.getSheet(0);            sheet.suspendPaint();            var cfs = sheet.conditionalFormats ;            var table = sheet.tables.add("tblOperatingExpenses2", 4, 1, 11, 6, new spreadNS.Tables.TableTheme());            table.showFooter(true);            var dataTable = [];            dataTable[0] = new Array("STATUS", "OPERATING", "BUDGET", "ACTUAL", "DIFFERENCE ($)", "DIFFERENCE (%)");            dataTable[1] = new Array(0, "Advertising", 600, 545, 0, 0);            dataTable[2] = new Array(0, "Debts", 125, 150, 0, 0);            dataTable[3] = new Array(0, "Benefits", 100, 100, 0, 0);            dataTable[4] = new Array(0, "Supplies", 100, 90, 0, 0);            dataTable[5] = new Array(0, "Postage", 150, 145, 0, 0);            dataTable[6] = new Array(0, "Rent or mortgage", 1000, 1000, 0, 0);            dataTable[7] = new Array(0, "Sales expenses", 500, 630, 0, 0);            dataTable[8] = new Array(0, "Taxes", 350, 375, 0, 0);            dataTable[9] = new Array(0, "Utilities", 400, 370, 0, 0);            dataTable[10] = new Array(0, "Other", 500, 435, 0, 0);            dataTable[11] = new Array(0, "Total Expenses", 0, 0, 0, 0);            sheet.addSpan(0, 1, 1, 4);            sheet.addSpan(0, 5, 1, 2);            sheet.addSpan(1, 1, 1, 6);            sheet.addSpan(2, 1, 1, 6);            sheet.addSpan(3, 1, 1, 2);            sheet.addSpan(16, 1, 1, 6);            sheet.addSpan(17, 1, 1, 6);            sheet.getDefaultStyle().vAlign = spreadNS.VerticalAlign.center;            sheet.getDefaultStyle().font = "lighter 10pt Calibri";            sheet.getDefaultStyle().foreColor = "rgb(68, 84, 106)";            sheet.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };            sheet.getCell(0, 1).value("Expense Budget").font("lighter 28pt Calibri");            sheet.getCell(0, 5).value("CONTOSO, 2013").font("11pt Calibri").foreColor("rgb(64, 64, 64)")                .hAlign(spreadNS.HorizontalAlign.right).vAlign(spreadNS.VerticalAlign.bottom);            sheet.getCell(3, 1).value("OPERATING BUDGET").font("bold 13pt Calibri ").foreColor("rgb(64, 64, 64)");            sheet.getRange(4, 1, 1, 6).font("bold 10pt Calibri")                .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));            sheet.getRange(15, 1, 1, 6).font("bold 10pt Calibri")                .borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));            sheet.getRange(3, 1, 1, 2)                .borderLeft(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin))                .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin))                .borderRight(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));            sheet.getRange(4, 1, 12, 1).borderLeft(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin)).hAlign(spreadNS.HorizontalAlign.center);            sheet.getRange(4, 6, 12, 1).borderRight(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));            sheet.getRange(5, 1, 10, 6).borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.dashed));;            for (var row = 4; row < 16; row++) {                for (var col = 1; col < 7; col++) {                    sheet.setValue(row, col, dataTable[row - 4][col - 1]);                }            }            var rowHeights = new Array(74, 4, 20);            for (var row = 0; row < 3; row++) {                sheet.setRowHeight(row, rowHeights[row]);            }            for (var row = 3; row < 17; row++) {                sheet.setRowHeight(row, 24);            }            sheet.setRowHeight(17, 4);            var colWidths = new Array(18, 83, 111, 84, 85, 126, 129);            for (var col = 0; col < 7; col++) {                sheet.setColumnWidth(col, colWidths[col]);            }            sheet.getRange(1, 1, 1, 6)                .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thick))                .borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));            sheet.getRange(17, 1, 1, 6)                .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thick))                .borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));            sheet.getRange(3, 3, 13, 3).formatter('"$"#,##0.00');            sheet.getRange(3, 6, 12, 1).formatter('0%');            sheet.getCell(15, 6).formatter('0.0%');            var iconSetRule1 = new spreadNS.ConditionalFormatting.IconSetRule();            iconSetRule1.ranges([new spreadNS.Range(15, 1, 1, 1)]);            iconSetRule1.iconSetType(spreadNS.ConditionalFormatting.IconSetType.threeSymbolsCircled)                .showIconOnly(true);            var icons = iconSetRule1.iconCriteria();            icons[0] = new spreadNS.ConditionalFormatting.IconCriterion(false, spreadNS.ConditionalFormatting.IconValueType.number, -1);            icons[1] = new spreadNS.ConditionalFormatting.IconCriterion(true, spreadNS.ConditionalFormatting.IconValueType.number, 1);            cfs.addRule(iconSetRule1);            var style = new spreadNS.Style();            style.foreColor = "red";            cfs.addCellValueRule(spreadNS.ConditionalFormatting.ComparisonOperators.lessThan, 0, 0, style, [new spreadNS.Range(15, 5, 1, 2)]);            var dataBarRule1 = new spreadNS.ConditionalFormatting.DataBarRule();            dataBarRule1.minType(spreadNS.ConditionalFormatting.ScaleValueType.automin);            dataBarRule1.maxType(spreadNS.ConditionalFormatting.ScaleValueType.automax);            dataBarRule1.ranges([new spreadNS.Range(5, 4, 10, 1)]);            dataBarRule1.color("orange").showBorder(true).borderColor("orange").dataBarDirection(spreadNS.ConditionalFormatting.BarDirection.rightToLeft);            cfs.addRule(dataBarRule1);            var dataBarRule2 = new spreadNS.ConditionalFormatting.DataBarRule();            dataBarRule2.minType(spreadNS.ConditionalFormatting.ScaleValueType.automin);            dataBarRule2.maxType(spreadNS.ConditionalFormatting.ScaleValueType.automax);            dataBarRule2.ranges([new spreadNS.Range(5, 3, 10, 1)]);            dataBarRule2.color("rgb(0,138,239)").showBorder(true).borderColor("rgb(0,138,239)");            cfs.addRule(dataBarRule2);            var dataBarRule3 = new spreadNS.ConditionalFormatting.DataBarRule();            dataBarRule3.minType(spreadNS.ConditionalFormatting.ScaleValueType.lowestValue);            dataBarRule3.maxType(spreadNS.ConditionalFormatting.ScaleValueType.highestValue);            dataBarRule3.ranges([new spreadNS.Range(5, 6, 10, 1)]);            dataBarRule3.color("rgb(91,155,213)").showBorder(true).borderColor("rgb(91,155,213)")                .negativeFillColor("rgb(237,125,49)").useNegativeBorderColor(true).negativeBorderColor("rgb(237,125,49)")                .axisPosition(spreadNS.ConditionalFormatting.DataBarAxisPosition.cellMidPoint);            cfs.addRule(dataBarRule3);            var iconSetRule2 = new spreadNS.ConditionalFormatting.IconSetRule();            iconSetRule2.ranges([new spreadNS.Range(5, 1, 10, 1)]);            iconSetRule2.iconSetType(spreadNS.ConditionalFormatting.IconSetType.threeSymbolsUncircled)                .reverseIconOrder(true)                .showIconOnly(true);            cfs.addRule(iconSetRule2);            table.setColumnFormula(0, "=tblOperatingExpenses2[[#Totals],[DIFFERENCE (%)]]");            table.setColumnFormula(2, "=SUBTOTAL(109,[BUDGET])");            table.setColumnFormula(3, "=SUBTOTAL(109,[ACTUAL])");            table.setColumnFormula(4, "=SUBTOTAL(109,[DIFFERENCE ($)])");            table.setColumnFormula(5, '=IFERROR(SUM(tblOperatingExpenses2[[#Totals],[DIFFERENCE ($)]]/tblOperatingExpenses2[[#Totals],[BUDGET]]),"")');            table.setColumnDataFormula(0, '=IFERROR([@ACTUAL]/[@BUDGET],"")');            table.setColumnDataFormula(4, '=[@BUDGET]-[@ACTUAL]');            table.setColumnDataFormula(5, '=IFERROR([@[DIFFERENCE ($)]]/[@BUDGET],"")');            sheet.resumePaint();        }

index.html：

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

style.css：

.sample-tutorial {    position: relative;    height: 100%;    overflow: hidden;}body {    position: absolute;    top: 0;    bottom: 0;    left: 0;    right: 0;}

赶快下载体验吧！现推出限时活动，SpreadJS正版授权低至3490元！欢迎咨询在线客服了解详情！

标签：