Export to Excel (Data Only) is slow in Crystal Reports 2013

[former_member541327]

Have anyone here encountered a scenario where exporting to excel (data only) performs slow if compared to Crystal Reports XI?

For quite a number of years, we been using Crystal Reports XI in our product. Last year, I changed this to support Crystal Reports 2013.

The client did an export to excel (data only) for 3K+ records and it is now taking them 10 mins where it only took them 2mins for the same number of records back when we were doing Crystal Reports XI.

Any clue as to why the sudden drop in performance when we integrated Crystal Reports 2013?

Here is a sample coding of how I did the export: (I am using the Export/ExportToDisk() in the ReportDocument class in the Crystal Engine)

Public Sub ExportToDiskFile(ByVal crExportFormatType As ExportFormatType, ByVal crExportFileName As String, _ ByVal toExportFormatOptions As ExportFormatOptions)

If toExportFormatOptions Is Nothing Then

' Export without the Export Option mReportDocument.ExportToDisk(crExportFormatType, crExportFileName)

Else

' Export wit the Export Option

Dim crDiskOpts As DiskFileDestinationOptions crDiskOpts = ExportOptions.CreateDiskFileDestinationOptions()

crDiskOpts.DiskFileName = crExportFileName

Dim crExportOpts As New ExportOptions

With crExportOpts

.ExportFormatType = crExportFormatType

.ExportDestinationType = ExportDestinationType.DiskFile

.ExportFormatOptions = toExportFormatOptions

.ExportDestinationOptions = crDiskOpts

End With

mReportDocument.Export(crExportOpts)

End If

End Sub

[former_member11696 · Accepted solution]

Try using RAS to export, and remember when exportin gthe report is running. If you don't want it to refresh then export to RPT format and then open that report and export to Excel.

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 = newExcelExportFormatOptions();

RasXLSExpOpts = rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatMSExcel);

// textBox1 = "Excel - BaseAreaGroupNumber: " + RasXLSExpOpts.BaseAreaGroupNumber.ToString() + "\n";

//textBox1 += "Excel - BaseAreaType: " + RasXLSExpOpts.BaseAreaType.ToString() + "\n";

//textBox1 += "Excel - FormulaExportPageAreaType: " + RasXLSExpOpts.ExportPageAreaPairType.ToString() + "\n";

//textBox1 += "Excel - ExportPageBreaks: " + RasXLSExpOpts.ExportPageBreaks.ToString() + "\n";

//textBox1 += "Excel - ConstantColWidth: " + RasXLSExpOpts.ConstantColWidth.ToString() + "\n";

//textBox1 += "Excel - ConvertDatesToStrings: " + RasXLSExpOpts.ConvertDatesToStrings.ToString() + "\n";

//textBox1 += "Excel - StartPageNumber: " + RasXLSExpOpts.StartPageNumber.ToString() + "\n";

//textBox1 += "Excel - EndPageNumber: " + RasXLSExpOpts.EndPageNumber.ToString() + "\n";

//textBox1 += "Excel - ExportPageBreaks: " + RasXLSExpOpts.ExportPageBreaks.ToString() + "\n";

//textBox1 += "Excel - MRelativeObjectPosition: " + RasXLSExpOpts.MaintainRelativeObjectPosition.ToString() + "\n";

//textBox1 += "Excel - ShowGridlines: " + RasXLSExpOpts.ShowGridlines.ToString() + "\n";

//textBox1 += "Excel - UseConstantColWidth: " + RasXLSExpOpts.UseConstantColWidth.ToString() + "\n";

// Set them now:

//RasXLSExpOpts.BaseAreaType = CrAreaSectionKindEnum.crAreaSectionKindPageHeader;

//RasXLSExpOpts.UseConstantColWidth = false;

//RasXLSExpOpts.ShowGridlines = false;

//RasXLSExpOpts.StartPageNumber = 3;

//RasXLSExpOpts.EndPageNumber = 10;

// 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 = newDataOnlyExcelExportFormatOptions();

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.BaseAreaType = CrAreaSectionKindEnum.crAreaSectionKindPageHeader;

//RasXLSExpOpts.UseConstantColWidth = false;

//RasXLSExpOpts.ShowGridlines = false;

//RasXLSExpOpts.StartPageNumber = 3;

//RasXLSExpOpts.EndPageNumber = 10;

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

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= newExcelExportFormatOptions();

//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

[former_member11696]

What happens when exporting in CR Designer?

Are you suing the now supported .NET SDK for CR for VS from here:

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

Don

[former_member541327]

Hi Don,

Exporting via CR Designer has no performance issue. It exports it in under 2 mins.

Will exporting via the CR Designer run the ReportDocument.Export() function I referenced from CrystalDecisions.CrystalReports.Engine?

Kent Lucero

[former_member11696]

I can't say for sure, not enough info. It could be in the Report you need to check on Dissociate so the engine doesn't look for the printer the report was designer with. Or depending on the Data source it's using it may be a delay there.

Or it could be subreports or bad formula fields referencing unknown fields.

Or could be just poor report design and needs to be optimized for exporting to Excel.

When opening reports in the SDK it loads all of the report objects, good and bad, check all objects.

Don

[former_member11696]

No, CRD uses C++ to do all of it's functions.

CR for VS uses the framework and CR assemblies to export.