SAP Knowledge Base Article - Public

3065649 - Error: 'The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt.' when opening a file generated by exporting a report containing hyperlink to MS Excel (XLSX) from Crystal Reports

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

  1. In Crystal Reports 2020, create a report based on any data source.
  2. Format at least one field with a hyperlink.
  3. Export the report to MS Excel (XLSX) format.
  4. 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:
      
    1. In Crystal Reports, open the report.
        
    2. Right click on the field formatted with a hyperlink, and select "Format Field..."
        
    3. In the "Format Editor", select the tab "Hyperlink"
        
    4. For the option "Hyperlink information", click on the "X-2" button.

        Hyperlink - 01.png
        
    5. 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)

         Hyperlink - 02.png
           
    6. Click on"Save and close"
       
    7. Back to the "Format Editor" click "OK" to accept the change.
        
    8. Save the report.

Keywords

 CR, xport , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

SAP Crystal Reports 2020