Excel Read - Read Values from Spreadsheet - July 2010
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.
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.
Use code similar to this to get the values:
The Read method opens the file and reads values from the first worksheet
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
moColl = .Read(sFilenameFullyQualified) 'this is were it all happens
in the first workbook of sFilenameFullyQualified
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
.Caption = sFilenameFullyQualified
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
.Text = oECell.oValue
oRow.Controls.Add(oCell) 'add the table cell to the table row
oTable.Controls.Add(oRow) 'add the table row to the table
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.
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.
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 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