Export to PDF not exporting formula result

Export to PDF not exporting formula result

Posted by: jayesh.upadhyay on 27 December 2017, 8:03 am EST

• 

  jayesh.upadhyay

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 27 December 2017, 8:03 am EST

  We are using ExcelIO to export the SpreadJS to PDF document (version 10.0.0).

  Exporter exporter = new Exporter(spreadJson);
  
  exporter.SavePdf(stream, pdfSettings, 0);

  The generated PDF displays the cell content correctly wherever there is “value” defined for cells. However, wherever there are formulas like “SUM(A1,B1)” or SUBTOTAL(9,A1:C1) the generated PDF displays those cells as blank on PDF.

  The exportToExcel function works perfectly fine and the generated .XSLX has the correct numbers.

  Is there something I need to apply to PDFSettings so generated PDF file displays those formula based cell with correct values ?

  Appreciate your help.

  Thanks
  
  Jayesh
  
  Thomson Reuters

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 28 December 2017, 2:23 am EST

  Hi Jayesh,

  I tested this issue with the latest build of SpreaJS v10.0 and it seems to be working fine. Please download the build from here and test the issue. Let me know in case the issue still persists.
  
  https://cdn.grapecity.com/devchannel/ExcelIO+Server+Component.10.2.3.zip

  Thanks,
  
  Deepak Sharma

• 

  jayesh.upadhyay

  • Post Options:
  • Link
    Copy

  Posted 29 December 2017, 1:57 am EST

  Hello Deepak,

  Yes … above link works as expected and the generated PDF has correct numbers.

  Let me tell you about how we use SpreadJS on server-side (c#). We have template based workpapers and generate .ssjson from server side and give it to the frontend.
  
  Frontend code uses FromJSON() to render it on UI.

  We use ExcelIO on server side to give Excel file for client to download as well. So everything happening on server-side, there is very little happens on client (javascript) side (just FromJSON).

  Our generated SpreadJS JSON looks like this :
  
  “0” : {
  
  “formula”:“=SUM(D3,E3,F3,G3,H3,I3,J3,K3,L3,M3,N3,O3,P3)”,
  
  “style”:“_sNumUnLockedPdf”
  
  }

  When we call exporter.SavePdf(stream, pdfSettings, 0); it can not convert formula into the actual value and hence the output is empty for formula based cell.

  As you see, there is no “value” element since the cell content is computed by formula.

  The link that you provided me generates following JSON from client-side. Even it is formula based, there is still value calculated by frontEnd spreadJS engine.

  “1”: { “value”: 712, “formula”: “SUM(B1:B3)” }

  Not sure if there is any workaround you can suggest to help us generate PDF from serverside with formulas in many cells.

  Thanks

• 

  jayesh.upadhyay

  • Post Options:
  • Link
    Copy

  Posted 29 December 2017, 2:14 am EST

  So it comes down to one of the possible solution from my point of view:

  1. Is there .SavePDF() available on client side ?
  2. Is there a way for the generated SpreadJS JSON on server side to populate “value” without rendering on client side ?

  Please guide. Thanks

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 2 January 2018, 1:05 am EST

  Hi Jayesh,

  1. SpreadJS v11.0 has this feature of exporting to PDF client side using SavePDF.
     
     http://help.grapecity.com/spread/SpreadSheets11/webframe.html#ExportingToPDF.html

  2. You load the json in hidden spreadJS to calculate the value and theb remove it after getting the JSON.

  Thanks,
  
  Deepak Sharma

• 

  jayesh.upadhyay

  • Post Options:
  • Link
    Copy

  Posted 8 January 2018, 5:30 am EST

  Thanks. It worked.
  
  I am able to use “Microsoft.Office.Interop.Excel”.

  Steps I used were …
  
  convert SpreadJS → Excel →
  
  Import Excel using “Microsoft.Office.Interop.Excel” (this fixes formula issue)
  
  → convert Excel to SpreadJS Json and finally
  
  SpreadJS JSON → PDF

  Thanks for your help.
  
  Appreciated.