[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Reading Excel File

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.

Thanks
0
SETP
Asked:
SETP
  • 2
1 Solution
 
SanclerCommented:
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)
        targetCommand.Connection.Open()
        sourceCon.Open()
        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 ...
            Next
            '... or here
            'then write to target
            targetCommand.ExecuteNonQuery()
        End While
        sourceReader.Close()
        sourceCon.Close()
        targetCommand.Connection.Close()

    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.

Roger
0
 
YZlatCommented:
you could save the Excel file as CSV file and then read it into a datatable and insert in Access
0
 
mydasxCommented:
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

http://support.microsoft.com/default.aspx?scid=kb;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 = "@"

            Try
                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()

                Next
                If (File.Exists(xlsPath)) Then
                    File.Delete(xlsPath)
                End If
                exbook.SaveAs(xlsPath)
            Catch ex As Exception
                retVal = False
            Finally
                exbook.Close()
            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
0
 
mydasxCommented:
my controller writes excel, but you should get the gist :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now