Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 23, 2017 at 09:21 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 17, 2017 at 05:19 PM

    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

    Add comment
    10|10000 characters needed characters exceeded