save all the data from datagrid to ms excel?

N

nire06

Member
Joined
May 3, 2009
Messages
9
Programming Experience
1-3
Hello everyone! Please bare with me because I'm new to vb.net .I do have 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. Can someone please show me some code on how to create that? Thanks!
 
Sort by date Sort by votes
nire06 said:
Hello everyone! Please bare with me because I'm new to vb.net .I do have 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. Can someone please show me some code on how to create that? Thanks!
Click to expand...

How to export from DataGridView to excel
 
Upvote 0 Downvote
Thanks a lot Madder. I used DataGrid, not DataGridView, but I try the code. Now, I only have one problem, in DataGrid, there's no RowCount, it is only for DataGridView. Do you know what exactly the equivalent of RowCount in DataGrid?
 
Upvote 0 Downvote
This is my code. I tried to change RowCount to VisibleRowCount but it did not work. The blue text says "Public member 'Value' on type 'String' not found." Anyone help?

VB.NET: 
Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim Table1 As DataTable
    Dim ds As New DataSet()
    Dim Transaction As String
    Dim Quantity, Price, Amount, TotalAmount As Integer



    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        ComboBox1.Items.Add("Select...".ToString)
        ComboBox1.Items.Add("CD/DVD Burning".ToString)
        ComboBox1.Items.Add("Downloads".ToString)
        ComboBox1.Items.Add("Printing".ToString)

        ComboBox1.Width = 100

        Table1 = New DataTable("Table1")
        ds = New DataSet()

        Try
            Dim Transaction As DataColumn = New DataColumn("Transaction")
            'Transaction.DataType = System.Type.GetType("System.String")
            Table1.Columns.Add(Transaction)

            Dim Quantity As DataColumn = New DataColumn("Quantity")
            'Quantity.DataType = System.Type.GetType("System.String")
            Table1.Columns.Add(Quantity)

            Dim Price As DataColumn = New DataColumn("Price")
            'Price.DataType = System.Type.GetType("System.String")
            Table1.Columns.Add(Price)

            Dim Amount As DataColumn = New DataColumn("Amount")
            'Amount.DataType = System.Type.GetType("System.String")
            Table1.Columns.Add(Amount)

            Dim TotalAmount As DataColumn = New DataColumn("TotalAmount")
            'TotalAmount.DataType = System.Type.GetType("System.String")
            Table1.Columns.Add(TotalAmount)

        Catch
        End Try

        ds.Tables.Add(Table1)
        DataGrid1.SetDataBinding(ds, "Table1")

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim Row As DataRow = Table1.NewRow()

        Try
            Transaction = ComboBox1.Text
            Quantity = Integer.Parse(TextBox1.Text)
            Price = Integer.Parse(TextBox2.Text)
            TextBox3.Text = Quantity * Price
            Amount = Integer.Parse(TextBox3.Text)
            TextBox4.Text = TotalAmount + Amount
            TotalAmount = Integer.Parse(TextBox4.Text)

            Row.Item("Transaction") = ComboBox1.Text
            Row.Item("Quantity") = Quantity
            Row.Item("Price") = Price
            Row.Item("Amount") = Amount
            Row.Item("TotalAmount") = TotalAmount

            Table1.Rows.Add(Row)

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error Message")
        End Try

        ComboBox1.Text = "Select..."
        TextBox1.Text = " "
        TextBox2.Text = " "
        TextBox3.Text = " "
        TextBox4.Text = " "

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To DataGrid1.VisibleRowCount - 2
            For j = 0 To DataGrid1.VisibleColumnCount - 1
[COLOR="Blue"]              xlWorkSheet.Cells(i + 1, j + 1) = _
                DataGrid1(j, i).Value.ToString()[/COLOR]
            Next
        Next

        xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\vbexcel.xlsx")

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class
 
Upvote 0 Downvote
I already change it but it doesn't work. The blue text is still the error. Can you please help me to debug?

VB.NET: 
 [COLOR="Blue"]       For i = 0 To DataGrid1.Item(rowIndex:=10, columnIndex:=10)[/COLOR]
            For j = 0 To DataGrid1.Item(rowIndex:=10, columnIndex:=10)
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGrid1(j, i).Value.ToString()
            Next
        Next
 
Upvote 0 Downvote
nire06 said:
I already change it but it doesn't work. The blue text is still the error. Can you please help me to debug?

VB.NET: 
 [COLOR="Blue"]       For i = 0 To DataGrid1.Item(rowIndex:=10, columnIndex:=10)[/COLOR]
            For j = 0 To DataGrid1.Item(rowIndex:=10, columnIndex:=10)
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGrid1(j, i).Value.ToString()
            Next
        Next
Click to expand...

i've never seen it done that way?

try
'10 rows, 10 cells grid

For i = 0 To DataGrid1.Item(9) ' 0 based index
For j = 0 To 9 ' 10 items - 0 based index
xlWorkSheet.Cells(i + 1, j + 1) = DataGrid1.item(i).item( j).Value.ToString()
Next
Next

this will probably create a null datafield if one of your cells contains a control (label, textbox etc) but for straight data in the field it should be fine.
 
Upvote 0 Downvote
It says that the type integer cannot be converted to System.Windows.Forms.DataGridCell. The line For i = 0 To DataGrid1.Item(9) has error on 9. And this line xlWorkSheet.Cells(i + 1, j + 1) = DataGrid1.item(i).item( j).Value.ToString() got error on i.
 
Upvote 0 Downvote
nire06 said:
It says that the type integer cannot be converted to System.Windows.Forms.DataGridCell. The line For i = 0 To DataGrid1.Item(9) has error on 9. And this line xlWorkSheet.Cells(i + 1, j + 1) = DataGrid1.item(i).item( j).Value.ToString() got error on i.
Click to expand...

how many items have you on the row that youre trying to capture?


Dim f As DataGrid = New DataGrid
Dim n = 0, nn = 0
For n = 0 To f.Items.Count
For nn = 0 To f.Items(n).Cells.Count
xlWorkSheet.Cells(n + 1, nn + 1) = f.Items(n).Cells.Item(nn).Text

Next
Next
 
Upvote 0 Downvote
Upvote 0 Downvote
nire06 said:
Still it has errors. See the pic so you can see what I mean. Do you have any other way on saving it to datagrid?
http://i590.photobucket.com/albums/ss348/nire06/datagrid.png
Click to expand...

change f.item to f.items as it was in the code i gave :)


the example i gave you would need changing to :

Dim n = 0, nn = 0
For n = 0 To datagrid1.items.count
For nn = 0 To datagrid1.Items(n).Cells.Count
xlWorkSheet.Cells(n + 1, nn + 1) = datagrid1.Items(n).Cells.Item(nn).Text

Next
Next
 
Upvote 0 Downvote
in that case (sorry was working on aspx base) you should be using the datatable you filled from the datasource to do your operation rather than the datagrid imo.

or you can get the row count from DataGrid1.BindingContext(DataGrid1.DataSource, DataGrid1.DataMember).Count

using the example you give in the screen shots, i would create a datatable to hold your user data
like

i would add imports system.data at the top of the page above all else then you dont need to use system.data.XXX all the way through the code below..

' inside class header
Public Shared dt As System.Data.DataTable = New System.Data.DataTable("table")

'on form load...

Dim dc1 As System.Data.DataColumn = New System.Data.DataColumn("name")
Dim dc2 As System.Data.DataColumn = New System.Data.DataColumn("value")
Dim dc3 As System.Data.DataColumn = New System.Data.DataColumn("anything")
Dim dc4 As System.Data.DataColumn = New System.Data.DataColumn("goes")
Dim dc5 As System.Data.DataColumn = New System.Data.DataColumn("here")
dt.Columns.Add(dc1)
dt.Columns.Add(dc2)
dt.Columns.Add(dc3)
dt.Columns.Add(dc4)
dt.Columns.Add(dc5)
datagrid1.datasource = dt

' in click event
Dim dr As New System.Data.DataRow
dr.Item(0) = textbox1.text
dr.Item(1) = textbox2.text
dr.Item(2) = textbox3.text
dr.Item(3) = textbox4.text
dr.Item(4) = textbox5.text
dt.Rows.Add(dr)
datagrid1.DataBind()

then you can dt.clear when you have exported to excel...
 
Upvote 0 Downvote

Similar threads

H
  • Question
Question Data Export - advice needed
Replies
1
Views
525
jdelano
J
A
  • Question
Subtract the quantity of partsDGV <- bound from DGV3 <- unbound
Replies
3
Views
375
jdelano
J
J
  • Question
I can see the Excel Workbook, but the WorkSheet methods etc don't work :(
Replies
4
Views
2K
johnhoens
J
Jonathan1994
  • Question
creating a quotation (estimate) system and saving to a access database, but cant get passed on how to save the quote and multiple line items.
Replies
3
Views
949
jmcilhinney
jmcilhinney
K
  • Question
extract text from pdf?
Replies
1
Views
478
JohnH
JohnH

Share this page

Latest posts

Back
Top