The dirty concept is defined as:
In general, only changing a cell value causes it to be dirty.
If the cell is dirty, the current row is also dirty.
If one row is inserted and a value is set in a cell, the row is considered an inserted row, not a dirty row. The cell is not considered a dirty cell.
An item will not be considered dirty when loading bound data, but it will become dirty after changing the value of bound data.
Items will keep their dirty status even after undo actions.
You can get all dirty rows by calling the getDirtyRows method in the sheet. If there is existing bound data, users will get the row, item, and originalItem from dirty rows; otherwise, only the row with the value.
You can get all dirty cells by calling the getDirtyCells method. Provide operational arguments in the following order if a specified range is given.
row: The row index of the top-left cell in the range.
col: The column index of the top-left cell in the range.
rowCount: Total row count of the range.
colCount: Total column count of the range.
Each dirty cell provides this information: row, col, oldValue, newValue.
You can get all deleted rows by calling the getDeletedRows method in the sheet. If there is existing bound data, users will get the row and originalItem from dirty rows; otherwise, only the row with the value.
You can get all inserted rows by calling the getInsertRows method in the sheet. Each inserted row provides information about the row and item (data).
Dirty, inserted, and deleted status can be cleared by clearPendingChanges. They will be automatically cleared after calling setRowCount, setColumnCount, fromJSON, or setDataSource.
You can also clear the dirty/inserted/deleted status by range. Provide optional object parameters in clearChangeInfo to specify the clearing operation.
clearChangeInfo.row: [optional] The row index of clearing range.
clearChangeInfo.col: [optional] The col index of clearing range.
clearChangeInfo.rowCount: [optional] The row count of clearing range.
clearChangeInfo.colCount: [optional] The col count of clearing range.
clearChangeInfo.clearType: [optional] The type of clearing pending change, contains dirty/insert/delete, default is dirty. The value is a enum of GC.Spread.Sheets.ClearPendingChangeType.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet />
</gc-spread-sheets>
<div class="options-container">
<p>Insert/delete rows or change row/column count using the buttons below.</p>
<p>You can then get the dirty (what’s changed) rows or cells by clicking the “Get Dirty Rows”, “Get All
Dirty Cells”, etc. buttons.</p>
<div class="option-row">
<input type="button" id="btnInsertRow" value="Insert Row" title="Insert an new row at selected row"
@click="insertRow" />
<input type="button" id="btnDeleteRow" value="Delete Rows" title="Deleted seleted rows"
@click="deleteRow" />
</div>
<p>If clear pending change by range, please select range in sheet and select type with Dirty/Insert/Delete.
</p>
<div class="option-row">
<div>
<input id="clearByRange" type="checkbox" v-model="clearByRange" />
<label for="clearByRange">Clear by Range</label>
</div>
<div>
<input id="clearDirty" type="checkbox" v-model="clearDirty" />
<label for="clearDirty">Dirty</label>
<input id="clearInsert" type="checkbox" v-model="clearInsert" />
<label for="clearInsert">Insert</label>
<input id="clearDelete" type="checkbox" v-model="clearDelete" />
<label for="clearDelete">Delete</label>
</div>
<input type="button" id="btnClearPendingChanges" value="Clear Pending Changes"
title="Clear pending changes" @click="clearPendingChanges" />
</div>
<div class="option-row">
<input type="button" id="btnSetRowCount" value="Set Row Count" title="Set row count to 60"
@click="setRowCount" />
<input type="button" id="btnSetColumnCount" value="Set Column Count" title="Set column count to 16"
@click="setColumnCount" />
</div>
<div class="option-row">
<input type="button" id="btnGetDirtyRows" value="Get Dirty Rows" @click="getDirtyRows" />
<input type="button" id="btnGetDirtyCells" value="Get All Dirty Cells" @click="getAllDirtyCells" />
<input type="button" id="btnGetSelectionDirtyCells" value="Get Selection Dirty Cells"
@click="getSelectionDirty" />
</div>
<div class="option-row">
<input type="button" id="btnGetInsertRows" value="Get Insert Rows" @click="getInsertRow" />
<input type="button" id="btnGetDeleteRows" value="Get Delete Rows" @click="getDeleteRow" />
</div>
<div class="option-row" style="width:100%;height:80px;">
<textarea id="taResult" style="width:100%;padding:0;float:right;height:80px;background:none"
v-model="text"></textarea>
</div>
</div>
</div>
</template>
<script setup>
import { ref } from 'vue';
import '@mescius/spread-sheets-vue';
import GC from '@mescius/spread-sheets';
const text = ref('');
const spread = ref(null);
const clearByRange = ref(false);
const clearDirty = ref(false);
const clearInsert = ref(false);
const clearDelete = ref(false);
const initSpread = (spreadInstance) => {
spread.value = spreadInstance;
const sheet = spreadInstance.getSheet(0);
sheet.suspendPaint();
for (let r = 0; r < 30; r++) {
sheet.setText(r, 0, `A${r + 1}`);
}
for (let c = 1; c < 20; c++) {
sheet.setText(0, c, String.fromCharCode(65 + c) + '1');
}
sheet.resumePaint();
sheet.clearPendingChanges();
const spreadNS = GC.Spread.Sheets;
spreadInstance.bind(spreadNS.Events.CellChanged, (event, data) => {
const row = data.row;
const col = data.col;
if (row === undefined || col === undefined) return;
if (sheet.hasPendingChanges(row, col)) {
const dirtyDataArray = sheet.getDirtyCells(row, col);
if (dirtyDataArray.length > 0) {
appendResult(getChangedCellData(dirtyDataArray[0]));
}
}
});
spreadInstance.bind(spreadNS.Events.RowChanged, (event, data) => {
const row = data.row;
const count = data.count;
const propName = data.propertyName;
if (row === undefined || count === undefined || propName === undefined) return;
if (propName === 'addRows' || propName === 'deleteRows') {
appendResult(`${propName} @ ${row}${count > 1 ? ` count: ${count}` : ''}`);
}
});
};
const insertRow = () => {
const sheet = spread.value.getActiveSheet();
const sels = sheet.getSelections();
if (sels.length > 0) {
const s = sels[0];
sheet.addRows(s.row, 1);
}
};
const deleteRow = () => {
const sheet = spread.value.getActiveSheet();
const sels = sheet.getSelections();
if (sels.length > 0) {
const s = sels[0];
sheet.deleteRows(s.row, s.rowCount);
}
};
const clearPendingChanges = () => {
const sheet = spread.value.getActiveSheet();
if (clearByRange.value) {
const clearType = (clearDirty.value ? 1 : 0) | (clearInsert.value ? 2 : 0) | (clearDelete.value ? 4 : 0);
const selections = sheet.getSelections();
selections.forEach((selection) => {
sheet.clearPendingChanges({
clearType,
row: selection.row,
rowCount: selection.rowCount,
col: selection.col,
colCount: selection.colCount,
});
});
} else {
sheet.clearPendingChanges();
}
text.value = '';
};
const setRowCount = () => {
const sheet = spread.value.getActiveSheet();
sheet.setRowCount(60);
};
const setColumnCount = () => {
const sheet = spread.value.getActiveSheet();
sheet.setColumnCount(16);
};
const getDirtyRows = () => {
const sheet = spread.value.getActiveSheet();
const rows = sheet.getDirtyRows();
if (rows.length > 0) {
appendResult(`Dirty rows @ ${rows.map((item) => item.row).join(', ')}`);
}
};
const getAllDirtyCells = () => {
const sheet = spread.value.getActiveSheet();
const cells = sheet.getDirtyCells();
if (cells.length > 0) {
appendResult(`Dirty Cells:\n${cells.map((item) => getDirtyCellData(item)).join('\n')}`);
}
};
const getSelectionDirty = () => {
const sheet = spread.value.getActiveSheet();
const sels = sheet.getSelections();
if (sels.length > 0) {
const s = sels[0];
const row = s.row >= 0 ? s.row : 0;
const col = s.col >= 0 ? s.col : 0;
const cells = sheet.getDirtyCells(row, col, s.rowCount, s.colCount);
if (cells.length > 0) {
appendResult(`Dirty Cells:\n${cells.map((item) => getDirtyCellData(item)).join('\n')}`);
}
}
};
const getInsertRow = () => {
const sheet = spread.value.getActiveSheet();
const rows = sheet.getInsertRows();
if (rows.length > 0) {
appendResult(`Inserted rows @ ${rows.map((item) => item.row).join(', ')}`);
}
};
const getDeleteRow = () => {
const sheet = spread.value.getActiveSheet();
const rows = sheet.getDeletedRows();
if (rows.length > 0) {
appendResult(`Deleted rows @ ${rows.map((item) => item.row).join(', ')}`);
}
};
const appendResult = (txt) => {
text.value += `${txt}\n`;
};
const getChangedCellData = (dirtyItem) => [
'Cell (', dirtyItem.row, ',', dirtyItem.col, ') changed from ', dirtyItem.oldValue, ' to ', dirtyItem.newValue,
].join('');
const getDirtyCellData = (dirtyItem) => [
'Cell (', dirtyItem.row, ',', dirtyItem.col, ') oldValue: ', dirtyItem.oldValue, ' newValue: ', dirtyItem.newValue,
].join('');
</script>
<style scoped>
#app {
height: 100%;
}
.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;
}
p {
padding: 2px 10px;
background-color: #F4F8EB;
}
input[type="button"] {
width: 100%;
margin-bottom: 2px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-print': 'npm:@mescius/spread-sheets-print/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
