Is it possible to copy a worksheet from an other workbook

Is it possible to copy a worksheet from an other workbook

Posted by: adrien.corbin on 29 May 2018, 11:26 pm EST

• 

  adrien.corbin

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 29 May 2018, 11:26 pm EST

  We are looking to do something similar to this and we could not find any example where worksheet are moved from one workbook to an other. Our current implementation fails, once we do any action after a copy like this we have error like thi one: Cannot read property ‘Ky’ of null

  // mainWorkbookBlob is comming from server as base64 and then converted to blob;
  // tempWorkbookBlob is comming from server as base64 and then converted to blob;
  
  let excelIO = new GC.Spread.Excel.IO();
  let mainWorkbook = new GC.Spread.Sheets.Workbook();
  
  excelIO.open(mainWorkbookBlob, (json: any) => {
      mainWorkbook.suspendPaint();
      mainWorkbook.fromJSON(json, null);
  }, (e: any) => {
      console.log(e);
  });
  
  let tempWorkbook = new GC.Spread.Sheets.Workbook();
  
  excelIO.open(tempWorkbookBlob, (json: any) => {
      tempWorkbook.suspendPaint();
      tempWorkbook.fromJSON(json, null);
  
      mainWorkbook.workbook.addSheet(0, tempWorkbook.getSheet(0));
  
      tempWorkbook.destroy();
  }, (e: any) => {
      console.log(e);
  });
  

  Do you have any advice on how we would implement such feature if there isn’t already an helper function to do this I might have missed.

  Thanks

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 30 May 2018, 5:51 pm EST

  You can simply use toJSON and fromJSON methods to serialize Spread . Please have a look at the code below:

  
   $(function () {
  var spread1 = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
         
               $("#btn1").click(function () {
                   //Serialize ss to ss1.
                   var jsonStr = JSON.stringify(spread1.toJSON());
                 
   var spread2 = new GC.Spread.Sheets.Workbook(document.getElementById("ss1"),{sheetCount:3});
                   spread2.fromJSON(JSON.parse(jsonStr));
                   var sheet2 = spread2.getActiveSheet();
                
               });
           });
  
  

  I hope it helps.

  Thanks,
  
  Deepak Sharma

• 

  adrien.corbin

  • Post Options:
  • Link
    Copy

  Posted 31 May 2018, 12:51 am EST

  Your example open a new workbook and replace the original content. My goal is to add sheets comming from an other workbook.

  I modified your example to represent what I am trying to do

  
  $(function () {
  var spread1 = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
  var spread2 = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
         
               $("#btn1").click(function () {
                   //Serialize ss to ss1.
                   var spread1Json = JSON.stringify(spread1.toJSON());
                    var spread2Json = JSON.stringify(spread2.toJSON());
  
   		var spread3 = new GC.Spread.Sheets.Workbook(document.getElementById("ss1"),{sheetCount:3});
  
  		// This will replace spread 3 with spread 1, I would like to add those sheet to spread 3 instead. For a total of 6 worksheet, in this example worksheet have the same name so it wouldn't work, but in my case, all sheet will have different name.
                   spread3.fromJSON(JSON.parse(spread1Json));
  
  		// This will replace spread 1 with spread 2, I would like to add those sheet to spread 3 instead. For a total of 9 worksheet.
                   spread3.fromJSON(JSON.parse(jsonStr));
  
  		// This will be spread 2 active sheet.
                   var sheet = spread3.getActiveSheet();
  
  		// I was expecting something like this.
  		spread3.addSheet(spread1.getSheet(0));
  		spread3.addSheet(spread1.getSheet(1));
  		spread3.addSheet(spread1.getSheet(2));
  		spread3.addSheet(spread2.getSheet(0));
  		spread3.addSheet(spread2.getSheet(1));
  		spread3.addSheet(spread2.getSheet(2));
  
               });
           });
  

  Is it possible to transfer a worksheet to an existing workbook without replacing it?

  Thanks

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 31 May 2018, 5:18 pm EST

  Hello,

  To copy sheet you can use the toJson() and fromJson() methods on sheet instead of Spread. For example:

  var jsonStr = null;
  //export
  jsonStr = JSON.stringify(spread1.getSheet(1).toJSON());
  //import
  spread2.getSheet(1).fromJSON(JSON.parse(jsonStr));
  alert(jsonStr);
  

  I hope it helps.

  Thanks,
  
  Deepak Sharma

• 

  adrien.corbin

  • Post Options:
  • Link
    Copy

  Posted 13 June 2018, 7:19 am EST

  Thanks it work the function properly I had not realised this function was on the workbook, worksheet and event cell level and even on sparkline and filters.

  http://help.grapecity.com/spread/SpreadSheets11/webframe.html#SpreadJS~GC.Spread.Sheets.Worksheet~fromJSON.html

  However, it seem to keep only the data. Is it suposed to copy the style, I might be doing something wrong. From an Excel file the style is not part of the data worksheet, so I understand why it might not follow.

  Note, I tried the serialization and deserialization option ```
  
  fromJSON(json, { ignoreStyle: false });

  http://help.grapecity.com/spread/SpreadSheets11/webframe.html#SpreadJS~GC.Spread.Sheets.Workbook~toJSON.html
  http://help.grapecity.com/spread/SpreadSheets11/webframe.html#SpreadJS~GC.Spread.Sheets.Workbook~fromJSON.html
  
  If it is not possible is it possible to import stylePart from an other workbook?
  
  Here an pseudo example of what I'm trying to do:
  

  let mainWorkbook = …
  
  let mainFileBlob = …

  let excelIO = new GC.Spread.Excel.IO();

  excelIO.open(mainFileBlob, (json: any) => {
  
  mainWorkbook.fromJSON(json, { ignoreStyle: false });
  
  }, (e: any) => {
  
  console.log(e);
  
  });

  onReceiveWorksheet.do((worksheetIndex, worksheetBlob) => {
  
  excelIO.open(worksheetBlob, (json: any) => {
  
  let tempWorkbook = new GC.Spread.Sheets.Workbook();

      tempWorkbook.suspendPaint();
      tempWorkbook.fromJSON(json, { ignoreStyle: false });
      tempWorkbook.removeSheet(0); //Only to remove Evaluation sheet for the moment
  
      let tempWorksheet = new GC.Spread.Sheets.Worksheet(tempWorkbook.getSheet(0).name());
  
      tempWorksheet.fromJSON(tempWorkbook.getSheet(0).toJSON({ ignoreStyle: false }));
  
      mainWorkbook.addSheet(worksheetIndex, tempWorksheet);
  
      tempWorkbook.destroy();
  }, (e: any) => {
      console.log(e);
  });
  

  }

  
  Thanks

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 17 June 2018, 11:34 pm EST

  Hello,

  Bydefault it saves all the style to JSON. You can check the saved JSON schema for ‘style’ element.
  
  I checked with the online demo and found that all the styles were saved and loaded back to another workbook with no issues.

  Please check:
  
  https://www.grapecity.com/en/demos/spread/JS/InspectorSample/

  Thanks,
  
  Deepak Sharma

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 20 June 2018, 11:19 pm EST - Updated 3 October 2022, 1:52 am EST

  Hi,

  I tested the code as attached in the html file and found that newly added imported sheet only has data and not styles.
  
  Please confirm you are seeing the same issue with the JSON data.

  Thanks,
  
  Deepak Sharma
  
  SpreadJSStyleJSon.zip
  
  

  

  1209×764 31.1 KB

• 

  datta

  • Post Options:
  • Link
    Copy

  Posted 8 May 2019, 4:29 am EST

  Hi Deepak I am also facing same issue. Styles are not being copied only values are copies. Is it a bug?Any update on this?

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 8 May 2019, 7:44 pm EST

  Hi,

  The issue with the styles is arising because when importing excel files, styles are imported as named styles and we need to register them into our workbook as well. Please refer to the following thread which demonstrates how we could import and merge excel files along with styles:
  
  https://www.grapecity.com/forums/spread-sheets/importing-without-overwrit#hiif-you-would-like-to-mer_1

  Regards

• 

  datta

  • Post Options:
  • Link
    Copy

  Posted 8 May 2019, 9:44 pm EST

  No the solution mentioned in the link didn’t work for me as namedStyles property was missing in JSON. I did manual copy of the styles as given in below snippet and got it working. Please let me know if there will be any problem with this approach

  function copyStyle(sourceSheet,targetSheet)
  {
  for(var rowIndex=0;rowIndex < sourceSheet.getRowCount();rowIndex++)
  	{
  	   
  	for(var colIndex=0;colIndex < sourceSheet.getColumnCount();colIndex++ )
  		{
  		   var sourceStyle = sourceSheet.getStyle(rowIndex,colIndex,GC.Spread.Sheets.SheetArea.viewport, true);
  		   if(sourceStyle)
  			   {
  			   targetSheet.setStyle(rowIndex,colIndex,sourceStyle.clone(),GC.Spread.Sheets.SheetArea.viewport);
  			   }
  		}
  	}
  
  }
  

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 9 May 2019, 5:44 pm EST

  We are sorry but we are unable to replicate the issue at our end. We are able to access the namedStyles object on the imported json without any issues. Could you please let us know on which json object you tried to access the namedStyles object. It is expected to be present on the json provided by the excelIO.open() method. Please refer to the following code snippet:

  excelIO.open(excelFile, function(json){
      var wbJSON = json;
      // access namedStyles on wbJSON object.
  });
  

  If you still face issues, could you please share a sample replicating the issue so that we could investigate it further.

  Regarding copying styles using getStyle()/setStyle() method, it would do the job, but it would be less efficient.