Reading Excel File

Posted on 2006-06-02
Last Modified: 2008-02-01
How can I read an Excel spreadsheet into a VB.BET 2003 WinForm app? Assuming the client does not have Excel installed - all he has is an Excel file - can this be done? I just need to read the rows/columns from the Excel file and save them to a table in an Access database - and this "importing" function must be implemented in VB.NET 2003.

Question by:SETP
    LVL 34

    Accepted Solution

    Here's a sub I use

        Private Sub ExcelToAccess(ByVal sourceFile As String, ByVal sourceSheet As String, ByVal targetFile As String, ByVal targetTable As String)

            'Sub transfers all records from .xls sourcefile.sourcesheet ...
            '... to .mdb targetfile.targettable
            'It is assumed that the .mdb targettable definition already ...
            '... exists, with the same number and types of fields, ...
            '... in the same order, as the .xls worksheet columns.
            'It does not depend on the .mdb field names being the same ...
            '... as the .xls column headings: although it does assume ...
            '... that the .xls columns are named.

            If Not sourceSheet.EndsWith("$") Then
                sourceSheet &= "$"
            End If

            Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
            Dim sourceSQL As String = "SELECT * FROM [" & sourceSheet & "]"
            Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetFile & ";User Id=admin;Password=;"
            Dim targetSQL As String = "SELECT * FROM " & targetTable

            'use dataadapter for target and command builder to ...
            '... create insert command, including parameter collection
            Dim targetCon As New OleDbConnection(targetConStr)
            Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
            Dim cb As New OleDbCommandBuilder(targetDA)
            Dim targetCommand As OleDbCommand = cb.GetInsertCommand

            'now do the work
            Dim sourceCon As New OleDbConnection(sourceConStr)
            Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
            Dim sourceReader As OleDbDataReader
            sourceReader = sourceCommand.ExecuteReader()
            While sourceReader.Read()
                'for each row from source
                For i As Integer = 0 To sourceReader.FieldCount - 1
                    'load values into parameters
                    targetCommand.Parameters(i).Value = sourceReader(i)
                    'if any of the source data needs to be modified ...
                    '... for the target, it could be done here ...
                '... or here
                'then write to target
            End While

        End Sub

    So far as I know it's not dependent on the source machine having Excel installed, but I haven't ever (so far as I know) tested it in that environment.

    LVL 35

    Expert Comment

    you could save the Excel file as CSV file and then read it into a datatable and insert in Access
    LVL 5

    Expert Comment

    you just include the excel interop into your project excel.dll.  Make sure you copy local.

    past that here is an article for using excel from MS;EN-US;306022

    This is my excel controller

    Imports Excel
    Imports System.Collections.Generic
    Imports System.IO

    Namespace WinShed.Data
        Public Delegate Sub ExcelWriteProgressEventHandler()

        Public Class ExcelSpreadSheetController
    #Region "Events"
            Public Event ExcelRowWrittenMessageEvent As ExcelWriteProgressEventHandler
    #End Region

    #Region "Spread Sheet Logic"
            Public Function CreateSpreadSheet(ByRef sched As System.Data.DataTable, ByRef xlsPath As String) As Boolean
                Dim retVal As Boolean = True
                Dim row As Integer = 1

                Dim app As New Excel.Application
                Dim exbook As Excel.Workbook
                Dim exsheet As Excel.Worksheet
                exbook = app.Workbooks.Add
                exsheet = CType(exbook.Sheets(1), Worksheet)
                Dim style1 As Excel.Style = exbook.Styles.Add("Style1")
                style1.Font.Size = 9
                style1.NumberFormat = "@"

                    exsheet.Cells(row, 1) = "COL1"
                    exsheet.Cells(row, 2) = "COL2"
                    exsheet.Cells(row, 3) = "COL3"

                    For Each schedRow As DataRow In sched.Rows
                    row += 1
                    Me.PutValueIntoCellAndFormat(exsheet, style1, row, 1, schedRow("Col1").ToString())
                    Me.PutValueIntoCellAndFormat(exsheet, style1, row, 2, schedRow("Col2").ToString())
                    Me.PutValueIntoCellAndFormat(exsheet, style1, row, 3, schedRow("Col3").ToString())
                    RaiseEvent ExcelRowWrittenMessageEvent()

                    If (File.Exists(xlsPath)) Then
                    End If
                Catch ex As Exception
                    retVal = False
                End Try
                Return retVal
            End Function

            Private Sub PutValueIntoCellAndFormat(ByRef sheet As Excel.Worksheet, ByRef style As Excel.Style, ByRef row As Integer, ByRef col As Integer, ByRef val As String)
                Dim cell As Excel.Range = CType(sheet.Cells(row, col), Range)
                cell.Style = style
                cell.Value = val
            End Sub
    #End Region
        End Class
    End Namespace
    LVL 5

    Expert Comment

    my controller writes excel, but you should get the gist :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    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…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now