1645531 - Export to Excel (2007 - 2010) - ExcelWorkbook (XLSX) loses format style

SAP Knowledge Base Article - Public

1645531 - Export to Excel (2007 - 2010) - ExcelWorkbook (XLSX) loses format style

Symptom

  • Exporting to Excel (2007 - 2010) from a VS2010 application results in output equivalent to Excel (97 - 2003) Data only 
  • Required output is the same as when exporting to Excel (97 - 2003):

 

 

Environment

  • Crystal Reports for Visual Studio 2010 (CRVS2010)
  • VS2010

Reproducing the Issue

  • Use the following code in a VS2010 application using CRVS2010
 InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.
        Dim crReportDocument As New ReportDocument
        Dim crExportOptions As ExportOptions
        Dim crDiskFileDestinationOptions As DiskFileDestinationOptions
        crReportDocument.Load("C:\Crystal\formulas.rpt")
        crDiskFileDestinationOptions = New DiskFileDestinationOptions()
        crDiskFileDestinationOptions.DiskFileName = "C:\Crystal\excelworkbook.xlsx"

        ''Set the exporting information
        crExportOptions = crReportDocument.ExportOptions
        With crExportOptions
            .DestinationOptions = crDiskFileDestinationOptions
            .ExportDestinationType = ExportDestinationType.DiskFile
            .ExportFormatType = ExportFormatType.ExcelWorkbook
        End With
        ''Export the report
        crReportDocument.Export()
        MessageBox.Show("done export")

Cause

Resolution

To use RAS to export here is the code:

if (ExportTypeSelected == "crReportExportFormatXLSX")
{
    // this gets the report name and sets the export name to be the same less the extension
    string outputFileName = "";
    string MyRptName = rpt.FileName.ToString();
    outputFileName = MyRptName.Substring(9, rpt.FileName.Length - 9);
    outputFileName = outputFileName.Substring(0, (outputFileName.Length - 3)) + "xlsx";

    try
    {
        if (File.Exists(outputFileName))
        {
            File.Delete(outputFileName);
        }

        CrystalDecisions.ReportAppServer.ReportDefModel.CrReportExportFormatEnum RASXLXSExportOpts = CrystalDecisions.ReportAppServer.ReportDefModel.CrReportExportFormatEnum.crReportExportFormatXLSX;

        CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions exportOpts1 = new CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions();
        exportOpts1.ExportFormatType = CrReportExportFormatEnum.crReportExportFormatXLSX;
        exportOpts1.FormatOptions = RASXLXSExportOpts;

        // And Export
        rptClientDoc.PrintOutputController.ExportEx(exportOpts1).Save(outputFileName, true);
        MessageBox.Show("Export to Excel XLXS Completed", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (Exception ex)
    {
        btnSQLStatement.Text = "ERROR: " + ex.Message;
        return;
    }
}

Note: There is no options available for this export format and it does not use the saved Report Export Options... in the menu Export feature.

Asking R&D if we can enhance this API.

Keywords

workbook work book CR2010 2010 CR4VS2010 CR4VS VS2010 NET2010 Crystal Reports for .NET Framework 4.0 crforvs 13 retain lost , KBA , BI-DEV-NET-SDK , .NET SDK / COM SDK , How To

Product

SAP Crystal Reports, developer version for Microsoft Visual Studio