Unable to export to xlsx format using Crystal .NET viewer

[dave_smith2]

Using Crystal for VS runtime SP 30, we now see that there are some additional export options available, including .xlsx formats.

When exporting to such a format using the viewer, the file is created but when opening it using Excel, the following warning is displayed:

"We found a problem with some content in [name].xlsx. Do you want us to try and recover as much as we can? If you trust the source of this workbook, click Yes."

When selecting Yes, the workbook opens and an Excel repair dialog is displayed. We viewed the following articles regarding this issue:

https://userapps.support.sap.com/sap/support/knowledge/en/3065649

https://userapps.support.sap.com/sap/support/knowledge/en/3065649

And yet, the problem still occurs if the report is empty so I do not believe the issue is related to embedded content. Has anyone else run into this issue and is there a resolution to it?

Thank you in advance.

[former_member11696 · Accepted solution]

Hi Dave,

I would use ProcessMonnitor and verify your app is loading SP 31 runtime, likely cause is mixed runtime versions.

Could be the CR Viewer is not being loaded correctly.

The case I escalated to DEV they had a .5M file size limit before they got the error. Some dependency seems to be the problem. We are both using Office 365 to open the excel file.

Don

[dave_smith2 · Accepted solution]

We will try that.

What happens if you are using the local, on-premise version of Excel to open the file? That is what we are using.

Office Professional 2019

Excel Version 2201

[former_member11696]

Hi Dave,

Sorry for the delay, I've been busy.

Rename the PDF to *.txt and you should be able to attach the file. you may have to zip and then rename it to *.txt.

I don't have that version of Excel so I'll try it in my 360 version

Don

[former_member11696]

FYI - there seems to be an issue when using this format. I have a thread going with DEV to get them to look into it.

Not your specific issue though but one related to the file size over .5 meg and Excel won't open it without error.

[dave_smith2]

OK, thanks.

We still have not been able to get this to work with .xlsx, even with an empty report devoid of any URLs or non-standard characters.

[former_member11696]

Works fine for my test reports.

Use google and search for this KBA, that could be the issue:

1903666 - Error: 'Excel found unreadable content in <FILE NAME.xlsx> ...' when opening a report exported MS Excel from Crystal Reports

[former_member11696]

Hi Dave,

Still works for me.

KBA for export to CSV is listed above, use Google and search for it.

You need to use RAS to get the history info:

14.3.1.3780: Date: 2022.02.01 11:56:49 -0800

Download my Parameter test app, it's C# but does the same thing.

how-to-parameters-in-crystal-reports-for-visual-studio-net

I don't use VB but your code looks a little off.

See if this works for you:

        Dim rpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument
        Dim RCD As New CrystalDecisions.ReportAppServer.ClientDoc.ReportClientDocument

        rpt.Load("c:\reports\blank.rpt", CrystalDecisions.Shared.OpenReportMethod.OpenReportByTempCopy)
        CrystalReportViewer1.ReportSource = rpt

[dave_smith2]

Thanks Don. We will try out the sample app.

We are not using RAS, but the ReportDocument object model. Does this mean we are unable to access the version history and also manage the export options the way you do?

And would this explain why we are not able to use the xlsx export options and render them as you do?

[dave_smith2]

Hi Don,

Using the ReportDocument object model, we were finally able to get the correct version information and resolved that issue, but we are still struggling with the xlsx output format generating a warning in Excel when exporting from a CR 2020 report.

Is it possible the object model we are using does not behave the same way as RAS when exporting to xlsx?

Are you able to test this out on your end?

[former_member11696]

I just did a quick test with a new report with absolutely nothing in it and it opens fine in Excel., both Data only and Page format. Using the viewer and using the PrintController() API's to export to that format.

The file size is 86K, using Data Only it's 4k.

Only reason I can think of is you do have a URL in the report that may be suppressed when viewing but is not suppressed when exporting.

Look for it in your report and do as the KBA you noted to see if that works.

It works fine for me so it's got to be something environmental or in your report on your side.

If you want I'll send you an e-mail and you can send me your report with saved data and I'll test it?

I would look at your CR Viewer in your app and confirm it is using SP 31. In VS open the ToolBox, right click and Choose Item.... and delete the old viewer and and the new Viewer.

CSV has been escalated:

3107956 - Exporting to CSV from WEB Viewer includes HTML tags in Crystal Reports for Visual Studio SP 30 and SP 31

History info in a drop down box:

for (int x = 0; x < rpt.HistoryInfos.Count; x++)
{
    cbLastSaveHistory.Items.Add(rpt.HistoryInfos[x].BuildVersion.ToString() + ": Date: " + rpt.HistoryInfos[x].SavedDate.ToString());
}

cbLastSaveHistory.SelectedIndex = 0;

[dave_smith2]

sap.png

Hi Don,

We tried it with a completely blank report (see attached hello2020 - please rename to .rpt) and received the same error when exporting to xlsx. hello2020.txt

What about the XML issue? After exporting to XML and attempting to open the file, the following error is generated:

error on line 166 at column 1: Extra content at the end of the document

Regarding the sample code, we dynamically create an object of type CrystalDecisions.CrystalReports.Engine.ReportDocument from the Crystal runtime dll. There is no static reference.

Dim objType As Type = vAssembly.GetType("CrystalDecisions.CrystalReports.Engine.ReportDocument")

Dim doc As Object = Activator.CreateInstance(objType)

Next we load the rpt file into the object as follows:

doc.Load(rptFileName)

When inspecting this object, we find the HistoryInfos property but there are no elements in the array and most properties are null references as in the attached SAP.png image.

We did inspect the entire object and found a version property in CrystalDecisions.Shared.ReportServiceVersionHeader

Name

Value

Type

◢

m_ClientVersionHeader

{CrystalDecisions.Shared.ReportServiceVersionHeader}

CrystalDecisions.Shared.ReportServiceVersionHeader

with the following version property:

Document

Name

Value

Type

version

920

Integer

However, this value was the same for all rpt files, regardless of the Crystal report editor version

[former_member11696]

Yes you can:

// set up the format export types:
int myFOpts = (int)(
    CrystalDecisions.Shared.ViewerExportFormats.RptFormat |
    CrystalDecisions.Shared.ViewerExportFormats.PdfFormat |
    CrystalDecisions.Shared.ViewerExportFormats.RptrFormat |
    CrystalDecisions.Shared.ViewerExportFormats.CsvFormat |
    CrystalDecisions.Shared.ViewerExportFormats.EditableRtfFormat |
    CrystalDecisions.Shared.ViewerExportFormats.RtfFormat |
    CrystalDecisions.Shared.ViewerExportFormats.WordFormat |
    CrystalDecisions.Shared.ViewerExportFormats.XmlFormat |
    CrystalDecisions.Shared.ViewerExportFormats.XLSXPagebasedFormat |
    CrystalDecisions.Shared.ViewerExportFormats.XLSXRecordFormat |
    CrystalDecisions.Shared.ViewerExportFormats.XLSXFormat |
    CrystalDecisions.Shared.ViewerExportFormats.ExcelFormat |
    CrystalDecisions.Shared.ViewerExportFormats.XLSXPagebasedFormat |
    CrystalDecisions.Shared.ViewerExportFormats.XLSXRecordFormat |
    CrystalDecisions.Shared.ViewerExportFormats.ExcelRecordFormat);
//CrystalDecisions.Shared.ViewerExportFormats.NoFormat); // no exports allowed
//int myFOpts = (int)(CrystalDecisions.Shared.ViewerExportFormats.AllFormats);

crystalReportViewer1.AllowedExportFormats = myFOpts;

[dave_smith2]

Thank you.

[dave_smith2]

Hi Don,

We upgraded to SP 31 on the runtime and created a report using Crystal 2020.

We ran the report and rendered to the viewer, then exported to xlsx. We still got the same error when attempting to open the xlsx file in Excel:

"We found a problem with some content in [name].xlsx. Do you want us to try and recover as much as we can? If you trust the source of this workbook, click Yes."

Also when exporting to CSV and XML, there were issues when opening the exported file:

CSV: Column headings were duplicated and a bunch of HTML and JS code was appended to the CSV file:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script type="text/javascript">

XML: This page contains the following errors:

error on line 166 at column 1: Extra content at the end of the document

Any ideas about this?

Finally, we looked at the sample code you provided. Your code does not show how the object "rpt" was obtained in the for loop below:

for (int x = 0; x < rpt.HistoryInfos.Count; x++)

Could you show us how this object is created/obtained? We have been unable to extract the version/history information from the report.

Thank you.

[former_member11696]

Hi Dave,

The new format was added to SP 30 but I suggest upgrading to SP 31 because we only support the latest rlease because we don't patch this product, full builds only:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

Use Google and search for this KBA:

2997876 - Expose XLSX page based format in RAS .NET/Java SDK and CR .NET SDK

Was the report saved with CR 2020?

If not the older RPT files can't use the new format

You can get the Report history this way:

cbLastSaveHistory.Text = "";
try
{
    // Read File Details from CFileInfo Object
    //cbLastSaveHistory.Sorted = true; 

    for (int x = 0; x < rpt.HistoryInfos.Count; x++)
    {
        cbLastSaveHistory.Items.Add(rpt.HistoryInfos[x].BuildVersion.ToString() + ": Date: " + rpt.HistoryInfos[x].SavedDate.ToString());
    }

    cbLastSaveHistory.SelectedIndex = 0; // rpt.HistoryInfos.Count - 1;
                                            //SP 13
                                            //•	RAS .NET SDK 
                                            //ReportClientDocument.HistoryInfos[i].SavedDate
                                            //ReportClientDocument.HistoryInfos[i].BuildVersion

    //•	CR .NET SDK 
    //ReportDocument.HistoryInfos[i].SavedDate
    //ReportDocument.HistoryInfos[i].BuildVersion
}
catch (Exception ex)
{
    //MessageBox.Show("Error: " + ex.Message);
    //MessageBox.Show("This report has no Save history");
    cbLastSaveHistory.Text = "This report has no Save history";
}

Or this way:

MessageBox.Show("Report version: " + rptClientDoc.MajorVersion.ToString() + "." + rptClientDoc.MinorVersion.ToString(), "RAS" ,MessageBoxButtons.OK,MessageBoxIcon.Information );

If the Report version doesn't match 2020 it won't work so add a check to confirm the RPT version.

Don

[dave_smith2]

Thanks Don.

So if I understand you correctly, the report must be saved using CR 2020 before it will work when using the runtime? These reports were saved using CR 2016 and earlier versions.

Is there a way to remove the xlsx export options from the Export dialog in the viewer based on the report history? Right now, users have the option to export to xlsx and will get that error.