GC.... spread.toJSON excludes the dataTable

GC.... spread.toJSON excludes the dataTable

Posted by: architect.trader on 9 July 2019, 2:07 pm EST

• 

  architect.trader

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 9 July 2019, 2:07 pm EST

  I am using SpreadJS.
  
  I am trying to automatically save a user altered spreadsheet and automatically repopulate the spreadsheet later. To fetch user changes I did the following:

    window.onblur = function () {
      const spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadsheetId"));
  
      let jsonStr = JSON.stringify(spread.toJSON());
      console.log(jsonStr);
    }
  

  output: {“version”:“12.0.8”,“sheetCount”:3,“sheets”:{“Sheet1”:{“name”:“Sheet1”,“theme”:“Office”,“data”:{“defaultDataNode”:{“style”:{“themeFont”:“Body”}}},“rowHeaderData”:{“defaultDataNode”:{“style”:{“themeFont”:“Body”}}},“colHeaderData”:{“defaultDataNode”:{“style”:{“themeFont”:“Body”}}},“selections”:{“0”:{“row”:0,“rowCount”:1,“col”:0,“colCount”:1},“length”:1},“index”:0},“Sheet2”:{“name”:“Sheet2”,“theme”:“Office”,“data”:{},“rowHeaderData”:{},“colHeaderData”:{},“selections”:{“0”:{“row”:0,“rowCount”:1,“col”:0,“colCount”:1},“length”:1},“index”:1},“Sheet3”:{“name”:“Sheet3”,“theme”:“Office”,“data”:{},“rowHeaderData”:{},“colHeaderData”:{},“selections”:{“0”:{“row”:0,“rowCount”:1,“col”:0,“colCount”:1},“length”:1},“index”:2}}}

  As you can see the dataTableis missing and so there is no data that can be repopulated.

  When I save the file via File > Export Export SSJSON File. I can see the full output including the dataTable property which contains all user data

  output: {“version”:“12.0.8”,“sheetCount”:2,“sheets”:{“Sheet1”:{“name”:“Sheet1”,“activeRow”:16,“activeCol”:9,“theme”:“Office”,“data”:{“dataTable”:{“0”:{“0”:{“style”:{“hAlign”:3,“vAlign”:0,“themeFont”:“Body”,“imeMode”:1}},“1”:{“value”:“Expectancy: “,“style”:{“hAlign”:3,“vAlign”:0,“themeFont”:“Body”,“imeMode”:1}},“2”:{“value”:0.48,“style”:{“hAlign”:3,“vAlign”:0,“themeFont”:“Body”,“imeMode”:1}},“3”:{“style”:{“hAlign”:3,“vAlign”:0,“themeFont”:“Body”,“imeMode”:1}},“4”:{“value”:“Total P&L:”,“style”:{“hAlign”:3,“vAlign”:0,“themeFont”:“Body”,“imeMode”:1}},“5”:{“value”:776,“style”:{“autoFormatter”:{“formatCached”:”$#,##0.00;Red”},“hAlign”:3,“vAlign”:0,“themeFont”:“Body”,"imeMode …

  How to I automatically export the state of the spreadsheet using javascript without users having to explicitly export the spreadsheet?

  Again the objective is to stringify the entire spreasheet and load it again later.

  Thanks

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 9 July 2019, 5:50 pm EST

  Hi,

  To include the binding data source into the exported JSON, we need to set the includeBindingSource flag to true. Please refer to the following code snippet:

  let wbJson = spread.toJSON({
  	includeBindingSource: true
  });
  

  Please refer to the following document for a list of all available export flags: http://help.grapecity.com/spread/SpreadSheets12/webframe.html#SpreadJS~GC.Spread.Sheets.Workbook~toJSON.html

  If the issue persists for you, please share a small sample replicating the issue so that we could further investigate it.

  Regards
  
  Sharad

• 

  architect.trader

  • Post Options:
  • Link
    Copy

  Posted 21 July 2019, 10:36 am EST

  I tried the code above. Now I see the datatable but its always empty. Whenever I add text to the spreadsheet grids and stringify the sheet, I see the datatable now but its always empty. Eg below:

  {“version”:“12.0.8”,“sheets”:{“Sheet1”:{“name”:“Sheet1”,“theme”:“Office”,“data”:{“defaultDataNode”:{“style”:{“themeFont”:“Body”}},“dataTable”:{}},“rowHeaderData”:{“defaultDataNode”:{“style”:{“themeFont”:“Body”}}},“colHeaderData”:{“defaultDataNode”:{“style”:{“themeFont”:“Body”}}},“selections”:{“0”:{“row”:0,“rowCount”:1,“col”:0,“colCount”:1},“length”:1},“index”:0}}}

  All I am doing is typing into the sheet then clicking out of the spreadsheet.

    window.onblur = function () {
      // debugger;
      const spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadsheetId"));
  
      let jsonStr = JSON.stringify(spread.toJSON({
        includeBindingSource: true,
      }));
      console.log(jsonStr);
    }
  

  OnBlur: I expect text with within the data table but its empty. What am I doing wrong?

  I also tried applying to same code to https://www.grapecity.com/demos/spread/JS/WebDesigner/content/index.html

  Add : ```
  
  window.onblur = function () {const spread = new GC.Spread.Sheets.Workbook(document.getElementById(“spreadsheetId”));let jsonStr = JSON.stringify(spread.toJSON({includeBindingSource: true,}));console.log(jsonStr);}

  
  To the console and OnBlur it will stringify the spreadsheet.  Again the DataTable is empty.

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 21 July 2019, 5:28 pm EST

  The issue with the code snippet is that you are creating a new Workbook instance every time in the blur handler. We should create spread instance only once and then use that instance for further operations.
  
  Please refer to the following code snippet:

  /* create spread */
  const spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadsheetId"));
  /* add blur handler */
  window.onblur = function () {
  	/* if you can't access the spread instance as closure then you may also find the existing instance of spread using the findControl method */
  	/* const spread = GC.Spread.Sheets.findControl("spreadsheetId"); */
  	let jsonStr = JSON.stringify(
  		spread.toJSON({
  			includeBindingSource: true
  		})
  	);
  	console.log(jsonStr);
  }
  

• 

  architect.trader

  • Post Options:
  • Link
    Copy

  Posted 22 July 2019, 4:31 am EST

  I tried the above code on https://www.grapecity.com/demos/spread/JS/WebDesigner/content/index.html and the data table is empty. Sorry if I’m doing something wrong.

  
      <script>
  //tested locally
        window.onload = function () {
          let spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadsheetId"));
  
          window.onblur = function () {
            let jsonStr = JSON.stringify(spread.toJSON({
              includeBindingSource: true,
            }));
            console.log(jsonStr);
          }
        }
      </script>
  
  

  To test I pasted the following into the browser at url = https://www.grapecity.com/demos/spread/JS/WebDesigner/content/index.html:

  
  let spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'));
  window.onblur = function () {let jsonStr = JSON.stringify(spread.toJSON({includeBindingSource: true,}));console.log(jsonStr);}
  
  

  Result:

  
  {"version":"12.1.3","sheets":{"Sheet1":{"name":"Sheet1","theme":"Office","data":{"defaultDataNode":{"style":{"themeFont":"Body"}},"[b]dataTable[/b]":{}},"rowHeaderData":{"defaultDataNode":{"style":{"themeFont":"Body"}}},"colHeaderData":{"defaultDataNode":{"style":{"themeFont":"Body"}}},"selections":{"0":{"row":0,"rowCount":1,"col":0,"colCount":1},"length":1},"index":0}}}
  
  

  I then entered text into the spreadsheet and clicked away from the window for the script to log into the console. Try it yourself and see the result. The datatable is still empty

• 

  alex.risch

  • Post Options:
  • Link
    Copy

  Posted 22 July 2019, 5:44 am EST

  You are creating a new instance of a Workbook when using the code:

  let spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'));
  

  This will not have the same information as the one you see on the page as you are creating a new instance.

  Change the code instead to:

  let spread = GC.Spread.Sheets.findControl(document.getElementById('ss'));
  window.onblur = function () {let jsonStr = JSON.stringify(spread.toJSON({includeBindingSource: true,}));console.log(jsonStr);}
  

• 

  architect.trader

  • Post Options:
  • Link
    Copy

  Posted 22 July 2019, 6:07 am EST

  Ah that works. I missed findControl. Sorry for the back and forth.

  
  {"version":"12.1.3","sheetCount":2,"sheets":{"Sheet1":{"name":"Sheet1","activeRow":10,"activeCol":6,"theme":"Office","data":{"dataTable":{"7":{"3":{"value":"fsdfdsf"}},"10":{"6":{"value":"fsfdfsf"}},"14":{"13":{"value":"fsdfdsfsdsd"}},"25":{"3":{"value":"fsdfdsfsdf"}},"40":{"10":{"value":"fsdfsd"}},"50":{"3":{"value":"fsdfsdfdsf"}},"74":{"5":{"value":"sfsdfdsfs"}}},"defaultDataNode":{"style":{"themeFont":"Body"}}},"rowHeaderData":{"defaultDataNode":{"style":{"themeFont":"Body"}}},"colHeaderData":{"defaultDataNode":{"style":{"themeFont":"Body"}}},"selections":{"0":{"row":10,"rowCount":1,"col":6,"colCount":1},"length":1},"index":0},"About":{"name":"About","theme":"Office","data":{"dataTable":{"1":{"1":{"value":"GrapeCity SpreadJS","style":{"font":"bold 26.667px Calibri"}}},"3":{"1":{"value":"This SpreadJS spreadsheet demo uses the online version of the SpreadJS Sheets Designer","style":{"font":"18.67px Calibri"}}},"4":{"1":{"value":"to showcase several of the features that SpreadJS Sheets offers.","style":{"font":"18.67px Calibri"}}},"6":{"1":{"value":"Explore the features and enter sample data directly into the cells to add formulas or charts,","style":{"font":"18.67px Calibri"}}},"7":{"1":{"value":"similar to Microsoft Excel. You can also import and export Excel files using the File menu.","style":{"font":"18.67px Calibri"}}},"8":{"1":{"value":"Excel imports/exports are handled client-side only and no data is sent to the server.","style":{"font":"18.67px Calibri"}}},"10":{"1":{"value":"Note – the JavaScript source code of this Designer is available separately for purchase,","style":{"font":"18.67px Calibri"}}},"11":{"1":{"value":"allowing you to quickly add and offer any of these menu features to your customers.","style":{"font":"18.67px Calibri"}}},"14":{"1":{"value":"Contact us.to learn more:","style":{"font":"18.67px Calibri"}}},"15":{"1":{"value":"us.sales@grapecity.com","style":{"font":"18.67px Calibri","cellType":{"typeName":"8"}}}},"16":{"1":{"value":"Visit the SpreadJS page:","style":{"font":"18.67px Calibri"}}},"17":{"1":{"value":"https://www.grapecity.com/spreadjs","style":{"font":"18.67px Calibri","cellType":{"typeName":"8"}}}}}},"rowHeaderData":{},"colHeaderData":{},"rows":[null,{"size":38},null,{"size":24},{"size":24},{"size":14},{"size":24},{"size":24},{"size":24},{"size":14},{"size":24},{"size":24},null,{"size":24},{"size":24},{"size":24},{"size":24},{"size":24}],"columns":[null,{"size":350}],"selections":{"0":{"row":0,"rowCount":1,"col":0,"colCount":1},"length":1},"index":1}}}