SpreadJS 是一款基于 HTML5 的純前端電子表格控件，兼容 450 種以上的 Excel 公式，具有 “高性能、跨平臺、與 Excel 高度兼容”的產品特性SpreadJS 為用戶帶來親切的 Excel 使用體驗的同時，滿足 Web Excel 組件開發、表格文檔協同編輯、數據填報、Excel 類報表設計等業務場景，極大降低了企業研發成本和項目交付風險。下面我們將講述在使用SpreadJS進行開發時如何應用Excel主題。

下載最新版SpreadJS

下面的示例展示了如何用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元！歡迎咨詢慧都在線客服了解詳情！