How to ignore the DataGrid filed ?

Hi,

Below codes is allowed me to import the Excel Data into DataGrid(vb.net), in my Exceel Sheet my data is look like this;

Batch No.     Chassis No.
111111      ABC123454B
222222      SADF14539L
333333      OIS123457K
 
When I import it into DataGrid it will look like this;

Batch No.     Chassis No.     F3    F4     F5     F6
111111      ABC123454B   (null) (null) (null) (null)
222222      SADF14539L   (null) (null) (null) (null)
333333      OIS123457K    (null) (null) (null) (null)

How do I ignore the F3 - F6 Field in my DataGrid?

Below is a code to Load my Excel Data into DataGrid;

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim NewRecord As Boolean = False

        MyConnection = New System.Data.OleDb.OleDbConnection( _
                      "provider=Microsoft.Jet.OLEDB.4.0; " & _
                      "data source=c:\data.xls; " & _
                      "Extended Properties=""Excel 8.0;IMEX=1;ReadOnly:=False;UpdateLinks:=0""")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
                  "select * from [sheet1$]", MyConnection)

        Dim dsi As DataSet
            dsi = New System.Data.DataSet

            MyCommand.Fill(dsi)
            MyConnection.Close()

        DataGrid1.DataSource = dsi.Tables(0)

    End Sub
 


kaifong78Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
flavoConnect With a Mentor Commented:
change  select * from [sheet1$]", MyConnection

to

select [Batch No.],  [Chassis No.] from [sheet1$]", MyConnection

Im not too sure if its going to like those dots though..

Good Luck!

DAve

0
 
123654789987Commented:
Try this function. In this function you open a Excel worksheet. Only the UsedColumns and Rows of the excel application are added to a datatable. Then this datatable is assigned to a datagrid.

    Private Sub btUpload_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btUpload.Click
        Dim objExcel As Excel.Application
        Dim objWorkBook As Excel.Workbook
        Dim objWorksheet As Excel.Worksheet
        Dim worklistsheet As Excel.Worksheet
        Dim dtUploadWorklist As New DataTable()
        Dim dtExcelTable As New DataTable("UploadTable")
        Dim excelRows As Int32
        Dim excelColumns As Int32
        Dim dgColumn As Integer
        Dim dgRow As Integer
        Dim i As Integer
       

        dsUpload = New DataSet()
        'A table by name dtExcelTable is added to the dataset
        dsUpload.Tables.Add(dtExcelTable)
        Try
            'An excel object is being created.
            objExcel = CreateObject("Excel.Application")
            'If the user clicks on Ok in the OpenFile Dialog then, if the selected file
            'exists it is assigned to the excel object created.
            If (ofdWorklist.ShowDialog() = DialogResult.OK) Then
                dgWorkList.DataSource = Nothing
                ofdWorklist.Filter = "Xls files (*.xls)|*.XLS"
                ofdWorklist.CheckFileExists = True
                objWorkBook = objExcel.Workbooks.Open(ofdWorklist.FileName)
            Else
                'If the user clicks on Cancel in the OpenFileDialog, the dialog is closed
                ofdWorklist.Dispose()
            End If
            ' The contents of Worksheet(1) is being assigned to a worksheet
            objWorksheet = objWorkBook.Sheets(1)
         
               

            'dtExcelTable is table created to store the  lines from the excel.
            Dim objArray(objWorksheet.UsedRange.Columns.Count - 1) As String
            For excelColumns = 1 To objWorksheet.UsedRange.Columns.Count
                dtExcelTable.Columns.Add(objWorksheet.Cells(8, excelColumns).Text)
            Next
            For excelRows = 1 To objWorksheet.UsedRange.Rows.Count + 1
                For excelColumns = 1 To objWorksheet.UsedRange.Columns.Count
                    objArray(excelColumns - 1) = _
                    objWorksheet.Cells(excelRows, excelColumns).Text
                Next

                Dim columnCount As Int32
                columnCount = dtExcelTable.Columns.Count()
                dtExcelTable.Rows.Add(objArray)
                Array.Clear(objArray, 0, objArray.Length)
            Next

         
         
              dgWorkList.DataSource = dsUpload.Tables("UploadTable")

                     'To kill the excel object created.
            objWorksheet = Nothing
            objWorkBook = Nothing
            objExcel.Quit()
            objExcel = Nothing
            GC.Collect()
        Catch
            If ofdWorklist.ShowDialog.Cancel Then
                Exit Sub
            End If
            MessageBox.Show("Please select an excel file to upload to the grid.", _
            Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End Try
    End Sub
0
 
kaifong78Author Commented:
Hi Dave,

I try your code 'select [Batch No.],  [Chassis No.] from [sheet1$]", MyConnection' it is woking....Thanks a lot. :)

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
flavoCommented:
Cheers mate!

Just wasnt 100% about the dots.

:-)

Dave
0
 
kaifong78Author Commented:
Hi,

I have an errors using these code...

dsUpload -> name is not declared......  
ofdWorklist.  -> same errors
dgWorkList.   -> same error

How do I declared ?

Thanks
0
 
kaifong78Author Commented:
Hmmm ..how about if allowed user choose which location they want to save the file? rather than "data source=c:\data.xls

can sent me a code? thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.