Formulas with references to a protected sheet not evaluating

Formulas with references to a protected sheet not evaluating

Posted by: lpagliaro on 17 February 2021, 4:21 am EST

• 

  lpagliaro

  • Post Options:
  • Link
    Copy

  Posted 17 February 2021, 4:21 am EST

  Hi! We have an issue with formulas that have a reference to a protected sheet. If we create a workbook using SpreadJS, add a protected sheet with some values and then create a formula with a reference to the cells of the protected sheet; when we load this workbook on the backend using 'Documents for Excel' and calculate the formulas we get a 0 value. Removing the protection gives the correct value. Calculating the formulas should be done even thought the sheet you are referencing to is protected, like Excel does.
  
  ------
  
  Seems that the protection options supported by SpreadJS are not the same as the ones supported by Documents for Excel:
  
  https://www.grapecity.com/spreadjs/docs/v14/online/celllock.html
  https://www.grapecity.com/documents-api-excel/docs/online/GrapeCity.Documents.Excel~GrapeCity.Documents.Excel.IProtectionSettings_members.html
  
  Can we expect to have support for the missing ones in Documents for Excel?
  
  Regards.
• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Replied 17 February 2021, 11:37 pm EST

  Hello,
  
  Sorry, we do not face the issue at our end in getting the calculated values.
  Please see the attached sample for reference and in case if you are doing anything else then please modify the sample accordingly else share your excel file with dummy data so that we can assist you accordingly.
  
  Note: It seems that a similar case is posted by one of you employee on the MySupport portal as well. If yes, I would request you to please continue the thread there only to avoid confusion.
  
  Regards,
  Prabhat Sharma.
  ProtectedDemo.zip
• 

  lpagliaro

  • Post Options:
  • Link
    Copy

  Replied 18 February 2021, 4:23 am EST

  Hi! We went through our code and made some changes and now references seems to work fine. However, we do lose the ability to resize rows and columns in the protected sheet. I attached an example of this, before loading a workbook from JSON (created using SpreadJS) that has a protected sheet with resizing of rows and columns allowed we have this object in the JSON:
  
  

  protectionOptions: { allowResizeRows: true, allowResizeColumns: true }

  
  
  But after it is loaded into 'Documents For Excel', and converted back to JSON we get:
  
  

  protectionOption: { allowSelectLockedCells: true, allowSelectUnlockedCells: true, allowEditObjects: true }

  
  
  As you can see, the options set using SpreadJS are lost.
  
  ProtectedDemo.zip
• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Replied 18 February 2021, 9:15 pm EST

  Hello,
  
  We have escalated this to the developers and will let you know once we get any update on this from their end.
  [Internal Tracking Id: 3772]
  Regards,
  Prabhat Shaarma.

• Marked as Answer

  

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Replied 8 March 2021, 1:41 pm EST

  Hello,
  
  This issue has been fixed in the latest version of GcExcel i.e. 4.0.4.
  Please update it in your project to avoid the issue that you were facing.
  https://www.nuget.org/packages/GrapeCity.Documents.Excel/
  
  Regards,
  Prabhat Sharma.