Results 1 to 3 of 3

Thread: Excel Component Tray helper

  1. #1

    Thread Starter
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Excel Component Tray helper

    The attached VS2012 solution contains a project which has a component tray designed to assist those who have not done much with creating connection strings via OleDb data provider to work with Excel. The component tray if found useful could be placed into a class project, compiled then added to the IDE toolbox.

    Please note that the component works for many xlsx and xls file but there may be some it does not until IMEX is setup correctly.

    MSDN_ExcelHelper.zip

    The main form has two of these components to examine that are setup while the third is not setup. To setup the component single click on ExcelHelperEmpty, select properties then select FileName which shows a button. Pressing the button you are prompted for a xlsx file, change the filter for selecting a xlx file. Once a file has been selected set the property Headers. Yes indicates sheets that you want to read the first row represents field names while No indicates the first row has data. If there is nothing special about the sheets to read leave Mode at Normal, Mode indicates the IMEX setting for the connection.

    In the demo project's main form the first two lines show the connection strings for two of the components, next IsReady determines if it is safe to get sheet names for the ExcelHelper1 file and place the names into a ComboBox used later to read one of the sheets.

    Code:
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Console.WriteLine(ExcelHelper1.ConnectionString)
        Console.WriteLine(ExcelHelper2.ConnectionString)
        '
        ' Lets make sure the file exists
        '
        If ExcelHelper1.IsReady Then
            cboSheetNames.DataSource = ExcelHelper1.SheetNames
            txtConnectionString.Text = ExcelHelper1.ConnectionString
        Else
            cmdDataInformation.Enabled = False
            cmdLoad.Enabled = False
            MessageBox.Show("Excel file not available.")
        End If
    
    End Sub
    Select a sheet then pressing the load button reads the sheet data into a DataGridView.
    Code:
    Private Sub cmdLoad_Click(sender As Object, e As EventArgs) Handles cmdLoad.Click
    
        Dim dt As New DataTable
    
        Using cn As New OleDb.OleDbConnection With
            {
                .ConnectionString = ExcelHelper1.ConnectionString
            }
            Using cmd As New OleDb.OleDbCommand With
                {
                    .Connection = cn,
                    .CommandText = "SELECT * FROM [" & cboSheetNames.Text & "]"}
                cn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using
    
        DataGridView1.DataSource = dt
    
    End Sub
    Properties of the component
    Name:  EH.png
Views: 442
Size:  23.1 KB

  2. #2
    Frenzied Member
    Join Date
    Oct 2012
    Location
    Tampa, FL
    Posts
    1,187

    Re: Excel Component Tray helper

    Is there a way to force the typing to always choose system.string for the entire sheet?

    I tried modifying registry entry HKLM\...\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows to 0, but that did not seem to help.

  3. #3

    Thread Starter
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Excel Component Tray helper

    Quote Originally Posted by jayinthe813 View Post
    Is there a way to force the typing to always choose system.string for the entire sheet?

    I tried modifying registry entry HKLM\...\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows to 0, but that did not seem to help.
    That is beyond the scope of the component.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width