Solved to excel in (urgent)

Posted on 2004-08-10
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)
                    cellnumber += 1

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


dim sqlconnection as new sqlconnection(strconnstring)
   Dim da = New SqlClient.SqlDataAdapter
            ds = New DataSet
            With da
                .SelectCommand = New SqlClient.SqlCommand(QueryStmt, SqlConnection)
                .Fill(ds, "QueryResults")
            End With
            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

            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)
                    cellnumber += 1

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

            End If
Question by:raghu11
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 25

Accepted Solution

RonaldBiemans earned 250 total points
ID: 11770649

Author Comment

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

            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)

            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)" & ")"
                    objCmd.CommandText = objCmd.CommandText & "[" & dt.Columns.Item(n - 1).ColumnName & "] varchar(255)" & ","
                End If

            Dim parametersstring As String
            For n = 0 To dt.Columns.Count - 1
                If n = dt.Columns.Count - 1 Then
                    parametersstring = parametersstring & "?"
                    parametersstring = parametersstring & "?,"
                End If

            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)
            End With
            Dim daole As New System.Data.OleDb.OleDbDataAdapter
            daole.InsertCommand = objCmd

Author Comment

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

Author Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

627 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