Solved

How to ignore the DataGrid filed ?

Posted on 2004-10-11
6
231 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
  • 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

785 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