How to Transfer Data from DataGridView to MS Excel File and MS Word 2003

How to Transfer Data from DataGridView to MS Excel File and MS Word 2003

• NPaul

  palestine,

  Just like I described in the previous post, please use the ADO.NET data access methods. ADO.NET is an improved edition of ADO 2.6 but run on .NET Framework. Also the ODBC, OLEDB are commonly used in data access. There are so many descriptions on this subject and here I will give you an example on access and data modified using the DataGrid.

  In the example, there are two main method you'd better pay more attention: RefreshData() and SaveChanges() method

  Imports System.Data.SqlClient

  Public Class Form1

  Inherits System.Windows.Forms.Form

  Public Const ConnectionString As String = _

  "integrated security=sspi;initial catalog=pubs;data source=localhost"

  Protected Const GetAllAuthorsSqlString As String = "select au_id,au_lname,au_fname,phone," + _

  "address,city,state,zip,contract from authors order by au_lname,au_fname"

  Public Sub RefreshData()

  Dim connection As New SqlConnection(ConnectionString)

  connection.Open()

  Dim adapter As New SqlDataAdapter(GetAllAuthorsSqlString, connection)

  Dim dataset As New DataSet

  adapter.Fill(dataset)

  adapter.Dispose()

  connection.Close()

  Dim table As DataTable = dataset.Tables(0)

  AddHandler table.ColumnChanged, New DataColumnChangeEventHandler(AddressOf ColumnChanged)

  datagridAuthors.DataSource = table

  End Sub

  Protected Sub ColumnChanged(ByVal sender As Object, ByVal e As DataColumnChangeEventArgs)

  menuSaveChanges.Enabled = True

  End Sub

  Private Sub menuRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles menuRefresh.Click

  RefreshData()

  End Sub

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

  RefreshData()

  End Sub

  Private Sub menuSaveChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles menuSaveChanges.Click

  SaveChanges()

  End Sub

  Public Sub SaveChanges()

  Dim table As DataTable = CType(datagridAuthors.DataSource, DataTable)

  Dim changedRows As New ArrayList

  For Each row As DataRow In table.Rows

  If row.RowState <> DataRowState.Unchanged Then

  changedRows.Add(row)

  End If

  Next

  If changedRows.Count = 0 Then

  Return

  End If

  Dim connection As New SqlConnection(ConnectionString)

  connection.Open()

  Dim adapter As New SqlDataAdapter(GetAllAuthorsSqlString, connection)

  Dim builder As New SqlCommandBuilder(adapter)

  Dim rows() As DataRow = CType(changedRows.ToArray(GetType(DataRow)), DataRow())

  adapter.Update(rows)

  adapter.Dispose()

  connection.Close()

  menuSaveChanges.Enabled = False

  End Sub

  End Class

  
  
  

• London Calling

  palestine,

  Transfer data from DataGridView to Excel or Word file by program may be harder than transfer them in database. So I suggest you to transfer the data by import and export from your Database Management System.

  For example, if the data shown in DataGridView is from SQL Server, you can just export the related table to access file or some other file format, then import the file in Excel or Word.

  Hope that can help you.

  
  
  

• c_shah

  Thanks u>>>>
  
  can u give me more Info. about this technique
  
  note...
  now i want to add this property to my project (Accounts)
  
  the dataBase of the program is Sqlserver2005 express
  
  
  

• jcnconnect

  After you bind the grid use this code in a button event to save it to a csv file, MS Excel has no problems opening this file and it basically the same thing.

  Imports System.IO

  Dim numCols As Integer = dg1.ColumnCount

  Dim numRows As Integer = dg1.RowCount - 1

  Dim strDestinationFile As String = SaveFile.FileName.ToString

  Dim tw As TextWriter = New StreamWriter(strDestinationFile)

  'writing the header

  For count As Integer = 0 To numCols - 1

  tw.Write(dg1.Columns(count).HeaderText)

  If (count <> numCols - 1) Then

  tw.Write(", ")

  End If

  Next

  tw.WriteLine()

  For count As Integer = 0 To numRows - 1

  For count2 As Integer = 0 To numCols - 1

  tw.Write(dg1.Rows(count).Cells(count2).Value)

  If (count2 <> numCols) Then

  tw.Write(", ")

  End If

  Next

  tw.WriteLine()

  Next

  tw.Close()

  End If

  
  
• How to Transfer Data from DataGridView to MS Excel File and MS Word 2003