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