Exporting DataGrid to Excel
I am displaying certain records in a DataGrid whose number of field display changes depending upon the fields the user want to select.
I want to EXPORT this whole DataGrid to MS Excel as it is. How to do so?
Since the number of field display changes, I cannot reserve the number of cells in Excel. So I want to transfer the Grid itself.
Re: Exporting DataGrid to Excel
Isnt the DataGrid VB.NET?
Re: Exporting DataGrid to Excel
Re: Exporting DataGrid to Excel
Is your grid bound to an ADO rs?
Re: Exporting DataGrid to Excel
I am using:
Dim rs as ADODB.Recordset
object to populate DataGrid
Re: Exporting DataGrid to Excel
Cool, easier. Use the .CopyFromRecordset method in Excel to import your object's recordset
directly to an Excel spreadsheet.
VB Code:
'From Excel help:
'This example copies the field names from a DAO Recordset object into the
'first row of a worksheet and formats the names as bold. The example then
'copies the recordset onto the worksheet, beginning at cell A2.
For iCols = 0 to rs.Fields.Count - 1
ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1), _
ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset rs
Re: Exporting DataGrid to Excel
Re: Exporting DataGrid to Excel
No prob. I like it when there is a method or function that can solve the problem like this. ;)
Re: Exporting DataGrid to Excel
Rob, I changed your last line of the answer to something like this, and that also worked well:
oWorkSheet.Range("A2").CopyFromRecordset DataGrid1.DataSource
This is beneficial when you are using the same recordset to execute different queries. In that case if the results the grid results can be transferred as above.
Re: Exporting DataGrid to Excel
Re: Exporting DataGrid to Excel
Rob,
Is this possible when the datagrid is not bound.
I built the datadrid up manually, the results are passed back from a server side subroutine, so binding was not possible.
Thanks
andy
Re: Exporting DataGrid to Excel
If your server side function returns an ADO recordset then you can use that rs to copyinto excel.
Re: Exporting DataGrid to Excel
I created a front end sales analysis in VB.net, the user fills in the details and then the request is sent to the server. The server does all the processing, which is quite a lot and returns the results in a system delimited format.
I then split the data up and fille the datgrid.
An export to Excel would then be great when the user needs that liitle more funtionality.
My skills are more server side, I'm making the VB up as a go a long, only started last month, but having great fun.
Cheers
andy
Re: Exporting DataGrid to Excel
good day! i'm using microsoft visual basic 2008 express edition and i wanted to export the datagrid to excel too but i'm having errors. This code i got from microsoft but when i tried it, i got these errors, type 'ADODB Connection' is not defined but that was already defined using dim, am i right? i'm newbie to visual basic so bare with me. any help would really be appreciated. thanks!
Code:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Create a Recordset from all the records in the Orders table.
Dim sNWind As String
Dim conn As New ADODB.Connection()
Dim rs As ADODB.Recordset
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNorthwind & ";")
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)
'Create a new workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
'Transfer the field names to Row 1 of the worksheet:
'Note: CopyFromRecordset copies only the data and not the field
' names, so you can transfer the fieldnames by traversing the
' fields collection.
Dim n As Int32
For n = 1 To rs.Fields.Count
oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
Next
'Transfer the data to Excel.
oSheet.Range("A2").CopyFromRecordset(rs)
'Save the workbook and quit Excel.
oBook.SaveAs(sSampleFolder & "Book3.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
'Close the connection
rs.Close()
conn.Close()
End Sub
Re: Exporting DataGrid to Excel
I do have the same problem. I cannot save data from datagrid to excel. I use inputbox so user can input data to datagrid. Then the user can click the button to save it to excel. I'm using VB.NET. Can someone please show me some code on how to create that?