基本应用

使用最受欢迎的电子表格图标来可视化你的数据,支持图标的类型包括柱状图,折线图,饼状图,面积图,条形图,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 支持导入时保留或者忽略不受支持的图标类型
import * as React from 'react'; import * as ReactDOM from 'react-dom'; import './styles.css'; import { AppFunc } from './app-func'; import { App } from './app-class'; // 1. Functional Component sample ReactDOM.render(<AppFunc />, document.getElementById('app')); // 2. Class Component sample // ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react'; import { useState } from 'react'; import * as ReactDOM from 'react-dom'; import GC from '@grapecity-software/spread-sheets'; import { SpreadSheets, Worksheet } from '@grapecity-software/spread-sheets-react'; import '@grapecity-software/spread-sheets-charts'; import '@grapecity-software/spread-sheets-resources-zh'; GC.Spread.Common.CultureManager.culture("zh-cn"); import './styles.css'; const Component = React.Component; const 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 _getElementById(id) { return document.getElementById(id); } export function AppFunc() { const [spread, setSpread] = useState(null); const [ignoreHidden, setIgnoreHidden] = useState(true); let initSpread = function (value) { setSpread(value); value.suspendPaint(); value.setSheetCount(3); let sheet1 = value.getSheet(0); sheet1.name("Common Chart"); let sheet2 = value.getSheet(1); sheet2.name("Custom Chart"); let sheet3 = value.getSheet(2); sheet3.name("Table Binding Chart"); initSheet(sheet1); initSheet(sheet2); initBindingTable(sheet3); //add chart initChart(sheet1); initChart(sheet2); initBindingChart(sheet3); //custom chart customChartStyle(sheet2); value.resumePaint(); changeTypeSelect(); } let initSheet = function (sheet) { sheet.suspendPaint(); //prepare data for chart let 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(); } let initBindingTable = function(sheet) { sheet.suspendPaint(); let 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 } ] }; let tableColumns = [], names = ['orderDate', 'item', 'units', 'cost'], labels = ['Order Date', 'Item', 'Units', 'Cost']; let table = sheet.tables.add('tableRecords', 0, 0, 4, 4); table.autoGenerateColumns(false); names.forEach(function (name, index) { let tableColumn = new GC.Spread.Sheets.Tables.TableColumn(); tableColumn.name(labels[index]); tableColumn.dataField(name); tableColumns.push(tableColumn); }); table.bindColumns(tableColumns); table.bindingPath('sales'); let source = new GC.Spread.Sheets.Bindings.CellBindingSource(data); sheet.setDataSource(source); let button = sheet.shapes.addFormControl("button", GC.Spread.Sheets.Shapes.FormControlType.button, 500, 10, 150, 50); button.text("Set Table Data Source"); let 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 () { let length = Math.floor(Math.random() * 20) + 1 data.sales = generateTestData(length, 10, 20); let source = new GC.Spread.Sheets.Bindings.CellBindingSource(data); sheet.setDataSource(source); }); sheet.resumePaint(); } let initChart = function (sheet) { //add common chart sheet.charts.add('Chart1', GC.Spread.Sheets.Charts.ChartType.columnClustered, 0, 100, 800, 300, "A1:H4"); } let initBindingChart = function(sheet) { //add table binding chart 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]]"); } let customChartStyle = function (sheet) { let changeChart = sheet.charts.all()[0]; changeChartStyle(changeChart); } let changeChartStyle = function (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); } let switchOrientation = function (chart) { chart.switchDataOrientation(); } let changeChartLegend = function (chart) { let legend = chart.legend(); legend.visible = true; let legendPosition = GC.Spread.Sheets.Charts.LegendPosition; legend.position = legendPosition.top; chart.legend(legend); } let changeChartArea = function (chart) { let chartArea = chart.chartArea(); chartArea.backColor = "rgba(93,93,93,1)"; chartArea.color = "rgba(255,255,255,1)"; chartArea.fontSize = 14; chart.chartArea(chartArea); } let changeChartTitle = function (chart) { let title = chart.title(); title.text = "Browser Market Share"; title.fontSize = 18; chart.title(title); } let changeChartDataLabels = function (chart) { let dataLabels = chart.dataLabels(); dataLabels.showValue = true; dataLabels.showSeriesName = false; dataLabels.showCategoryName = false; dataLabels.format = "0.00%"; let dataLabelPosition = GC.Spread.Sheets.Charts.DataLabelPosition; dataLabels.position = dataLabelPosition.outsideEnd; chart.dataLabels(dataLabels); let 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); let 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); let 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); } let changeChartAxisTitles = function (chart) { let axes = chart.axes(); axes.primaryCategory.title.text = 'Year'; axes.primaryCategory.title.fontSize = 14; chart.axes(axes); } let changeChartAxesLine = function (chart) { let axes = chart.axes(); axes.primaryValue.format = "0%"; chart.axes(axes); } let changeSeries = function (chart) { let series = chart.series(); let seriesItem = series.get(6); seriesItem.backColor = "#a3cf62"; series.set(6, seriesItem); } let changeGridLine = function (chart) { let axes = chart.axes(); axes.primaryCategory.majorGridLine.visible = false; axes.primaryValue.majorGridLine.visible = false; chart.axes(axes); } let changeSeriesBorder = function (chart) { let series = chart.series().get(); for (let i = 0; i < series.length; i++) { let seriesItem = series[i]; seriesItem.border.color = 'rgb(255,255,255)'; seriesItem.border.width = 1; chart.series().set(i, seriesItem); } } let getActiveChart = function (sheet) { let activeChart = null; sheet.charts.all().forEach(function (chart) { if (chart.isSelected()) { activeChart = chart; } }); return activeChart; } let judgeIsEmptyOneCell = function (sheet, range) { if (range.rowCount === 1 && range.colCount === 1) { let cell = sheet.getCell(range.row, range.col); if (!cell.text()) { return true; } } return false; } let changeTypeSelect = function (e) { let index = parseInt(e && e.target.value || 0); if (index !== null && index !== undefined && index < chartType.length) { _getElementById('typeSelect').innerHTML = ''; let typeArray = chartType[index]; for (let i = 0; i < typeArray.length; i++) { let item = typeArray[i]; let option = document.createElement('option'); let value = document.createAttribute('value'); value.nodeValue = i; option.setAttributeNode(value); option.innerHTML = item.typeDesc; _getElementById('typeSelect').appendChild(option); } } } let insertChart = function (chartInfo) { let activeSheet = spread.getActiveSheet(); let dataRanges = activeSheet.getSelections(); let dataFormulas = []; if (dataRanges) { dataRanges.forEach((dataRange) => { if (!judgeIsEmptyOneCell(activeSheet, dataRange)) { let rangeToFormula = GC.Spread.Sheets.CalcEngine.rangeToFormula; dataFormulas.push(rangeToFormula(dataRange)); } }) } let groupIndex = parseInt(chartInfo.groupType); let typeIndex = parseInt(chartInfo.chartType); if (groupIndex < chartType.length) { let typeArray = chartType[groupIndex]; if (typeIndex < typeArray.length) { let 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); } } } } let switchRowColumn = function (e) { let activeSheet = spread.getActiveSheet(); let activeChart = getActiveChart(activeSheet); if (activeChart) { let isSwitched = activeChart.switchDataOrientation(); if (!isSwitched) { alert("'Can't switch row/column"); } } } let removeChart = function () { let activeSheet = spread.getActiveSheet(); let activeChart = getActiveChart(activeSheet); if (activeChart) { activeSheet.charts.remove(activeChart.name()); } } let removeAllChart = function () { let activeSheet = spread.getActiveSheet(); activeSheet.charts.clear(); } let ignoreHiddenRowAndColumn = function (e) { let activeSheet = spread.getActiveSheet(); let activeChart = getActiveChart(activeSheet); let checked = e.target.checked; activeChart && activeChart.ignoreHidden(checked); setIgnoreHidden(checked); } let displayBlanksCells = function (e) { let activeSheet = spread.getActiveSheet(); let activeChart = getActiveChart(activeSheet); let index = parseInt(e.target.value); if (index !== null && index !== undefined) { activeChart && activeChart.displayBlanksAs(index); } } let showNAAsBlanks = function (e) { let activeSheet = spread.getActiveSheet(); let activeChart = getActiveChart(activeSheet); activeChart && activeChart.displayNaNAsBlank(e.target.checked); } let generateTestData = function(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 (let 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; } return (<div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={spread => initSpread(spread)}> <Worksheet /> </SpreadSheets> </div> <Panel ignoreHidden={ignoreHidden} insertChart={(e) => { insertChart(e) }} switchRowColumn={(e) => { switchRowColumn(e) }} removeChart={(e) => { removeChart(e) }} removeAllChart={(e) => { removeAllChart(e) }} changeTypeSelect={(e) => { changeTypeSelect(e) }} displayBlanksCells={(e) => { displayBlanksCells(e) }} showNAAsBlanks={(e) => { showNAAsBlanks(e) }} ignoreHiddenRowAndColumn={(e) => { ignoreHiddenRowAndColumn(e) }} /> </div>); } class Panel extends Component { constructor(props) { super(props); this.state = { groupType: '0', chartType: '0' } } render() { return ( <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" }} value={this.state.groupType} onChange={(e) => { this.setState({ groupType: e.target.value }) this.props.changeTypeSelect(e); }}> <option value="0">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" }} value={this.state.chartType} onChange={(e) => { this.setState({ chartType: e.target.value }) }}></select> </div> <div class="option-row"> <input type="button" style={{ width: "150px", margin: "5px", "margin-bottom": "15px" }} value="Add Chart" id="insertChart" onClick={(e) => { this.props.insertChart(this.state) }} /> <input type="button" style={{ width: "150px", margin: "5px" }} value="Switch row/column" id="switchRowColumn" onClick={(e) => { this.props.switchRowColumn(e) }} /> <input type="button" style={{ width: "150px", margin: "5px" }} value="Remove chart" id="removeChart" onClick={(e) => { this.props.removeChart(e) }} /> <input type="button" style={{ width: "150px", margin: "5px" }} value="Remove all charts" id="removeAllCharts" onClick={(e) => { this.props.removeAllChart(e) }} /> </div> <div class="option-row"> <label>Display Blanks Cells As:</label> <select id="displayBlanksCells" style={{ width: "80px" }} onChange={(e) => { this.props.displayBlanksCells(e) }}> <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" onClick={(e) => { this.props.showNAAsBlanks(e) }}></input> <label for="showNAAsBlanks">Display #N/A Cells As Blank Cells</label> </div> <div class="option-row"> <input type="checkbox" id="ignoreHidden" checked={this.props.ignoreHidden} onClick={(e) => { this.props.ignoreHiddenRowAndColumn(e) }}></input> <label for="ignoreHidden">Ingore Hidden Rows And Columns</label> </div> </div> ) } }
import * as React from 'react'; import * as ReactDOM from 'react-dom'; import GC from '@grapecity-software/spread-sheets'; import { SpreadSheets, Worksheet } from '@grapecity-software/spread-sheets-react'; import '@grapecity-software/spread-sheets-charts'; import '@grapecity-software/spread-sheets-resources-zh'; GC.Spread.Common.CultureManager.culture("zh-cn"); import './styles.css'; const Component = React.Component; const 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 _getElementById(id) { return document.getElementById(id); } export class App extends Component { constructor(props) { super(props); this.spread = null; this.state = { ignoreHidden: true }; } render() { return (<div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={spread => this.initSpread(spread)}> <Worksheet/> </SpreadSheets> </div> <Panel ignoreHidden={this.state.ignoreHidden} insertChart={(e) => { this.insertChart(e) }} switchRowColumn={(e) => { this.switchRowColumn(e) }} removeChart={(e) => { this.removeChart(e) }} removeAllChart={(e) => { this.removeAllChart(e) }} changeTypeSelect={(e) => { this.changeTypeSelect(e) }} displayBlanksCells={(e) => { this.displayBlanksCells(e) }} showNAAsBlanks={(e) => { this.showNAAsBlanks(e) }} ignoreHiddenRowAndColumn={(e) => { this.ignoreHiddenRowAndColumn(e) }} /> </div>); } initSpread(spread) { this.spread = spread; spread.suspendPaint(); spread.setSheetCount(3); let sheet1 = spread.getSheet(0); sheet1.name("Common Chart"); let sheet2 = spread.getSheet(1); sheet2.name("Custom Chart"); let sheet3 = spread.sheets[2]; sheet3.name("Table Binding Chart"); this.initSheet(sheet1); this.initSheet(sheet2); this.initBindingTable(sheet3); //add chart this.initChart(sheet1); this.initChart(sheet2); this.initBindingChart(sheet3); //custom chart this.customChartStyle(sheet2); spread.resumePaint(); this.changeTypeSelect(); } initSheet(sheet) { sheet.suspendPaint(); //prepare data for chart let 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(); } initBindingTable(sheet) { let self = this; sheet.suspendPaint(); let 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 } ] }; let tableColumns = [], names = ['orderDate', 'item', 'units', 'cost'], labels = ['Order Date', 'Item', 'Units', 'Cost']; let table = sheet.tables.add('tableRecords', 0, 0, 4, 4); table.autoGenerateColumns(false); names.forEach(function (name, index) { let tableColumn = new GC.Spread.Sheets.Tables.TableColumn(); tableColumn.name(labels[index]); tableColumn.dataField(name); tableColumns.push(tableColumn); }); table.bindColumns(tableColumns); table.bindingPath('sales'); let source = new GC.Spread.Sheets.Bindings.CellBindingSource(data); sheet.setDataSource(source); let button = sheet.shapes.addFormControl("button", GC.Spread.Sheets.Shapes.FormControlType.button, 500, 10, 150, 50); button.text("Set Table Data Source"); let 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 () { let length = Math.floor(Math.random() * 20) + 1 data.sales = self.generateTestData(length, 10, 20); let source = new GC.Spread.Sheets.Bindings.CellBindingSource(data); sheet.setDataSource(source); }); sheet.resumePaint(); } initChart(sheet) { //add common chart sheet.charts.add('Chart1', GC.Spread.Sheets.Charts.ChartType.columnClustered, 0, 100, 800, 300, "A1:H4"); } initBindingChart(sheet) { //add table binding chart 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]]"); } customChartStyle(sheet) { let changeChart = sheet.charts.all()[0]; this.changeChartStyle(changeChart); } changeChartStyle(chart) { //change orientation this.switchOrientation(chart); //change legend this.changeChartLegend(chart); //change chartArea this.changeChartArea(chart); //change chartTitle this.changeChartTitle(chart); //change dataLabels this.changeChartDataLabels(chart); //change axisTitles this.changeChartAxisTitles(chart); //change axesLine this.changeChartAxesLine(chart); //change series this.changeSeries(chart); //change gridLine this.changeGridLine(chart); //change seriesBorder this.changeSeriesBorder(chart); } switchOrientation(chart) { chart.switchDataOrientation(); } changeChartLegend(chart) { let legend = chart.legend(); legend.visible = true; let legendPosition = GC.Spread.Sheets.Charts.LegendPosition; legend.position = legendPosition.top; chart.legend(legend); } changeChartArea(chart) { let chartArea = chart.chartArea(); chartArea.backColor = "rgba(93,93,93,1)"; chartArea.color = "rgba(255,255,255,1)"; chartArea.fontSize = 14; chart.chartArea(chartArea); } changeChartTitle(chart) { let title = chart.title(); title.text = "Browser Market Share"; title.fontSize = 18; chart.title(title); } changeChartDataLabels(chart) { let dataLabels = chart.dataLabels(); dataLabels.showValue = true; dataLabels.showSeriesName = false; dataLabels.showCategoryName = false; dataLabels.format = "0.00%"; let dataLabelPosition = GC.Spread.Sheets.Charts.DataLabelPosition; dataLabels.position = dataLabelPosition.outsideEnd; chart.dataLabels(dataLabels); let 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); let 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); let 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); } changeChartAxisTitles(chart) { let axes = chart.axes(); axes.primaryCategory.title.text = 'Year'; axes.primaryCategory.title.fontSize = 14; chart.axes(axes); } changeChartAxesLine(chart) { let axes = chart.axes(); axes.primaryValue.format = "0%"; chart.axes(axes); } changeSeries(chart) { let series = chart.series(); let seriesItem = series.get(6); seriesItem.backColor = "#a3cf62"; series.set(6, seriesItem); } changeGridLine(chart) { let axes = chart.axes(); axes.primaryCategory.majorGridLine.visible = false; axes.primaryValue.majorGridLine.visible = false; chart.axes(axes); } changeSeriesBorder(chart) { let series = chart.series().get(); for (let i = 0; i < series.length; i++) { let seriesItem = series[i]; seriesItem.border.color = 'rgb(255,255,255)'; seriesItem.border.width = 1; chart.series().set(i, seriesItem); } } getActiveChart(sheet) { let activeChart = null; sheet.charts.all().forEach(function (chart) { if (chart.isSelected()) { activeChart = chart; } }); return activeChart; } judgeIsEmptyOneCell(sheet, range) { if (range && range.rowCount === 1 && range.colCount === 1) { let cell = sheet.getCell(range.row, range.col); if (!cell.text()) { return true; } } return false; } changeTypeSelect(e) { let index = parseInt(e && e.target.value || 0); if (index !== null && index !== undefined && index < chartType.length) { _getElementById('typeSelect').innerHTML = ''; let typeArray = chartType[index]; for (let i = 0; i < typeArray.length; i++) { let item = typeArray[i]; let option = document.createElement('option'); let value = document.createAttribute('value'); value.nodeValue = i; option.setAttributeNode(value); option.innerHTML = item.typeDesc; _getElementById('typeSelect').appendChild(option); } } } insertChart(chartInfo) { let activeSheet = this.spread.getActiveSheet(); let dataRanges = activeSheet.getSelections(); let dataFormulas = []; if (dataRanges) { dataRanges.forEach((dataRange) => { if (!this.judgeIsEmptyOneCell(activeSheet, dataRange)) { let rangeToFormula = GC.Spread.Sheets.CalcEngine.rangeToFormula; dataFormulas.push(rangeToFormula(dataRange)); } }) } let groupIndex = parseInt(chartInfo.groupType); let typeIndex = parseInt(chartInfo.chartType); if (groupIndex < chartType.length) { let typeArray = chartType[groupIndex]; if (typeIndex < typeArray.length) { let 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); } } } } switchRowColumn(e) { let activeSheet = this.spread.getActiveSheet(); let activeChart = this.getActiveChart(activeSheet); if (activeChart) { let isSwitched = activeChart.switchDataOrientation(); if (!isSwitched) { alert("'Can't switch row/column"); } } } removeChart() { let activeSheet = this.spread.getActiveSheet(); let activeChart = this.getActiveChart(activeSheet); if (activeChart) { activeSheet.charts.remove(activeChart.name()); } } removeAllChart() { let activeSheet = this.spread.getActiveSheet(); activeSheet.charts.clear(); } ignoreHiddenRowAndColumn(e) { let activeSheet = this.spread.getActiveSheet(); let activeChart = this.getActiveChart(activeSheet); let checked = e.target.checked; activeChart && activeChart.ignoreHidden(checked); this.setState(() => ({ ignoreHidden: checked })); } displayBlanksCells(e) { let activeSheet = this.spread.getActiveSheet(); let activeChart = this.getActiveChart(activeSheet); let index = parseInt(e.target.value); if (index !== null && index !== undefined) { activeChart && activeChart.displayBlanksAs(index); } } showNAAsBlanks(e) { let activeSheet = this.spread.getActiveSheet(); let activeChart = this.getActiveChart(activeSheet); activeChart && activeChart.displayNaNAsBlank(e.target.checked); } 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 (let 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; } } class Panel extends Component { constructor(props) { super(props); this.state = { groupType: '0', chartType: '0' } } render() { return ( <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" }} value={this.state.groupType} onChange={(e) => { this.setState({ groupType: e.target.value }) this.props.changeTypeSelect(e); }}> <option value="0">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"}} value = {this.state.chartType} onChange={(e) => { this.setState({chartType: e.target.value}) }}></select> </div> <div class="option-row"> <input type="button" style={{width: "150px", margin:"5px", "margin-bottom": "15px"}} value="Add Chart" id="insertChart" onClick={(e) => { this.props.insertChart(this.state) }} /> <input type="button" style={{width: "150px", margin:"5px"}} value="Switch row/column" id="switchRowColumn" onClick={(e) => { this.props.switchRowColumn(e) }} /> <input type="button" style={{width: "150px", margin:"5px"}} value="Remove chart" id="removeChart" onClick={(e) => { this.props.removeChart(e) }} /> <input type="button" style={{width: "150px", margin:"5px"}} value="Remove all charts" id="removeAllCharts" onClick={(e) => { this.props.removeAllChart(e) }} /> </div> <div class="option-row"> <label>Display Blanks Cells As:</label> <select id="displayBlanksCells" style={{width: "80px"}} onChange={(e) => { this.props.displayBlanksCells(e) }}> <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" onClick={(e) => { this.props.showNAAsBlanks(e) }}></input> <label for="showNAAsBlanks">Display #N/A Cells As Blank Cells</label> </div> <div class="option-row"> <input type="checkbox" id="ignoreHidden" checked = {this.props.ignoreHidden} onClick={(e) => { this.props.ignoreHiddenRowAndColumn(e) }}></input> <label for="ignoreHidden">Ingore Hidden Rows And Columns</label> </div> </div> ) } }
<!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$/zh/react/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- SystemJS --> <script src="$DEMOROOT$/zh/react/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('$DEMOROOT$/zh/lib/react/license.js').then(function() { System.import('./src/app'); }); </script> </head> <body> <div id="app"></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; } #app { height: 100%; }
(function(global) { System.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true, react: true }, meta: { '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { '@grapecity-software/spread-sheets-shapes': 'npm:@grapecity-software/spread-sheets-shapes/index.js', '@grapecity-software/spread-sheets-shapes': 'npm:@grapecity-software/spread-sheets-shapes/index.js', '@grapecity-software/spread-sheets-charts': 'npm:@grapecity-software/spread-sheets-charts/index.js', '@grapecity-software/spread-sheets': 'npm:@grapecity-software/spread-sheets/index.js', '@grapecity-software/spread-sheets-resources-zh': 'npm:@grapecity-software/spread-sheets-resources-zh/index.js', '@grapecity-software/spread-sheets-react': 'npm:@grapecity-software/spread-sheets-react/index.js', '@grapecity-software/jsob-test-dependency-package/react-components': 'npm:@grapecity-software/jsob-test-dependency-package/react-components/index.js', 'react': 'npm:react/umd/react.production.min.js', 'react-dom': 'npm:react-dom/umd/react-dom.production.min.js', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build': 'npm:systemjs-plugin-babel/systemjs-babel-browser.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'jsx' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);