Net-informations.com
SiteMap  | About    

How to read an Excel file using VB.Net

VB.Net - Excel Data Reader Library

The following program illustrates how to open an existing Excel spreadsheet in VB.Net using .NET Framework COM interop capability. Also this program explains how to Find the last used row in Excel and how to find Named Ranges in Excel

Excel Data Reader using Excel Interop

How to specify a range in Excel sheet in VB.Net?

The following VB.Net source code using Microsoft Excel 12.0 Object Library for reading an Excel file. In the previous section we saw how to import Microsoft Excel 12.0 Object Library in the VB.Net project .

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

For reading all used rows of an Excel file in VB.Net, we have to know how many cells used in the Excel file. In order to find the used range we use "UsedRange" property of xlWorkSheet . A used range includes any cell that has ever been used. It will return the last cell of used area.

Dim range As Excel.Range range = xlWorkSheet.UsedRange



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.






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.