Using VB and the SDK to export a report into Excel format

[Former Member]

I am using VB and the SDK to export a Crytal Report.

Here is the code I am attempting to use:

Dim rd As New CrystalDecisions.CrystalReports.Engine.ReportDocument()

rd.load("myreport.rpt")

Dim rcd As ISCDReportClientDocument = rd.ReportClientDocument

Dim myBytes As ByteArray

If strExpType = "14" Then ' word format

myBytes = rcd.PrintOutputController.Export(CrReportExportFormatEnum.crReportExportFormatMSWord)

ElseIf strExpType = "30" Or strExpType = "27" Then ' excel format

myBytes = rcd.PrintOutputController.Export(CrReportExportFormatEnum.crReportExportFormatMSExcel)

Else ' PDF Format

myBytes = rcd.PrintOutputController.Export(CrReportExportFormatEnum.crReportExportFormatPDF)

End If

When I set the strExpType to 14, I successfullly create a MS Word document. When I set the StrExpType to 1, I successfully create a PDF.

However, when I set the strExpType to 30 (or 27), I end up with an empty ByteArray.

I have not been able to capture an error message, I just get an empty ByteArray.

Is there a trick to getting an export to Excel format to work?

[Former Member · Accepted solution]

I tried using the code in the supplied example, and I am still unable to export to Excel.

I am getting an output file, but it is 0 bytes in size (empty file).

If I change the export type to crReportExportFormatTabSeparatedText, then I get a file, an ugly file, but it does have the data.

Here is the code as it looks now

01 Dim myBytes As ByteArray

02 If strExpType = "14" Then ' word format

03 myBytes = rcd.PrintOutputController.Export(CrReportExportFormatEnum.crReportExportFormatMSWord)

04 ElseIf strExpType = "30" Or strExpType = "27" Then ' excel format

05 Dim exportOptions As New CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions

06 exportOptions.ExportFormatType = CrReportExportFormatEnum.crReportExportFormatMSExcel

07 Dim ExcelexportOptions As New ExcelExportFormatOptions

08 ExcelexportOptions.BaseAreaGroupNumber = 1

09 ExcelexportOptions.BaseAreaType = CrAreaSectionKindEnum.crAreaSectionKindReportHeader

10 ExcelexportOptions.ConstantColWidth = 20

11 ExcelexportOptions.ConvertDatesToStrings = True

12 ExcelexportOptions.StartPageNumber = 1

13 ExcelexportOptions.EndPageNumber = 5

14 ExcelexportOptions.ExcelTabHasColumnHeadings = True

15 ExcelexportOptions.ExportPageAreaPairType = CrExportPageAreaKindEnum.crExportPageAreaKindForEachPage

16 ExcelexportOptions.ExportPageBreaks = True

17 ExcelexportOptions.MaintainRelativeObjectPosition = True

18 ExcelexportOptions.ShowGridlines = False

19 ExcelexportOptions.UseConstantColWidth = True

20

21 exportOptions.FormatOptions = ExcelexportOptions

22

23 myBytes = rcd.PrintOutputController.ExportEx(exportOptions)

24 Else ' PDF Format

25 myBytes = rcd.PrintOutputController.Export(CrReportExportFormatEnum.crReportExportFormatPDF)

26 End If

Again, changing line 06 to

exportOptions.ExportFormatType = CrReportExportFormatEnum.crReportExportFormatTabSeparatedText

produces results.

I am using Crystal Reports 2008 v1

[Former Member · Accepted solution]

Hi, Mark;

I've tried here with a simple sample report, and it works fine for me. I'm using Crystal Reports 2008 with Service Pack 1.

Here is the simple code I used:

Dim myBytes As Byte()

Dim exportOptions As New CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions

exportOptions.ExportFormatType = CrystalDecisions.ReportAppServer.ReportDefModel.CrReportExportFormatEnum.crReportExportFormatMSExcel

Dim excelExportOptions As New CrystalDecisions.ReportAppServer.ReportDefModel.ExcelExportFormatOptions

With excelExportOptions

.UseConstantColWidth = True

End With

exportOptions.FormatOptions = excelExportOptions

myBytes = (myReportClientDocument.PrintOutputController.ExportEx(exportOptions).ByteArray)

Response.ClearContent()

Response.ClearHeaders()

Response.ContentType = "application/vnd.ms-excel"

Response.AddHeader("Content-disposition", "finename=myReport.xls")

Response.BinaryWrite(myBytes)

Response.End()

Regards,

Jonathan

[Former Member]

Hi, Mark;

You may need to use the ExcelExportOptions for an Excel file.

Have a look at the sample here, and see if the code within helps:

http://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/b01c0a37-9e3b-2c10-4aa7-f0f31472367f

Regards,

Jonathan