Importing ms excel data to datagrid. first row is column names in dg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums
Results 1 to 6 of 6

Thread: Importing ms excel data to datagrid. first row is column names in dg

  1. #1
    Join Date
    Jun 2003

    Importing ms excel data to datagrid. first row is column names in dg

    I am using the following code to import msexcel data and put it in a data grid. It all works well except that the first row of data is assumed to be the column names. So I lose the first row of data. Anyone know how i can fix this?

    Private Function GetData()
    Dim myFile As String
    'Dim dt As System.Data.DataTable
    Dim openFileDialog1 As New OpenFileDialog
    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    Dim MyConnection As System.Data.OleDb.OleDbConnection

    openFileDialog1.InitialDirectory = "c:\mcmcom\chambers\ptsdatabase\"
    openFileDialog1.Filter = ".xls files (*.xls)|*.xls|All files (*.*)|*.*"
    openFileDialog1.FilterIndex = 2
    openFileDialog1.RestoreDirectory = True

    If openFileDialog1.ShowDialog() = DialogResult.OK Then
    myFile = openFileDialog1.FileName
    End If

    MyConnection = New System.Data.OleDb.OleDbConnection( _
    "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source= " & myFile & "; Extended Properties=Excel 8.0;")
    ' Select the data from Sheet1 of the workbook.
    MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
    "select * from [Sheet1$]", MyConnection)

    dt = New System.Data.DataTable


    So basically it fills the datagrid, but the column header names are the values of the first record. If i set the datagrid properties to No column headers, i just lose the first record!
    Its annoying

  2. #2
    Join Date
    Feb 2001
    Stamford CT USA
    After you fill the DATASET, just remove the FIRST ROW of in the DATATABLE.

    -Cool Bizs

  3. #3
    Join Date
    Dec 2002
    Alternately, add a header row to the Excel data before importing it to datagrid. You can do this programmatically or outside. If you are working with Windows Forms, you can open Excel file and add a header row, close it, and use OleDb to read in data. If you are using Webform, it is better to steer clear of editing Excel programmatically. Do it outside, then import.

  4. #4
    Join Date
    Jun 2003
    its pure windows forms. Its a application not or anything. How do i do it programmatically? Can I just add a new row to the top of the datatable? This program will analyze excel files, so I can't really manually add a header row in excel, kinda defeats the purpose of the program. Basically it loads the excel file, uses regular expressions to match columns, then sets the column name based on the regular expression match. So what i need to do is add a row to the data table maybe BEFORE i insert the excel data to it? Who knows now, i need sleep!

  5. #5
    Join Date
    Dec 2002
    Open Excel file using the following (after including Excel Application in your reference: From Project Menu select Add Reference, select COM tab, select Excel Object, Press OK)

    ' After identifying the Excel file name from OpenFiledialog1)

    dim Xl as new Excel.Application
    dim Xlb as Excel.Workbook = Xl.Workbooks.Open(Openfiledialog1.filename)
    Dim Xls as Excel.Worksheet=CType(Xlb.worksheets(1),Excel.worksheet)

    'insert a row

    'Add headers


    'Save file with the same name

    ' Now open the Excel file as before using OleDB ...

    Good luck.

  6. #6
    Join Date
    Aug 2012

    Re: Importing ms excel data to datagrid. first row is column names in dg

    I found a simple solution,
    1.create a new row,
    2.copy values from header to new row,
    3.rename header names
    4.insert new row to table at pos index 0

    myDataRow = DtSet.Tables(0).NewRow()
    For i = 0 To DtSet.Tables(0).Columns.Count - 1
    myDataRow(i) = DtSet.Tables(0).Columns(i).Caption
    DtSet.Tables(0).Columns(i).ColumnName = CStr(i)
    DtSet.Tables(0).Rows.InsertAt(myDataRow, 0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Windows Mobile Development Center

Click Here to Expand Forum to Full Width

This a survey!

On-Demand Webinars (sponsored)