3027152 - Enhancement Request - Expose XLSX page based format in RAS .NET/Java SDK and CR .NET SDK

No option in CR/BOE Viewers or SDK's to select the new Microsoft Excel(XLXS) format introduced in CR 2020 (64bit)

• SAP Crystal Reports Server 2020, OEM edition
• SAP Crystal Reports, version for Microsoft Visual Studio SP 30
• SAP BI PLATFORM SERVERS 4.3 SP002
  

Try to use RAS .Net/Java SDK or CR .Net SDK to export report to pagebased XLSX format, there is no option to select the new CR 2020 format

No corresponding API is available before.

This is an enhancement request. See the details in the "Solution" section below for how to use the new API.

This enhancement is introduced in the patches listed in the "Support Packages & Patches" section below.

Due to different internal processes, the "Support Packages & Patches" section of the SAP Note will be populated as below:

Note: because CRS/BOE/CRSE is gated not adding this to the Product list.

• for fixes delivered in "Support Packages": immediately (even if the "Support Package" is planned to be released several months later)
• for fixes delivered in "Patches":  ONLY once the "Patch" is released AND available on SMP (not before)
• 2997876 - Expose XLSX page based format in RAS .NET/Java SDK and CR .NET SDK

For Business Intelligence Platform maintenance schedule and strategy see the Knowledge Base Article 2144559 in References section.

For Crystal Reports for Visual Studio SP 30 and above.

Update “ReportExportFormat” in RAS Java SDK API

• Add “XLSX” for page based XLSX
• Add “recordToXLSX” for record based XLSX (same as existing “recordToMSExcel2007”)

Update “CrReportExportFormatEnum” in RAS .NET SDK API

• Add “crReportExportFormatPageToXLSX” for page based XLSX
• Add “crReportExportFormatRecordToXLSX” for record based XLSX (same as existing “crReportExportFormatXLSX”)

Update “ExportFormatType” in CR .NET SDK API

• Add “XLSXPagebased” for page based XLSX
• Add “XLSXRecord” for record based XLSX (same to existing “ExcelWorkbook”)

Update “ViewerExportFormats” in CR .NET SDK API

• Add “XLSXPagebasedFormat” for page based XLSX
• Add “XLSXRecordFormat” for record based XLSX (same to existing “XLSXFormat”)

C# Sample code to use these new formats, NOTE: they will only work if the report has been saved in CR 2020 format:

if (ExportTypeSelected == "crReportExportFormatRecordToXLSX") // new in SP30

#region MSRecordExcel

{

    // This works do not alter

    // 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 // test if the reprot is saved in CR 2020

    {

        if ((rpt.HistoryInfos[0].BuildVersion.ToString()) != null)

        {

            if ((rpt.HistoryInfos[0].BuildVersion.ToString()).Substring(0, 4) != "14.3")

            {

                MessageBox.Show("Report must be saved in CR 2020 to support this feature");

                return;

            }

        }

    }

    catch (Exception ex)

    {

        MessageBox.Show("Report must be saved in CR 2020 to support this feature");

        return;

    }

    try

    {

        if (File.Exists(outputFileName))

        {

            File.Delete(outputFileName);

        }

        CrystalDecisions.ReportAppServer.ReportDefModel.DataOnlyExcelExportFormatOptions RasXLSExpOpts = (DataOnlyExcelExportFormatOptions)rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatRecordToXLSX); //new API, introduced from BI 4.3 SP02 And Cortez SP30

        RasXLSExpOpts = rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatRecordToXLSX);

        if (RasXLSExpOpts != null)

        {

            btnReportObjects.Text  = "Excel - BaseAreaGroupNumber:               " + RasXLSExpOpts.BaseAreaGroupNumber.ToString() + "\n";

            btnReportObjects.Text += "Excel - BaseAreaType:                      " + RasXLSExpOpts.BaseAreaType.ToString() + "\n";

            btnReportObjects.Text += "Excel - ConstantColWidth:                  " + RasXLSExpOpts.ConstantColWidth.ToString() + "\n";

            btnReportObjects.Text += "Excel - ExportImages:                      " + RasXLSExpOpts.ExportImages.ToString() + "\n";

            btnReportObjects.Text += "Excel - ExportObjectFormatting:            " + RasXLSExpOpts.ExportObjectFormatting.ToString() + "\n";

            btnReportObjects.Text += "Excel - ExportObjectFormatting:            " + RasXLSExpOpts.ExportObjectFormatting.ToString() + "\n";

            btnReportObjects.Text += "Excel - ExportPageHeaderAndFooter:         " + RasXLSExpOpts.ExportPageHeaderAndFooter.ToString() + "\n";

            btnReportObjects.Text += "Excel - MaintainColumnAlignment:           " + RasXLSExpOpts.MaintainColumnAlignment.ToString() + "\n";

            btnReportObjects.Text += "Excel - MaintainRelativeObjectPosition:    " + RasXLSExpOpts.MaintainRelativeObjectPosition.ToString() + "\n";

            btnReportObjects.Text += "Excel - ShowGroupOutlines:                 " + RasXLSExpOpts.ShowGroupOutlines.ToString() + "\n";

            btnReportObjects.Text += "Excel - SimplifyPageHeaders:               " + RasXLSExpOpts.SimplifyPageHeaders.ToString() + "\n";

            btnReportObjects.Text += "Excel - UseConstantColWidth:               " + RasXLSExpOpts.UseConstantColWidth.ToString() + "\n";

            btnReportObjects.Text += "Excel - UseWorksheetFunctionsForSummaries: " + RasXLSExpOpts.UseWorksheetFunctionsForSummaries+ "\n";

            // Set them now:

            //RasXLSExpOpts.BaseAreaType = CrAreaSectionKindEnum.crAreaSectionKindPageHeader;

            //RasXLSExpOpts.UseConstantColWidth = false;

            //RasXLSExpOpts.ShowGridlines = false;

            //RasXLSExpOpts.StartPageNumber = 3;

            //RasXLSExpOpts.EndPageNumber = 10;

            //RasXLSExpOpts.ExcelTabHasColumnHeadings = true;

            // Save the udpated info

            //rptClientDoc.set_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatMSExcel, RasXLSExpOpts);

        }

        CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions exportOpts1 = new CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions();

        exportOpts1.ExportFormatType = CrReportExportFormatEnum.crReportExportFormatRecordToXLSX;

        exportOpts1.FormatOptions = RasXLSExpOpts;

        // Show start time

        DateTime dtStart;

        TimeSpan difference;

        dtStart = DateTime.Now;

        btnReportObjects.Text += "Report Export Started: " + dtStart + "\r\n";

        // And Export

        rptClientDoc.PrintOutputController.ExportEx(exportOpts1).Save(outputFileName, true);

        difference = DateTime.Now.Subtract(dtStart);

        btnReportObjects.Text += "Report Export Completed in: " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + "\r\n";

        MessageBox.Show("Export to MicrosoftExcel(XLS) Completed", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

    }

    catch (Exception ex)

    {

        btnSQLStatement.Text = "ERROR: " + ex.Message;

        return;

    }

    // This works do not alter

} // new in SP30

#endregion MSRecordExcel

if (ExportTypeSelected == "crReportExportFormatPageToXLSX") // new in SP30

#region MSPageXLSX

{

    // This works do not alter

    // 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 // test if the reprot is saved in CR 2020

    {

        if ((rpt.HistoryInfos[0].BuildVersion.ToString()) != null)

        {

            if ((rpt.HistoryInfos[0].BuildVersion.ToString()).Substring(0, 4) != "14.3")

            {

                MessageBox.Show("Report must be saved in CR 2020 to support this feature");

                return;

            }

        }

    }

    catch (Exception ex)

    {

        MessageBox.Show("Report must be saved in CR 2020 to support this feature");

        return;

    }

    try

    {

        if (File.Exists(outputFileName))

        {

            File.Delete(outputFileName);

        }

        CrystalDecisions.ReportAppServer.ReportDefModel.ExcelExportFormatOptions RASXLXSExportOpts = (ExcelExportFormatOptions)rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatPageToXLSX);

        if (RASXLXSExportOpts != null)

        {

            btnReportObjects.Text  = "Excel - BaseAreaGroupNumber:       " + RASXLXSExportOpts.BaseAreaGroupNumber.ToString() + "\n";

            btnReportObjects.Text += "Excel - BaseAreaType:              " + RASXLXSExportOpts.BaseAreaType.ToString() + "\n";

            btnReportObjects.Text += "Excel - ConstantColWidth:          " + RASXLXSExportOpts.ConstantColWidth.ToString() + "\n";

            btnReportObjects.Text += "Excel - ConvertDatesToStrings:     " + RASXLXSExportOpts.ConvertDatesToStrings.ToString() + "\n";

            btnReportObjects.Text += "Excel - CurrentPageNumber:         " + RASXLXSExportOpts.CurrentPageNumber.ToString() + "\n";

            btnReportObjects.Text += "Excel - EndPageNumber:             " + RASXLXSExportOpts.EndPageNumber.ToString() + "\n";

            btnReportObjects.Text += "Excel - ExcelTabHasColumnHeadings: " + RASXLXSExportOpts.ExcelTabHasColumnHeadings.ToString() + "\n";

            btnReportObjects.Text += "Excel - ExportPageAreaPairType:    " + RASXLXSExportOpts.ExportPageAreaPairType.ToString() + "\n";

            btnReportObjects.Text += "Excel - ExportPageBreaks:          " + RASXLXSExportOpts.ExportPageBreaks.ToString() + "\n";

            btnReportObjects.Text += "Excel - MaintainRelativeObjectPos: " + RASXLXSExportOpts.MaintainRelativeObjectPosition.ToString() + "\n";

            btnReportObjects.Text += "Excel - ShowGridlines:             " + RASXLXSExportOpts.ShowGridlines.ToString() + "\n";

            btnReportObjects.Text += "Excel - StartPageNumber:           " + RASXLXSExportOpts.StartPageNumber.ToString() + "\n";

            btnReportObjects.Text += "Excel - UseConstantColWidth:       " + RASXLXSExportOpts.UseConstantColWidth.ToString() + "\n";

        }

        // Manually set the values

        //RasXLSExpOpts.BaseAreaType = CrAreaSectionKindEnum.crAreaSectionKindPageHeader;

        //RasXLSExpOpts.UseConstantColWidth = false;

        //RasXLSExpOpts.ShowGridlines = false;

        //RasXLSExpOpts.StartPageNumber = 3;

        //RasXLSExpOpts.EndPageNumber = 10;

        //RASXLXSExportOpts.ConstantColWidth = Convert.ToInt32(36.6);

        //RASXLXSExportOpts.ExportObjectFormatting = true;

        //RASXLXSExportOpts.ExportImages = false;

        //RASXLXSExportOpts.UseWorksheetFunctionsForSummaries = false;

        //RASXLXSExportOpts.MaintainRelativeObjectPosition = true;

        //RASXLXSExportOpts.MaintainColumnAlignment = true;

        //RASXLXSExportOpts.ExportPageHeaderAndFooter = false;

        //RASXLXSExportOpts.SimplifyPageHeaders = true;

        //RASXLXSExportOpts.ShowGroupOutlines = false;

        // Save the udpated info

        //rptClientDoc.set_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatPageToXLSX, RASXLXSExportOpts);

        CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions exportOpts1 = new CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions();

        exportOpts1.ExportFormatType = CrReportExportFormatEnum.crReportExportFormatPageToXLSX;

        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;

    }

}// new in SP30

#endregion MSPageXLSX

CR for VS, CR2020, Excel, XLSX, Export, RAS .Net SDK, RAS Java SDK, CR .Net SDK , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , BI-DEV-JAV , BI Software Development Kits (SDKs) - Java , Problem

SAP Crystal Reports, developer version for Microsoft Visual Studio