Excel Read - Read Values from Spreadsheet - July 2010

If you download this tip please consider making a modest donation.

I recently needed to open, then read values stored in an Excel spreadsheet. So I wrote some VB.Net code to perform that function. I encapsulated the code in a module called 'cExcelHandler'. This becomes the July 2010 tip-of-the-month.

The Demo Application

I wrote a VB.Net, Asp.Net, VS2008 application to demonstrate the code. This application opens a specified Excel file and displays its contents in a table. The sample is provided in the downloadable source - see below.

Sample Calling Code

Use code similar to this to get the values:

Private Sub ReadExcel(ByVal sFilenameFullyQualified As String)
   
Dim moColl As New Collection  'this is were the Excel values are stored
    Dim oEH As New cExcelHandler  'the class - the subject of this tip
   
With oEH
        moColl = .
Read(sFilenameFullyQualified) 'this is were it all happens
   
End With
End
Sub
The Read method opens the file and reads values from the first worksheet
in the first workbook of sFilenameFullyQualified

Sample Code to Use the Values

Use code similar to this to process the values:

Private Sub ShowResults(ByVal oTable As Table, ByVal oColl As Collection, _
                        ByVal
sFilenameFullyQualified As String)
   
Dim oERow As New cExcelRow   'Excel Row
    Dim oECell As New cExcelCell 'Excel Cell
   
Dim oT As New cTable 'some useful table handling methods
    With oT
        With oTable
        .Caption = sFilenameFullyQualified
        End With
        For
Each oERow In oColl 'for each Excel row in the spreadsheet
            Dim oRow As New TableRow 'define a new table row
            For Each oECell In oERow.collCells 'loop thru the row cell collection
               
Dim oCell As New TableCell 'define a new table cell
                With oCell
                    .Text = oECell.oValue

               
End With
               
oRow.Controls.Add(oCell) 'add the table cell to the table row
           
Next
           
oTable.Controls.Add(oRow) 'add the table row to the table
       
Next
    End
With
End
Sub
 

Opens the first Workbook and first Worksheet

The 'Read' method opens the Excel spreadsheet, opens the first workbook then reads data from the first worksheet. There may be more than one workbook and more than one worksheet in each workbook. You may want to modify the code to process all your worksheets and workbooks.

Operative Code in cExcelHandler - UsedRange

Excel provides two properties that help determine the range of active cells. The properties are: UsedRange.Rows.Count and UsedRange.Columns.Count. Those properties determine the limits of the active values. See the source code provided in the downloadable file for details.

Launching Excel

You may want to review the VB6 'Launch Excel and Open a Spreadsheet - January 2004' tip which shows how to open an Excel spreadsheet in a desktop application. Use your browser's back button to return to this page.

Download VB.Net Code

Download the source code by clicking on the zipped file name.

Download - Source Zipped File Size
VS2008 VB Solution ExcelRead 91K

Previous Tip   Tip Index    Next Tip

Home

Updated 2010/07/30