You can get or set a value for a specified cell.
To get a series of values or set values to a range of cells, you can use code similar to the following:
You can also get or set text to a specified cell. This is similar to getting or setting a value to a cell.
If you want to delete data, you can use the following method to clear the data from the specified range.
SpreadJS also supports copying or moving a series of data from one range to another range.
SpreadJS also supports usedRange. You can use the API (getUsedRange) and set different usedRangeType to get the corresponding range.
The UsedRangeType enumeration contains the following types:
all: Sheet all used range type.
axis: Sheet header used range.
style: Sheet cell style used range.
rowStyle: Sheet row style used range.
colStyle: Sheet column style used range.
data: Sheet data used range.
formula: Sheet formula used range.
span: Sheet span used range.
shape: Sheet shape used range.
table: Sheet table used range.
sparkLine: Sheet sparkLine used range.
comment: Sheet comment used range.
slicer: Sheet slicer used range.
pivotTable: Sheet PivotTable used range.
filter: Sheet filter used range.
dataValidation: Sheet dataValidation used range.
conditionFormat: Sheet conditionFormat used range.
chart: Sheet chart used range.
picture: Sheet picture used range.
You can use the sheet.options.showZeros option to control whether display the zero values in the cell. When false, cells with zero value appear blank instead of showing the number zero.
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'));
var spreadNS = GC.Spread.Sheets;
var sheet = spread.getSheet(0);
let styleType, dataType;
sheet.suspendPaint();
sheet.setValue(1, 1, 'setValue');
sheet.setValue(1, 2, 2014);
sheet.setText(3, 1, 'setText');
sheet.setText(3, 2, '01/01/2014');
sheet.setText(5, 1, '0 Value');
sheet.setValue(5, 2, 0);
sheet.setColumnWidth(2, 90);
sheet.setColumnWidth(8, 120);
sheet.setColumnWidth(9, 120);
sheet.setValue(0, 7, 'setArray');
sheet.setArray(1, 7, [
[ 'ID', 'Phone Number', 'Address' ],
[ 1, '021-432378', 'Marbury Road' ],
[ 2, '021-432668', 'Chester Road' ],
[ 3, '021-432238', 'Gertt Road' ],
[ 4, '021-432533', 'Jnyliner Road' ],
[ 5, '021-432125', 'Approach Road' ],
[ 6, '021-432789', 'Jones Road' ]
]);
initStyle(sheet);
document.getElementById('btnCopy').addEventListener('click', function() {
sheet.copyTo(1, 7, 10, 7, 7, 3, spreadNS.CopyToOptions.all);
});
document.getElementById('btnMove').addEventListener('click', function() {
sheet.moveTo(0, 7, 9, 7, 8, 3, spreadNS.CopyToOptions.all);
});
document.getElementById('style-used-range').addEventListener('change', function() {
styleType = this.checked;
setUsedRange(styleType, dataType, sheet);
});
document.getElementById('data-used-range').addEventListener('change', function() {
dataType = this.checked;
setUsedRange(styleType, dataType, sheet);
});
document.getElementById('showZeros').addEventListener('change', function() {
spread.getActiveSheet().options.showZeros = this.checked;
});
sheet.resumePaint();
};
function initStyle (sheet) {
let style = new GC.Spread.Sheets.Style();
style.backColor = '#c6c0ca';
for (let row = 1; row < 6; row++) {
for (let col = 1; col < 3; col++) {
sheet.setStyle(row, col, style);
}
}
style = new GC.Spread.Sheets.Style();
style.backColor = '#bbd2cc';
for (let row = 1; row < 8; row++) {
for (let col = 7; col < 10; col++) {
sheet.setStyle(row, col, style);
}
}
style = new GC.Spread.Sheets.Style();
style.backColor = '#fcd8dd';
for (let row = 10; row < 17; row++) {
for (let col = 7; col < 10; col++) {
sheet.setStyle(row, col, style);
}
}
}
function setUsedRange (styleType, dataType, sheet) {
let type = 0;
if (styleType) {
type = type | GC.Spread.Sheets.UsedRangeType.style;
}
if (dataType) {
type = type | GC.Spread.Sheets.UsedRangeType.data;
}
if (type) {
let usedRange = sheet.getUsedRange(type);
if (usedRange) {
sheet.setSelection(usedRange.row, usedRange.col, usedRange.rowCount, usedRange.colCount);
return;
}
}
sheet.setSelection(0, 0, 1, 1);
}
<!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$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.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>Users can set the data for the worksheet or get the data from the worksheet.</p>
<p>Clicking the "Copy" button will copy the data from H2:J8 to H11:J17.</p>
<p>Clicking the "Move" button will move the data from H1:J8 to H10:J17.</p>
<input type="button" value="Copy" id="btnCopy" />
<input type="button" value="Move" id="btnMove" />
<p>Users can quickly obtain the maximum range of different data types in the current sheet</p>
<p>Check style to get the maximum range of cells with style</p>
<p>Check data to get the maximum range of cells with data</p>
<input type="checkbox" class="usedRange" id="style-used-range">
<label for="style-used-range" class="usedRange">style</label>
<input type="checkbox" class="usedRange" id="data-used-range">
<label for="data-used-range" class="usedRange">data</label>
<hr/>
<input type="checkbox" class="usedRange" id="showZeros" checked/>
<label for="showZeros" class="usedRange">Show Zero Value Cells</label>
</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: 5px;
margin-top: 10px;
}
label {
display: block;
margin-bottom: 6px;
margin-top: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: inline-block;
width: 85px;
}
.usedRange {
display: inline-block;
}
#btnCopy {
margin-right: 40px;
}
#data-used-range {
margin-left: 75px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
