issue in exporting crystal report to excel on server

[yasinirshad]

Hi,
Am getting this error
"System.NullReferenceException: Object reference not set to an instance of an object."

This error comes when i am trying to export crystal report in excel format.
Exporting to pdf works fine though.

On local development machine, exporting to pdf and excel works.
On server, only exporting to pdf works..exporting to excel throws this error.

can anyone pls help. Below is my code sample for export button click.

Private Sub Btn_Export_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Export.Click
        Try
            Dim oStream As New MemoryStream
            Me.Crystalreportviewer1.ParameterFieldInfo.Clear()
            Me.Crystalreportviewer1.ReportSource = Server.MapPath("Capex_Summary.rpt")
            Dim ParamFields As ParameterFields = Me.Crystalreportviewer1.ParameterFieldInfo
            Dim Per As New ParameterField
            Per.ParameterFieldName = "Period"
            Dim ParamCurrentValues As New ParameterValues
            Dim Period_Value As New ParameterRangeValue
            Period_Value.StartValue = Me.txtSTdate.Text
            Period_Value.EndValue = Me.txtCLdate.Text
            ParamCurrentValues.Add(Period_Value)
            ParamFields.Add(Per)
            crReportDocument.DataDefinition.ParameterFields(0).ApplyCurrentValues(ParamCurrentValues)

            'Set Division Paramter                
            Dim GetDivisionValues As String
            GetDivisionValues = Trim(Request.Form(ddl_division.UniqueID))
            Per = New ParameterField
            ParamCurrentValues = New ParameterValues
            Per.ParameterFieldName = "Division"
            Dim Division_Value As New ParameterDiscreteValue
            If GetDivisionValues = "Select All" Then
                Division_Value.Value = "*"
            Else
                Division_Value.Value = GetDivisionValues
            End If
            'Per.CurrentValues.Add(Division_Value)
            ParamCurrentValues.Add(Division_Value)
            ParamFields.Add(Per)
            crReportDocument.DataDefinition.ParameterFields(1).ApplyCurrentValues(ParamCurrentValues)

            'Set Department Parameter
            Dim GetDeptValues As String
            GetDeptValues = Trim(Request.Form(ddl_department.UniqueID))
            Per = New ParameterField
            ParamCurrentValues = New ParameterValues
            Per.ParameterFieldName = "Department"
            Dim Department_Value As New ParameterDiscreteValue
            If GetDeptValues = "Select All" Then
                Department_Value.Value = "*"
            Else
                Department_Value.Value = GetDeptValues
            End If
            'Per.CurrentValues.Add(Department_Value)
            ParamCurrentValues.Add(Department_Value)
            ParamFields.Add(Per)
            crReportDocument.DataDefinition.ParameterFields(2).ApplyCurrentValues(ParamCurrentValues)

            'Category
            Dim GetCategory As String
            GetCategory = Trim(Request.Form(ddl_Category.UniqueID))
            Per = New ParameterField
            ParamCurrentValues = New ParameterValues
            Per.ParameterFieldName = "Category"
            Dim Category_Value As New ParameterDiscreteValue
            If GetCategory = "Select All" Then
                Category_Value.Value = "*"
            Else
                Category_Value.Value = GetCategory
            End If
            'Per.CurrentValues.Add(Category_Value)
            ParamCurrentValues.Add(Category_Value)
            ParamFields.Add(Per)
            crReportDocument.DataDefinition.ParameterFields(3).ApplyCurrentValues(ParamCurrentValues)
            'crReportDocument.SetDatabaseLogon(gbVariables.strUsrName, gbVariables.strUsrPwd, gbVariables.strServerName, gbVariables.strDBName)
            Dim ExportFormatType As String
            ExportFormatType = Trim(Request.Form(ddl_export.UniqueID))
            Select Case ExportFormatType
                Case "Portable Document (PDF)"
                    crReportDocument.SetDatabaseLogon(gbVariables.strUsrName, gbVariables.strUsrPwd, gbVariables.strServerName, gbVariables.strDBName)
                    'oStream = crReportDocument.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat)
                    oStream = DirectCast(crReportDocument.ExportToStream(CrystalDecisions.[Shared].ExportFormatType.PortableDocFormat), MemoryStream)
                    Response.Clear()
                    Response.Buffer = True
                    Response.ContentType = "application/pdf"
                    Response.BinaryWrite(oStream.ToArray())
                    Response.End()
                Case "MS Excel (XLS)"
                    crReportDocument.SetDatabaseLogon(gbVariables.strUsrName, gbVariables.strUsrPwd, gbVariables.strServerName, gbVariables.strDBName)
                    'oStream = crReportDocument.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel)
                    oStream = DirectCast(crReportDocument.ExportToStream(CrystalDecisions.[Shared].ExportFormatType.Excel), MemoryStream)
                    Response.Clear()
                    Response.Buffer = True
                    Response.ContentType = "application/vnd.ms-excel"
                    Response.BinaryWrite(oStream.ToArray())
                    Response.End()                    
            End Select            
        Catch ex As Exception
            lblError.Text = ex.Message.ToString
        End Try

Thanks to help.

[gever]

didnt find any mistakes in ur code

u just try this code

Dim CrFormatTypeOptions As New ExcelFormatOptions
            CrDiskFileDestinationOptions.DiskFileName = _
                                        "c:\crystalExport.xls"
            CrExportOptions = cryRpt.ExportOptions
            With CrExportOptions
                .ExportDestinationType = ExportDestinationType.DiskFile
                .ExportFormatType = ExportFormatType.Excel
                .DestinationOptions = CrDiskFileDestinationOptions
                .FormatOptions = CrFormatTypeOptions
            End With
            cryRpt.Export()

http://vb.net-informations.com/crystal-report/vb.net_crystal_report_export_excel.htm

gever

[guytang]

I am getting the same error with the following code:

Function Execute_Report_Crystal_Export(ByVal rptAssemblyPath As String, ByVal rptAssemblyName As String, ByVal rptObjName As String, ByVal ProcessDS As DataSet, ByVal SourceDS As DataSet, ByVal RptSubDS As DataSet, ByVal Path As String, ByVal FileName As String, ByVal ExpTyp As String) As Integer
        Dim crReportDocument As ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument
        Dim rptParam As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition
        Dim strExportFile As String
        Dim ex As Exception
        Dim oReflection As Reflection.Assembly
        Dim oEnum As System.Collections.IEnumerator
        Dim oType As Type
        Dim fullName As String
        Dim i As Integer

        Try

            If Not rptAssemblyPath Is Nothing Then
                If Not Right(rptAssemblyPath, 1) = "\" Then
                    rptAssemblyPath &= "\"
                End If
            Else
                Throw ex
            End If

            If Not rptAssemblyName Is Nothing Then
                If Not Right(rptAssemblyName, 4) = ".dll" Then
                    rptAssemblyName &= ".dll"
                End If
            Else
                Throw ex
            End If
            If System.IO.File.Exists(rptAssemblyPath & rptAssemblyName) Then
                oReflection = Reflection.Assembly.LoadFrom(rptAssemblyPath & rptAssemblyName)
                oEnum = oReflection.GetTypes.GetEnumerator
                fullName = ""

                While oEnum.MoveNext
                    oType = oEnum.Current()
                    If oType.Name.ToUpper = rptObjName.ToUpper Then
                        fullName = oType.FullName
                        Exit While
                    End If
                End While

                If fullName = "" Then
                    Throw ex
                End If

                oType = oReflection.GetType(fullName)
                crReportDocument = Activator.CreateInstance(oType)
            Else
                Throw ex
            End If

            If ProcessDS.Tables.Count = 1 And SourceDS.Tables.Count = 1 And ProcessDS.Tables(0).Rows.Count = 1 Then

                crReportDocument.SetDataSource(SourceDS.Tables(0))

                'Set datasource for subreports
                For i = 0 To (RptSubDS.Tables.Count - 1)
                    crReportDocument.OpenSubreport(RptSubDS.Tables(i).TableName).SetDataSource(RptSubDS.Tables(i))
                Next

                oEnum = crReportDocument.DataDefinition.ParameterFields.GetEnumerator

                While oEnum.MoveNext
                    rptParam = oEnum.Current
                    If rptParam.IsLinked = False Then
                        If ProcessDS.Tables(0).Columns.Contains(rptParam.Name) Then
                            If Not ProcessDS.Tables(0).Rows(0).Item(rptParam.Name) Is DBNull.Value Then
                                crReportDocument.SetParameterValue(rptParam.Name, ProcessDS.Tables(0).Rows(0).Item(rptParam.Name))
                            Else
                                crReportDocument.SetParameterValue(rptParam.Name, "")
                            End If
                        Else
                            crReportDocument.SetParameterValue(rptParam.Name, "")
                        End If
                    End If
                End While
            Else
                Throw ex
            End If

            Dim objExOpt As New ExportOptions
            objExOpt = crReportDocument.ExportOptions
            Dim objDiskOpt As DiskFileDestinationOptions
            objDiskOpt = ExportOptions.CreateDiskFileDestinationOptions()

            strExportFile = Path & FileName


            objDiskOpt.DiskFileName = strExportFile
            Dim pdfRtfWordFormatOptions = CrystalDecisions.Shared.ExportOptions.CreatePdfRtfWordFormatOptions
            objExOpt.ExportFormatOptions = pdfRtfWordFormatOptions


            Select Case ExpTyp
                Case CODE_OUTTYP_PDF
                    objExOpt.ExportFormatType = ExportFormatType.PortableDocFormat
                Case CODE_OUTTYP_EXCEL_CRYSTAL
                    objExOpt.ExportFormatType = ExportFormatType.Excel
                    Dim CrFormatTypeOptions As New ExcelFormatOptions
                    objExOpt.FormatOptions = CrFormatTypeOptions
                Case CODE_OUTTYP_HTML
                    objExOpt.ExportFormatType = ExportFormatType.HTML40
                Case CODE_OUTTYP_RICHTEXT
                    objExOpt.ExportFormatType = ExportFormatType.RichText
                Case CODE_OUTTYP_WORD
                    objExOpt.ExportFormatType = ExportFormatType.WordForWindows
            End Select
            objExOpt.ExportDestinationOptions = objDiskOpt

            crReportDocument.Export(objExOpt)
            Execute_Report_Crystal_Export = CODE_OK
        Catch ex
            Execute_Report_Crystal_Export = CODE_ERROR
        Finally
            crReportDocument.Close()
        End Try

    End Function