A Way to refresh data in spreadsheet before converting to PDF

[Daniel Roberts]

Hi,

I have an excel based form that is passed through our product data management system collecting signatures and approvals. There are two worksheets, worksheet(0) is a printable form with most of the sheet protected to maintain format, the protected cells are populated with formulas such as "=DATA!A4", worksheet(1) is named "DATA" and is hidden and is where all the information is aggregated.  These signatures are passed to the spreadsheet through the use of custom properties that are "linked to content" - attached to a named range. I want to convert this excel form to PDF at the end of its approval cycle. I have two problems:

1. When saving the .xlsm file with XLSIO it removes the content links on the custom properties. All the custom properties are still there but their source links are gone.

2. Even if I update the content through code right before the PDF convert worksheet(0) the printable form will not update with the correct data.

[Ishwarya Narayanan]

Hi Daniel,

 

Thank you for contacting Syncfusion support.

 

Queries	Answer
1. When saving the .xlsm file with XLSIO it removes the content links on the custom properties. All the custom properties are still there but their source links   are gone.	Thank you for helping us define this feature. Currently we don’t have a support for content links in custom properties with XlsIO. We have added it to our feature request list, and we will update you once implemented.
2. Even if I update the content through code right before the PDF convert worksheet(0) the printable form will not update with the correct data.	We are unable to reproduce the issue with the information you have shared. However we have prepared a simple sample in the following link as per your scenario. Kindly modify the sample with input file to reproduce the issue and share us to investigate further on this.   Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/XlsIO_Sample545520307

 

Regards,

Ishwarya N

[Daniel Roberts]

Ishwarya,

Thank you for the reply . I look forward to the update that will maintain the content links. On the second problem I have found a workaround. I found that if I loop through the cells that need to be updated and access their Worksheet.Range.CalculatedValue this will update the cells and I can then convert to PDF. (EX: stringTmp = worksheet.Range("A1").CalculatedValue ) I don't use the string for anything but it forces the cell to update. The fact that calculated cells won't update unless their values are accessed should really be listed more prominently somewhere in the documentation. I had to dig through the forums for several hours and finally found a comment on a post about a pivot table that provided a clue.

[Ishwarya Narayanan]

Hi Daniel,

 

Thank you for updating us.

 

We will update you regarding support for content links in custom properties with XlsIO once implemented.

 

Regarding second query, It is not necessary to invoke CalculatedValue property to refresh the updated value. We had such issue in our older versions. So we recommend you to check the scenario with the latest version (13.4.0.58). If you are still facing the issue after checking with latest version, kindly update the file which you trying to reproduce the issue at your end along with your working version to provide prompt solution at the earliest.  

 

Regards,

Ishwarya N