Importing ms excel data to datagrid. first row is column names in dg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
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
    Location
    Toronto
    Posts
    805

    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

    Try
    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

    MyCommand.Fill(dt)


    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
    mcm

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

    -Cool Bizs

  3. #3
    Join Date
    Dec 2002
    Posts
    305
    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
    Location
    Toronto
    Posts
    805
    its pure windows forms. Its a vb.net application not asp.net 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
    Posts
    305
    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
    Xls.rows("1:1").Insert

    'Add headers

    xls.cells(1,1).value="Column1"
    xls.cells(1,2).value="Column2"
    xls.cells(1,3).value="Column3"
    ...
    ..

    'Save file with the same name
    Xlb.save
    Xls=nothing
    Xlb.close(False)
    Xlb=nothing
    Xl.quit
    Xl=nothing

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


    Good luck.

  6. #6
    Join Date
    Aug 2012
    Posts
    1

    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

    MyCommand.Fill(DtSet)
    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)
    Next
    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 is a CodeGuru survey question.


Featured


HTML5 Development Center