Solved

How to ignore the DataGrid filed ?

Posted on 2004-10-11
6
243 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
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!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
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 …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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