SAP Knowledge Base Article - Public

2181164 - Get Saved XLSX Export options generates an exception in CR for VS .NET

Symptom

At this time when getting the Saved Excel Workbook options .NET API generates and exception:

CrystalDecisions.ReportAppServer.ReportDefModel.CrReportExportFormatEnum.crReportExportFormatXLSX

The work flow is to save the Export options in the Report file by editing the report as follows:

Click on File, Export, Report Export Options and selecting "MS Excel Workbook Data only".

 

Environment

Crystal Reports for Visual Studio

Reproducing the Issue

To get the Saved Export options:

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.CrReportExportFormatEnum.crReportExportFormatXLSX;

        //CrystalDecisions.ReportAppServer.ReportDefModel.ex RasXLSExpOpts = new ExcelExportFormatOptions();

 // throws exception here
        RASXLXSExportOpts = rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatXLSX);
 

        // Save the udpated info
        //rptClientDoc.set_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatXLSX, RASXLXSExportOpts);

        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;
    }
}

 

Resolution

This export format type uses the XLS collection of properties to use when exporting to Workbook.

The following code snippet is how to.

Note: you can update the Saved Options and also note if no options are saved this will generate an exception, simply ignore it:

// 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.DataOnlyExcelExportFormatOptions RASXLXSExportOpts = new DataOnlyExcelExportFormatOptions();
    RASXLXSExportOpts = (DataOnlyExcelExportFormatOptions)rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatXLSX);

    //textBox1 = "Excel - BaseAreaGroupNumber:       " + RASXLXSExportOpts.BaseAreaGroupNumber.ToString() + "\n";
    //textBox1 += "Excel - BaseAreaType:              " + RASXLXSExportOpts.BaseAreaType.ToString() + "\n";
    //textBox1 += "Excel - ConstantColWidth: " + RASXLXSExportOpts.ConstantColWidth.ToString() + "\n";
    //textBox1 += "Excel - ExportImages:          " + RASXLXSExportOpts.ExportImages.ToString() + "\n";
    //textBox1 += "Excel - ExportObjectFormatting:     " + RASXLXSExportOpts.ExportObjectFormatting.ToString() + "\n";
    //textBox1 += "Excel - ExportPageHeaderAndFooter:           " + RASXLXSExportOpts.ExportPageHeaderAndFooter.ToString() + "\n";
    //textBox1 += "Excel - MaintainColumnAlignment:             " + RASXLXSExportOpts.MaintainColumnAlignment.ToString() + "\n";
    //textBox1 += "Excel - MaintainRelativeObjectPosition:          " + RASXLXSExportOpts.MaintainRelativeObjectPosition.ToString() + "\n";
    //textBox1 += "Excel - ShowGroupOutlines:   " + RASXLXSExportOpts.ShowGroupOutlines.ToString() + "\n";
    //textBox1 += "Excel - SimplifyPageHeaders:             " + RASXLXSExportOpts.SimplifyPageHeaders.ToString() + "\n";
    //textBox1 += "Excel - UseConstantColWidth:       " + RASXLXSExportOpts.UseConstantColWidth.ToString() + "\n";
    //textBox1 += "Excel - UseWorksheetFunctionsForSummaries:       " + RASXLXSExportOpts.UseWorksheetFunctionsForSummaries.ToString() + "\n";

    // Set them now:
    //RasXLSExpOpts.UseConstantColWidth = false;
    //...

    // Save the udpated info
    rptClientDoc.set_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatXLSX, RASXLXSExportOpts);

    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;
}

Keywords

Crystal Reports for Visual Studio, XLSX, Get Saved Export Options , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

Product

SAP Crystal Reports, developer version for Microsoft Visual Studio