Large document handling (server to client exchange)

Large document handling (server to client exchange)

Posted by: ismael.zamora on 21 September 2020, 12:48 am EST

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 21 September 2020, 12:48 am EST

  Hello, we’re using the Document for Excel (Java) on the server side and SpreadJS on the client side in our application. Basically the server-side code is generating a (big) excel file on the server, and then we allow the user to access and manipulate it on the client side. Our problem is this: the final excel file could be very big, like two sheets, and on every sheet we could have thousands of rows (>10.000) and the same for the columns (from 1.000 to 10.000), and the second sheet usually contains a lot of formulas too.

  At the beginning we tried to transfer the complete JSON file from the server to the client, but then when the client is going to inject the JSON into the SpreadJS component we have the browser blocked for a long time.

  To avoid this we have implemented a progressive load system that starts to load a limited sets of rows for each sheet, then we put these data into the spreadsheet with:

  
  sheet.setArray(startRow, 0, data, false);
  
  

  and it works very fast, but now we faced two problems:

  • The user could add to the spreadsheet some charts, and we have to “rebuild” these charts on the client side, there is any example on how to extract the charts informations from the “server-side” with “Document for Excel” API and rebuild them with SpreadJS?

  • When we get the data in the described way we loose any formatting property set by the user, also for this there are any possible strategy to transfer it from the server to the client?

  Let me know if you need more details or informations about these two requests.

  Regards.

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy

  Posted 21 September 2020, 1:31 am EST

  I forget that I also checked if I can try to “open” from the client side the excel file that we have in the server, using GC.Spread.Excel.IO.open function, but looking the documentation seems it is not possible to put in the open first parameter a remote URL to the file to open.

  https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Excel.IO~open.html

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 21 September 2020, 9:12 pm EST

  Hello,

  We are discussing your issue with the development team and we will get back to you soon on the same.

  Tracking id :- DOCXLS-3121

  Thanks, Reeva

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 23 September 2020, 2:49 am EST

  Hello,

  Our developers looked into your issue and here is the response to your issues: -

  1) How to extract only the charts informations from the “server-side” with “Document for Excel” API and rebuild them with SpreadJS?

  Before supporting JSON I/O of each feature, the only way for now is:

  1. Call Worksheet.toJson()
  2. Extract the chart json and send it to spreadjs - ( check attached image 1.png)
  3. In spreadjs, add a new chart and restore it by call chart.fromJSON()

  2) When they get the data in the described way, they lose any formatting property set by the user, also for this are there any possible strategies to transfer it from the server to the client?

  A similar way to resolve this request:

  1. Call workbook.toJson() locate the style in whole json string and send it to spreadjs, like below picture - ( check attached image 2.png)
  2. In spreadjs, add a named style and restore it by calling Style.fromJSON(), then give it to the cell.

  Hope it answers your queries to some extent.

  And further, our developers have already considered to support JSON I/O of each feature, such as shape, chart, table, sparkline, conditional formats and so on, all these are supported by SpreadJs and they are already working on making GcExcel & SpreadJs more compatible with each other i.e. “GCExcel as a real-time collaboration server for SpreadJS”.

  Thanks, Reeva Images.zip

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy

  Posted 16 October 2020, 2:26 am EST

  Hello and sorry for the late reply but I was busy in a different project and now I’m back to this issue. We implemented the charts transfer from the server to the client but seems not working. Just to be sure that the code we implemented is correct we have done these steps:

  On server side we load the excel file, and for each sheet we are going to get the JSON array of the charts using this approach (we are using Jackson lib for JSON handling on the server-side code):

  IWorksheet dataSheet = workbook.getWorksheets().get(0);
  JsonNode obj = mapper.readTree(dataSheet.toJson());
  if (obj.has("charts")) {
    ret.setJsonChart(mapper.writeValueAsString(obj.get("charts")));
  }
  

  basically we check if there is any attribute named “charts” and in that case we send to the client the JSON string, and in the attached zip file you can see the content of the string in the “chart.json” file.

  Then, in the client side, if the above string is not empty we are going to inject the charts in this way:

  var s = spread.sheets[0];
  var charts = JSON.parse(json);
  
  s.charts.clear();
  for (var n = 0; n < charts.length; n++) {
      var created = s.charts.add(charts[n].name, gcns.Charts.ChartType.line, 10, 10, 100, 100, '$A$1:$A$1', gcns.Charts.RowCol.columns);
      created.fromJSON(charts[n]);
  }
  

  so the loop is checking the server-side string (converted to JSON), and of each chart it creates a new chart and we apply it. But as you can see in the attached images, when we rebuild the charts in this way the chart is not well-rendered (even if the data-area, the position and the title are OK). I attached in the script also the excel file we have on the server side if you need!

  chart.zip

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 19 October 2020, 12:39 am EST

  Hello,

  We are discussing your issue with our developers. We will get back to you soon on the same.

  Thanks, Reeva

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 19 October 2020, 8:17 pm EST

  Hello Ismael,

  Our developers have researched on your issue and according to them, the chart data has been updated correctly after chart.fromJSON, but the chart view hasn’t been updated. Here is a workaround for the same. Please use the code snippet like this:-

  for (var n = 0; n < charts.length; n++) {
    var created = s.charts.add(charts[n].name, gcns.Charts.ChartType.line, 10, 10, 100, 100, '$A$1:$A$1', gcns.Charts.RowCol.columns);
    created.fromJSON(charts[n]);
  
    // Record chart name
    var originChartName = created.name();
  
    // Change chart name to update the chart view
    created.name('_' + originChartName);
  
    // Recover origin chart name
    created.name(originChartName);
  }
  

  Please try if it works for you and let us know your observations.

  Thanks, Reeva

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy

  Posted 19 October 2020, 11:58 pm EST - Updated 29 September 2022, 6:49 am EST

  Hello, after your trick the chart is rendered correctly, but as you can see from the two attached images the position (horizontally) is not correct, the charts appears in the wrong position and if you try to move them with the mouse they “jump” to the left side of the sheet.

  Something that we can do?

  

  1732×773 188 KB

  

  1732×773 188 KB

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy

  Posted 20 October 2020, 12:01 am EST

  Looks like in my previous post I accidentally uploaded the same picture two times, I will place them in a zip, naming them “before.png” and “after.png” to show the wrong position “after” the chart is rebuilt with the JSON approach.images.zip

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 20 October 2020, 10:50 pm EST

  Hello,

  We are discussing your issue with our developers. We will get back to you soon on the same.

  Thanks, Reeva

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 21 October 2020, 9:44 pm EST

  Hello,

  Our developers can not reproduce the chart position problem as you described. But they found out a new workaround to refresh chart view:-

  for (var n = 0; n < charts.length; n++) {
    var created = s.charts.add(charts[n].name, gcns.Charts.ChartType.line, 10, 10, 100, 100, '$A$1:$A$1', gcns.Charts.RowCol.columns);
    created.fromJSON(charts[n]);
  }
  
  // use zoom to refresh chart
  sheet.zoom(1);
  
  

  Please try if it works for you.

  Thanks, Reeva

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy

  Posted 22 October 2020, 7:05 pm EST

  Hello, just tested your solution with the call to zoom method but we have these two issues:

  • the content of the chart is not well rendered (with the previous trick about the “rename” of the chart was rendered correctly, so I reverted to that implementation
  • the position still wrong

  Just a note, we are using SpreadJS version 13.1.2, could be related to some problems in that version? Thanks in advance for the help!

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy

  Posted 23 October 2020, 12:17 am EST

  Also, just to better debug the issue on our side, can you point me which properties of the charts I have to inspect to know the position relative to the sheet? I will compare the numbers before and after the JSON serialization…

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 25 October 2020, 4:30 pm EST

  Hello,

  We are discussing your issue with our developers. We will get back to you soon on the same.

  Thanks, Reeva

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 26 October 2020, 1:52 am EST

  Hello,

  Can you please send your demo project as our developers could not reproduce your issue. You may create an issue on SupportOne here. https://supportone.componentone.com/home.

  Please be assure that we will retain the confidentiality of your issue.

  Thanks, Reeva

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy

  Posted 29 October 2020, 6:51 pm EST

  I am going to build a mini-project to reproduce the issue. It will be not simple because we have a server-side code and the JS code on the client. I will come back to you as soon as possible. Thanks

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 29 October 2020, 7:38 pm EST

  Hello,

  We will wait for your sample.

  Thanks, Reeva

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy

  Posted 20 November 2020, 2:46 am EST

  Hello and sorry for the delay, but finally we organized our test project to be built to reproduce the issue. The file is larger that 5M (I added some needed libraries) so you can download the zip package from this URL:

  https://te935901470-my.sharepoint.com/:u:/g/personal/luca_leadmolecular_com/EeRPaklW5q5FgGDghrdIIroBxmWLh13-tzt3C2S3m0qwMw?e=Ah7ZPP

  inside you can see the instructions to build and start the application in the README.txt file.

  When the system starts you will see the application in the browser; on the top you will have two buttons to load the excel file from the server (the file will be splitted in multiple batches because we may have large files in our real examples). If you try for example the file #2 you can see that in the “Data output” sheet we have two charts, and sometimes they are rendere “outside” the view, and to get them back in the right position you have to change the current selected sheet and then come back to the “Data output”.

  I noticed that the issue is more frequent if you click the “Load” button and the “Data output” sheet is already selected (try to load it multiple times).

  Let me know if you need more informations and/or details.

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 23 November 2020, 6:25 pm EST

  Hello,

  We are discussing your issue with our developers. We will get back to you soon on the same.

  Thanks, Reeva

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy

  Posted 8 December 2020, 7:11 pm EST

  Good morning, do you have any comment about our test-project?

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 8 December 2020, 9:08 pm EST

  Hello,

  We have asked for an update from our developers and we will get back to you as soon as we hear anything from them.

  Thanks, Reeva

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 16 December 2020, 9:10 pm EST

  Hello,

  Our developers have written the workaround in the attached file “DataGatheringWidget.java”, and it works well for the project that you have provided.

  Please search “Workaround” key words in this file, and you will see the workaround that formed by two pieces of code logic as shown in attached images.

  Please invoke them into your actual project. And let us know your further observations.

  Thanks, Reeva Workaround.zip

• 

  ismael.zamora

  • Post Options:
  • Link
    Copy

  Posted 20 December 2020, 9:26 pm EST

  Hello, the fix looks working fine!! Thanks for your support!

  I have another question/issue related to this “batch-loading” approach, do you prefer if I open another issue in the ticketing system or I can add it here?

  Thanks again and have a nice and safe day!

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 21 December 2020, 4:24 pm EST

  Hello,

  You may start a new issue in the ticketing system.

  Thanks, Reeva