Results 1 to 2 of 2

Thread: How to save data from datagrid to excel in vb.net?

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2009
    Posts
    3

    How to save data from datagrid to excel in vb.net?

    Please bare with me, I'm new to vb.net. I want to save the data that I inpputted to datagrid using inputbox to excel. Can someone please help me to debug or show me how to save correctly?

    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 oExcel As Object
            Dim oBook As Object
            Dim oSheet As Object
    
            'Start a new workbook in Excel.
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.Add
    
            'Create an array with 3 columns and 100 rows.
            Dim DataArray(99, 2) As Object
            Dim r As Integer
    
            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)
    
            'Add headers to the worksheet on row 1.
            oSheet = oBook.Worksheets(1)
            oSheet.Range("A1").Value = "Transaction"
            oSheet.Range("B1").Value = "Quantity"
            oSheet.Range("C1").Value = "Price"
            oSheet.Range("C1").Value = "Amount"
            oSheet.Range("C1").Value = "Total Amount"
    
            'Transfer the array to the worksheet starting at cell A2.
            oSheet.Range("A2").Resize(100, 3).Value = DataArray
    
            'Save the Workbook and quit Excel.
            oBook.SaveAs("C:\Book1.xls")
            oSheet = Nothing
            oBook = Nothing
            oExcel.Quit()
            oExcel = Nothing
            GC.Collect()
    
        End Sub
    
    End Class

  2. #2

    Thread Starter
    New Member
    Join Date
    May 2009
    Posts
    3

    Re: How to save data from datagrid to excel in vb.net?

    Any help? Please? I'm just new to vb.net that why I cannot figure it out alone. I would appreciate all the help.

Tags for this Thread

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