Save Spreadsheet Changes

SpreadJS lets you store modifications to a spreadsheet. In the sections below, let's discuss the step-by-step process required.

Save Spreadsheet Changes and Refresh

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 the 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 the isProtected option to true to protect the spreadsheet.

4. Set the locked method to false to prevent the locking of cells in the spreadsheet.

   //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.

   <!--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.

   // 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.

// 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 the Refresh button, to remove all changes. The output of the above code will look like the below:

Save Spreadsheet Changes to Database

The following section describes the steps to save spreadsheet changes to the database.

Note: SpreadJS does not update the database directly, but you can get the changed data by the following method and update the database accordingly.

Step 1: Get Dirty Cell Information and Convert it to JSON String

1. Create saveToDB function and use getDirtyCells method to get information about changed cells, known as dirty cells.

2. Use  JSON.stringify method to convert dirty cell information to JSON string.

   //1: Create saveToDB()
   function saveToDB() {
       // Get the activesheet from the DOM element "ss"
       var ss = GC.Spread.Sheets.findControl(document.getElementById("ss"));
       var sheet = ss.getActiveSheet();
       // Store the dirty cells information in the dirtyCells variable
       var dirtyCells = sheet.getDirtyCells();
       // Get the HTML <div> element "msg" by id, this will be created in Step 2
       var div = document.getElementById("msg");
       // Display the JSON string in the div element to visualize the changed cell information
       div.innerHTML = JSON.stringify(dirtyCells, null, 4);
       // Note: Post changes to your database
   }

Step 2: Save Changes to Database

1. Create an HTML <div> element with msg ID after SpreadJS <div> element to display JSON string.

2. Create an input button Save to DB where the saveToDB function is invoked. The changes captured and converted to JSON string will be displayed in the msg ID <div> when clicking the Save to DB button.

   The new HTML <body> with all buttons and <div> elements will look like below:

   <body>
       <h1>Save changes made to the spreadsheet to a database</h1>
       <p>
           During this section we will create a Save to DB button and a DIV to
           display the changes as a JSON string when the Save to DB button is
           clicked.
       </p>
       <!-- Create a refresh button -->
       <input type="button" value="Refresh" onclick="refresh()" />
       <!-- Create Save to DB <div> and button -->
       <input type="button" value="Save to DB" onclick="saveToDB()" />
       <br />
       <br />
       <div id="ss" style="height:400px;width:900px"></div>
       <!-- Changes <div> -->
       <div class="options-container">
           <h4> Changes saved to database: </h4>
           <div id="msg" style="width: 100%; height: 260px; border: 1px solid grey;"></div>
       </div>
   </body>

The JSON string which contains the changes and will be posted to the database is displayed in the output as shown below: