save all the data from datagrid to ms excel?

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!
 

nire06

Member
Joined
May 3, 2009
Messages
9
Programming Experience
1-3
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?
 

nire06

Member
Joined
May 3, 2009
Messages
9
Programming Experience
1-3
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?

Code:
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
 

madder69

Member
Joined
May 4, 2009
Messages
16
Location
london
Programming Experience
5-10
it should be datagrid.items.count

accessing cells is datagrid.items(x).item(y)...

where x is row and y is cell
 

nire06

Member
Joined
May 3, 2009
Messages
9
Programming Experience
1-3
I already change it but it doesn't work. The blue text is still the error. Can you please help me to debug?

Code:
 [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
 

madder69

Member
Joined
May 4, 2009
Messages
16
Location
london
Programming Experience
5-10
I already change it but it doesn't work. The blue text is still the error. Can you please help me to debug?

Code:
 [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
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.
 

nire06

Member
Joined
May 3, 2009
Messages
9
Programming Experience
1-3
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.
 

madder69

Member
Joined
May 4, 2009
Messages
16
Location
london
Programming Experience
5-10
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.
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
 

nire06

Member
Joined
May 3, 2009
Messages
9
Programming Experience
1-3

madder69

Member
Joined
May 4, 2009
Messages
16
Location
london
Programming Experience
5-10
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
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
 

madder69

Member
Joined
May 4, 2009
Messages
16
Location
london
Programming Experience
5-10
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...
 

nire06

Member
Joined
May 3, 2009
Messages
9
Programming Experience
1-3
I tried your code but it did not work. Thanks for all the help. Hopes some gurus in vb.net help us here.
 
Top Bottom