Results 1 to 11 of 11

Thread: [RESOLVED] Is it possible to put data on SQL to EXCEL?

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2016
    Posts
    9

    Resolved [RESOLVED] Is it possible to put data on SQL to EXCEL?

    I have been searching for answers regarding my problem, I need to put data from SQL server to Excel, but it should be done inside VB.Net. The problem is I have to deal with data with plenty of rows and columns. I'm still scratching my head for a place to start, but an example or two may be helpful.

  2. #2
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    985

    Re: Is it possible to put data on SQL to EXCEL?

    its been a wwhile since i worked with excel through vb.net, do you know about office interop?

    its actually pretty much like using VBA, just add an office reference to your project and your half way there.

    i have a project on hand here that deals with pulling data within vba, the process is pretty much the same in vb.net..... ill try and find some snippets in my code that might help.....


    here are 2 helper functions i wrote to pull data in VB.NET into a dataset.....
    1 you give a query string and connection string and the other you proved the details for an SP that returns a table...... its what im using in my project right now, its not perfect but it works..

    Code:
    'QryStr is the query
    'ConStr is the connection string
    Friend Function GetDataSet_FromQuery(QryStr As String, ConStr As String) As Data.DataSet
            Dim Retry As Boolean = True
    
            GetDataSet_FromQuery = Nothing
    
            While Retry
                Try
                    GetDataSet_FromQuery = New DataSet()
                    Using Adapter = New SqlClient.SqlDataAdapter(QryStr, ConStr)
    
                        Adapter.Fill(GetDataSet_FromQuery)
                    End Using
                    Retry = False
                Catch ex As Exception
                    If MessageBox.Show("Error Retreiving Data..." & vbCrLf & "Error Location: Public Function GetDataSet_FromQuery" & vbCrLf & "Error Description: " & ex.Message, "Communication Error", MessageBoxButtons.RetryCancel) = DialogResult.Retry Then
                        Retry = True
    
                    Else
                        Retry = False
                        GetDataSet_FromQuery = Nothing
                    End If
                End Try
            End While
    
        End Function
    
    'SPStr is the name of the Stored Procedure
    'ConStr is the connection string
    'with sqlconnection class you need to provide the variable names that the stored procedure takes which is ParamName
    'ParamString is any string you send to the stored procedure
        Friend Function GetDataSet_FromStoredProcedure_A(SPStr As String, ConStr As String, ParamName As String, ParamStr As String) As Data.DataSet
            Dim Retry As Boolean = True
    
            GetDataSet_FromStoredProcedure_A = Nothing
    
            While Retry
    
                Try
                    Using Connection = New SqlClient.SqlConnection(ConStr)
                        Connection.Open()
                        Using Command = New SqlClient.SqlCommand()
                            With Command
                                .Connection = Connection
                                .CommandTimeout = 60
                                .CommandType = CommandType.StoredProcedure
                                .CommandText = SPStr
                                .Parameters.Add(New SqlClient.SqlParameter(ParamName, ParamStr))
                            End With
    
                            Using Adapter = New SqlClient.SqlDataAdapter(Command)
                                GetDataSet_FromStoredProcedure_A = New DataSet
                                Adapter.Fill(GetDataSet_FromStoredProcedure_A)
                            End Using
                        End Using
    
                        Connection.Close()
                        Retry = False
                    End Using
                Catch ex As Exception
                    If MessageBox.Show("Error Retreiving Data..." & vbCrLf &
                                "Error Location: Public Function GetDataSet_FromStoredProcedure_A" & vbCrLf &
                                "Called Procedure: " & SPStr & vbCrLf &
                                "Parameters: " & ParamStr & vbCrLf &
                                "Error Description: " & ex.Message, "Communication Error", MessageBoxButtons.RetryCancel) = DialogResult.Retry Then
                        Retry = True
                    Else
                        Retry = False
                    End If
                    GetDataSet_FromStoredProcedure_A = Nothing
                End Try
    
            End While
    
        End Function
    i dont have access to my excel file right now which basically the VBA version of this, but this should start you off...

    ill try and get back to you later
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  3. #3
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Is it possible to put data on SQL to EXCEL?

    Quote Originally Posted by JoshuaMagsino View Post
    I have been searching for answers regarding my problem, I need to put data from SQL server to Excel, but it should be done inside VB.Net. The problem is I have to deal with data with plenty of rows and columns. I'm still scratching my head for a place to start, but an example or two may be helpful.
    Look here:
    http://vb.net-informations.com/excel..._tutorials.htm

    Specifically:
    http://vb.net-informations.com/excel...e_to_excel.htm
    Please remember next time...elections matter!

  4. #4
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    985

    Re: Is it possible to put data on SQL to EXCEL?

    how i use the code above is here... note that this code is putting the table into a datagridview but the principle is the same with excel.......ill try and get more help later regarding putting it into an excel sheet, but google is there and its not hard....

    Code:
     Public Sub FillTable()
            Dim DT As DataTable
            Dim ParamString As String
    
            ParamString = 'Your Query String'
    
            Try
                With My.Settings
                    DT = GetDataSet_FromQuery(QryStr:= ParamString, ConStr:= .ConnectionString).Tables(0)
                End With
    
                MyTable.DataSource = DT 'This line is where your table gets populated
    
            Catch ex As Exception
                "error'
            End Try
    
        End Sub
    NOTE: i removed alot of code and just tried to simplify this here, its just the basics of how to populate a datagridview, ill try to get the code later that you would use to populate an excel sheet instead...
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  5. #5
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    985

    Re: Is it possible to put data on SQL to EXCEL?

    Thanks TysonLPrice +1

    Read the links TysonLPrice posted they are very good.....

    as far as my code snippets go look below for how to use them here

    Code:
    Public Sub FillTable()
            Dim DT As DataTable
            Dim ParamString As String
    
            ParamString = 'Your Query String'
    
            Try
                With My.Settings
                    DT = GetDataSet_FromQuery(QryStr:= ParamString, ConStr:= .ConnectionString).Tables(0)
                End With
    
                ''-----------Replace this with whats below ''MyTable.DataSource = DT 'This line is where your table gets populated
                For i = 0 To dt.Rows.Count - 1
    	        For j = 0 To dt.Columns.Count - 1
    		    xlWorkSheet.Cells(i + 1, j + 1) =  _
    		    dt.Rows(i).Item(j)
    	        Next
                Next
    
            Catch ex As Exception
                "error'
            End Try
    
        End Sub
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  6. #6
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Is it possible to put data on SQL to EXCEL?

    Actually, as lame as it sounds, its soooo much faster to use the clipboard. This was just discussed in a not so distant thread from here.

    Code:
             DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
            DataGridView1.SelectAll()
            Clipboard.SetDataObject(DataGridView1.GetClipboardContent())
            DataGridView1.ClearSelection()
    
            Dim _excel As New Microsoft.Office.Interop.Excel.Application
            Dim wBook As Microsoft.Office.Interop.Excel.Workbook
            Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
            wBook = _excel.Workbooks.Add()
            wSheet = CType(wBook.ActiveSheet(), Microsoft.Office.Interop.Excel.Worksheet)
            _excel.Visible = True
            wSheet.Paste()

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Is it possible to put data on SQL to EXCEL?

    Here's a class I use to write from an Access mdb to Excel:

    http://www.vbforums.com/showthread.p...ass&highlight=

    It's been a while since I looked at that class, but I think it takes either a datatable or datareader, so it doesn't actually care what database the data came from. You'd query the DB for the data, then call the method to write it out. One thing about the class is that it uses late binding, which means that you don't have a reference to Excel. Instead, you are effectively promising that some version of the Excel PIAs are available on whatever machine you run the code on. If you use an Excel reference, then you are tied to that particular version of Excel. Change versions and you either have to change Excel references, or the code will fail. Late binding means not needing to worry about the Excel version, and not needing a reference in the project. It's a total pain to debug, though, since you also don't get intellisense help (everything is an Object, so intellisense only gives you the members of type Object).
    My usual boring signature: Nothing

  8. #8
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Is it possible to put data on SQL to EXCEL?

    In the DAO days I recall I created a sub that would execute an INSERT INTO OPENROWSET command that was lightning fast. I remember it would also create DBF files on the fly, and that is more the reason I used DAO VS ADODB.

    ...Anyway, this is the closest I could come in VB.NET
    Code:
    Imports System.Data.SqlClient
    
    Public Class SelectIntoExample
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim XLFile As String = "C:\DATA\XLInsertIntoTest.XLS"
            Dim SQL As String = "INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & XLFile &
                                ";','SELECT * FROM [Sheet1$]') select * from Employees"
            Using SQLConn As New SqlConnection("Data Source=KPMSQLB\STD;Initial Catalog=KPMIT;Integrated Security=True")
                Using CMD As New SqlCommand(SQL, SQLConn)
                    SQLConn.Open()
                    CMD.ExecuteNonQuery()
                    SQLConn.Close()
                End Using
            End Using
        End Sub
    End Class
    I want to say that excel/interop was not required, and that the file was created by the DAO provider
    When executing the above code in .NET, however, I get the following, which I am not the DBA here so my testing is done
    Name:  ERR.png
Views: 156
Size:  14.4 KB

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Is it possible to put data on SQL to EXCEL?

    Using ADO.NET to write to Excel is a good way to go about it, if available.
    My usual boring signature: Nothing

  10. #10
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Is it possible to put data on SQL to EXCEL?

    Quote Originally Posted by kpmc View Post
    In the DAO days I recall I created a sub that would execute an INSERT INTO OPENROWSET command that was lightning fast. I remember it would also create DBF files on the fly, and that is more the reason I used DAO VS ADODB.

    ...Anyway, this is the closest I could come in VB.NET
    Code:
    Imports System.Data.SqlClient
    
    Public Class SelectIntoExample
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim XLFile As String = "C:\DATA\XLInsertIntoTest.XLS"
            Dim SQL As String = "INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & XLFile &
                                ";','SELECT * FROM [Sheet1$]') select * from Employees"
            Using SQLConn As New SqlConnection("Data Source=KPMSQLB\STD;Initial Catalog=KPMIT;Integrated Security=True")
                Using CMD As New SqlCommand(SQL, SQLConn)
                    SQLConn.Open()
                    CMD.ExecuteNonQuery()
                    SQLConn.Close()
                End Using
            End Using
        End Sub
    End Class
    I want to say that excel/interop was not required, and that the file was created by the DAO provider
    When executing the above code in .NET, however, I get the following, which I am not the DBA here so my testing is done
    Name:  ERR.png
Views: 156
Size:  14.4 KB
    Many DBAs consider OPENROWSET a security risk and keep it turned off. It is turned off here at my site.

    https://www.sqlservercentral.com/For...793-146-1.aspx
    Please remember next time...elections matter!

  11. #11
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Is it possible to put data on SQL to EXCEL?

    I have a code sample for INSERT INTO OPENROWSET where it explains also how to set things up on SQL Server thru SQL-Server Management Studio.

    https://code.msdn.microsoft.com/Expo...994cb5?redir=0

    Another options is SpreadSheetLight library if you have read data from SQL-Server into a DataTable there is a method ImportTable with options for row and column to start in a specific WorkSheet plus an option to include or exclude column headers. If you need styling the library has this too.

    http://spreadsheetlight.com/

    There is a CHM file to download for this library.

    Installation is done via NuGet

    There is a working example here
    https://code.msdn.microsoft.com/Alte...52c4a2?redir=0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width