FILTERXML 함수는 XML 텍스트에 XPath 식을 적용하여 일치하는 값을 반환합니다.
구문
인수
설명
xml
(필수) 유효한 XML 텍스트가 포함된 문자열입니다.
xpath
(필수) XML 텍스트에서 요소나 특성을 선택하는 XPath 식입니다.
사용 참고 사항
FILTERXML은 단일 값을 반환하거나 일치하는 여러 결과를 세로로 분산할 수 있습니다.
XPath는 //book/title과 같은 요소 텍스트 또는 //book/@category와 같은 특성 값을 대상으로 지정할 수 있습니다.
FILTERXML은 사용자 지정 구문 분석 코드 없이 워크시트 수식에서 XML 페이로드를 사용해야 할 때 유용합니다.
예제
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss"));
spread.options.allowDynamicArray = true;
initSpread(spread);
};
function initSpread(spread) {
spread.setSheetCount(1);
spread.suspendPaint();
spread.suspendCalcService();
initSheet1(spread.getSheet(0));
spread.resumeCalcService();
spread.resumePaint();
}
function initSheet1(sheet) {
var xmlData = [
'<catalog>',
'<book category="Fiction"><title>The Lost Realm</title><author>Ava Stone</author><price>18.50</price></book>',
'<book category="Science"><title>Data Patterns</title><author>Leo Kim</author><price>24.00</price></book>',
'<book category="History"><title>City of Kings</title><author>Mia Chen</author><price>21.75</price></book>',
'</catalog>'
].join('');
sheet.suspendPaint();
sheet.name('FILTERXML');
sheet.setColumnWidth(0, 108);
sheet.setColumnWidth(1, 520);
sheet.setColumnWidth(2, 160);
sheet.setColumnWidth(3, 100);
sheet.setColumnWidth(4, 120);
sheet.setRowHeight(1, 90);
sheet.setValue(0, 1, 'XPath로 XML 값 추출');
sheet.setValue(1, 0, 'XML 소스');
sheet.setValue(1, 1, xmlData);
sheet.setValue(3, 1, '제목');
sheet.setValue(3, 2, '저자');
sheet.setValue(3, 3, '가격');
sheet.setValue(3, 4, '범주');
sheet.setValue(9, 0, 'XPath 쿼리');
sheet.setValue(9, 1, '//book[price>20]/title');
sheet.setValue(10, 0, '실행');
sheet.setValue(10, 1, '//book[price>20 and @category!="Science"]/title');
sheet.setValue(11, 1, '쿼리 결과');
var titleStyle = new GC.Spread.Sheets.Style();
titleStyle.font = 'bold 14pt Calibri';
sheet.setStyle(0, 1, titleStyle);
var labelStyle = new GC.Spread.Sheets.Style();
labelStyle.font = 'bold 11pt Calibri';
sheet.setStyle(1, 0, labelStyle);
sheet.setStyle(9, 0, labelStyle);
sheet.setStyle(10, 0, labelStyle);
var xmlInputStyle = new GC.Spread.Sheets.Style();
xmlInputStyle.backColor = '#FEF3CD';
sheet.setStyle(1, 1, xmlInputStyle);
sheet.getCell(1, 1).wordWrap(true);
var queryInputStyle = new GC.Spread.Sheets.Style();
queryInputStyle.backColor = '#FEF3CD';
sheet.setStyle(9, 1, queryInputStyle);
var headerStyle = new GC.Spread.Sheets.Style();
headerStyle.backColor = 'rgb(222,235,246)';
headerStyle.font = 'bold 11pt Calibri';
headerStyle.hAlign = 1;
var borderBottom = new GC.Spread.Sheets.LineBorder();
borderBottom.color = 'black';
borderBottom.style = GC.Spread.Sheets.LineStyle.thin;
headerStyle.borderBottom = borderBottom;
sheet.setStyle(3, 1, headerStyle);
sheet.setStyle(3, 2, headerStyle);
sheet.setStyle(3, 3, headerStyle);
sheet.setStyle(3, 4, headerStyle);
sheet.setStyle(11, 1, headerStyle);
sheet.setFormula(4, 1, '=FILTERXML($B$2,"//book/title")');
sheet.setFormula(4, 2, '=FILTERXML($B$2,"//book/author")');
sheet.setFormula(4, 3, '=FILTERXML($B$2,"//book/price")');
sheet.setFormula(4, 4, '=FILTERXML($B$2,"//book/@category")');
sheet.setFormula(12, 1, '=FILTERXML($B$2,$B$10)');
sheet.resumePaint();
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="ko-kr"/>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.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>
</body>
</html>
input[type="text"] {
width: 200px;
margin-right: 20px;
}
label {
display: inline-block;
width: 110px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
label {
display: block;
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
code {
border: 1px solid #000;
}
