Monday, July 21, 2008

Read data from Excel in VB.NET

The following code will show you how to read data from Excel file and to be able to check whether a specific column name exists in the excel file.

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="ExcelImport._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

<html xmlns="" >

<head runat="server">

<title>Untitled Page</title>



<form id="form1" runat="server">


Column A: <asp:Label ID="lblColumnA" runat="server"></asp:Label><br />

Column B: <asp:Label ID="lblColumnB" runat="server"></asp:Label><br />

Column C: <asp:Label ID="lblColumnC" runat="server"></asp:Label><br />





Imports System.Data.OleDb

Partial Public Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim drExcel As OleDbDataReader = GetData()


While drExcel.Read()

lblColumnA.Text = drExcel("columnA").ToString()

lblColumnB.Text = drExcel("columnB").ToString()

'If IsColumnExist(drExcel, "columnv") Then

' lblColumnC.Text = drExcel("columnv").ToString()

'End If

If IsColumnExist("columnv") Then

lblColumnC.Text = drExcel("columnv").ToString()

End If

End While

Catch ex As Exception

Throw ex


If drExcel IsNot Nothing Then


End If

End Try

End Sub

'This function is not ideal

Private Function IsColumnExist(ByVal drExcel As OleDbDataReader, ByVal columnName As String) As Boolean


If drExcel(columnName) IsNot Nothing Then

End If

Catch ex As Exception

Return False

End Try

Return True

End Function

Private Function GetData() As OleDbDataReader

Dim cnAdo As New OleDbConnection()

Dim cmCommand As New OleDbCommand()


cnAdo.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES';Data Source=C:\\Book1.xls;"


cmCommand.Connection = cnAdo

cmCommand.CommandText = "SELECT * FROM [Sheet1$]"

cmCommand.CommandType = CommandType.Text

Return cmCommand.ExecuteReader(CommandBehavior.CloseConnection)

Catch ex As Exception

If cnAdo.State = ConnectionState.Open Then


End If

Throw ex

End Try

End Function

Private Function IsColumnExist(ByVal columnName As String) As Boolean

Dim dt As DataTable

Dim restrictions As String() = {Nothing, Nothing, "Sheet1$", Nothing}

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES';Data Source=C:\\Book1.xls;"

Using connection As New OleDbConnection(connectionString)


dt = connection.GetSchema("Columns", restrictions)

End Using

For Each row As DataRow In dt.Rows

If row("COLUMN_NAME") IsNot DBNull.Value AndAlso row("COLUMN_NAME").ToString.Equals(columnName) Then

Return True

End If


Return False

End Function

End Class

This is the excel file that I used in this demo:

To figure out the column name (COLUMN_NAME) of the datatable, you will need to use the DataSet Visualizer, just double click the magnifier icon.

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
blog comments powered by Disqus