<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="ExcelImport._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
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 />
</div>
</form>
</body>
</html>
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()
Try
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
Finally
If drExcel IsNot Nothing Then
drExcel.Close()
End If
End Try
End Sub
'This function is not ideal
Private Function IsColumnExist(ByVal drExcel As OleDbDataReader, ByVal columnName As String) As Boolean
Try
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()
Try
cnAdo.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES';Data Source=C:\\Book1.xls;"
cnAdo.Open()
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
cnAdo.Close()
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)
connection.Open()
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
Next
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.
Reference:
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
