How to use the new CR 2020 export to Microsoft Excel(XLXS) in Visual Studio .NET code


Purpose

To take advantage of the new export type in your Crystal Reports for Visual Studio from the viewer or from code.

Note: I’m currently using SP 31

Overview

When upgrading existing project to the latest CR for VS runtime there are a few things you need to do after first opening your project. Visual Studio does not do a complete upgrade of the CR Assemblies.

CR for VS minimum Framework version is based on the version noted:

13.0.2000.0 = 2.0 Framework

13.0.3500.0 = 3.5 Framework

13.0.4000.0 = 4.x Framework

With SP 29 and above we recommend using 4.7.2

Note: see this Blog for upgrading your existing project to the latest SP

Some things to add to your existing project to use the new format:

Upgrade Windows Framework and use 4.7.2 or above, it’s a requirement for the current SP 31 for CR for VS runtime.

To check which platform you are compiling in use this:

// show if app is using x86 or x64 runtime

if (Environment.Is64BitProcess)

txtRuntimeVersion.Text = “x64: “;

else

txtRuntimeVersion.Text = “x32: “;

In CR 2020 Designer you see this:

Highlighted one is the page based export to XLSX, second one is data only.

To be able to use these new formats the RPT file must be saved in either CR 2020 or CR for VS SP 31 or above.

To check what version the report has been saved in CR use this code, loads it into a ListBox:

for (int x = 0; x < rpt.HistoryInfos.Count; x++)

{

cbLastSaveHistory.Items.Add(rpt.HistoryInfos[x].BuildVersion.ToString() + “: Date: ” + rpt.HistoryInfos[x].SavedDate.ToString());

}

cbLastSaveHistory.SelectedIndex = 0;

If it doesn’t say 14.3.x it can’t use the new export formats.

If it does have a 14.3.x or 13.0.31 or higher then in the Viewer will have the option for export types like this:

// set up the format export types:

int myFOpts = (int)(

CrystalDecisions.Shared.ViewerExportFormats.RptFormat |

CrystalDecisions.Shared.ViewerExportFormats.PdfFormat |

CrystalDecisions.Shared.ViewerExportFormats.RptrFormat |

CrystalDecisions.Shared.ViewerExportFormats.CsvFormat |

CrystalDecisions.Shared.ViewerExportFormats.EditableRtfFormat |

CrystalDecisions.Shared.ViewerExportFormats.RtfFormat |

CrystalDecisions.Shared.ViewerExportFormats.WordFormat |

CrystalDecisions.Shared.ViewerExportFormats.XmlFormat |

// add if statement to check version and if true then show the next 2 types:

CrystalDecisions.Shared.ViewerExportFormats.XLSXPagebasedFormat | // I believe this is the new one.

CrystalDecisions.Shared.ViewerExportFormats.XLSXRecordFormat | // I believe this is the new one.

CrystalDecisions.Shared.ViewerExportFormats.XLSXFormat |

CrystalDecisions.Shared.ViewerExportFormats.ExcelFormat |

CrystalDecisions.Shared.ViewerExportFormats.ExcelRecordFormat);

//CrystalDecisions.Shared.ViewerExportFormats.NoFormat); // no exports allowed

//int myFOpts = (int)(CrystalDecisions.Shared.ViewerExportFormats.AllFormats);

crystalReportViewer1.AllowedExportFormats = myFOpts;

If you use your own Export button/routine without the viewer they can limit the destination type accordingly.

NOTE: the only way to upgrade the report is to open it in CR Designer or export it to RPT format in code using SP 31 or above, simply saving it in code will work also.

Once saved the history is updated:

The code to export, I used a ListBox to select the output type:

private void lstExportFormatType_SelectedIndexChanged(object sender, EventArgs e)
{ // This gets populated when you click on the export ENUM string ExportTypeSelected = lstExportFormatType.SelectedItem.ToString(); if (ExportTypeSelected == "crReportExportFormatCrystalReports") #region RPT { // 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 - 4)) + "1.rpt"; try { if (File.Exists(outputFileName)) { File.Delete(outputFileName); } CrystalDecisions.ReportAppServer.ReportDefModel.RPTExportFormatOptions RasRPTExpOpts = new RPTExportFormatOptions(); try { //RasRPTExpOpts = rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatCrystalReports); } catch (Exception ex) { btnSQLStatement.Text = "ERROR: " + ex.Message; //return; } // Set them now: //RasPDFExpOpts.CreateBookmarksFromGroupTree = false; //RasPDFExpOpts.EndPageNumber = 1; //RasPDFExpOpts.StartPageNumber = 1; CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions exportOpts1 = new CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions(); exportOpts1.ExportFormatType = CrReportExportFormatEnum.crReportExportFormatCrystalReports; exportOpts1.FormatOptions = RasRPTExpOpts; // And Export rptClientDoc.PrintOutputController.ExportEx(exportOpts1).Save(outputFileName, true); MessageBox.Show("Export to RPT Completed. NOTE: report is *1.RPT", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { btnSQLStatement.Text = "ERROR: " + ex.Message; return; } // This works do not alter } #endregion RPT if (ExportTypeSelected == "crReportExportFormatMSExcel") #region MSExcel { // 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)) + "xls"; try { if (File.Exists(outputFileName)) { File.Delete(outputFileName); } CrystalDecisions.ReportAppServer.ReportDefModel.ExcelExportFormatOptions RasXLSExpOpts = new ExcelExportFormatOptions(); RasXLSExpOpts = rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatMSExcel); btnReportObjects.Text = "Excel - BaseAreaGroupNumber: " + RasXLSExpOpts.BaseAreaGroupNumber.ToString() + "\n"; btnReportObjects.Text += "Excel - BaseAreaType: " + RasXLSExpOpts.BaseAreaType.ToString() + "\n"; btnReportObjects.Text += "Excel - FormulaExportPageAreaType: " + RasXLSExpOpts.ExportPageAreaPairType.ToString() + "\n"; btnReportObjects.Text += "Excel - ExportPageBreaks: " + RasXLSExpOpts.ExportPageBreaks.ToString() + "\n"; btnReportObjects.Text += "Excel - ConstantColWidth: " + RasXLSExpOpts.ConstantColWidth.ToString() + "\n"; btnReportObjects.Text += "Excel - ConvertDatesToStrings: " + RasXLSExpOpts.ConvertDatesToStrings.ToString() + "\n"; btnReportObjects.Text += "Excel - StartPageNumber: " + RasXLSExpOpts.StartPageNumber.ToString() + "\n"; btnReportObjects.Text += "Excel - EndPageNumber: " + RasXLSExpOpts.EndPageNumber.ToString() + "\n"; btnReportObjects.Text += "Excel - ExportPageBreaks: " + RasXLSExpOpts.ExportPageBreaks.ToString() + "\n"; btnReportObjects.Text += "Excel - MRelativeObjectPosition: " + RasXLSExpOpts.MaintainRelativeObjectPosition.ToString() + "\n"; btnReportObjects.Text += "Excel - ShowGridlines: " + RasXLSExpOpts.ShowGridlines.ToString() + "\n"; btnReportObjects.Text += "Excel - UseConstantColWidth: " + RasXLSExpOpts.UseConstantColWidth.ToString() + "\n"; btnReportObjects.Text += "Excel - ExcelTabHasColumnHeadings: " + RasXLSExpOpts.ExcelTabHasColumnHeadings + "\n"; try { RasXLSExpOpts = rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatRecordToMSExcel); } catch (Exception ex) { btnSQLStatement.Text = "ERROR: " + ex.Message; //return; } // 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.crReportExportFormatMSExcel; 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 Excel Completed", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { btnSQLStatement.Text = "ERROR: " + ex.Message; return; } // This works do not alter } #endregion MSExcel if (ExportTypeSelected == "crReportExportFormatXLSX") #region XLSX { // 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 { if (File.Exists(outputFileName)) { File.Delete(outputFileName); } CrystalDecisions.ReportAppServer.ReportDefModel.DataOnlyExcelExportFormatOptions RASXLXSExportOpts = new DataOnlyExcelExportFormatOptions(); RASXLXSExportOpts = (DataOnlyExcelExportFormatOptions)rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatXLSX); //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 - Export Images: " + RASXLXSExportOpts.ExportImages.ToString() + "\n"; //btnReportObjects.Text += "Excel - ExportObject Formatting: " + RASXLXSExportOpts.ExportObjectFormatting.ToString() + "\n"; //btnReportObjects.Text += "Excel - Export Page Header Footer: " + RASXLXSExportOpts.ExportPageHeaderAndFooter.ToString() + "\n"; //btnReportObjects.Text += "Excel - Maintain Column Alignment: " + RASXLXSExportOpts.MaintainColumnAlignment.ToString() + "\n"; //btnReportObjects.Text += "Excel - MaintainRelativeObjectPos: " + RASXLXSExportOpts.MaintainRelativeObjectPosition.ToString() + "\n"; //btnReportObjects.Text += "Excel - ShowGroupOutlines: " + RASXLXSExportOpts.ShowGroupOutlines.ToString() + "\n"; //btnReportObjects.Text += "Excel - SimplifyPageHeaders: " + RASXLXSExportOpts.SimplifyPageHeaders.ToString() + "\n"; //btnReportObjects.Text += "Excel - UseConstantColWidth: " + RASXLXSExportOpts.UseConstantColWidth.ToString() + "\n"; //btnReportObjects.Text += "Excel - UseWorkstFuncForSummaries: " + RASXLXSExportOpts.UseWorksheetFunctionsForSummaries.ToString() + "\n"; // Set them now: //RasXLSExpOpts.BaseAreaType = CrAreaSectionKindEnum.crAreaSectionKindPageHeader; //RasXLSExpOpts.UseConstantColWidth = false; //RasXLSExpOpts.ShowGridlines = false; //RasXLSExpOpts.StartPageNumber = 3; //RasXLSExpOpts.EndPageNumber = 10; try { RASXLXSExportOpts = rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatXLSX); } catch (Exception ex) { btnSQLStatement.Text = "ERROR: " + ex.Message; //return; } // 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; // Manually set the values //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; CrystalDecisions.ReportAppServer.ReportDefModel.ExcelExportFormatOptions ExpXLXSOpts = new ExcelExportFormatOptions(); //ExpXLXSOpts.ConstantColWidth = 45; //ExpXLXSOpts.ConvertDatesToStrings = true; exportOpts1.ExportOptionsEx = null; // 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; } } #endregion XLSX 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

Batch Processing All reports:

So you can do a batch open/save as to update the reports:

Note: this can be added to the Parameter or Printer test app’s on the WIKI page:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

Add a button called: OpenAll and paste in the code below.

NOTE: not all routines are required, shows an example also if you want to update or get more info etc.

private void btrOpenAll_Click(object sender, EventArgs e)
{ rptClientDoc = new CrystalDecisions.ReportAppServer.ClientDoc.ReportClientDocument(); // ReportClientDocumentClass(); using (var dialog = new System.Windows.Forms.FolderBrowserDialog()) { DateTime dtStart; TimeSpan difference; System.Windows.Forms.DialogResult result = dialog.ShowDialog(); string selectedFolder = @"C:\"; if (result == System.Windows.Forms.DialogResult.OK) { selectedFolder = dialog.SelectedPath; } OpenFileDialog openFileDialog1 = new OpenFileDialog(); openFileDialog1.InitialDirectory = selectedFolder; DirectoryInfo di = new DirectoryInfo(selectedFolder); FileInfo[] rptFiles = di.GetFiles("*.rpt"); int flcnt1 = 0; foreach (object rptName in rptFiles) { dtStart = DateTime.Now; flcnt1++; btnCount.Text = flcnt1.ToString(); try { rpt.Load(di.FullName + "\\" + rptName.ToString(), OpenReportMethod.OpenReportByTempCopy); difference = DateTime.Now.Subtract(dtStart); btnReportObjects.Text += "Report Document Load: " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + " - " + rptName.ToString(); rptClientDoc = rpt.ReportClientDocument; } catch (Exception ex) { btnReportObjects.Text = "Error opening: " + rptName.ToString() + "\n"; } // now do what ever to each report // check if the report is based on a Command and if so then display the SQL. This causes a huge delay opening report //btnReportObjects.AppendText(""); dtStart = DateTime.Now; try { int dbConCount1 = rptClientDoc.DatabaseController.GetConnectionInfos().Count; } catch (Exception ex) { btnReportObjects.Text += "\nError connectionInfo: " + rptName.ToString() + "\n"; rpt.Close(); return; } int dbConCount = rptClientDoc.DatabaseController.GetConnectionInfos().Count; difference = DateTime.Now.Subtract(dtStart); //btnReportObjects.Text += "GetConnectionInfos().Count took: " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + "\r\n"; // get the DB name from the report CrystalDecisions.CrystalReports.Engine.Database crDatabase; CrystalDecisions.CrystalReports.Engine.Tables crTables; crDatabase = rpt.Database; crTables = crDatabase.Tables; int dbx = 0; String DBDriver = ""; for (int x = 0; x < dbConCount; x++) { try { if (((dynamic)rptClientDoc.Database.Tables[0].Name) == "Command") { CrystalDecisions.ReportAppServer.Controllers.DatabaseController databaseController = rpt.ReportClientDocument.DatabaseController; ISCRTable oldTable = (ISCRTable)databaseController.Database.Tables[0]; btnReportObjects.Text += "Yes \n" + ((dynamic)oldTable).CommandText.ToString(); btnReportObjects.Text += "\n"; IsLoggedOn = false; IsCMD = true; } if (DBDriver.ToString() == "crdb_bwmdx.dll") IsBEX = true; } catch (Exception ex) { //btnDBDriver.Text = "ERROR: " + ex.Message; //btnDBDriver.Text += "Main Report has no Data Driver"; } } //get the subreport connection infos string SecName = ""; CrystalDecisions.CrystalReports.Engine.ReportObjects crReportObjects; CrystalDecisions.CrystalReports.Engine.SubreportObject crSubreportObject; CrystalDecisions.CrystalReports.Engine.ReportDocument crSubreportDocument; //set the crSections object to the current report's sections CrystalDecisions.CrystalReports.Engine.Sections crSections = rpt.ReportDefinition.Sections; int flcnt = 0; //loop through all the sections to find all the report objects foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections) { crReportObjects = crSection.ReportObjects; //loop through all the report objects to find all the subreports foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects) { if (crReportObject.Kind == ReportObjectKind.SubreportObject) { try { ++flcnt; btnCount.Text = flcnt.ToString(); dbx = 0; //you will need to typecast the reportobject to a subreport //object once you find it crSubreportObject = (CrystalDecisions.CrystalReports.Engine.SubreportObject)crReportObject; crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName); SubreportClientDocument subRCD = rptClientDoc.SubreportController.GetSubreport(crSubreportObject.SubreportName); string mysubname = crSubreportObject.SubreportName.ToString(); try { CrystalDecisions.Shared.ConnectionInfo crSubConnectioninfo = new CrystalDecisions.Shared.ConnectionInfo(); //btnReportObjects.Text += "\n\nSubReport Table count: " + subRCD.DatabaseController.Database.Tables.Count.ToString(); // get the DB names from the subreport //crDatabase = subRCD.DatabaseController.Database; //crTables = crDatabase.Tables; if (subRCD.DatabaseController.Database.Tables.Count != 0) { foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table crTable in subRCD.DatabaseController.Database.Tables) { try { // Subreport is using a Command so use RAS to get the SQL btnDBDriver.Text += DBDriver + " :"; if (((dynamic)crTable.Name) == "Command") { CrystalDecisions.ReportAppServer.Controllers.DatabaseController databaseController = subRCD.DatabaseController; CommandTable SuboldTable = (CommandTable)databaseController.Database.Tables[0]; btnReportObjects.Text += "SubReport is using Command: \n" + ((dynamic)SuboldTable).CommandText.ToString(); btnReportObjects.Text += "\n"; IsLoggedOn = false; IsCMD = true; } if (DBDriver.ToString() == "crdb_bwmdx.dll") IsBEX = true; } catch (Exception ex) { //btnDBDriver.Text += "ERROR: " + ex.Message; btnDBDriver.Text += "Main Report has no Data Driver"; } } } else { try { btnReportObjects.Text += "\nSubreport: " + subRCD.Name.ToString() + ": Has no Data Source\n"; } catch (Exception ex) { //btnDBDriver.Text += "ERROR: " + ex.Message; btnReportObjects.Text += "Error: "; } } } catch (Exception ex) { btnReportObjects.Text += "SubreportName: " + subRCD.Name + "\n"; btnReportObjects.Update(); btnReportObjects.AppendText("Error in " + SecName + " : " + ex.Message + "\n\n"); } break; } catch (Exception ex) { btnReportObjects.AppendText("Error in " + SecName + " : " + ex.Message + "\n"); } } } } btnReportObjects.AppendText("\n"); rpt.Close(); btnReportObjects.ScrollToCaret(); } } MessageBox.Show("Done");
}