Import Data from Excel File using VB .NET

The following is the sample code how to query an Excel spreadsheet from an ASP.NET page using VB .NET:

Dim strConn As String
Dim da As OleDbDataAdapter
Dim ds As New DataSet

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""
da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

dataGrid1.DataSource = ds.Tables(0).DefaultView
dataGrid1.DataBind()
da.Dispose()

Note:

• The code above is just select data from Sheet1 (Worksheet) only.

If you want to select data from first sheet in the Excel workbook, you need to know the name of the first sheet first.
To get the name of the first sheet in the Excel workbook, refer code below:

Dim dao_dbE As dao.DBEngine
Dim dao_DB As DAO.Database
Dim strFirstSheetName As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strFirstSheetName = dao_DB.TableDefs(0).Name
da0_DB.Close()

Note:

• Microsoft DAO 3.5 Library needs to add to the project when source code above is used. From the Project menu, click References, click Add Reference… and then select the Microsoft DAO 3.5 Library to add.

So, complete source code should be like this:

Dim strConn As String
Dim da As OleDbDataAdapter
Dim ds As New DataSet
Dim dao_dbE As dao.DBEngine
Dim dao_DB As DAO.Database
Dim strFirstSheetName As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strFirstSheetName = dao_DB.TableDefs(0).Name
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""

da = New OleDbDataAdapter("SELECT * FROM [" & _
strFirstSheetName & "]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

dataGrid1.DataSource = ds.Tables(0).DefaultView
dataGrid1.DataBind()

da.Dispose()
da0_DB.Close()

Highlight Lottery Numbers (TOTO) using Microsoft Excel

Let me show you an example how to highlight the ticket numbers that have been drawn in a lottery. Let said you have 5 ticket numbers that are in cells B4:G8 and the drawn numbers are entered in cells B2:G2.

1. Select cells B4:G8.
   
2. Choose [Format] > [Conditional Formatting].
   
3. From the first dropdown list, choose Formula Is.
   
4. For the formula, use the CountIf function: =COUNTIF($B$2:$G$6,B4)
   
5. Click the [Format] button.
   
6. Select formatting options (green pattern, in this example), then click [OK] button.
   
7. Finally, click [OK] button.

Wish you have a lucky day!

How to link to location on the same page using HTML

How to create a links that can jump into specific section on a page? We can do it by using the Anchor Tag and the Name Attribute. That is simple and direct.
Below is the syntax:
<a name=”name_of_section”>Text to be displayed</a>
<a href=#name_of_section”>Text to be displayed</a>

For Example:
I put the line below at the top of this post
<a name=”top”></a>
To go back to the top of this post, I use the following line:
<a href=#top>Go to top of this post</a>
Go to top of this post

You can also link to a particular section on another page directly too; just add a # sign and the name of the anchor to the end of a URL. For Example, to link directly to section 2 (“w2”) of my previous post (with title “ASP .NET Web Service: “The request failed with HTTP status 401: Access Denied.” Error”), I use like this:
<a href="http://wec-library.blogspot.com/2008/02/asp-net-web-service-request-failed-with.html#w2”>Section 2 of Previous Post</a>
Section 2 of Previous Post

ASP .NET Web Service: “The request failed with HTTP status 401: Access Denied.” Error

You may face this error when trying to access a Web Service from your ASP.NET Application. This is because the Anonymous access authentication for the Web Service is turned off. I also was facing this error before. However, I found out 2 ways to resolve this error, there are:

1. Enable Anonymous Access in the IIS Directory Security
2. Assign the Credential Cache Programmatically

1. Enable Anonymous Access in the IIS Directory Security
To do this, try the following steps:

1. Click [Start] -> [Run] -> Type “inetmgr” and press [OK] or [Enter] key to open IIS Control panel.
   
2. Expand the appropriate nodes and navigate to the virtual directory of your Web Service Application.
   
3. Select the Virtual Directory, right click and select [Properties].
   
4. Click to [Directory Security] Tab and then click [Edit] button.
   
5. Check the [Anonymous access] check box.
   
6. Click [OK] twice to exit.

2. Assign the Credential Cache Programmatically
You also can programmatically to allow the permissions by specifying the credential cache.

Let say WS is the object of your Web Service (WebService1 which has the default Method HelloWorld.)

C#
WebService1 WS = new WebService1();
WS.Credentials = System.Net.CredentialCache.DefaultCredentials;
Response.Write(WS.HelloWorld());

Visual Basic
Dim WS As New WebService1
WS.Credentials = System.Net.CredentialCache.DefaultCredentials
Response.Write(WS.HelloWorld())