基本应用

使用最受欢迎的电子表格图标来可视化你的数据,支持图标的类型包括柱状图,折线图,饼状图,面积图,条形图,XY散点图,股票图,组合图,雷达图,旭日图,树状图以及对趋势线,误差线以及其他自定义选项的额外支持。图表也可以从Excel文件导入或者导出到Excel文件。

要使用图表功能,请将SpreadJS Chart JS文件链接添加到文档的head部分 您可以使用sheet.charts.add方法创建图表,如下所示: 另外,您还可以指定图表的多个数据源: 当设置的数据源等于表结构引用时,图表将自动更新系列区域 你可以通过 sheet.charts.get 方法来获取一个图表的实例,通过 sheet.charts.remove 方法来移除sheet上的一个图表,也可以通过 sheet.charts.clear 方法来清空sheet上的所有chart。 一个图表是由许多子元素构成的, 你可以通过chart的API来自定义它们: Chart Title: chart的标题 你可以通过如下方式来获取或者设置一个chart的标题 (标题可设置的属性包括:text, back color, fore color, font and fontSize) Series: chart的系列 你可以从chart的series collection中获取、添加或者移除一个系列, 也可以自定义一个系列的属性 (系列可设置的属性包括:name, border width, border color, yValue, xValue and fill color),具体请参照如下的示例代码: Data Labels: 数据标签,用于标识数据点的详情 你可以通过如下方式来获取或者设置一个chart的数据标签的样式 showValue showSeriesName showCategoryName showPercentage separator position format color transparency backColor backColorTransparency borderColor borderWidth borderColorTransparency SpreadJS 提供了28种chart类型,你可以获取当前chart的类型或者将它改为另一个不同的类型。 SpreadJS 图表支持对于空值单元格或#N/A值单元格的控制处理,如下。 gaps: 空值将被过滤 zero: 空值将被当做0值处理 connected: 空值将被过滤并且线形将跳过空值区域 SpreadJS 图表支持对于隐藏行列的控制处理,如下。 SpreadJS 支持导入时保留或者忽略不受支持的图标类型
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 3 }); initSpread(spread); _getElementById("insertChart").addEventListener('click',function () { insertChart(spread) }); _getElementById("switchRowColumn").addEventListener('click',function () { switchRowColumn(spread) }); _getElementById("removeChart").addEventListener('click',function () { removeChart(spread) }); _getElementById("removeAllCharts").addEventListener('click',function () { removeAllChart(spread) }); _getElementById("groupSelect").addEventListener('change',function () { changeTypeSelect(); }); _getElementById("displayBlanksCells").addEventListener('change',function () { displayBlanksCells(spread, this.value); }); _getElementById("showNAAsBlanks").addEventListener('click',function () { showNAAsBlanks(spread, this.checked); }); _getElementById("ignoreHidden").addEventListener('click',function () { ignoreHiddenRowAndColumn(spread, this.checked); }); changeTypeSelect(); }; var chartType = [ [{ typeDesc: 'Clustered Column', type:GC.Spread.Sheets.Charts.ChartType.columnClustered },{ typeDesc: 'Stacked Column', type:GC.Spread.Sheets.Charts.ChartType.columnStacked },{ typeDesc: '100% Stacked Column', type:GC.Spread.Sheets.Charts.ChartType.columnStacked100 }], [{ typeDesc: 'Line', type:GC.Spread.Sheets.Charts.ChartType.line },{ typeDesc: 'Stacked Line', type:GC.Spread.Sheets.Charts.ChartType.lineStacked },{ typeDesc: '100% Stacked Line', type:GC.Spread.Sheets.Charts.ChartType.lineStacked100 },{ typeDesc: 'Line With Markers', type:GC.Spread.Sheets.Charts.ChartType.lineMarkers },{ typeDesc: 'Stacked Line With Markers', type:GC.Spread.Sheets.Charts.ChartType.lineMarkersStacked },{ typeDesc: '100% Stacked Line With Markers', type:GC.Spread.Sheets.Charts.ChartType.lineMarkersStacked100 }], [{ typeDesc: 'Pie', type:GC.Spread.Sheets.Charts.ChartType.pie },{ typeDesc: 'Doughnut', type:GC.Spread.Sheets.Charts.ChartType.doughnut }], [{ typeDesc: 'Clustered Bar', type:GC.Spread.Sheets.Charts.ChartType.barClustered },{ typeDesc: 'Stacked Bar', type:GC.Spread.Sheets.Charts.ChartType.barStacked },{ typeDesc: '100% Stacked Bar', type:GC.Spread.Sheets.Charts.ChartType.barStacked100 }], [{ typeDesc: 'Area', type:GC.Spread.Sheets.Charts.ChartType.area },{ typeDesc: 'Stacked Area', type:GC.Spread.Sheets.Charts.ChartType.areaStacked },{ typeDesc: '100% Stacked Area', type:GC.Spread.Sheets.Charts.ChartType.areaStacked100 }], [{ typeDesc: 'Scatter', type:GC.Spread.Sheets.Charts.ChartType.xyScatter },{ typeDesc: 'Scatter With Smooth Lines And Markers', type:GC.Spread.Sheets.Charts.ChartType.xyScatterSmooth },{ typeDesc: 'Scatter With Smooth Lines', type:GC.Spread.Sheets.Charts.ChartType.xyScatterSmoothNoMarkers },{ typeDesc: 'Scatter With Straight Lines And Markers', type:GC.Spread.Sheets.Charts.ChartType.xyScatterLines },{ typeDesc: 'Scatter With Straight Lines', type:GC.Spread.Sheets.Charts.ChartType.xyScatterLinesNoMarkers },{ typeDesc: 'Bubble', type:GC.Spread.Sheets.Charts.ChartType.bubble }], [{ typeDesc: 'High-Low-Close', type:GC.Spread.Sheets.Charts.ChartType.stockHLC },{ typeDesc: 'Open-High-Low-Close', type:GC.Spread.Sheets.Charts.ChartType.stockOHLC },{ typeDesc: 'Volume-High-Low-Close', type:GC.Spread.Sheets.Charts.ChartType.stockVHLC },{ typeDesc: 'Volume-Open-High-Low-Close', type:GC.Spread.Sheets.Charts.ChartType.stockVOHLC }] ]; function initSpread(spread) { var sheet1 = spread.sheets[0]; sheet1.name("Common Chart"); var sheet2 = spread.sheets[1]; sheet2.name("Custom Chart"); var sheet3 = spread.sheets[2]; sheet3.name("Table Binding Chart"); initSheet(sheet1); initSheet(sheet2); initBindingTable(sheet3); //add chart initChart(sheet1); initChart(sheet2); initBindingChart(sheet3); //custom chart customChartStyle(sheet2); } function initSheet(sheet){ sheet.suspendPaint(); //prepare data for chart var dataArray = [ ["", 'Chrome', 'FireFox', 'IE', 'Safari', 'Edge', 'Opera', 'Other'], ["2015", 0.5651, 0.1734, 0.1711, 0.427, 0, 0.184, 0.293], ["2016", 0.6230, 0.1531, 0.1073, 0.464, 0.311, 0.166, 0.225], ["2017", 0.6360, 0.1304, 0.834, 0.589, 0.443, 0.223, 0.246] ]; sheet.setArray(0, 0, dataArray); sheet.resumePaint(); } function initBindingTable(sheet) { sheet.suspendPaint(); var data = { name: 'Jones', region: 'East', sales: [{ orderDate: '1/6/2013', item: 'Pencil', units: 95, cost: 1.99 }, { orderDate: '4/1/2013', item: 'Binder', units: 60, cost: 4.99 }, { orderDate: '6/8/2013', item: 'Pen Set', units: 16, cost: 17.99 }, { orderDate: '7/8/2013', item: 'ball-point pen', units: 16, cost: 8.99 }, { orderDate: '8/8/2013', item: 'writing brush', units: 16, cost: 18.99 }, { orderDate: '9/8/2013', item: 'painting brush', units: 16, cost: 10.99 } ] }; var tableColumns = [], names = ['orderDate', 'item', 'units', 'cost'], labels = ['Order Date', 'Item', 'Units', 'Cost']; var table = sheet.tables.add('tableRecords', 0, 0, 4, 4); table.autoGenerateColumns(false); names.forEach(function (name, index) { var tableColumn = new GC.Spread.Sheets.Tables.TableColumn(); tableColumn.name(labels[index]); tableColumn.dataField(name); tableColumns.push(tableColumn); }); table.bindColumns(tableColumns); table.bindingPath('sales'); var source = new GC.Spread.Sheets.Bindings.CellBindingSource(data); sheet.setDataSource(source); var button = sheet.shapes.addFormControl("button", GC.Spread.Sheets.Shapes.FormControlType.button, 500, 10, 150, 50); button.text("Set Table Data Source"); var style = button.style(); style.textEffect.color = "rgb(0, 0, 0)"; style.textEffect.font = "bold 15px Calibri"; style.textFrame.vAlign = GC.Spread.Sheets.VerticalAlign.center; style.textFrame.hAlign = GC.Spread.Sheets.HorizontalAlign.center; button.style(style); sheet.bind(GC.Spread.Sheets.Events.FormControlButtonClicked, function (s, args) { var length = Math.floor(Math.random() * 20) + 1 data.sales = generateTestData(length, 10, 20); var source = new GC.Spread.Sheets.Bindings.CellBindingSource(data); sheet.setDataSource(source); }); sheet.resumePaint(); } function initChart(sheet) { //add common chart sheet.charts.add('Chart1', GC.Spread.Sheets.Charts.ChartType.columnClustered, 0, 100, 800, 300, "A1:H4"); } function initBindingChart(sheet) { sheet.charts.add('Chart1', GC.Spread.Sheets.Charts.ChartType.columnClustered, 250, 80, 800, 300, "tableRecords[[#Headers], [#Data], [Item]],tableRecords[[#Headers], [#Data], [Units]],tableRecords[[#Headers], [#Data], [Cost]]"); } function customChartStyle(sheet){ var changeChart = sheet.charts.all()[0]; changeChartStyle(changeChart); } function changeChartStyle(chart) { //change orientation switchOrientation(chart); //change legend changeChartLegend(chart); //change chartArea changeChartArea(chart); //change chartTitle changeChartTitle(chart); //change dataLabels changeChartDataLabels(chart); //change axisTitles changeChartAxisTitles(chart); //change axesLine changeChartAxesLine(chart); //change series changeSeries(chart); //change gridLine changeGridLine(chart); //change seriesBorder changeSeriesBorder(chart); } function switchOrientation(chart){ chart.switchDataOrientation(); } function ignoreHiddenRowAndColumn(spread, value){ var activeSheet = spread.getActiveSheet(); var activeChart = getActiveChart(activeSheet); activeChart && activeChart.ignoreHidden(value); } function displayBlanksCells(spread, value) { var activeSheet = spread.getActiveSheet(); var activeChart = getActiveChart(activeSheet); var index = parseInt(value); if (index !== null && index !== undefined) { activeChart && activeChart.displayBlanksAs(index); } } function showNAAsBlanks(spread, value){ var activeSheet = spread.getActiveSheet(); var activeChart = getActiveChart(activeSheet); activeChart && activeChart.displayNaAsBlank(value); } function changeChartLegend(chart) { var legend = chart.legend(); legend.visible = true; var legendPosition = GC.Spread.Sheets.Charts.LegendPosition; legend.position = legendPosition.top; chart.legend(legend); } function changeChartArea(chart) { var chartArea = chart.chartArea(); chartArea.backColor = "rgba(93,93,93,1)"; chartArea.color = "rgba(255,255,255,1)"; chartArea.fontSize = 14; chart.chartArea(chartArea); } function changeChartTitle(chart) { var title = chart.title(); title.text = "Browser Market Share"; title.fontSize = 18; chart.title(title); } function changeChartDataLabels(chart) { var dataLabels = chart.dataLabels(); dataLabels.showValue = true; dataLabels.showSeriesName = false; dataLabels.showCategoryName = false; dataLabels.format = "0.00%"; var dataLabelPosition = GC.Spread.Sheets.Charts.DataLabelPosition; dataLabels.position = dataLabelPosition.outsideEnd; chart.dataLabels(dataLabels); var series0 = chart.series().get(0); series0.dataLabels= { showSeriesName: true, showCategoryName: true, separator: ";", position: GC.Spread.Sheets.Charts.DataLabelPosition.Center, color: "red", backColor: "white", borderColor: "blue", borderWidth: 2 }; chart.series().set(0, series0); var series2 = chart.series().get(2); series2.dataLabels= { showSeriesName: true, separator: "/", position: GC.Spread.Sheets.Charts.DataLabelPosition.insideEnd, color: "yellow", backColor: "white", borderColor: "green", borderWidth: 1 }; chart.series().set(2, series2); var series4 = chart.series().get(4); series4.dataLabels= { showCategoryName: true, separator: ":", position: GC.Spread.Sheets.Charts.DataLabelPosition.above, color: "blue", backColor: "white", borderColor: "red", borderWidth: 2.5 }; chart.series().set(4, series4); } function changeChartAxisTitles(chart) { var axes = chart.axes(); axes.primaryCategory.title.text = 'Year'; axes.primaryCategory.title.fontSize = 14; chart.axes(axes); } function changeChartAxesLine(chart) { var axes = chart.axes(); axes.primaryValue.format = "0%"; chart.axes(axes); } function changeSeries(chart) { var series = chart.series(); var seriesItem = series.get(6); seriesItem.backColor = "#a3cf62"; series.set(6, seriesItem); } function changeGridLine(chart) { var axes = chart.axes(); axes.primaryCategory.majorGridLine.visible = false; axes.primaryValue.majorGridLine.visible = false; chart.axes(axes); } function changeSeriesBorder(chart) { var series = chart.series().get(); for (var i = 0; i < series.length; i++) { var seriesItem = series[i]; seriesItem.border.color = 'rgb(255,255,255)'; seriesItem.border.width = 1; chart.series().set(i, seriesItem); } } function insertChart(spread) { var activeSheet = spread.getActiveSheet(); var dataRanges = activeSheet.getSelections(); var dataFormulas = []; if (dataRanges) { dataRanges.forEach((dataRange) => { if (!judgeIsEmptyOneCell(activeSheet, dataRange)) { var rangeToFormula = GC.Spread.Sheets.CalcEngine.rangeToFormula; dataFormulas.push(rangeToFormula(dataRange)); } }) } var groupIndex = parseInt(_getElementById('groupSelect').value); var typeIndex = parseInt(_getElementById('typeSelect').value); if(groupIndex < chartType.length){ var typeArray = chartType[groupIndex]; if(typeIndex < typeArray.length){ var type = typeArray[typeIndex].type; try{ activeSheet.charts.add('', type, 30, 120, 500, 300, dataFormulas.join(','), GC.Spread.Sheets.Charts.RowCol.rows); }catch(e){ alert(e.message); } } } } function switchRowColumn(spread) { var activeSheet = spread.getActiveSheet(); var activeChart = getActiveChart(activeSheet); if(activeChart){ var isSwitched = activeChart.switchDataOrientation(); if (!isSwitched) { alert("'Can't switch row/column"); } } } function removeChart(spread) { var activeSheet = spread.getActiveSheet(); var activeChart = getActiveChart(activeSheet); if(activeChart){ activeSheet.charts.remove(activeChart.name()); } } function removeAllChart(spread) { var activeSheet = spread.getActiveSheet(); activeSheet.charts.clear(); } function getActiveChart(sheet) { var activeChart = null; sheet.charts.all().forEach(function (chart) { if (chart.isSelected()) { activeChart = chart; } }); return activeChart; } function judgeIsEmptyOneCell(sheet,range){ if(range.rowCount === 1 && range.colCount === 1){ var cell = sheet.getCell(range.row, range.col); if(!cell.text()){ return true; } } return false; } function changeTypeSelect(){ var index = parseInt(_getElementById('groupSelect').value); if(index!==null && index!==undefined && index < chartType.length){ _getElementById('typeSelect').innerHTML=''; var typeArray = chartType[index]; for(var i=0;i<typeArray.length;i++){ var item = typeArray[i]; var option = document.createElement('option'); var value = document.createAttribute('value'); value.nodeValue=i; option.setAttributeNode(value); option.innerHTML=item.typeDesc; _getElementById('typeSelect').appendChild(option); } } } function generateTestData(length, maxUnits, maxCost) { const testData = []; const items = [ 'apple', 'banana', 'orange', 'strawberry', 'grape', 'mango', 'watermelon', 'pineapple', 'kiwi', 'pear', 'peach', 'blueberry', 'cherry', 'lemon', 'lime', 'plum', 'raspberry', 'blackberry', 'pomegranate', 'coconut' ]; for (var i = 0; i < length; i++) { const orderDate = new Date().toISOString().slice(0, 10); const item = items[Math.floor(Math.random() * items.length)]; const units = Math.floor(Math.random() * maxUnits) + 1; const cost = Math.floor(Math.random() * maxCost) + 1; const order = { orderDate: orderDate, item: item, units: units, cost: cost }; testData.push(order); } return testData; } function _getElementById(id){ return document.getElementById(id); }
<!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-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <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-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-charts/dist/gc.spread.sheets.charts.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 class="options-container"> <p>Highlight a range of cells, select a chart group and chart type then click “Add Chart” to add the chart to the sheet. Switch the axes with “Switch row/column” or remove the chart(s) with “Remove chart” or “Remove all charts”. </p> <div class="option-row"> <label>Group:</label> <select id="groupSelect" style="width: 160px"> <option value="0" selected="selected">Column</option> <option value="1">Line</option> <option value="2">Pie</option> <option value="3">Bar</option> <option value="4">Area</option> <option value="5">Scatter</option> <option value="6">Stock</option> </select> </div> <div class="option-row"> <label>Type:</label> <select id="typeSelect" style="width: 160px"></select> </div> <div class="option-row"> <input type="button" style="width: 150px;margin:5px ;margin-bottom: 15px;" value="Add Chart" id="insertChart" /> <input type="button" style="width: 150px;margin:5px" value="Switch row/column" id="switchRowColumn" /> <input type="button" style="width: 150px;margin:5px" value="Remove chart" id="removeChart" /> <input type="button" style="width: 150px;margin:5px" value="Remove all charts" id="removeAllCharts" /> </div> <div class="option-row"> <label>Display Blanks Cells As:</label> <select id="displayBlanksCells" style="width: 80px"> <option value="1" selected="selected">Gaps</option> <option value="2">Zero</option> <option value="0">Connect</option> </select> </div> <div class="option-row"> <input type="checkbox" id="showNAAsBlanks"></input> <label for="showNAAsBlanks">Display #N/A Cells As Blank Cells</label> </div> <div class="option-row"> <input type="checkbox" id="ignoreHidden" checked></input> <label for="ignoreHidden">Ingore Hidden Rows And Columns</label> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 2px; margin-top: 2px; } .sample-options { z-index: 1000; } label { margin-bottom: 6px; } p{ padding:2px 10px; background-color:#F4F8EB; } input { padding: 2px 6px; } input[type=button] { margin-top: 6px; display: block; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }