-
Nov 22nd, 2017, 01:01 AM
#1
Thread Starter
New Member
[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.
-
Nov 22nd, 2017, 05:46 AM
#2
Fanatic Member
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
-
Nov 22nd, 2017, 05:54 AM
#3
Re: Is it possible to put data on SQL to EXCEL?
Originally Posted by JoshuaMagsino
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!
-
Nov 22nd, 2017, 05:54 AM
#4
Fanatic Member
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
-
Nov 22nd, 2017, 05:58 AM
#5
Fanatic Member
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
-
Nov 22nd, 2017, 08:20 AM
#6
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()
-
Nov 22nd, 2017, 11:12 AM
#7
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
-
Nov 22nd, 2017, 01:53 PM
#8
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
-
Nov 22nd, 2017, 04:40 PM
#9
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
-
Nov 27th, 2017, 06:12 AM
#10
Re: Is it possible to put data on SQL to EXCEL?
Originally Posted by kpmc
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
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!
-
Nov 28th, 2017, 06:38 PM
#11
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|