Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleRead data from an Excel workbook in Visual Basic .NET
DescriptionThis example shows how to read data from an Excel workbook in Visual Basic .NET.
KeywordsMicrosoft Office, Office, Excel, Microsoft Excel, read from Excel, cell color, cell format, Excel cell color, Excel cell format
CategoriesOffice, VB.NET
 

A Visual Basic .NET program can open the Excel application and use it as a server to manipulate Excel workbooks.

First open the Add References dialog. On the COM tab, select "Microsoft 12.0 Object Library" (or whatever version you have installed on your system).

Add the following using statement to make working with the Excel namespace easier. The "Excel =" part means you can use "Excel" as an alias for the namespace.

    Imports Excel = Microsoft.Office.Interop.Excel

This example uses the following code to open a workbook and read data from it.

 
' Read from the Excel workbook.
Private Sub btnRead_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnRead.Click
    ' Get the Excel application object.
    Dim excel_app As New Excel.ApplicationClass()

    ' Make Excel visible (optional).
    excel_app.Visible = True

    ' Open the workbook read-only.
    Dim workbook As Excel.Workbook = _
        excel_app.Workbooks.Open( _
        Filename:=txtFile.Text, ReadOnly:=True)

    ' Get the first worksheet.
    Dim sheet As Excel.Worksheet = _
        DirectCast(workbook.Sheets(1), Excel.Worksheet)

    ' Get the titles and values.
    SetTitleAndListValues(sheet, 1, 1, lblTitle1, lstItems1)
    SetTitleAndListValues(sheet, 1, 2, lblTitle2, lstItems2)

    ' Save the changes and close the workbook.
    workbook.Close(SaveChanges:=True)

    ' Close the Excel server.
    excel_app.Quit()
End Sub
 
First the code creates an Excel.Application object to control Excel. It makes that object visible so you can watch it work. Often you will not want to make the server visible.

Next the code opens a workbook. Most of the Open method's parameters are optional (things such as passwords) so they are omitted. This example opens the workbook read-only because it doesn't need to write into the workbook.

The code uses the first worksheet. Note that indexes in Excel tend to start with 1 not 0.

The code calls the SetTitleAndListValues method twice to get data from the worksheet and then closes Excel. The most interesting work is performed by the following SetTitleAndListValues method.

 
' Set a title Label and the values in a ListBox. Get the
' title from cell (row, col).
' Get the values from cell (row + 1, col) to the end of the
' column.
Private Sub SetTitleAndListValues(ByVal sheet As _
    Excel.Worksheet, _
    ByVal row As Integer, ByVal col As Integer, ByVal lbl As _
        Label, ByVal lst As ListBox)
    Dim range As Excel.Range

    ' Set the title.
    range = DirectCast(sheet.Cells(row, col), Excel.Range)
    lbl.Text = CStr(range.Value2)
    lbl.ForeColor = System.Drawing.ColorTranslator.FromOle( _
        _
        CInt(CDbl(range.Font.Color)))
    lbl.BackColor = System.Drawing.ColorTranslator.FromOle( _
        _
        CInt(CDbl(range.Interior.Color)))

    ' Get the values.
    ' Find the last cell in the column.
    range = DirectCast(sheet.Columns(col, Type.Missing), _
        Excel.Range)
    Dim last_cell As Excel.Range = _
        range.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)

    ' Get a Range holding the values.
    Dim first_cell As Excel.Range = _
        DirectCast(sheet.Cells(row + 1, col), Excel.Range)
    Dim value_range As Excel.Range = _
        DirectCast(sheet.Range(first_cell, last_cell), _
        Excel.Range)

    ' Get the values.
    Dim range_values As Object(,) = _
        DirectCast(value_range.Value2, Object(,))

    ' Convert this into a 1-dimensional array.
    ' Note that the Range's array has lower bounds 1.
    Dim num_items As Integer = range_values.GetUpperBound(0)
    Dim values1(num_items) As String
    For i As Integer = 0 To num_items - 1
        values1(i) = CStr(range_values(i + 1, 1))
    Next i

    ' Display the values in the ListBox.
    lst.DataSource = values1
End Sub
 
SetTitleAndListValues is a fairly specialized method. It gets a title from a cell and gets values from the cells below that one.

The code first gets a Range representing the title cell. It uses the Range's Value2 property for the title text. It sets the Label's colors from the Range's Font.Color and Interior.Color values.

Next the code gets the values below the title. It first gets a Range representing the entire column. It then uses the Range's End method to get a range representing the column's last used cell. The code creates another Range representing the first cell containing values and then uses the two cells to get a new Range representing all of the value cells. (This is easier if you know where the cells are ahead of time so you don't need to mess around trying to find the last cell.)

The code then uses the Range's Value method to get a 2-dimensional array of values. Note that this is a 2-dimensional array even though it contains only one column.

The code copies the values into a 1-dimensional array and displays it in the ListBox.

 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated