Xcel Comparison in VISUAL STUDIO

Using Excel In VISUAL STUDIO.NET 2008

Hamsa Gayatri Palepu

CIS 764, Fall 2008

Purpose:

This tutorial shows use of Microsoft Visual Studio 2008 using VB.net and using Excel sheets as a data source to build a web application.

Topics:

Overview

Prerequisites

Creating Excel sheets

Create a new project

Overview:

ASP.NET is a rich programming framework for building web-based applications. It offers outstanding support for both developers and administrators, providing improved ease-of-use, tool support, reliability, scalability, administration and security.

In this tutorial if a person receives any packet he/she is informed with a mail in which the tracking number of the packet is present. All the packet information is stored in the excel sheets.

The excel sheet which contains the email, its path is hardcoded in .net code. Whereas we have to select the other file which contains the packet information using the browse button.

Prerequisites:

Before starting the tutorial, you should:

1. Have Microsoft Visual Studio 2008 installed.

2. Have Microsoft Office Excel 2007 installed.

3. Create an Excel containing columns Name and Email.

4. Create another Excel containing columns Slno, Type, Code and Count.

Create Excel Sheets:

We compare two excel sheets, so creation of two excel sheets is must. One excel sheet must contain columns Name and Email. The other excel sheets must contain Slno, Type, Code and Count. (NOTE: Type and Name are same fields).Code is the tracking number of the packet. These excel sheets must be saved as Microsoft Office Excel 97-2003 Worksheet (.xls).

TUTORIAL:

Create a new project

Step 1: Open Microsoft Visual Studio 2008.

> Programs > Microsoft Visual Studio 2008 >

Step 2:

Create a New Web Site

Click on the visual studio 2008 it opens startup page as below. Here select New Website.

File > New Web Site…

Step 3:

Select ASP.NET Web Site and click OK

Step 4:

This opens a Default aspx page.

Step 5:

Click on the Property Window and Rename the file (Here it is renamed as MailExcel)

Step 6:

After renaming the solution explorer appears as follows:

Step 7: switch to design view

Click on the Source button at the bottom of the page. It appears as follows..

Step 8:

The Design view appears as follows

Step 9:

From the toolbox click, drag and drop FileUpload.

In the source view we see

<asp:FileUpload ID="FileUpload1" runat="server" />

Replace this with

<input type="file" runat="server" class="textcls" id="browseFile" style="width: 300px;height: 19px" size="43"

onclick="return browseFile_onclick()" />

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Select a File to Upload" Font-Size="XX-Small" ControlToValidate="browseFile">*

</asp:RequiredFieldValidator>

</td>

By using this browse button we can select any excel sheet saved on the computer.

RequiredFieldValidator: - Makes the associated input control a required field.

Step 10:

Drag and drop a button adjacent to the browse.

In the source view we see

<asp:Button ID="Button1" runat="server" Text="Button" />

Replace this with

<asp:Button ID="Display_Btn" runat="server" Text="Display" CausesValidation="False" />

Step 11:

Change properties to the button and rename it.

Step 12:

Similarly add another button send mail

<asp:Button ID="SendMail_Btn" runat="server" Text="SendMail" Enabled = "false" CausesValidation="False"/>

and change its properties.

Also add a hiddenfield from the toolbox and rename it as MailFilePath.

<asp:HiddenField ID="MailFilePath" runat="server" EnableViewState = "true"/>

Add

This helps to view the data and if the data oveflows we get a scroll on the right side.

In the Source view

Add the function

<title>Untitled Page</title>

// <!CDATA[

function browseFile_onclick() {

SendMail_Btn = document.getElementById("SendMail_Btn");

SendMail_Btn.disabled=true;

}

// ]]>

</script>

</head>

Step 13:

Now the design view appears as follows after editing the code.

Step 14:

Double click on MailExcel.aspx.vb in the Solution Explorer.

Step 15:

It appears as follows:-

Step 16:

The above code in MailExcel.aspx.vb must be removed.

Now

Add the import statements

Imports System.Data.OleDb

Imports System.Text

Imports System.IO

Declare all the variables (Dim: Declares variables and allocates storage space.)

Partial Class MailExcel

Inherits System.Web.UI.Page

Dim objda As OleDbDataAdapter

Dim objds As Data.DataSet

Dim Datafilepath As String

Dim dsretData, dsretMail As Data.DataSet

The function performed when we click the display button is given below

Protected Sub Display_Btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Display_Btn.Click

DataView()

End Sub

This in turn calls the function DataView().

Sub DataView()

Dim strbChk As New StringBuilder

Dim rs As Data.DataView = Nothing

Dim myView As Data.DataView

If Path.GetExtension(browseFile.PostedFile.FileName).ToLower = ".xls" Then

Dim strquery As String = "select distinct type from [Sheet1$] where type <> ''"

MailFilePath.Value() = browseFile.PostedFile.FileName

'----------Ecel file path from where u will retrive the data

If Not MailFilePath.Value().ToString().Substring(1, 1) = ":"

Then

MailFilePath.Value() = "C:\test\" + MailFilePath.Value().ToString()

End If

dsretData = New Data.DataSet

dsretData = ExeExlqry(strquery, MailFilePath.Value())

' Function to retive data

Datafilepath = "C:\test\Mail Notifier Map File123.xls"

Dim strqry As String = "select * from [Sheet1$]"

dsretMail = New Data.DataSet

sretMail = ExeExlqry(strqry, Datafilepath)

SendMail_Btn.Enabled = True

Else

Response.Write("<script>alert('Upload Only Excel file')</script>")

divSIP.InnerHtml = ""

Exit Sub

End If

strbChk = New StringBuilder

strbChk.Append("<TABLE class='TableCls' width=70% border='1' align='center'>")

'appending the data from data set to string builder

For j As Integer = 0 To dsretData.Tables(0).Rows.Count - 1

myView = dsretMail.Tables(0).DefaultView

rs = FetchData(myView,dsretData.Tables(0).Rows(j).Item(0).ToString,"Name")

If rs.Count > 0 Then

strbChk.Append("<tr><td>" & dsretData.Tables(0).Rows(j).Item(0).ToString & "</td><td>" & rs.Item(0).Row(1).ToString & "</td></tr>")

Else

strbChk.Append("<tr><td>" & dsretData.Tables(0).Rows(j).Item(0).ToString & "</td><td style='color:Red'>Mail Id Not Available</td></tr>")

End If

strbChk.Append("<tr><td colspan='3'> </td></tr></table>")

divSIP.InnerHtml = strbChk.ToString

'Displaying the data to div

End Sub

In the DataView we call two more functions namely FetchData and ExeExlqry.

Private Function FetchData(ByVal rs As Data.DataView, _ByVal FilterOn As String, ByVal TypeorName As String) As Data.DataView

rs.RowFilter = TypeorName & "='" & FilterOn & "'"

Return rs

End Function

Function ExeExlqry(ByVal strQry As String, ByVal Path As String) As Data.DataSet

Try

objds = New Data.DataSet

'--conection string to retrive the data from excel

Dim con As New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=" & Path & ";Extended properties=Excel 8.0")

objda = New OleDbDataAdapter(strQry, con)

objds = New Data.DataSet

objda.Fill(objds)

'---To store the excel data into the data set

objda.Dispose()

Return objds

Catch ex As Exception

If Not objds Is Nothing Then objds = Nothing

End Try

End Function

The function performed when we click the SendMail button is given below

Protected Sub SendMail_Btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SendMail_Btn.Click

Dim strbChk As New StringBuilder

Dim rs As Data.DataView = Nothing

Dim myView As Data.DataView

Dim strquery As String = "select * from [Sheet1$]"

dsretData = New Data.DataSet

dsretData = ExeExlqry(strquery, MailFilePath.Value())

' Function to retive data

Datafilepath = "C:\test\Mail Notifier Map File123.xls"

'Path of the excel which contains the column email

dsretMail = New Data.DataSet

dsretMail = ExeExlqry(strquery, Datafilepath)

For j As Integer = 0 To dsretMail.Tables(0).Rows.Count - 1

myView = dsretData.Tables(0).DefaultView

rs = FetchData(myView,dsretMail.Tables(0).Rows(j).Item(0).ToString, "Type")

strbChk = New StringBuilder

strbChk.Append("<TABLE class='TableCls' width=70% border='1' align='center'>")

'appending the data from data set to string builder

For P As Integer = 0 To rs.Count - 1

strbChk.Append("<tr><td>" & rs.Item(P).Row(1).ToString & " </td><td> " & rs.Item(P).Row(2).ToString & " </td><td> " & rs.Item(P).Row(3).ToString & " </td></tr>")

strbChk.Append("<tr><td colspan='3'> </td></tr></table>")

If rs.Count Then

sendConfMail(dsretMail.Tables(0).Rows(j).Item(1).ToString,strbChk)

End If

divSIP.InnerHtml = strbChk.ToString

'Displaying the data to div

SendMail_Btn.Enabled = False

End Sub

This in turn calls the function sendConfMail().

Private Sub sendConfMail(ByVal ToId As String, ByVal sbMailBody As StringBuilder)

Dim strMailServer As String = "smtp.ksu.edu"

'--write smtpserver ip

Dim strMailUser As String = ""

'--User Name

Dim strMailPassword As String = ""

'----Password

Dim strMailSmtpSvrPort As String = "25"

Dim strMailSendUsing As String = "2"

Dim strMailSmtpAuthne As String = "1"

Dim strFromMailId As String = ""

'------------------FromId

Dim strBody = "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">" & vbCrLf _

& "<html>" & vbCrLf _

& "<head>" & vbCrLf _

& "<title> Package In Receiving </title>" & vbCrLf _

& "</head>" & vbCrLf _

& "<body bgcolor=""#FFFFCC"">" & vbCrLf _

& " You have recieved the following package(s). Please pickup the package(s) as soon as possible" & vbCrLf _

& "<p>" & vbCrLf _

& " Thank-You" & vbCrLf _

& " </p>" & vbCrLf _

& "<p>" & vbCrLf _

& " Receiving Department" & vbCrLf _

& " </p>" & vbCrLf _

& "</body>" & vbCrLf _

& "</html>" & vbCrLf

Dim objCDO As Object

objCDO = CreateObject("CDO.Message")

With objCDO.Configuration.Fields

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strMailServer

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = strMailSmtpSvrPort

.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = strMailSendUsing

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = strMailSmtpAuthne

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = strMailUser

.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = strMailPassword

.Update()

End With

objCDO.From = strFromMailId

objCDO.To = ToId

objCDO.Subject = "Package In Receiving"

objCDO.htmlBody = strBody + sbMailBody.ToString

objCDO.Send()

objCDO = Nothing

End Sub

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

End Sub

End Class

Step 17:

Save the website.

Then File > Save All

Step 18:

Then set MailExcel.aspx as a startup page.

Step 19:

Make sure the excel sheet which has the email is placed in the correct path and write the path in the code where it is commented as 'Path of the excel which contains the column email.

Step 20:

Now click on Run button () which is present on the top.

Step 21:

Now the page appears as follows:

NOTE:

If the page opens in copy paste the link

From the and paste it in Mozilla Firefox.

This is done because the Mozilla does not work properly with the java scripts.

Step 22:

Click on the browse button and search for the excel sheet which has to be compared with the other excel sheet.

When we click on browse a window appears

Select excel sheet and Click open.

Step 23:

The path appears as follows

Step 24:

Click on the Display button.

It appears as follows:

The sendmail button is visible now.

Step 25:

Click on the send mail button.

It appears as follows:

Step 26:

The mails are sent to the respective mail ids with the tracking number.

The End.