none
Bind datagridview to Excel Spreadsheet

    Question

  • Hello

     

    I am attempting to upgrade a program I wrote in Visual Basic 6.0 which bound a DBGrid control to an Excel spreadsheet.

     

    In VB 6.0 I was able to make changes in the DBGrid control and automatically update the underlying spreadsheet.

     

    With VB 2008 the functionality has been removed when the DAO and corresponding "recordset" support was removed.  I have found code samples that allow me to use the Dataset, DataAdapter, and Connection to Fill and display data in a DataGridView from the Excel Spreadsheet using the Microsoft Jet Engine 4.0.

     

    However, the creation of an Excel database or dataset with the Visual Studio Database wizard does not work.  Suggestions I have received from others on the MSDN indicate that they have only been able to read the data by writing the code separate from the Wizard.  This appears to be a "bug" with the wizard.

     

    Even when using the code I have not been successful, using a bindingsourcecontrol, in binding back to the Excel spreadsheet.  I want to make changes on the datagridview and automatically have those changes incorporated on the Excel spreadsheet without keeping track of the individual cell changes.

     

    The "UpdateCommand", using the Update Method, within the adapter, only allows me update the underlying spreadsheet by writing an SQL style query in the OleDB adapter but requires that I sort through all of the changes within the datagridview to determine what has changed.

     

    Does anyone have a code snippet that will bind the datagridview back to an Excel spreadsheet.

     

    JV

     

     

    Monday, October 13, 2008 3:06 AM

All replies

  • Hi JVDEV

     

    Please look at this article.

    How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

    In this example, it tell you how to use the Jet OLE DB Provider with Microsoft Excel Workbooks and how to use Excel Workbooks as ADO.NET Data Sources(just look the Excel Workbook as a database).

     

    The important components in this example are:

    OLEDbCommand

    Represents an SQL statement or stored procedure to execute against a data source.

     

    OleDbDataAdapter

    Represents a set of data commands and a database connection that are used to fill the DataSet and update the data source.

     

    Sincerely,

    Kira Qian

     

    Windows Forms General FAQs
    Windows Forms Data Controls and Databinding FAQs

    Wednesday, October 15, 2008 3:08 AM
  • Thank you for replying.  However the article you referred me to is one that I have already read.

     

    It doesn't specify how to "bind" the Excel spreadsheet to a "DataViewGrid".  With this setup all changes to the "DataViewGrid" have to be incorporated into the Update method by way of the UpdateCommand.

     

    What I am looking for is a method to change the bound Excel Spreadsheet without having to know in advance what the changes on the "DataGridView" consist of.

     

    Thanks

     

    JV

     

    Wednesday, October 15, 2008 3:39 AM
  • Hi

     

    Parameterized INSERT and UPDATE commands are required because the OleDbDataAdapter does not supply key/index information for Excel workbooks; without key/index fields, the CommandBuilder cannot automatically generate the commands for you.

     

    Kira Qian

    Wednesday, October 15, 2008 6:25 AM
  • Hello,

     

    I am aware that parameterized INSERT and UPDATE commands are required with the example commands, but they also require that I know the specific data that has been updated within the "dataViewGrid" or "dataSet".  Once again I am looking for the equivalent of a "bound" control so that the the changes in the "dataViewGrid" or the "dataSet" don't have to sorted.  

     

    For example, if a Microsoft Access database is being used with a "dataViewGrid", "tableAdapter", and "bindingSource", data within the "dataViewGrid" can be bound to the underlying database.  If a change is made in the "dataViewGrid" it can automatically be incorporated within the database.  The specific change in the "dataViewGrid" does not need to be known.

     

    I want to do the same thing using the Excel spreadsheet.

     

    JV

     

    Wednesday, October 15, 2008 2:46 PM
  • Hi JVDEV

     

    I think you are asking for a silent update. After some one edit a cell, you want to update the database immediately without click the save button. I think you can handle the DataGridView.CellEndEdit event to call update code.

     

    Sincerely,

    Kira Qian

    Friday, October 17, 2008 6:31 AM
  • Correct me if I am wrong but would you not just use a different connection string?

     

    That way all of the items below would apply.

     

    For example, if a Microsoft Access database is being used with a "dataViewGrid", "tableAdapter", and "bindingSource", data within the "dataViewGrid" can be bound to the underlying database

     

    Monday, October 20, 2008 12:54 PM
  • I'm not sure how the connection string would affect this.  The Excel Spreadsheet as I understand it is not a "typed" database as Access is.  The little information I have found indicates that the Update Method within VB 2008 has to be used, with a "dataAdapter". 

     

    The information I have been able to find, on the "UpdateCommand" seems to be geared for other than Excel Spreadsheets.  Looking for the syntax for the "UpdateCommand" reveals little information geared to an Excel Spreadsheet.  Everything I have found assumes that there is a "key" field that uses the "WHERE" command, which I don't have in my spreadsheet.

     

     

    Monday, October 20, 2008 2:55 PM
  • Tuesday, October 21, 2008 1:44 AM
  • Hi,

    you can easily import/export Excel to DataTable with this Excel C# / VB.NET component.

    After you import Excel to DataTable with this Excel VB.NET code sample:

    Dim ef = New ExcelFile()
    ef.LoadXls("Data.xls")
    
    Dim ws = ef.Worksheets(0)
    
    Dim dataTable = New DataTable(ws.Name)
    
    ' Create columns from first row cells.
    dataTable.Columns.AddRange(ws.Rows(0).AllocatedCells.Cast(Of ExcelCell)().Select(Function(cell) New DataColumn(DirectCast(cell.Value, String))).ToArray())
    
    ' If excel cell value is not string, call ToString() on it.
    ws.ExtractDataEvent += Function(sender, e) 
    If e.ErrorID = ExtractDataError.WrongType Then
    	e.DataTableValue = If(e.ExcelValue IsNot Nothing, e.ExcelValue.ToString(), String.Empty)
    	e.Action = ExtractDataEventAction.Continue
    End If
    
    End Function
    
    ws.ExtractToDataTable(dataTable, ws.GetUsedCellRange().Height, ExtractDataOptions.None, ws.Rows(1), ws.Columns(0))
    
    

    Now you should bind this DataTable to DataGridView.

    Edit the data in the DataGridView.

    When you are finished you can export DataTable to Excel with this code:

    Dim bindingSource = DirectCast(Me.peopleDataGridView.DataSource, BindingSource)
    
    Dim dataTable = DirectCast(bindingSource.List, DataView).Table
    
    Dim ef = New ExcelFile()
    
    ' Insert data table in worksheet, starting from worksheet's first row and column and include column headers
    ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, True)
    
    ef.SaveXls(Convert.ToString(dataTable.TableName) & ".xls")
    
    

    Friday, April 15, 2011 9:06 AM