Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to ignore the DataGrid filed ?

Posted on 2004-10-11
6
Medium Priority
?
251 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 500 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
Technology Partners: 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!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

670 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