Symptom
- Unable to open a spreadsheet in MS Excel.
- Error in MS Excel, when opening a report exported to MS Excel (XLSX) format, from Crystal Reports.
- When opening a file in MS Excel, that was generated by exporting a report containing hyperlinks, to MS Excel (XLSX), from Crystal Reports, it generates the error:
"Microsoft Excel
We found a problem with some content in <file name.xlsx>. Do you want us to try recover as much as we can?
If you trust the source of this workbook, click Yes”
And when clicking "Yes", it displays the error:
"The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt."
- Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.
Environment
- SAP Crystal Reports 2020
- MS Excel 2013
- MS Excel 2016
- MS Excel 2019 ( Office 365 )
Reproducing the Issue
- In Crystal Reports 2020, create a report based on any data source.
- Format at least one field with a hyperlink.
- Export the report to MS Excel (XLSX) format.
- In MS Excel, open the generated XLSX file, and it generates an error the file is corrupted.
Cause
- At least one field on the report is formatted with a hyperlink that contain the special character: "&", which is not accepted in Microsoft Excel (XLSX) file format.
Resolution
- Use the function "urlencode" forthe hyperlink, to substitute the special characters to an acceptable url values:
- In Crystal Reports, open the report.
- Right click on the field formatted with a hyperlink, and select "Format Field..."
- In the "Format Editor", select the tab "Hyperlink"
- For the option "Hyperlink information", click on the "X-2" button.
- Type the function "urlencode", then add parenthesis around the hyperlink url that contains the special character "&", and add ",1" right before the closing parenthesis like:
urlencode("http://sap&crystalreports.com",1)
- Click on"Save and close"
- Back to the "Format Editor" click "OK" to accept the change.
- Save the report.
Keywords
CR, xport , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
SAP Crystal Reports 2020