Reading an Excel Spreadsheet via VB.net

[Papa_Don]

Hi group,

I'm modifying a VB.net app where I need to loop through an Excel spreadsheet for data out of two column. In essence the code will need to read:

For i = 2 to end of file
    propNo = Cells(i, 2).Value
    crms = Cells(i, 8).Value
    If crms = "Y" Then
        ' Go run the routine
    End If
Next i

Unfortunately I have no idea how to get this information from the spreadsheet. I did find a link here that may explain it, but I'm not sure I fully grasp it and it seems rather long and complicated. I know how to read a text file. But since my info is actually in a Excel file, I need to read it.

Here's the link I was referring to: [(https://www.daniweb.com/programming/software-development/code/474907/how-to-read-an-excel-spreadsheet-using-adodb)]

Can you help?

As always, thanks for your assistance.

Don

[Minimalist]

Hi Don,
its not that hard. There is good example here:
http://www.visual-basic-tutorials.com/WriteToExcel2007.php
The most important thing is to follow the instructions in regard to add the reference.

[Teme64]

Before doing any coding you have to consider what will be the environment where your application will be used:
- is Office (i.e. Excel) installed
- is Office (i.e. Excel) installed with .NET programming support
- is reading Excel file(s) all you really need
- is there a possibility you will need to "do more Excel" like create Excel files, format cells etc.

Assuming all you need is to read Excel file(s), there are at least two ways to do it. First one is to threat Excel file as a database which has multiple tables (Excel sheets). The second one is to handle Excel file (almost) like you would do in the Excel application. The latter one requires that you do have Excel installed. The former one is simpler and it works weather Excel is installed or not. Both approaches do require some data access components which you may (or may not) have installed.

I wrote examples for both cases. I wrote them first with CSharp and then converted to VB.NET so there may be some oddities in these examples. I used VS Express 2010 version for programming with 64-bit version of Windows 7 and .NET 4.0 and I had 32-bit Office 2010 installed. For testing I used a very short and simple Excel file in 2010's native XLSX-format. Examples should work with newer Office version too. For the older Excel file formats you have to change a bit the connectionstring in the first example (OleDb-version).

I started with an empty WinForms project, added two buttons and a multiline textbox to the form. Button1 contains OleDb version and Button2 is for Interop-version (i.e. Excel installed).

    Option Strict On
    Option Explicit On

    ' Set references from project's properties to
    ' NET component: System.Data
    ' COM component: Microsoft Excel 14.0 Object Library

    Imports System.IO
    Imports System.Data.OleDb ' MDac (Microsoft Data Access Tool) and OleDb
    Imports Microsoft.Office.Interop.Excel ' Add ref to COM-object Microsoft.Office.Interop.Excel

    ' Notice: Do not compile code to 'Any CPU', you have to set the target platform to x86 (or x64)

    ' Possible errors caused by missing data components
    ' 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine:
    ' http://www.microsoft.com/en-us/download/details.aspx?id=13255
    '
    ' To start with missing Office PIA (Office Primary Interop Assemblies):
    ' https://msdn.microsoft.com/en-us/library/kh3965hw%28v=vs.100%29.aspx
    ' and to download PIA (for Office 2010): https://msdn.microsoft.com/en-us/library/kh3965hw%28v=vs.100%29.aspx

    Public Class Form1

      Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim conStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\temp\fruits.xlsx;" +
                    "Extended Properties='Excel 12.0 Xml;HDR=Yes;'"
        ' HDR=Yes skips first row which contains headers for the columns
        Dim conn As System.Data.OleDb.OleDbConnection ' Notice: I used a fully qualified name 
        ' because Microsoft.Office.Interop.Excel contains also a class named OleDbConnection
        Dim cmd As OleDbCommand
        Dim dataReader As OleDbDataReader
        Dim tempStr As String
        Dim tempStr2 As String

        ' Create a new connection object and open it
        conn = New System.Data.OleDb.OleDbConnection(conStr)
        conn.Open()
        ' Create command text with SQL-style syntax
        ' Notice: First sheet is named Sheet1. In the command, sheet's name is followed with dollar sign!
        cmd = New OleDbCommand("select * from [Sheet1$]", conn)
        ' Get data from Excel's sheet to OleDb datareader object
        dataReader = cmd.ExecuteReader()
        TextBox1.Clear()
        ' Read rows until an empty row is found
        While (dataReader.Read())
          ' Index of column B is 0 because it is range's first column
          tempStr = dataReader.GetValue(0).ToString()
          ' Index of column D is 2 because it is range's third column
          tempStr2 = dataReader.GetValue(2).ToString()
          TextBox1.AppendText(String.Format("Fruit {0} with {1} units", tempStr, tempStr2) + Environment.NewLine)
        End While
        conn.Close()
        ' Dispose all objects
      End Sub

      Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Dim App As Microsoft.Office.Interop.Excel.Application
        Dim Wb As Microsoft.Office.Interop.Excel.Workbook
        Dim wbSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim lastRowIndex As Integer
        Dim currRowIndex As Integer
        Dim currRow As Range
        Dim valueB As String
        Dim valueD As String

        ' Create a new Excel application instance
        App = New Microsoft.Office.Interop.Excel.Application()
        ' Open a workboob (i.e. Excel file)
        Wb = App.Workbooks.Open("D:\temp\fruits.xlsx")
        ' Read data from a sheet named "Sheet1" and from columns B and D
        wbSheet = CType(Wb.Sheets("Sheet1"), Worksheet)
        ' Here's a trick to find last row in this sheet
        lastRowIndex = wbSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row
        currRowIndex = 1 ' Set first row to 2 to skip header row if needed
        TextBox1.Clear()
        While (currRowIndex < lastRowIndex)
          ' Get one row at time. Now Cells' row index is alway 1
          currRow = CType(wbSheet.Rows(currRowIndex), Range)
          ' Get values from columns
          ' Columns are indexed as A=1, B=2, C=3, D=4...
          valueB = CType(currRow.Cells(1, 2), Range).Value2.ToString() ' Col B
          valueD = CType(currRow.Cells(1, 4), Range).Value2.ToString() ' Col D
          ' Output data
          TextBox1.AppendText(String.Format("Fruit {0} with {1} units", valueB, valueD) + Environment.NewLine)
          currRowIndex += 1
        End While
        ' Dispose all objects
      End Sub

    End Class

I put a lot of comments in the code so hopefully the code is "readable".

This is not meant to scare you :) This is basically not that much different from reading text files :)

HTH

P.s. here's the test data I used