Export to Excel in VB.net

Hi,

I want a solution how to Export to Excel from a data grid
Here I do not want to open the excel application.
I want the data from the data grid to be exported
in my default excel file (excelexp1.xls ).

Hope the question is clear.


Thanks and regards
shawlin

Use crystal reports
create a report document and then use the export options in crystal reports see sample below:
da1.SelectCommand = New OleDb.OleDbCommand(SqlStr, Cn1)
Dim ds1 As New DataSet()
' Connect to the data source, fetch the data, and disconnect
ds1.Clear()
da1.Fill(ds1, "datagirdata")

Dim Rpt1 As ReportDocument = New ReportDocument()
Rpt1.Load("C:\Yor Excel Spreadsheet")
Rpt1.SetDataSource(ds1)
Rpt1.PrintOptions.PaperOrientation = PaperOrientation.Portrait

Dim exp1 As New ExportOptions()
Dim dsk1 As New DiskFileDestinationOptions()
Dim Fnam As String = "C:Your spreadsheet"
exp1 = Rpt1.ExportOptions
exp1.ExportFormatType = ExportFormatType.Excel
exp1.ExportDestinationType = ExportDestinationType.DiskFile
dsk1.DiskFileName = Fnam
exp1.DestinationOptions = dsk1
Rpt1.Export()

Hi Richard,

Thanks for the reply.
but why i want to create a crystal report and export
to excel.Is it not possible for me to directly
send the output to the excel file (.xls) from the datagrid.



thanks for your reply

shawlin

No a datagrid is just a container for the data it holds in the underlying dataset, it is there to represent the data in the data set andany data you type in, goes directly into the disconnected copy of the dataset

I suggest you read up on datagrids and ADO in general.

I have the following code.

VB Code:

crpt = New RMSCompareRpt()
        For Each myTable In crpt.Database.Tables
            myLogin = myTable.LogOnInfo
            With myLogin.ConnectionInfo
                .ServerName = "Server7"
                .UserID = "user"
                .Password = "pass"
                .DatabaseName = "Miscellaneous"
            End With
            myTable.ApplyLogOnInfo(myLogin)
        Next
        crpt.RecordSelectionFormula = "{Compare.EmpID} = " & Request.Cookies("EmpID").Value
 
        CrystalReportViewer1.ReportSource = crpt
        CrystalReportViewer1.RefreshReport()
        CrystalReportViewer1.DataBind()
 
        Dim exp1 As New ExportOptions()
        Dim dsk1 As New DiskFileDestinationOptions()
        Dim Fnam As String = "C:\Test.pdf"
        exp1 = crpt.ExportOptions
        exp1.ExportFormatType = ExportFormatType.PortableDocFormat
        exp1.ExportDestinationType = ExportDestinationType.DiskFile
        dsk1.DiskFileName = Fnam
        exp1.DestinationOptions = dsk1
        crpt.Export()

This gives me the following error.
Error in File C:\DOCUME~1\WILHELM\ASPNET\LOCALS~1\Temp\temp_fcffba68-1f5f-47a7-8429-3587fcdf3dfe.rpt: Access to report file denied. Another program may be using it.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: CrystalDecisions.CrystalReports.Engine.LoadSaveReportException: Error in File C:\DOCUME~1\WILHELM\ASPNET\LOCALS~1\Temp\temp_fcffba68-1f5f-47a7-8429-3587fcdf3dfe.rpt: Access to report file denied. Another program may be using it.

Source Error:


Line 67: dsk1.DiskFileName = Fnam
Line 68: exp1.DestinationOptions = dsk1
Line 69: crpt.Export()
Line 70:
Line 71: End Sub


Source File: C:\Inetpub\wwwroot\McClure\RMSCompareViewer.aspx.vb Line: 69

Stack Trace:


[LoadSaveReportException: Error in File C:\DOCUME~1\WILHELM\ASPNET\LOCALS~1\Temp\temp_fcffba68-1f5f-47a7-8429-3587fcdf3dfe.rpt:
Access to report file denied. Another program may be using it.]
.K(String 
, EngineExceptionErrorID )
.F(Int16 , Int32 )
.E(Int16 
)
CrystalDecisions.CrystalReports.Engine.FormatEngine.Export(ExportRequestContext reqContext)
CrystalDecisions.CrystalReports.Engine.FormatEngine.Export()
CrystalDecisions.CrystalReports.Engine.ReportDocument.Export()
McClure.RMSCompareViewer.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\McClure\RMSCompareViewer.aspx.vb:69
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

By the way, I'm running this code in an ASP .Net web page.

Can anyone tell me what I'm doing wrong here? Do I need to save the open Crystal report to a file first, and then use that saved file? If so, how?

Thanks!