-
May 4th, 2009, 05:51 AM
#1
Thread Starter
New Member
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
-
May 4th, 2009, 07:16 AM
#2
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|