?
Solved

ado.net to excel in vb.net (urgent)

Posted on 2004-08-10
4
Medium Priority
?
1,268 Views
Last Modified: 2008-03-10
I was trying to show the results of a query in excel file. I was trying to bind the search results to excel file but some how i could not do that. In the following code, to the datatable i assigned  ds.Tables.Item("QueryResults"), then i was trying to put the contents of datatable in excel file with the following lines:
  For Each row In dt.Rows
                    For n = 1 To dt.Columns.Count
                        oSheet.Cells(cellnumber, n).value = row.Item(n - 1)
                    Next
                    cellnumber += 1
  Next

Because of too many rows it is taking long time to write to the excel file. Is there a simple way of directly binding the data in datatable in excel file. Thanks


CODE:


dim sqlconnection as new sqlconnection(strconnstring)
   Dim da = New SqlClient.SqlDataAdapter
            ds = New DataSet
            SqlConnection.Open()
            With da
                .SelectCommand = New SqlClient.SqlCommand(QueryStmt, SqlConnection)
                .Fill(ds, "QueryResults")
            End With
            SqlConnection.Close()
            dt = ds.Tables.Item("QueryResults")
          Dim EXL As New Excel.Application
            If EXL Is Nothing Then
                MsgBox("Could not start excel")
                Exit Sub
            End If
            Dim wSheet As New Excel.Worksheet
            Dim oExcel As Object
            Dim oBook As Object

  'Dim oSheet As Object
            Dim oSheet As Excel.Worksheet

            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.Add
            oSheet = oBook.Worksheets(1)
            Dim n As Int32
            For n = 1 To dt.Columns.Count
                oSheet.Cells(1, n).Value = dt.Columns.Item(n - 1).ColumnName
            Next

            'oSheet.Range("A2").CopyFromRecordset(dt)
            Dim row As DataRow
            Dim cellnumber As Integer = 2
            If dt.Rows.Count > 0 Then
                For Each row In dt.Rows
                    For n = 1 To dt.Columns.Count
                        oSheet.Cells(cellnumber, n).value = row.Item(n - 1)
                    Next
                    cellnumber += 1
                Next

                oBook.SaveAs(System.Windows.Forms.Application.StartupPath & "\" & Format(Now, "yyyyMMddHHmmss") & ".xls")

            End If
0
Comment
Question by:raghu11
  • 3
4 Comments
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 750 total points
ID: 11770649
0
 

Author Comment

by:raghu11
ID: 11775173
I am dynamically building the excel sheet. I mean i give the option for the users to select the fields from list box and based on the fields select query is executed and a dataset is created. I implemented the following code but some how it creates excel file with column headings but no data in the excel. can someone help me
Thanks

            Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & System.Windows.Forms.Application.StartupPath & _
                        "\Book7.xls;Extended Properties=Excel 8.0;"
            Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
            objConn.Open()

            Dim objCmd As New System.Data.OleDb.OleDbCommand
            objCmd.Connection = objConn
            objCmd.CommandText = "create table test  ("

            For n = 1 To dt.Columns.Count
                 If n = dt.Columns.Count Then
                    objCmd.CommandText = objCmd.CommandText & "[" & dt.Columns.Item(n - 1).ColumnName & "] varchar(255)" & ")"
                Else
                    objCmd.CommandText = objCmd.CommandText & "[" & dt.Columns.Item(n - 1).ColumnName & "] varchar(255)" & ","
                End If

            Next
            objCmd.ExecuteNonQuery()
            Dim parametersstring As String
            For n = 0 To dt.Columns.Count - 1
                If n = dt.Columns.Count - 1 Then
                    parametersstring = parametersstring & "?"
                Else
                    parametersstring = parametersstring & "?,"
                End If
            Next

            objCmd.CommandText = "Insert into test values(" & parametersstring & ")"
            With objCmd.Parameters
                For n = 0 To dt.Columns.Count - 1
                    .Add("@" & dt.Columns.Item(n).ColumnName, OleDb.OleDbType.VarChar, 255, dt.Columns.Item(n).ColumnName)
                Next
            End With
            Dim daole As New System.Data.OleDb.OleDbDataAdapter
            daole.InsertCommand = objCmd
            daole.Update(dt)
            objConn.Close()
0
 

Author Comment

by:raghu11
ID: 11775423
I used daole.update(ds) but it was throwing exception because of which i used datatable dt
0
 

Author Comment

by:raghu11
ID: 11775648
adding this statement helped in solving the issue.  da.AcceptChangesDuringFill = False
tHANKS
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month12 days, 21 hours left to enroll

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question