Dirty Items

The dirty items can help you get changed data. Dirty items may be inserted rows, deleted rows, updated rows (row with updated cell), or updated cells. This can be particularly useful when you need to capture any user changes without having to get the entire sheet.

<p>The dirty concept is defined as:</p> <ul> <li><strong>In general, only changing a cell value causes it to be dirty.</strong></li> <li><strong>If the cell is dirty, the current row is also dirty.</strong></li> <li><strong>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.</strong></li> <li><strong>An item will not be considered dirty when loading bound data, but it will become dirty after changing the value of bound data.</strong></li> <li><strong>Items will keep their dirty status even after undo actions.</strong></li> </ul> <p>You can get all dirty rows by calling the <strong>getDirtyRows</strong> method in the sheet. If there is existing bound data, users will get the <strong>row</strong>, <strong>item</strong>, and <strong>originalItem</strong> from dirty rows; otherwise, only the <strong>row</strong> with the value.</p> <p>You can get all dirty cells by calling the <strong>getDirtyCells</strong> method. Provide operational arguments in the following order if a specified range is given.</p> <ul> <li><strong>row: The row index of the top-left cell in the range.</strong></li> <li><strong>col: The column index of the top-left cell in the range.</strong></li> <li><strong>rowCount: Total row count of the range.</strong></li> <li><strong>colCount: Total column count of the range.</strong></li> </ul> <p>Each dirty cell provides this information: <strong>row</strong>, <strong>col</strong>, <strong>oldValue</strong>, <strong>newValue</strong>.</p> <p>You can get all deleted rows by calling the <strong>getDeletedRows</strong> method in the sheet. If there is existing bound data, users will get the <strong>row</strong> and <strong>originalItem</strong> from dirty rows; otherwise, only the <strong>row</strong> with the value.</p> <p>You can get all inserted rows by calling the <strong>getInsertRows</strong> method in the sheet. Each inserted row provides information about the <strong>row</strong> and <strong>item</strong> (data).</p> <p>Dirty, inserted, and deleted status can be cleared by <strong>clearPendingChanges</strong>. They will be automatically cleared after calling <strong>setRowCount</strong>, <strong>setColumnCount</strong>, <strong>fromJSON</strong>, or <strong>setDataSource</strong>.</p>
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 }); initSpread(spread); }; function initSpread(spread) { var sheet = spread.getSheet(0); sheet.suspendPaint(); for (var r = 0; r < 30; r++) { sheet.setText(r, 0, "A" + (r + 1)); } for (var c = 1; c < 20; c++) { sheet.setText(0, c, String.fromCharCode(65 + c) + "1"); } sheet.resumePaint(); sheet.clearPendingChanges(); var _getElementByIdtaResult = _getElementById("taResult"); function getChangedCellData(dirtyItem) { return ["Cell (", dirtyItem.row, ",", dirtyItem.col, ") changed from ", dirtyItem.oldValue, " to ", dirtyItem.newValue].join(""); } function getDirtyCellData(dirtyItem) { return ["Cell (", dirtyItem.row, ",", dirtyItem.col, ") oldValue: ", dirtyItem.oldValue, " newValue: ", dirtyItem.newValue].join(""); } function appendResult(txt) { _getElementByIdtaResult.value=_getElementByIdtaResult.value + txt + "\n"; } spread.bind(spreadNS.Events.CellChanged, function (event, data) { var row = data.row, col = data.col; if (row === undefined || col === undefined) { return; } if (sheet.hasPendingChanges(row, col)) { var dirtyDataArray = sheet.getDirtyCells(row, col); if (dirtyDataArray.length > 0) { appendResult(getChangedCellData(dirtyDataArray[0])); } } }); spread.bind(spreadNS.Events.RowChanged, function (event, data) { var row = data.row, count = data.count, propName = data.propertyName; if (row === undefined || count === undefined || propName === undefined) { return; } if (propName === "addRows" || propName === "deleteRows") { appendResult(propName + " @ " + row + (count > 1 ? " count: " + count : "")); } }); _getElementById("btnInsertRow").addEventListener('click',function () { var sels = sheet.getSelections(); var len = sels.length; if (len > 0) { var s = sels[0]; sheet.addRows(s.row, 1); } }); _getElementById("btnDeleteRow").addEventListener('click',function () { var sels = sheet.getSelections(); var len = sels.length; if (len > 0) { var s = sels[0]; sheet.deleteRows(s.row, s.rowCount); } }); _getElementById("btnGetSelectionDirtyCells").addEventListener('click',function () { var sels = sheet.getSelections(); var len = sels.length; if (len > 0) { var s = sels[0]; var row = s.row, col = s.col; if (row < 0) { row = 0; } if (col < 0) { col = 0; } var cells = sheet.getDirtyCells(row, col, s.rowCount, s.colCount); if (cells.length > 0) { appendResult("Dirty Cells:\n" + cells.map(function (item) { return getDirtyCellData(item); }).join("\n")); } } }); _getElementById("btnGetDirtyCells").addEventListener('click',function () { var cells = sheet.getDirtyCells(); if (cells.length > 0) { appendResult("Dirty Cells:\n" + cells.map(function (item) { return getDirtyCellData(item); }).join("\n")); } }); _getElementById("btnGetDirtyRows").addEventListener('click',function () { var rows = sheet.getDirtyRows(); if (rows.length > 0) { appendResult("Dirty rows @ " + rows.map(function (item) { return item.row; }).join(", ")); } }); _getElementById("btnGetInsertRows").addEventListener('click',function () { var rows = sheet.getInsertRows(); if (rows.length > 0) { appendResult("Inserted rows @ " + rows.map(function (item) { return item.row; }).join(", ")); } }); _getElementById("btnGetDeleteRows").addEventListener('click',function () { var rows = sheet.getDeletedRows(); if (rows.length > 0) { appendResult("Deleted rows @ " + rows.map(function (item) { return item.row; }).join(", ")); } }); _getElementById("btnClearPendingChanges").addEventListener('click',function () { sheet.clearPendingChanges(); }); _getElementById("btnSetRowCount").addEventListener('click',function () { sheet.setRowCount(60); }); _getElementById("btnSetColumnCount").addEventListener('click',function () { sheet.setColumnCount(16); }); }; function _getElementById(id){ return document.getElementById(id); }
<!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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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>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" /> <input type="button" id="btnDeleteRow" value="Delete Rows" title="Deleted seleted rows" /> <input type="button" id="btnClearPendingChanges" value="Clear Pending Changes" title="Clear pending changes" /> </div> <div class="option-row"> <input type="button" id="btnSetRowCount" value="Set Row Count" title="Set row count to 60" /> <input type="button" id="btnSetColumnCount" value="Set Column Count" title="Set column count to 16" /> </div> <div class="option-row"> <input type="button" id="btnGetDirtyRows" value="Get Dirty Rows" /> <input type="button" id="btnGetDirtyCells" value="Get All Dirty Cells" /> <input type="button" id="btnGetSelectionDirtyCells" value="Get Selection Dirty Cells" /> </div> <div class="option-row"> <input type="button" id="btnGetInsertRows" value="Get Insert Rows" /> <input type="button" id="btnGetDeleteRows" value="Get Delete Rows" /> </div> <div class="option-row" style="width:100%;height:80px;"> <textarea id="taResult" style="width:100%;padding:0;float:right;height:80px;background:none"></textarea> </div> </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; } p{ padding:2px 10px; background-color:lavender; } input[type="button"] { width: 100%; margin-bottom: 2px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }