How to read an Excel file using VB.Net

By: Rajesh P.S.

The following program demonstrates the process of opening an existing Excel spreadsheet in VB.NET, using the COM interop capability provided by the .NET Framework. Additionally, this program provides insights on how to efficiently identify the last used row within the Excel worksheet and how to locate and retrieve named ranges present within the Excel file.

Excel Data Reader using Excel Interop

The provided code snippet demonstrates the utilization of the Microsoft Excel 12.0 Object Library in VB.Net to read data from an Excel file. As previously discussed, we have learned the procedure to import the Microsoft Excel 12.0 Object Library into a VB.Net project in the preceding section.

How to add Excel Library

Reading Cells in Excel Using VB.NET

If you want to select a specific cell in Excel sheet, you can code like this.

Dim excelSheet As Excel.Worksheet = workbook.ActiveSheet Dim rng As Excel.Range = DirectCast(excelSheet.Cells(10, 10), Excel.Range)

Using EXCEL Named Ranges in VB.NET

If you want to select multiple cell value from Excel sheet, you can code like this.

Dim excelSheet As Excel.Worksheet = workbook.ActiveSheet Dim rng As Excel.Range = DirectCast(excelSheet.get_Range(excelSheet.Cells(1, 1), excelSheet.Cells(3, 3)), Excel.Range)

How to get the range of occupied cells in excel sheet

Find the last used row in Excel with VB.Net , get the last filled excel row

To read all the utilized rows of an Excel file, it is necessary to determine the number of cells that have been used within the file. To accomplish this, we can employ the "UsedRange" property of the xlWorkSheet object. The UsedRange property encompasses all cells that have been utilized, providing us with the last cell within the used area of the worksheet.

Dim range As Excel.Range range = xlWorkSheet.UsedRange

Full Source VB.NET

Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim range As Excel.Range Dim rCnt As Integer Dim cCnt As Integer Dim Obj As Object xlApp = New Excel.ApplicationClass xlWorkBook = xlApp.Workbooks.Open("c:\vbexcel.xlsx") xlWorkSheet = xlWorkBook.Worksheets("sheet1") range = xlWorkSheet.UsedRange For rCnt = 1 To range.Rows.Count For cCnt = 1 To range.Columns.Count Obj = CType(range.Cells(rCnt, cCnt), Excel.Range) MsgBox(Obj.value) Next Next xlWorkBook.Close() xlApp.Quit() releaseObject(xlApp) releaseObject(xlWorkBook) releaseObject(xlWorkSheet) End Sub Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub End Class

When you execute this source code the program read all content from Excel file.

Next > Add new worksheet in Excel file - VB.Net

Related Topics

More Related Topics.....