SpreadJS 14
Getting Started / Quick Start / Store Changes Made to Spreadsheet
In This Topic
    Store Changes Made to Spreadsheet
    In This Topic

    The following section describes the steps to store changes made in a spreadsheet and to remove all the changes by refreshing the spreadsheet.

    Step 1: Modify Spreadsheet and Protect it

    1. Modify the refresh function created in Data Binding section. After invoking loadTable function, execute the suspendDirty and resumeDirty methods so that the changes made to column F are not stored.
    2. Set the value, background color, and horizontal al.ignment for cell F1 of the spreadsheet.
    3. Set isProtected option to true to protect the spreadsheet.
    4. Set locked method to false to prevent locking of cells in the spreadsheet.  
      JavaScript
      Copy Code
      //1: Set the value, backcolor, and halign between suspending / resume dirty & Protect the sheet and lock F column
      function refresh() {
          var ss = GC.Spread.Sheets.findControl(document.getElementById("ss"));
          // Get activesheet
          var sheet = ss.getActiveSheet();
          // Reset the sheet and set the column count
          sheet.reset();
          sheet.setColumnCount(7);
          // Load the table to the Spread instance using the sample data
          var data = createSampleData();
          loadTable(ss, data);
          // Set the value, backcolor, and halign between suspending / resume dirty
          // Suspend dirty cells
          sheet.suspendDirty();
          // Set the value, background color and halign for cell F1
          sheet
              .getCell(0, 5)
              .backColor("yellow")
              .value("Changes")
              .hAlign(GC.Spread.Sheets.HorizontalAlign.center);
          // Resume dirty cells
          sheet.resumeDirty();
          
          // Protect the sheet and lock F column
          sheet.options.isProtected = true;
          // 4.1) Setting the sheets default stye to not lock the cells
          var s = sheet.getDefaultStyle();
          s.locked = false;
          sheet.setDefaultStyle(s);
          // 4.1) Specifying to lock column F to display the new and old value (column index 5)
          sheet.getRange(-1, 5, -1, 1).locked(true);
      

    5. Create an HTML input button 'Refresh' that will execute the refresh function.
      JavaScript
      Copy Code
      <!--Create a refresh button-->
      <input type="button" value="Refresh" onclick="refresh()" />
      


    Step 2: Get Dirty Cell Information

    1. Create showChanges function and use getDirtyCells and hasPendingChanges methods to get information on dirty cells along with the pending changes.
    2. Create a data array, dirtyDataArray, to store the dirty cell information about row, col, rowCount, and colCount.
    3. Use setValue method to set the value of cell in column F such that it displays the old and the new values of dirty cells in it.
      JavaScript
      Copy Code
      // Step 2: Create showChanges()
      function showChanges(ss, row, col, rowCount, colCount) {
          // Get activesheet
          var sheet = ss.getActiveSheet();
          // If the activesheet has pending changes
          if (sheet.hasPendingChanges()) {
              // Suspend Paint, Event, Dirty
              ss.suspendPaint();
              ss.suspendEvent();
              sheet.suspendDirty();
              // Store dirty cell collection in data array called dirtyDataArray
              var dirtyDataArray = sheet.getDirtyCells(row, col, rowCount, colCount);
      
              // Skip dirty changes for column 'F'
              for (var i = 0; i < dirtyDataArray.length; i++) {
                  // Store dirty cell data in the dirty data array
                  var dirtyCell = dirtyDataArray[i];
      
                  // Display the old and new value from the dirtyDataArray values
                  // row index --> dirtyCell.row returns the row index of the row that had the changes made
                  // column index --> 5 is for the row index for column F
                  sheet.setValue(
                      dirtyCell.row,
                      5,
                      "old: " + dirtyCell.oldValue + ", new: " + dirtyCell.newValue
                  );
              }
              // Resume dirty, event, paint
              sheet.resumeDirty();
              ss.resumeEvent();
              ss.resumePaint();
          }
      }
      

     

    Step 3: Display Dirty Cell Values

    Bind CellChanged and RangeChanged events to the active sheet to execute the showChange function each time the spreadsheet is modified.

    JavaScript
    Copy Code
    // Step 3: Bind CellChanged and RangeChanged events to the sheet
    $(document).ready(function () {
        // initializing Spread
        var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
        // Invoke the refresh function
        refresh();
        // Bind CellChanged
        spread.bind(GC.Spread.Sheets.Events.CellChanged, function (event, data) {
            var row = data.row,
                col = data.col;
            if (
                data.propertyName !== "value" ||
                row === undefined ||
                col === undefined
            ) {
                return;
            }
            // Execute the showChanged function
            showChanges(spread, row, col);
        });
        // Bind RangeChanged
        spread.bind(GC.Spread.Sheets.Events.RangeChanged, function (event, data) {
            var row = data.row,
                col = data.col,
                rowCount = data.rowCount,
                colCount = data.colCount;
            if (
                row === undefined ||
                col === undefined ||
                rowCount === undefined ||
                colCount === undefined
            ) {
                return;
            }
            // Execute the showChanged function
            showChanges(spread, row, col, rowCount, colCount);
        });
    });
    

    The old and new values will appear in column F. Also, column F is locked so that its content cannot be modified. You can also refresh the spread instance using Refresh button, to remove all changes. The output of above code will look like below: