Symptom
No option in CR/BOE Viewers or SDK's to select the new Microsoft Excel(XLXS) format introduced in CR 2020 (64bit)
Environment
Reproducing the Issue
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
Cause
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.
Resolution
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
Keywords
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