여러 시트 영역에서 수식을 설정하기/가져오기 위해 아래 API에 sheetArea 인수가 추가되었으며, 기본 sheetArea는 GC.Spread.Sheets.SheetArea.viewport입니다.
수식 가져오기
여러 시트 영역에서 수식을 가져옵니다.
인수
유형
설명
row
숫자
(필수) 행 인덱스
col
숫자
(필수) 열 인덱스
sheetArea
GC.Spread.Sheets.SheetArea
시트 영역 기본값은 GC.Spread.Sheets.SheetArea.viewport입니다.
수식 설정
여러 시트 영역에 대한 수식을 설정하고 "A1:A2"와 "Sheet1!A1:A2" 간에 다른 수식 참조 문자열에 주의합니다.
수식 참조 문자열에 시트 이름이 없는 경우 계산 시 지정한 시트 영역 참조로 처리됩니다. 즉, 시트 뷰포트 참조를 헤더 영역으로 참조하려는 경우 시트 이름을 호출해야 합니다.
인수
유형
설명
row
숫자
(필수) 행 인덱스
col
숫자
(필수) 열 인덱스
formula
문자열
(필수) 지정된 셀에 배치하는 수식
sheetArea
GC.Spread.Sheets.SheetArea
시트 영역 기본값은 GC.Spread.Sheets.SheetArea.viewport입니다.
헤더 영역의 데이터 또는 계산 결과는 해당 헤더 영역만이 참조할 수 있고, 다른 시트 영역에서는 참조할 수 없습니다.
다음과 같은 참조 방법은 지원하지 않습니다.
헤더 영역이 다른 헤더 영역과 교차하는 방식. 이 경우, 행 헤더 영역이 열 헤더 또는 다른 시트의 헤더 영역을 참조하도록 할 수 없습니다.
헤더 영역이 뷰포트 영역을 참조하도록 하는 방식. 헤더 참조를 명확하게 나타냅니다.
SparklineEx
SparklineEx는 특수 수식 그룹으로, 기능/스파크라인 데모에서 자세한 내용을 확인할 수 있습니다.
sparklineEx를 헤더 영역으로 설정하는 것은 수식을 헤더 영역으로 설정하는 것과 동일합니다.
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 GC from '@mescius/spread-sheets';
import '@mescius/spread-sheets-resources-ko';
GC.Spread.Common.CultureManager.culture("ko-kr");
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
export function AppFunc() {
const initSpread = (spread) => {
spread.suspendPaint();
var sheet1 = spread.sheets[0];
var data = [
{ "name": "Student 1", "sex": "F", "school": "School A", "chinese": 70, "math": 90, "english": 51, "physics": 107 },
{ "name": "Student 2", "sex": "M", "school": "School D", "chinese": 99, "math": 59, "english": 63, "physics": 100 },
{ "name": "Student 3", "sex": "F", "school": "School A", "chinese": 89, "math": 128, "english": 74, "physics": 156 },
{ "name": "Student 4", "sex": "F", "school": "School D", "chinese": 93, "math": 61, "english": 53, "physics": 132 },
{ "name": "Student 5", "sex": "F", "school": "School A", "chinese": 106, "math": 82, "english": 80, "physics": 152 },
{ "name": "Student 6", "sex": "M", "school": "School A", "chinese": 108, "math": 124, "english": 90, "physics": 174 },
{ "name": "Student 7", "sex": "F", "school": "School C", "chinese": 112, "math": 100, "english": 75, "physics": 156 },
{ "name": "Student 8", "sex": "F", "school": "School C", "chinese": 78, "math": 111, "english": 84, "physics": 161 },
{ "name": "Student 9", "sex": "M", "school": "School C", "chinese": 116, "math": 116, "english": 99, "physics": 165 },
{ "name": "Student 10", "sex": "M", "school": "School B", "chinese": 119, "math": 114, "english": 92, "physics": 130 },
{ "name": "Student 11", "sex": "M", "school": "School C", "chinese": 121, "math": 99, "english": 93, "physics": 161 },
{ "name": "Student 12", "sex": "F", "school": "School B", "chinese": 112, "math": 93, "english": 95, "physics": 74 },
{ "name": "Student 13", "sex": "F", "school": "School D", "chinese": 55, "math": 66, "english": 105, "physics": 97 },
{ "name": "Student 14", "sex": "F", "school": "School B", "chinese": 104, "math": 51, "english": 118, "physics": 56 },
{ "name": "Student 15", "sex": "F", "school": "School D", "chinese": 77, "math": 81, "english": 99, "physics": 51 },
{ "name": "Student 16", "sex": "M", "school": "School B", "chinese": 70, "math": 91, "english": 120, "physics": 120 },
{ "name": "Student 17", "sex": "F", "school": "School B", "chinese": 113, "math": 56, "english": 153, "physics": 106 },
{ "name": "Student 18", "sex": "M", "school": "School A", "chinese": 77, "math": 58, "english": 141, "physics": 88 },
{ "name": "Student 19", "sex": "F", "school": "School C", "chinese": 84, "math": 78, "english": 160, "physics": 122 },
{ "name": "Student 20", "sex": "F", "school": "School D", "chinese": 109, "math": 103, "english": 106, "physics": 49 },
{ "name": "Student 21", "sex": "F", "school": "School A", "chinese": 55, "math": 118, "english": 111, "physics": 64 },
{ "name": "Student 22", "sex": "M", "school": "School B", "chinese": 75, "math": 86, "english": 110, "physics": 92 },
{ "name": "Student 23", "sex": "F", "school": "School B", "chinese": 66, "math": 67, "english": 99, "physics": 114 },
{ "name": "Student 24", "sex": "F", "school": "School D", "chinese": 123, "math": 88, "english": 124, "physics": 124 },
{ "name": "Student 25", "sex": "M", "school": "School A", "chinese": 90, "math": 84, "english": 154, "physics": 68 },
{ "name": "Student 26", "sex": "M", "school": "School B", "chinese": 124, "math": 95, "english": 101, "physics": 68 },
{ "name": "Student 27", "sex": "F", "school": "School C", "chinese": 91, "math": 79, "english": 116, "physics": 56 },
{ "name": "Student 28", "sex": "F", "school": "School D", "chinese": 119, "math": 120, "english": 134, "physics": 89 },
{ "name": "Student 29", "sex": "M", "school": "School A", "chinese": 116, "math": 123, "english": 174, "physics": 65 },
{ "name": "Student 30", "sex": "F", "school": "School B", "chinese": 61, "math": 73, "english": 171, "physics": 90 }
];
sheet1.autoGenerateColumns = true;
sheet1.setDataSource(data);
sheet1.getRange(-1, 0, -1, 7).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
sheet1.setValue(0, 0, "Student Grade Statistics", GC.Spread.Sheets.SheetArea.colHeader);
sheet1.addSpan(0, 0, 1, 7, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setRowCount(4, 1);
sheet1.setColumnWidth(0, 80);
sheet1.setColumnWidth(2, 120);
sheet1.setColumnWidth(3, 200);
sheet1.setColumnWidth(4, 200);
sheet1.setColumnWidth(5, 200);
sheet1.setColumnWidth(6, 200);
sheet1.setValue(1, 0, 'Name', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 1, 'Sex', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 2, 'School', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 3, 'Chinese', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 4, 'Math', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 5, 'English', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 6, 'Physics', GC.Spread.Sheets.SheetArea.colHeader);
// Chart by using sparklineEx formulas in header
sheet1.addSpan(2, 0, 1, 2, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setRowHeight(2, 100, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 0, 'PIESPARKLINE(COUNTIF(Sheet1!B:B,"F")/ROWS(Sheet1!B1:B30),"#33689e","#e91e63")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 2, 'COLUMNSPARKLINE(Sheet2!B1:B4,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 3, 'COLUMNSPARKLINE(Sheet1!D1:D30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 4, 'COLUMNSPARKLINE(Sheet1!E1:E30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 5, 'COLUMNSPARKLINE(Sheet1!F1:F30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 6, 'COLUMNSPARKLINE(Sheet1!G1:G30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
// Data aggregation by using built-in function formulas in header
sheet1.addSpan(3, 0, 1, 2, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 0, 'CEILING.MATH(SUMPRODUCT(1/COUNTIF(Sheet1!A1:A30,Sheet1!A1:A30)))&" Students"', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 2, '"From "&CEILING.MATH(SUMPRODUCT(1/COUNTIF(Sheet1!C1:C30,Sheet1!C1:C30)))&" Schools"', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 3, '"Min: "&MIN(Sheet1!D:D)&" Avg: "&ROUND(AVERAGE(Sheet1!D:D),0)&" Max: "&MAX(Sheet1!D:D)', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 4, '"Min: "&MIN(Sheet1!E:E)&" Avg: "&ROUND(AVERAGE(Sheet1!E:E),0)&" Max: "&MAX(Sheet1!E:E)', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 5, '"Min: "&MIN(Sheet1!F:F)&" Avg: "&ROUND(AVERAGE(Sheet1!F:F),0)&" Max: "&MAX(Sheet1!F:F)', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 6, '"Min: "&MIN(Sheet1!G:G)&" Avg: "&ROUND(AVERAGE(Sheet1!G:G),0)&" Max: "&MAX(Sheet1!G:G)', GC.Spread.Sheets.SheetArea.colHeader);
var sheet2 = spread.sheets[1];
sheet2.setValue(0, 0, 'School A');
sheet2.setValue(1, 0, 'School B');
sheet2.setValue(2, 0, 'School C');
sheet2.setValue(3, 0, 'School D');
sheet2.setFormula(0, 1, 'COUNTIF(Sheet1!C1:C30,A1)');
sheet2.setFormula(1, 1, 'COUNTIF(Sheet1!C1:C30,A2)');
sheet2.setFormula(2, 1, 'COUNTIF(Sheet1!C1:C30,A3)');
sheet2.setFormula(3, 1, 'COUNTIF(Sheet1!C1:C30,A4)');
spread.resumePaint();
}
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>;
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import '@mescius/spread-sheets-resources-ko';
GC.Spread.Common.CultureManager.culture("ko-kr");
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread=>this.initSpread(spread)}>
<Worksheet>
</Worksheet>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>;
}
initSpread(spread) {
spread.suspendPaint();
var sheet1 = spread.sheets[0];
var data = [
{"name":"Student 1","sex":"F","school":"School A","chinese":70,"math":90,"english":51,"physics":107},
{"name":"Student 2","sex":"M","school":"School D","chinese":99,"math":59,"english":63,"physics":100},
{"name":"Student 3","sex":"F","school":"School A","chinese":89,"math":128,"english":74,"physics":156},
{"name":"Student 4","sex":"F","school":"School D","chinese":93,"math":61,"english":53,"physics":132},
{"name":"Student 5","sex":"F","school":"School A","chinese":106,"math":82,"english":80,"physics":152},
{"name":"Student 6","sex":"M","school":"School A","chinese":108,"math":124,"english":90,"physics":174},
{"name":"Student 7","sex":"F","school":"School C","chinese":112,"math":100,"english":75,"physics":156},
{"name":"Student 8","sex":"F","school":"School C","chinese":78,"math":111,"english":84,"physics":161},
{"name":"Student 9","sex":"M","school":"School C","chinese":116,"math":116,"english":99,"physics":165},
{"name":"Student 10","sex":"M","school":"School B","chinese":119,"math":114,"english":92,"physics":130},
{"name":"Student 11","sex":"M","school":"School C","chinese":121,"math":99,"english":93,"physics":161},
{"name":"Student 12","sex":"F","school":"School B","chinese":112,"math":93,"english":95,"physics":74},
{"name":"Student 13","sex":"F","school":"School D","chinese":55,"math":66,"english":105,"physics":97},
{"name":"Student 14","sex":"F","school":"School B","chinese":104,"math":51,"english":118,"physics":56},
{"name":"Student 15","sex":"F","school":"School D","chinese":77,"math":81,"english":99,"physics":51},
{"name":"Student 16","sex":"M","school":"School B","chinese":70,"math":91,"english":120,"physics":120},
{"name":"Student 17","sex":"F","school":"School B","chinese":113,"math":56,"english":153,"physics":106},
{"name":"Student 18","sex":"M","school":"School A","chinese":77,"math":58,"english":141,"physics":88},
{"name":"Student 19","sex":"F","school":"School C","chinese":84,"math":78,"english":160,"physics":122},
{"name":"Student 20","sex":"F","school":"School D","chinese":109,"math":103,"english":106,"physics":49},
{"name":"Student 21","sex":"F","school":"School A","chinese":55,"math":118,"english":111,"physics":64},
{"name":"Student 22","sex":"M","school":"School B","chinese":75,"math":86,"english":110,"physics":92},
{"name":"Student 23","sex":"F","school":"School B","chinese":66,"math":67,"english":99,"physics":114},
{"name":"Student 24","sex":"F","school":"School D","chinese":123,"math":88,"english":124,"physics":124},
{"name":"Student 25","sex":"M","school":"School A","chinese":90,"math":84,"english":154,"physics":68},
{"name":"Student 26","sex":"M","school":"School B","chinese":124,"math":95,"english":101,"physics":68},
{"name":"Student 27","sex":"F","school":"School C","chinese":91,"math":79,"english":116,"physics":56},
{"name":"Student 28","sex":"F","school":"School D","chinese":119,"math":120,"english":134,"physics":89},
{"name":"Student 29","sex":"M","school":"School A","chinese":116,"math":123,"english":174,"physics":65},
{"name":"Student 30","sex":"F","school":"School B","chinese":61,"math":73,"english":171,"physics":90}
];
sheet1.autoGenerateColumns = true;
sheet1.setDataSource(data);
sheet1.getRange(-1, 0, -1, 7).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
sheet1.setValue(0, 0, "Student Grade Statistics", GC.Spread.Sheets.SheetArea.colHeader);
sheet1.addSpan(0, 0, 1, 7, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setRowCount(4, 1);
sheet1.setColumnWidth(0, 80);
sheet1.setColumnWidth(2, 120);
sheet1.setColumnWidth(3, 200);
sheet1.setColumnWidth(4, 200);
sheet1.setColumnWidth(5, 200);
sheet1.setColumnWidth(6, 200);
sheet1.setValue(1, 0, 'Name', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 1, 'Sex', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 2, 'School', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 3, 'Chinese', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 4, 'Math', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 5, 'English', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 6, 'Physics', GC.Spread.Sheets.SheetArea.colHeader);
// Chart by using sparklineEx formulas in header
sheet1.addSpan(2, 0, 1, 2, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setRowHeight(2, 100, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 0, 'PIESPARKLINE(COUNTIF(Sheet1!B:B,"F")/ROWS(Sheet1!B1:B30),"#33689e","#e91e63")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 2, 'COLUMNSPARKLINE(Sheet2!B1:B4,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 3, 'COLUMNSPARKLINE(Sheet1!D1:D30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 4, 'COLUMNSPARKLINE(Sheet1!E1:E30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 5, 'COLUMNSPARKLINE(Sheet1!F1:F30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 6, 'COLUMNSPARKLINE(Sheet1!G1:G30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
// Data aggregation by using built-in function formulas in header
sheet1.addSpan(3, 0, 1, 2, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 0, 'CEILING.MATH(SUMPRODUCT(1/COUNTIF(Sheet1!A1:A30,Sheet1!A1:A30)))&" Students"', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 2, '"From "&CEILING.MATH(SUMPRODUCT(1/COUNTIF(Sheet1!C1:C30,Sheet1!C1:C30)))&" Schools"', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 3, '"Min: "&MIN(Sheet1!D:D)&" Avg: "&ROUND(AVERAGE(Sheet1!D:D),0)&" Max: "&MAX(Sheet1!D:D)', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 4, '"Min: "&MIN(Sheet1!E:E)&" Avg: "&ROUND(AVERAGE(Sheet1!E:E),0)&" Max: "&MAX(Sheet1!E:E)', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 5, '"Min: "&MIN(Sheet1!F:F)&" Avg: "&ROUND(AVERAGE(Sheet1!F:F),0)&" Max: "&MAX(Sheet1!F:F)', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 6, '"Min: "&MIN(Sheet1!G:G)&" Avg: "&ROUND(AVERAGE(Sheet1!G:G),0)&" Max: "&MAX(Sheet1!G:G)', GC.Spread.Sheets.SheetArea.colHeader);
var sheet2 = spread.sheets[1];
sheet2.setValue(0, 0, 'School A');
sheet2.setValue(1, 0, 'School B');
sheet2.setValue(2, 0, 'School C');
sheet2.setValue(3, 0, 'School D');
sheet2.setFormula(0, 1, 'COUNTIF(Sheet1!C1:C30,A1)');
sheet2.setFormula(1, 1, 'COUNTIF(Sheet1!C1:C30,A2)');
sheet2.setFormula(2, 1, 'COUNTIF(Sheet1!C1:C30,A3)');
sheet2.setFormula(3, 1, 'COUNTIF(Sheet1!C1:C30,A4)');
spread.resumePaint();
}
}
<!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$/ko/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/ko/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('./src/app');
System.import('$DEMOROOT$/ko/lib/react/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
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: 5px;
margin-top: 10px;
}
#switchAutoMergeMode {
margin: 10px 0px;
}
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: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-ko': 'npm:@mescius/spread-sheets-resources-ko/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/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);
