Solved

How to ignore the DataGrid filed ?

Posted on 2004-10-11
6
246 Views
Last Modified: 2010-04-23
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
 


0
Comment
Question by:kaifong78
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 34

Accepted Solution

by:
flavo earned 125 total points
ID: 12284074
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
 
LVL 10

Expert Comment

by:123654789987
ID: 12284087
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
 

Author Comment

by:kaifong78
ID: 12284201
Hi Dave,

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

0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 34

Expert Comment

by:flavo
ID: 12284222
Cheers mate!

Just wasnt 100% about the dots.

:-)

Dave
0
 

Author Comment

by:kaifong78
ID: 12284273
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
 

Author Comment

by:kaifong78
ID: 12284281
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question