sudagn

Add and Remove HTML elements dynamically with Javascript

2010-01-12T04:11:00.000-08:00

http://www.dustindiaz.com/add-and-remove-html-elements-dynamically-with-javascript/

Encrypt and Decrypt Connection String

2009-11-15T21:49:00.000-08:00

http://www.dotnetspider.com/resources/2125-Encrypt-Decrypt-Connection-String.aspx

Make fancy buttons using CSS sliding doors technique

2009-10-23T06:21:00.000-07:00

http://www.jankoatwarpspeed.com/post/2008/04/30/make-fancy-buttons-using-css-sliding-doors-technique.aspx

Nested GridView in ASP.NET

2009-10-21T22:15:00.001-07:00

http://www.codeproject.com/KB/webforms/EditNestedGridView.aspx

Nested GridView in ASP.NET

2009-10-21T22:15:00.000-07:00

http://www.codeproject.com/KB/webforms/EditNestedGridView.aspx

Microsoft Anti-Cross Site Scripting Library V1.5: Protecting the Contoso Bookmark Page

2009-10-06T22:39:00.000-07:00

http://msdn.microsoft.com/en-us/library/aa973813.aspx

Export GridView To Word/Excel/PDF/CSV in ASP.Net

2009-08-26T00:15:00.000-07:00

Export GridView To Word/Excel/PDF/CSV in ASP.Net

In this article, I will explain how to export GridView to Word, Excel, PDF and CSV formats.

Exporting to Word, Excel and CSV can be easily achieved using ASP.Net without any third party tools, but for exporting GridView to PDF I am using iTextSharp which is a free library for exporting html to PDF.

To start with I have a GridView in which I am showing Customers records from the NorthWind Database.

The HTML markup of the GridView is as shown below

<asp:GridView ID="GridView1" runat="server"

AutoGenerateColumns = "false" Font-Names = "Arial"

Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"

HeaderStyle-BackColor = "green" AllowPaging ="true"

OnPageIndexChanging = "OnPaging" >

<Columns>

<asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"

HeaderText = "CustomerID" />

<asp:BoundField ItemStyle-Width = "150px" DataField = "City"

HeaderText = "City"/>

<asp:BoundField ItemStyle-Width = "150px" DataField = "Country"

HeaderText = "Country"/>

<asp:BoundField ItemStyle-Width = "150px" DataField = "PostalCode"

HeaderText = "PostalCode"/>

</Columns>

</asp:GridView>

In the figure below the GridView is shown with four buttons

1. Export To Word

2. Export To Excel

3. Export To PDF

4. Export To CSV

GridView with Sample Data

Export to Microsoft Word Format

C#

protected void btnExportWord_Click(object sender, EventArgs e)

{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition",

"attachment;filename=GridViewExport.doc");

Response.Charset = "";

Response.ContentType = "application/vnd.ms-word ";

StringWriter sw= new StringWriter();

HtmlTextWriter hw = new HtmlTextWriter(sw);

GridView1.AllowPaging = false;

GridView1.DataBind();

GridView1.RenderControl(hw);

Response.Output.Write(sw.ToString());

Response.Flush();

Response.End();

}

VB.Net

Protected Sub btnExportWord_Click(ByVal sender As Object,

ByVal e As EventArgs)

Response.Clear()

Response.Buffer = True

Response.AddHeader("content-disposition",

"attachment;filename=GridViewExport.doc")

Response.Charset = ""

Response.ContentType = "application/vnd.ms-word "

Dim sw As New StringWriter()

Dim hw As New HtmlTextWriter(sw)

GridView1.AllowPaging = False

GridView1.DataBind()

GridView1.RenderControl(hw)

Response.Output.Write(sw.ToString())

Response.Flush()

Response.End()

End Sub

The above function renders the GridView contents as Microsoft Word format. You will notice I have disabled paging before exporting, so that all the pages are exported.

The Output Exported File

GridView data exported to Word Document

Export to Microsoft Excel Format

For exporting the document to Excel if you do it directly as done in case of word the row background color is applied throughout to all the columns in the Excel Sheet hence in order to avoid it. I have done a workaround below.

First I am changing the background color of each row back to white.

Then I am applying the background color to each individual cell rather than the whole row. Thus when you export now you will notice that the formatting is applied only to the GridView cells and not all

Also I am applying textmode style class to all cells and then adding the style CSS class to the GridView before rendering it, this ensures that all the contents of GridView are rendered as text.

protected void btnExportExcel_Click(object sender, EventArgs e)

{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition",

"attachment;filename=GridViewExport.xls");

Response.Charset = "";

Response.ContentType = "application/vnd.ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter hw = new HtmlTextWriter(sw);

GridView1.AllowPaging = false;

GridView1.DataBind();

//Change the Header Row back to white color

GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

//Apply style to Individual Cells

GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");

GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");

GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");

GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");

for (int i = 0; i < GridView1.Rows.Count;i++ )

{

GridViewRow row = GridView1.Rows[i];

//Change Color back to white

row.BackColor = System.Drawing.Color.White;

//Apply text style to each Row

row.Attributes.Add("class", "textmode");

//Apply style to Individual Cells of Alternating Row

if (i % 2 != 0)

{

row.Cells[0].Style.Add("background-color", "#C2D69B");

row.Cells[1].Style.Add("background-color", "#C2D69B");

row.Cells[2].Style.Add("background-color", "#C2D69B");

row.Cells[3].Style.Add("background-color", "#C2D69B");

}

}

GridView1.RenderControl(hw);

//style to format numbers to string

string style = @"<style> .textmode { mso-number-format:\@; } </style>";

Response.Write(style);

Response.Output.Write(sw.ToString());

Response.Flush();

Response.End();

}

VB.Net

Protected Sub btnExportExcel_Click(ByVal sender As Object,

ByVal e As EventArgs)

Response.Clear()

Response.Buffer = True

Response.AddHeader("content-disposition",

"attachment;filename=GridViewExport.xls")

Response.Charset = ""

Response.ContentType = "application/vnd.ms-excel"

Dim sw As New StringWriter()

Dim hw As New HtmlTextWriter(sw)

GridView1.AllowPaging = False

GridView1.DataBind()

'Change the Header Row back to white color

GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF")

'Apply style to Individual Cells

GridView1.HeaderRow.Cells(0).Style.Add("background-color", "green")

GridView1.HeaderRow.Cells(1).Style.Add("background-color", "green")

GridView1.HeaderRow.Cells(2).Style.Add("background-color", "green")

GridView1.HeaderRow.Cells(3).Style.Add("background-color", "green")

For i As Integer = 0 To GridView1.Rows.Count - 1

Dim row As GridViewRow = GridView1.Rows(i)

'Change Color back to white

row.BackColor = System.Drawing.Color.White

'Apply text style to each Row

row.Attributes.Add("class", "textmode")

'Apply style to Individual Cells of Alternating Row

If i Mod 2 <> 0 Then

row.Cells(0).Style.Add("background-color", "#C2D69B")

row.Cells(1).Style.Add("background-color", "#C2D69B")

row.Cells(2).Style.Add("background-color", "#C2D69B")

row.Cells(3).Style.Add("background-color", "#C2D69B")

End If

Next

GridView1.RenderControl(hw)

'style to format numbers to string

Dim style As String = "<style>.textmode{mso-number-format:\@;}</style>"

Response.Write(style)

Response.Output.Write(sw.ToString())

Response.Flush()

Response.End()

End Sub

The Output Exported File

GridView data exported to Excel Document

Export to Portable Document Format

For exporting the GridView to PDF I am using the iTextSharp Library. You will need to Add Reference for the iTextSharp Library in your Website.

Then import the following Namespaces

C#

using iTextSharp.text;

using iTextSharp.text.pdf;

using iTextSharp.text.html;

using iTextSharp.text.html.simpleparser;

VB.Net

Imports iTextSharp.text

Imports iTextSharp.text.pdf

Imports iTextSharp.text.html

Imports iTextSharp.text.html.simpleparser

By default the iTextSharp Library does not support background color of table cells or table rows

Hence when you render it as PDF your GridView is rendered without any formatting.

Recently I read an article on hamang.net where the author has provided the snippet to modify the iTextSharp so that it exports the HTML with background color.

For this tutorial, I have already modified the iTextSharp Library DLL so that the GridView is rendered with all the background color used. You can refer the code for exporting GridView to PDF below

C#

protected void btnExportPDF_Click(object sender, EventArgs e)

{

Response.ContentType = "application/pdf";

Response.AddHeader("content-disposition",

"attachment;filename=GridViewExport.pdf");

Response.Cache.SetCacheability(HttpCacheability.NoCache);

StringWriter sw = new StringWriter();

HtmlTextWriter hw = new HtmlTextWriter(sw);

GridView1.AllowPaging = false;

GridView1.DataBind();

GridView1.RenderControl(hw);

StringReader sr = new StringReader(sw.ToString());

Document pdfDoc = new Document(PageSize.A4, 10f,10f,10f,0f);

HTMLWorker htmlparser = new HTMLWorker(pdfDoc);

PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

pdfDoc.Open();

htmlparser.Parse(sr);

pdfDoc.Close();

Response.Write(pdfDoc);

Response.End();

}

VB.Net

Protected Sub btnExportPDF_Click(ByVal sender As Object,

ByVal e As EventArgs)

Response.ContentType = "application/pdf"

Response.AddHeader("content-disposition",

"attachment;filename=GridViewExport.pdf")

Response.Cache.SetCacheability(HttpCacheability.NoCache)

Dim sw As New StringWriter()

Dim hw As New HtmlTextWriter(sw)

GridView1.AllowPaging = False

GridView1.DataBind()

GridView1.RenderControl(hw)

Dim sr As New StringReader(sw.ToString())

Dim pdfDoc As New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)

Dim htmlparser As New HTMLWorker(pdfDoc)

PdfWriter.GetInstance(pdfDoc, Response.OutputStream)

pdfDoc.Open()

htmlparser.Parse(sr)

pdfDoc.Close()

Response.Write(pdfDoc)

Response.End()

End Sub

The Output Exported File

GridView data exported to PDF Document

Export to Text/CSV

Finally comes exporting GridView to CSV or Text File delimited by a separator like comma.

To export the GridView as CSV, I am running a two for loops. While looping through the GridView columns and appending comma after each column and while looping through rows appending new line character. Refer the code below.

C#

protected void btnExportCSV_Click(object sender, EventArgs e)

{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition",

"attachment;filename=GridViewExport.csv");

Response.Charset = "";

Response.ContentType = "application/text";

GridView1.AllowPaging = false;

GridView1.DataBind();

StringBuilder sb = new StringBuilder();

for (int k = 0; k < GridView1.Columns.Count; k++)

{

//add separator

sb.Append(GridView1.Columns[k].HeaderText + ',');

}

//append new line

sb.Append("\r\n");

for (int i = 0; i < GridView1.Rows.Count; i++)

{

for (int k = 0; k < GridView1.Columns.Count; k++)

{

//add separator

sb.Append(GridView1.Rows[i].Cells[k].Text + ',');

}

//append new line

sb.Append("\r\n");

}

Response.Output.Write(sb.ToString());

Response.Flush();

Response.End();

}

VB.Net

Protected Sub btnExportCSV_Click(ByVal sender As Object,

ByVal e As EventArgs)

Response.Clear()

Response.Buffer = True

Response.AddHeader("content-disposition",

"attachment;filename=GridViewExport.csv")

Response.Charset = ""

Response.ContentType = "application/text"

GridView1.AllowPaging = False

GridView1.DataBind()

Dim sb As New StringBuilder()

For k As Integer = 0 To GridView1.Columns.Count - 1

'add separator

sb.Append(GridView1.Columns(k).HeaderText + ","c)

Next

'append new line

sb.Append(vbCr & vbLf)

For i As Integer = 0 To GridView1.Rows.Count - 1

For k As Integer = 0 To GridView1.Columns.Count - 1

'add separator

sb.Append(GridView1.Rows(i).Cells(k).Text + ","c)

Next

'append new line

sb.Append(vbCr & vbLf)

Next

Response.Output.Write(sb.ToString())

Response.Flush()

Response.End()

End Sub

The Output Exported File

GridView data exported to CSV File

When you run the application first time and click export you might receive the following error

Error encountered when you click export

To avoid the error you will need to add this event which ensures that the GridView is Rendered before exporting.

C#

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

VB.Net

Public Overloads Overrides Sub VerifyRenderingInServerForm

(ByVal control As Control)

' Verifies that the control is rendered

End Sub

This completes the article you can view the live demo here

The source code is available in C# and VB.Net here

Adding QueryString Parameters to the SiteMapNode

2009-08-25T23:58:00.000-07:00

There is no way to add querystring parameters to the SiteMapNode in a SiteMap control "out of the box." I'm quite surprised there is no option but luckily the SiteMap uses the provider model. After a Live search, I was able to find a solution provided by Bobby DeRosa. By creating a custom provider, this can be accomplished. The project I was on is an update of an ASP.net app written in VB.net so here is my VB.net port:

Imports System.Collections.Specialized
Imports System.Web

Namespace Configuration

Public Class ExtendedSiteMapProvider
Inherits XmlSiteMapProvider

Public Overrides Sub Initialize(ByVal name As String, ByVal attributes As NameValueCollection)
MyBase.Initialize(name, attributes)
Dim resolveHandler As New SiteMapResolveEventHandler(AddressOf SmartSiteMapProvider_SiteMapResolve)
AddHandler Me.SiteMapResolve, resolveHandler
End Sub

Function SmartSiteMapProvider_SiteMapResolve(ByVal sender As Object, ByVal e As SiteMapResolveEventArgs) As SiteMapNode
If (SiteMap.CurrentNode Is Nothing) Then Return Nothing

Dim this As New XmlSiteMapProvider
Dim temp As SiteMapNode
temp = SiteMap.CurrentNode.Clone(True)
Dim u As Uri = New Uri(e.Context.Request.Url.ToString())
Dim tempNode As SiteMapNode = temp

While Not tempNode Is Nothing
Dim qs As String = GetQueryString(tempNode, e.Context)
If Not qs Is Nothing Then
If Not tempNode Is Nothing Then
tempNode.Url += qs
End If
End If
tempNode = tempNode.ParentNode
End While

Return temp
End Function

Private Function GetQueryString(ByVal node As SiteMapNode, ByVal context As HttpContext) As String
If node("queryStringToInclude") Is Nothing Then Return Nothing

Dim values As NameValueCollection = New NameValueCollection
Dim vars() As String = node("queryStringToInclude").Split(",".ToCharArray())
Dim s As String

For Each s In vars
Dim var As String = s.Trim()
If context.Request.QueryString(var) Is Nothing Then Continue For
values.Add(var, context.Request.QueryString(var))
Next

If values.Count = 0 Then Return Nothing

Return NameValueCollectionToString(values)
End Function

Private Function NameValueCollectionToString(ByVal col As NameValueCollection) As String
Dim parts(col.Count - 1) As String
Dim keys() As String = col.AllKeys

For i As Integer = 0 To keys.Length - 1
parts(i) = keys(i) & "=" & col(keys(i))
Next

Dim url As String = "?" & String.Join("&", parts)

Return url
End Function

End Class

End Namespace

I added the following to the web.config:

<siteMap defaultProvider="ExtendedSiteMapProvider" enabled="true">
<providers>
<clear />
<add name="ExtendedSiteMapProvider" type="Configuration.ExtendedSiteMapProvider" siteMapFile="web.sitemap" securityTrimmingEnabled="true" />
</providers>
</siteMap>

If I had a page of products on products.aspx, displayed the details of the product on details.aspx, and had a separate update.aspx page, my web.sitemap might look like this:

<?xml version="1.0" encoding="utf-8" ?>
<siteMap xmlns="http://schemas.microsoft.com/AspNet/SiteMap-File-1.0" >
<siteMapNode url="~/" title="Main Page">
<siteMapNode url="~/products.aspx" title="Products" >
<siteMapNode url="~/details.aspx" title="Product Details" queryStringToInclude="ProductID" >
<siteMapNode url="~/update.aspx" title="Updating a Product" />
</siteMapNode>
</siteMapNode>
</siteMapNode>
</siteMap>

So, on /update.aspx?ProductID=3, the SiteMapNode for Product Details would have a url of /details.aspx?ProductID=3.

Hopefully a similar feature will be added soon to the ASP.net SiteMap control.

<span style="font-weight:bold;">C#:</span>
Great job! I took your C# code and had to make a few small changes to get it working correctly (on VS2008 & .NET 3.5 sp1):

#region Using Directives

using System.Collections.Specialized;

using System.Web;

#endregion

namespace Configuration

{

public class ExtendedSiteMapProvider : XmlSiteMapProvider

{

public override void Initialize(string name, NameValueCollection attributes)

{

base.Initialize(name, attributes);

this.SiteMapResolve += SmartSiteMapProvider_SiteMapResolve;

}

static SiteMapNode SmartSiteMapProvider_SiteMapResolve(object sender, SiteMapResolveEventArgs e)

{

if ((SiteMap.CurrentNode == null)) return null;

SiteMapNode temp = SiteMap.CurrentNode.Clone(true);

SiteMapNode tempNode = temp;

while (tempNode != null)

{

string qs = GetQueryString(tempNode, e.Context);

if (qs != null)

{

tempNode.Url += qs;

}

tempNode = tempNode.ParentNode;

}

return temp;

}

private static string GetQueryString(SiteMapNode node, HttpContext context)

{

if (node["queryStringToInclude"] == null) return null;

NameValueCollection values = new NameValueCollection();

string[] vars = node["queryStringToInclude"].Split(",".ToCharArray());

foreach (string s in vars)

{

string var = s.Trim();

if (context.Request.QueryString[var] == null) continue;

values.Add(var, context.Request.QueryString[var]);

}

if (values.Count == 0) return null;

return NameValueCollectionToString(values);

}

private static string NameValueCollectionToString(NameValueCollection col)

{

string[] parts = new string[col.Count];

string[] keys = col.AllKeys;

for (int i = 0; i <= keys.Length - 1; i++)

{

parts[i] = keys[i] + "=" + col[keys[i]];

}

return "?" + string.Join("&", parts);

}

}

}

SiteMapProvider doesn't take QueryString into consideration

2009-08-25T23:54:00.000-07:00

I was pretty stoked to find that ASP.NET 2.0 included a new control called SiteMap. This control will display a breadcrumb trail of links as you navigate through your site. By default, the data is loaded from a web.sitemap file that sits in the web app root. Although you can load the data from any source, this is the default and uses the XmlSiteMapProvider provider to populate the data.

This is an example of data found in the web.sitemap file:
Copy code to clipboard in IE or select code for Firefox

<?xml version="1.0" encoding="utf-8" ?>
<siteMap>
<siteMapNode url="~/default.aspx" title="Home">
<siteMapNode url="~/hotels/list.aspx" title="Hotel Listings">
<siteMapNode url="~/hotels/details.aspx" title="Hotel Information">
<siteMapNode url="~/hotels/update.aspx" title="Update Hotel"/>
</siteMapNode>
</siteMapNode>
</siteMapNode>
</siteMap>

This works fine if you aren't ever using querystring variables. But let's say when you get down to the details.aspx page, you tag on a HotelID variable to the querystring. Same thing happens when you go deeper into update.aspx.

Your breadcrumb will look like this:

Home > Hotel Listings > Hotel Information > Update Hotel

Given my current url is "update.aspx?HotelID=1234", I should have some way to propagate the current HotelID querystring variable to the other links, if they need it, which details.apx does.

I tried endlessly to get around this. I tried derriving my own class from XmlSiteProvider, but ASP.NET crashes as soon as you mess w/ the child nodes in the BuildSiteMap() method.

I tried binding an event handler to the SiteMap.SiteMapResolve event - but the Request collection is always unavailable at the time when this event is triggered.

I tried to modify the url's of the nodes showing on the screen dynamically, but supid SiteMapNode.Url property is Read-Only - ARGGG!!

It just amazes me that MS didn't see this coming & allow for it in the XML config. They could have added a "incorporate" attribute in the node that allowed you to specify a comma-delimited list of querystring variables that would be needed for that URL to work, like this:

Copy code to clipboard in IE or select code for Firefox

<siteMapNode url="~/hotels/details.aspx" title="Hotel Information" reliantOn="HotelID, UserID">

or give me a way to add in the querystring variables I want to pull from the current context like this:

Copy code to clipboard in IE or select code for Firefox

<siteMapNode url="~/hotels/details.aspx?HotelID={HotelID}" title="Hotel Information">

And the SiteMapProvider would be smart enough to replace the vars in braces w/ the variable from the current querystring.

I never thought getting something so simple to work would be so hard. If anyone has a solution, pony up.

GridView with Thumbnail Images

2009-07-01T04:54:00.000-07:00

GridView with Thumbnail Images – Part 2

In Part 1, we have seen how to display the full image in a popup window when the user clicks the thumbnail image displayed in the GridView. We all know that displaying something on a popup window will not give a better user experience. There might be a chance where the popup blocker is turned on in the user’s machine and it will not allow the user to view the full image.

Part 2 of this article will also generate the thumbnail image for the images stored in database to display on the GridView. Like Part 1, when the user clicks on the thumbnail image it will also display the full image, but inside a DIV tag on the same page instead of a popup, which gives a better user experience.

Creating the thumbnail image and displaying the full image is done by the same HttpHandler implementation we have used in Part 1.

Creating Thumbnail Image

For reference purpose, we will have the HttpHandler implementation here also. For description of the code refer Part 1.

Thumbnail.ashx

public void ProcessRequest (HttpContext context) {

string imageid = context.Request.QueryString["ImID"];

if (imageid == null || imageid == "")

{

//Set a default imageID

imageid = "1";

}

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);

connection.Open();

SqlCommand command = new SqlCommand("select Image from Image where ImageID="+imageid, connection);

SqlDataReader dr = command.ExecuteReader();

dr.Read();

Stream str = new MemoryStream((Byte[])dr[0]);

Bitmap loBMP = new Bitmap(str);

Bitmap bmpOut = new Bitmap(100, 100);

Graphics g = Graphics.FromImage(bmpOut);

g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;

g.FillRectangle(Brushes.White, 0, 0, 100, 100);

g.DrawImage(loBMP, 0, 0, 100, 100);

MemoryStream ms = new MemoryStream();

bmpOut.Save(ms, System.Drawing.Imaging.ImageFormat.Png);

byte[] bmpBytes = ms.GetBuffer();

bmpOut.Dispose();

ms.Close();

context.Response.BinaryWrite(bmpBytes);

connection.Close();

context.Response.End();

}

Displaying the Original Image

FullImage.ashx

public void ProcessRequest (HttpContext context) {

string imageid = context.Request.QueryString["ImID"];

if (imageid == null || imageid == "")

{

//Set a default imageID

imageid = "1";

}

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);

connection.Open();

SqlCommand command = new SqlCommand("select Image from Image where ImageID="+imageid, connection);

SqlDataReader dr = command.ExecuteReader();

dr.Read();

context.Response.BinaryWrite((Byte[])dr[0]);

connection.Close();

context.Response.End();

}

Note

I have given only the ProcessRequest() method in the above code. Refer the source code attachment for the full code. I have set default imageID, make sure you are giving an existing imageId in database for the code to work without any errors.

Using DIV Tag to display the full image

To implement this, we will declare a DIV tag in the ASPX page with display property set to none. This indicates that the DIV tag will not be visible by default when the page is loaded. Declare a tag inside this DIV tag to display the full image. When the user clicks the thumbnail image, we can enable the DIV tag through a javascript function and pass the ImID of the image to call the FullImage.ashx HttpHandler for displaying the full image in the tag. Also, we will have a button called Close inside the DIV tag to close the display of full image i.e. on click of this button we will again make the DIV tag’s display property to none. Refer the below Jscript code,

Hidden Columns with values: asp:GridView (access the value of the hidden columns in gridview )

2009-06-29T03:22:00.000-07:00

If you set Column Visible property to false, this column won't rendered. But if you want these values available, What will you do?

My trick was, set HeaderText to empty, convert the BoundField in TemplateField, and use a HiddenField control. The effect the column won't be visible. Also you can use the controls array to access to value property.

<columns><asp:boundfield datafield="CompanyCode" headertext="Company" sortexpression="CompanyCode">
...
<columns>
<asp:templatefield><itemtemplate><asp:hiddenfield id="hf1" value="">">
runat="server"></asp:hiddenfield></itemtemplate>
...
// accesing the value property
int tmpID =
Convert.ToInt32(((HiddenField)GridView1.SelectedRow.Cells[3].Controls[1]).Value); </asp:templatefield></columns></asp:boundfield></columns>

How to add link to a css file in content page

2009-06-23T06:45:00.000-07:00

Try this

In VB

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
   Dim css As HtmlGenericControl
   css = New HtmlGenericControl
   css.TagName = "style"
   css.Attributes.Add("type", "text/css")
   css.InnerHtml = "@import ""/foobar.css"";"
   Page.Header.Controls.Add(css)
End Sub

In C#

protected void Page_Init(object sender, System.EventArgs e)
{
   HtmlGenericControl css;
   css = new HtmlGenericControl();
   css.TagName = "style";
   css.Attributes.Add("type", "text/css");
   css.InnerHtml = "@import \"/foobar.css\";";
   Page.Header.Controls.Add(css);
}

How To Validate a Serial Number During an Installation Created with VSI

2009-06-23T04:13:00.001-07:00

How To Validate a Serial Number During an Installation Created with VSI

SUMMARY
In a Visual Studio Installer (VSI) project, the developer has the ability to ad...

In a Visual Studio Installer (VSI) project, the developer has the ability to add a Customer Information dialog box in the user interface. By default, this dialog box has an input field for a serial number. Visual Studio Installer provides minimal support for validating this field. This article provides the steps necessary to include custom code to validate the serial number that is entered by the user. These steps include using the Windows Installer SDK tool Orca, and creating a DLL for which sample code is provided.

To download the Orca tool, see the following MSDN Web site that includes samples, tools, and documentation for the Windows Installer

http://msdn.microsoft.com/downloads/default.asp?URL=/code/sample.asp?url=/msdn-files/027/001/457/msdncompositedoc.xml (http://msdn.microsoft.com/downloads/default.asp?URL=/code/sample.asp?url=/msdn-files/027/001/457/msdncompositedoc.xml)

Back to the top

MORE INFORMATION
Create an empty installer project with VSI.Add a Customer Information dialog box...

Create an empty installer project with VSI.
Add a Customer Information dialog box to the user interface. Do not change the default properties for the dialog box.
Build the project.
Create a custom action DLL. NOTE: You cannot use an EXE custom action because this custom action sets a property and that can only be accomplished by using a DLL, which has a handle to the installation.
On the Tools menu, click the Options Directories tab in your DLL project, and then add the path to the Windows Installer SDK's Include and Lib directories.

In the Project Settings dialog box, add msi.lib to the library list. Use either a .DEF file or the __declspec(dllimport) attribute to export the DLL functions. Note that the PIDKEY property has the value of "XXX -XXXXXXX" where X is a number and there is both a space and hyphen. The following sample code uses a string comparison to determine if the serial number is valid:

UINT __stdcall VerifyPID(MSIHANDLE hInstall)
{
  // Local variables  
  UINT    nRetVal = 0;  
  UINT    uiMsiRc;  
  TCHAR   szPidKey[MAX_PATH];  
  DWORD   dwBuffer;   

  dwBuffer = sizeof(szPidKey)/sizeof(TCHAR);    

  // First Step - Get the PIDKEY property  
  uiMsiRc = MsiGetProperty(hInstall, TEXT("PIDKEY"), szPidKey, &dwBuffer);

  if (ERROR_SUCCESS != uiMsiRc)
  {
     MessageBox(NULL, "PIDKEY", "Not able to retrieve PIDKEY property", MB_OK | MB_ICONEXCLAMATION);
     return 0;
  }

  //Insert code to check PIDKEY here
  int str = lstrcmp(szPidKey, "123 -4567890");

  //If PIDKEY passes check
  if (str == 0)
     MsiSetProperty(hInstall, "PIDCHECK", "TRUE");
  //If PIDKEY doesn't pass check
  else
  {
     MsiSetProperty(hInstall, "PIDCHECK", "FALSE");
     wsprintf(szText, "Please enter the correct code");
     MessageBox(NULL, szText, "PIDCHECK", MB_OK | MB_ICONINFORMATION);
  }

  return 0;
}

Open the Microsoft Windows installer package (.msi) file that you built with VSI in Orca.
Add the DLL to the Binary table and name it VerifyPIDDll.
Add a new custom action to the Custom Action Table:
Name - MyPIDChecker;
Type - 1; Source - VerifyPIDDll; Target - VerifyPID
In the ControlEvent table, find the UserNameForm, which corresponds to the Customer Information dialog box from VSI. Make all changes to the existing rows with UserNameForm in the Dialog column and Next in the Control column:
1. Replace the ValidateProductID Event with DoAction. On the same row, replace the Argument {} with MyPIDChecker.
2. Make sure that the value for the Condition column in the row with the EndDialog event is:
  UserNameForm_NextArgs="" AND UserNameForm_ShowSerial=""
3. Using the PIDCHECK property which was set in the precious sample code, make sure that the value for the Condition column in the row with the NewDialog event is:
  (PIDCHECK="TRUE") AND UserNameForm_NextArgs<>"" AND UserNameForm_ShowSerial<>""
4. Make sure that the value for the Condition column in the row with the [UserNameForm_ShowSerialDisabled] event is:
  PIDCHECK="TRUE"
Save the .msi file and run the installation.

Deploy your Application or Component Using Windows Installer and serial key

2009-06-23T04:08:00.000-07:00

Introduction

When ever we heard about deployment of our application or component we get scared with the traditional scripted setup programs. The main goal of this article is to make .NET programmers to get used to this easy to setup program which allows you to deploy your application even if you don't know how to write the setup script. Microsoft Dot Net Setup & Deployment will make the setup for you with simple clicks. It is also provided with self taught easy driven wizards. Many programmers who are using Microsoft Visual Studio Dot Net they are not fully aware of this windows installer that comes with Visual Studio; they are going for other setup programs that need's hard scripting knowledge. Using Windows Installer is very easy.

About Microsoft Windows Installer

Windows Installer enables you to deploy your Applications and Components in an efficient manner. The installer provides new capabilities to advertise features without installing them, to install products on demand, and to add user customizations. Windows Installer is an installation and configuration service that reduces the total cost of ownership. The installer enables customers to better address corporate deployment and provide a standard format for component management.

Run-Time Requirements

This technology is available as a redistributable for Windows Installer version 2.0, from the Microsoft Downloads.

Features of Windows Installer:

> It is based on a data-driven model that provides all installation data and instructions in a single package. In contrast, traditional scripted setup programs. > It also supports self-repair � the ability for an application to automatically reinstall. > It provides the ability to roll back an installation. The deployment tools in Visual Studio .NET build on the foundation of Windows Installer, providing you with rich capabilities for rapidly deploying and maintaining applications built with Visual Studio .NET. Creating your first Deployment Project Visual Studio provides templates for four types of deployment projects: Setup Project Builds an installer for a Windows-based application. Merge Module Project (.msm) is used to pack components that might be shared by multiple applications. Web Setup Project Builds an installer for a Web application. Cab Project (.cab) Creates a cabinet file for downloading to a legacy Web Browser In addition, a Setup wizard is provided to help step you through the process of creating deployment projects.

For a Setup project, the installer will install files into a Program Files directory on a target computer.
For a Web Setup project, the installer will install files into a Virtual Root directory on a Web server.

Note If you have created a standard deployment project and later decide to deploy it to a Web, you will need to create a new project.

Five Easy Steps:

Step 1

Open the Visual Studio IDE, and select File->New ->Setup and Deployment Projects. Select the Setup Wizard. Give the File name and location where to store that project as shown in the below figure.

Step 2

Welcome to Setup Project Wizard will appear. It is a simple Four Steps Wizard. Click on the Next Button. In the second step on the wizard it will ask the type of the setup. Choose appropriate here I has chosen setup for windows application and click Next.

Step 3

In the third step of this wizard it will ask to add your developed application or component and other files that you need to place in the clients machine, for example say documentation files

Now It will display as shown below figure.

Step 4

As you can see in above figure, The application folder is the folder where your application and supported files will be installed. User's Desktop and Programs Menu are the clients desktop and programs menus respectively. If Place the short files of your main application & Help files on the user's desktop and programs menu, then during the installation the application, supported files and the shortcuts will be placed automatically as you directed by placing the intended files in Application, User Desktop directories and programs menu.

We can use the following editors during the deployment of our application

You can find these editiors in the solution explorer each as a button as shown below,

File System Editor:

It allows you to add project outputs, files, and other items to a deployment project and specify where they will be installed on the target computer.

Registry Settings Management Editor:

Allows you to specify registry keys and values to be added to the registry of the target computer.

File Types Management Editor :

It is used to establish file associations on the target computer.

Custom Actions Management Editor

Allows you to specify additional actions to be performed on the target computer at the end of an installation.

Launch Condition Management Editor :

Allows you to specify conditions that must be met in order to successfully run an installation.

User Interface Management Editor

As shown in above figure, User InterfaceManagement Editor is used to specify and set properties for predefined dialog boxes that are displayed duging the installation on the target system.

The Customer Information Dialog is important to protect your software.

for that first set the Show SerialNumber property to true ( false by default).

there will be a template for the serial key called SerialNumberTemplate:-

Valid editable characters for the SerialNumberTemplate property are:

Character	Meaning
#	Requires a digit that will not be included in the validation algorithm.
%	Requires a digit that will be included in the validation algorithm.
?	Requires an alphanumeric character that will not be included in the validation algorithm.
^	Requires an uppercase or lowercase character. Numeric digits are not valid here.
<	Any characters to the left of this character will not be visible in the dialog box.
>	Any characters to the right of this character will not be visible in the dialog box. Required as a terminator if the <>

note that any other character is treated as a literal constant.

The Default template is <###-%%%%%%%>, Which create two text boxes separated by a dash surrounded by spaces.

The first has # which means that use must enter three numbers but those numbers will not be participating in the validation algorithm.

The Second box had seven digits,

% means that it will be involving in the validation. we apply modulo 7 to the sum of these required digits and if it equate to 0 algorithm returns true and the user can proceed to next dialog box.

Example:-

<###-%%%%%%%>,

124 - 7134501 is one of the key by which the user can proceed to next dialog box.

the first three digits 1, 2 and 4 can be any digits as they will not participate in validation.

the next seven digits 7134501 when you sum them you will get 21 and if you apply 21%7 you will get 0 so this is ok. Like wise you have to proceed.

You can add the dialog boxes like end user license agreement dialog and serial key dialog as shown.

Step 5

When you build the application, you will find the install program for your software or component in the projects debug directory.

Conclusion

I hope that many of the .Net developers who are using traditional setup script programs will find this as a easy and simple way to deploy your software. More than that if you got the licensed Visual Studio .Net with you then there is no need to purchase setup applications from other companies as Visual Studio .Net itself offers it for you. I think that I had given enough features of Windows Installer that comes with Visual Studio .Net. Now it's time that you try it just once to get used to it.

Javascript function to make asp.net control invisible

2009-06-10T23:31:00.000-07:00

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

function clearingTheLabels()

{

document.body.style.cursor = 'wait';

var lblGoodbad1='<%=lbtGoodBadStatus.ClientID %>';

var lblwordCount='<%=lblWordCount.ClientID %>';

document.getElementById(lblGoodbad1).style.visibility="hidden";

document.getElementById(lblwordCount).style.visibility="hidden";

document.getElementById('<%=grdScorer.ClientID %>').style.visibility="hidden";

}

script>

In Code behind

btnUrlToCrawl.Attributes.Add("onclick", "clearingTheLabels();");

Auto-Update as a Simple Custom Control

2009-06-09T04:18:00.000-07:00

http://www.vbdotnetheaven.com/UploadFile/scottlysle/CCAutoRefresh11012006154238PM/CCAutoRefresh.aspx

Run a .BAT file from ASP.NET

2009-06-05T06:33:00.000-07:00

http://codebetter.com/blogs/brendan.tompkins/archive/2004/05/13/13484.aspx

Export to Excel in ASP.Net 2.0 – Gridview to Excel, DataTable to Excel

2009-05-28T00:01:00.000-07:00

http://www.codedigest.com/Articles/ASPNET/130_Export_to_Excel_in_ASPNet_20_%E2%80%93Gridview_to_Excel_DataTable_to_Excel.aspx

It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level

2009-05-27T23:55:00.000-07:00

http://forums.asp.net/t/769696.aspx

ASP.Net 2.0: Export GridView to Excel - Part II

2009-05-27T05:41:00.001-07:00

ASP.Net 2.0: Export GridView to Excel - Part II

Introduction:

The article "ASP.Net 2.0: Export GridView to Excel" received a very good response from our user community. Some of the excellent tips collected from the user feedback have been included in the first version of the article.

One of the most common questions from our readers is regarding the handling of a Hyperlink column in the GridView Export to Excel. This article will expand on the original article and in this version, we will include the handling the export of the Hyperlink columns in the GridView export to Excel functionality and also re-factor our original logic to use more general features of reflection, allowing for easy extension to include additional control types. This code generalization does have a performance overhead and if the controls embedded in your GridView are limited to a particular set, the original implementation may be more suitable for your requirements.

Architectural Changes:

Define a Hash Table which holds the values for the controls to be replaced before the GridView control is exported to Excel.

This HashTable will map the control types that can be potentially embedded in the GridView to the corresponding control property that will be used to represent the particular control when exported to Excel.

The generalized "GetControlPropertyValue" method: In this version, we define a generalized method which will fetch the value of "key" property of the embedded control by using Reflection. We define the key properties for different types of embedded control using our HashTable member variable.

Our HashTable has been setup to perform the following property mappings.

Control Type	Corresponding Value to Represent in Excel
LinkButton or derived class	Text Property value
HyperLink or derived class	Text Property value
DropDownList or derived class	SelectedValue Property Value
CheckBox or derived class	Checked Property Value

If you need to handle additional control types separately for the export process, these control types can be added to the Hashtable.

New version of the PrepareGridViewForExport method: In this updated version, we get the "key" property for the control types that we have defined for replacement in our gridview, by calling GetControlPropertyValue if the control type or it's base type is included in the Hashtable for special handling. The control embedded in the GridView is then replaced by the value of the key property. After all the controls embedded in the GridView are processed recursively, the GridView is rendered into an HtmlTextWriter and output to the Excel formatted response.

Complete Code Listing:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Text;

using System.IO;

using System.Reflection;

public partial class DeleteConfirm : System.Web.UI.Page

{

Hashtable htControls = new Hashtable();

protected void Page_Load(object sender, EventArgs e)

{

htControls.Add("LinkButton", "Text");

htControls.Add("HyperLink", "Text");

htControls.Add("DropDownList", "SelectedItem");

htControls.Add("CheckBox", "Checked");

}

protected void Button1_Click(object sender, EventArgs e)

{

PrepareGridViewForExport(GridView1);

ExportGridView();

}

private void ExportGridView()

{

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

}

private void PrepareGridViewForExport(Control gv)

{

Literal l = new Literal();

for (int i = 0; i <>

{

if ((null != htControls[gv.Controls[i].GetType().Name]) || (null != htControls[gv.Controls[i].GetType

().BaseType.Name]))

{

l.Text = GetControlPropertyValue(gv.Controls[i]);

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

if (gv.Controls[i].HasControls())

{

PrepareGridViewForExport(gv.Controls[i]);

}

private string GetControlPropertyValue(Control control)

{

Type controlType = control.GetType();

string strControlType = controlType.Name;

string strReturn = "Error";

bool bReturn;

PropertyInfo[] ctrlProps = controlType.GetProperties();

string ExcelPropertyName = (string)htControls[strControlType];

if (ExcelPropertyName == null)

{

ExcelPropertyName = (string)htControls[control.GetType().BaseType.Name];

if (ExcelPropertyName == null)

return strReturn;

}

foreach (PropertyInfo ctrlProp in ctrlProps)

{

if (ctrlProp.Name == ExcelPropertyName &&

ctrlProp.PropertyType == typeof(String))

{

try

{

strReturn = (string)ctrlProp.GetValue(control, null);

break;

}

catch

{

strReturn = "";

}

if (ctrlProp.Name == ExcelPropertyName &&

ctrlProp.PropertyType == typeof(bool))

{

try

{

bReturn = (bool)ctrlProp.GetValue(control, null);

strReturn = bReturn ? "True" : "False";

break;

}

catch

{

strReturn = "Error";

}

if (ctrlProp.Name == ExcelPropertyName &&

ctrlProp.PropertyType == typeof(ListItem))

{

try

{

strReturn = ((ListItem)(ctrlProp.GetValue(control, null))).Text;

break;

}

catch

{

strReturn = "";

}

return strReturn;

}

Conclusion:

In this article, we saw the technique for including HyperLink controls embedded in the GridView to be exported to Excel, along with other controls such as DropDownList and CheckBox. We also saw how to use Reflection to setup an extensible function to handle various control types.

Happy Coding!

ASP.Net 2.0: Export GridView to Excel

2009-05-27T05:37:00.000-07:00

ASP.Net 2.0: Export GridView to Excel

Introduction:

In this article, we will see how to Export an ASP.Net 2.0 GridView to Excel.

The focus of the article is the Export to Excel functionality - the Gridview and it's data binding are only for demonstrating the Export functionality.

The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided.

Step 1: Setup your web page with the Gridview

In this article, we will assume you are starting with a web page which holds a GridView named GridView1. The GridView in our demo code is bound to a table named "ContactPhone" in a SQL Express database. The following code which exports the databound GridView to Excel is not dependent on the specific databindings and can be used without changes for your scenario.

ContactPhone Table Structure:

Column Name	Type
ContactID	Int (Identity)
FName	Varchar(50)
LName	Varchar(50)
ContactPhone	Varchar(20)

Step: The Actual Export

The code to do the Excel Export is very straightforward. You can also export to different application type by changing the content-disposition and ContentType.

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

If you run the code as above, it will result in an HttpException as follows:

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."

To avoid this error, add the following code:

public override void VerifyRenderingInServerForm(Control control)

{

}

Step : Convert the contents

If the GridView contains any controls, such as Checkboxes, Dropdownlists, we need to replace the contents with their relevant values. The following recursive function uses Reflection to determine the type of control. The control is deleted in preparation for the Excel export and the relevant value of the control is added.

private void PrepareGridViewForExport(Control gv)

{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

for (int i = 0; i <>

{

if (gv.Controls[i].GetType() == typeof(LinkButton))

{

l.Text = (gv.Controls[i] as LinkButton).Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(DropDownList))

{

l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(CheckBox))

{

l.Text = (gv.Controls[i] as CheckBox).Checked? "True" : "False";

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

if (gv.Controls[i].HasControls())

{

PrepareGridViewForExport(gv.Controls[i]);

}

Code Listing:

Image: Page Design

Image : Sample in action

Image: Export to Excel button is clicked

Image: GridView contents exported to Excel

ExcelExport.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportExcel.aspx.cs" Inherits="DeleteConfirm" %>

DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Contacts Listingtitle>

head>

<body>

<div>

Contacts Listing

<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export To Excel" />span>strong><br />

<br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ContactID"

DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." style="font-size: small; font-family: Arial" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">

<asp:BoundField DataField="ContactID" HeaderText="ContactID" ReadOnly="True" SortExpression="ContactID" Visible="False" />

<asp:BoundField DataField="FName" HeaderText="First Name" SortExpression="FName" />

<asp:BoundField DataField="LName" HeaderText="Last Name" SortExpression="LName" />

<asp:BoundField DataField="ContactPhone" HeaderText="Phone" SortExpression="ContactPhone" />

<asp:TemplateField HeaderText="Favorites">

<asp:CheckBox ID="CheckBox1" runat="server" />

ItemTemplate>asp:TemplateField>

Columns>

asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ContactsConnectionString1 %>"

DeleteCommand="DELETE FROM [ContactPhone] WHERE [ContactID] = @ContactID" InsertCommand="INSERT INTO [ContactPhone] ([FName], [LName], [ContactPhone]) VALUES (@FName, @LName, @ContactPhone)"

ProviderName="<%$ ConnectionStrings:ContactsConnectionString1.ProviderName %>"

SelectCommand="SELECT [ContactID], [FName], [LName], [ContactPhone] FROM [ContactPhone]"

UpdateCommand="UPDATE [ContactPhone] SET [FName] = @FName, [LName] = @LName, [ContactPhone] = @ContactPhone WHERE [ContactID] = @ContactID">

<asp:Parameter Name="FName" Type="String" />

<asp:Parameter Name="LName" Type="String" />

<asp:Parameter Name="ContactPhone" Type="String" />

InsertParameters>

<asp:Parameter Name="FName" Type="String" />

<asp:Parameter Name="LName" Type="String" />

<asp:Parameter Name="ContactPhone" Type="String" />

<asp:Parameter Name="ContactID" Type="Int32" />

UpdateParameters>

<asp:Parameter Name="ContactID" Type="Int32" />

DeleteParameters>

asp:SqlDataSource>

<br />

div>

form>

body>

html>

ExcelExport.aspx.cs

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Text;

using System.IO;

public partial class DeleteConfirm : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

//Export the GridView to Excel

PrepareGridViewForExport(GridView1);

ExportGridView();

}

private void ExportGridView()

{

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

}

private void PrepareGridViewForExport(Control gv)

{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

for (int i = 0; i <>

{

if (gv.Controls[i].GetType() == typeof(LinkButton))

{

l.Text = (gv.Controls[i] as LinkButton).Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(DropDownList))

{

l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(CheckBox))

{

l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

if (gv.Controls[i].HasControls())

{

PrepareGridViewForExport(gv.Controls[i]);

}

Implementation Options:

In quite a few cases, developers face an error in the Export functionality - typically the error message is "RegisterForEventValidation can only be called during Render();".

Our website readers have contributed some good suggestions in the article comments below. I would particularly like to highlight the suggestion by Marianna, who provides an alternative implementation to the VerifyRenderingInServerForm override. This approach is described below:

Step 1: Implement the Export functionality as described above.
Step 2: Remove the code to override the VerifyRenderingInServerForm method.
Step 3: Modify the code for the ExportGridView function as below. The code highlighted in green creates and HtmlForm on the fly, before exporting the gridview, adds the gridview to this new form and renders the form (instead of rendering the gridview in our original implementation)

private void ExportGridView()

{

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

// Create a form to contain the grid

HtmlForm frm = new HtmlForm();

GridView1.Parent.Controls.Add(frm);

frm.Attributes["runat"] = "server";

frm.Controls.Add(GridView1);

frm.RenderControl(htw);

//GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

This implementation has the advantage that it can be setup as re-usable code in a separate library, without having to override the base class method each time.

Note to readers:

Thank you for your comments and feedback! Happy coding!!!

ASP.Net 2.0: Export GridView to Excel - Part II

This version of the article includes handling of the Hyperlink columns in the GridView export to Excel and also re-factors our original logic to use more general features of Reflection

Conclusion:

In this article, we saw how to Export an ASP.Net 2.0 GridView to Excel. The code fragments are not linked to the backend structure and can be re-used across projects for the common functionality provided.

The focus of the article is the Export to Excel functionality - the Gridview and it's data binding are only for demonstrating the following functionality.

For reference on Exporting ASP.Net 1.1 DataGrid to Excel: Export ASP.NET DataGrid to Excel

Disclaimer: This article is for purely educational purposes and is a compilation of notes, material and my understanding on this subject. Any resemblance to other material is an un-intentional coincidence and should not be misconstrued as malicious, slanderous, or any anything else hereof.

Java script for button control in code behind in asp.net

2009-05-26T04:02:00.000-07:00

protected void Page_Load(object sender, EventArgs e)
{
btnFirst.Attributes.Add("onmouseover", "this.className='handCursor';");
btnFirst.Attributes.Add("onmouseout", "this.className='defaultCursor';");
btnPrev.Attributes.Add("onmouseover", "this.className='handCursor';");
btnPrev.Attributes.Add("onmouseout", "this.className='defaultCursor';");
btnNext.Attributes.Add("onmouseover", "this.className='handCursor';");
btnNext.Attributes.Add("onmouseout", "this.className='defaultCursor';");
btnLast.Attributes.Add("onmouseover", "this.className='handCursor';");
btnLast.Attributes.Add("onmouseout", "this.className='defaultCursor';");

if (!Page.IsPostBack)
{
}
}

CSS functions

.handCursor
{
cursor:hand;
color:Orange;
background-color:Transparent;
border-left-color: transparent;
border-bottom-color: transparent;
border-top-style: none;
border-top-color: transparent;
border-right-style: none;
border-left-style: none;
border-bottom-style:none;
border-right-color: transparent;
font:Arial;
font-size:11pt;
font-weight:bold;

}
.defaultCursor
{
cursor:default;
background-color:Transparent;
border-left-color: transparent;
border-bottom-color: transparent;
border-top-style: none;
border-top-color: transparent;
border-right-style: none;
border-left-style: none;
border-bottom-style:none;
border-right-color: transparent;
color:Teal;
font:Arial;
font-size:12pt;
font-weight:bold;
}

GridView Sorting

2009-05-25T00:25:00.000-07:00

protected void gridViolationDetails_Sorting(object sender, GridViewSortEventArgs e)
{
DataTable dtsort = (DataTable)ViewState["viewStatViolation"];

string sortExpression = e.SortExpression;
if (GridViewSortDirection == SortDirection.Ascending)
{
GridViewSortDirection = SortDirection.Descending;
SortGridView(sortExpression, "DESC", gridViolationDetails, dtsort);
}
else
{
GridViewSortDirection = SortDirection.Ascending;
SortGridView(sortExpression, "ASC", gridViolationDetails, dtsort);
}
}

public SortDirection GridViewSortDirection
{
get
{
if (ViewState["sortDirection"] == null)
ViewState["sortDirection"] = SortDirection.Ascending;
return (SortDirection)ViewState["sortDirection"];
}
set
{
ViewState["sortDirection"] = value;
}
}

private void SortGridView(string sortExpression, string direction,GridView grdV,DataTable dt)
{
DataView dv = new DataView(dt);
dv.Sort = sortExpression + " " + direction;
grdV.DataSource = dv;
grdV.DataBind();
}

Practical threaded programming with Python

2009-04-22T06:42:00.000-07:00

http://www.ibm.com/developerworks/aix/library/au-threadingpython/index.html

Creating Setup and Deployment Projects in VS.NET && Set a password textbox for Visual Studio .NET setup project

2009-04-06T08:37:00.000-07:00

http://www.c-sharpcorner.com/UploadFile/vishnuprasad2005/SetupProjects12022005022406AM/SetupProjects.aspx

http://www.codeproject.com/KB/install/setupprjpwd.aspx?fid=33512&df=90&mpp=25&noise=3&sort=Position&view=Quick&select=746338

50 Ways to Kill Excel using .net

2009-03-20T02:45:00.000-07:00

http://www.devcity.net/PrintArticle.aspx?ArticleID=239

Disabling Close Button on Forms using .net 2.0

2009-02-20T04:23:00.000-08:00

Using the Code

During construction and creation of the Form object, .NET would use the default creation parameters available in the base class CreateParams property. In fact, CreateParams property is available in Forms.Control class. In our form class (derived from System.Windows.Forms.Form), override this property and modify the creation flags. For disabling the Close button use 0x200 to modify the ClassStyle member of the CreateParams.

private const int CP_NOCLOSE_BUTTON = 0x200;
protected override CreateParams CreateParams
{
get
{
CreateParams myCp = base.CreateParams;
myCp.ClassStyle = myCp.ClassStyle | CP_NOCLOSE_BUTTON;
return myCp;
}
}

How to implement a simple filewatcher Windows service in C#

2009-02-13T02:17:00.000-08:00

http://www.codeproject.com/KB/files/C__FileWatcher.aspx?display=PrintAll&fid=410731&df=90&mpp=25&noise=3&sort=Position&view=Quick&select=2014241

How can I run some Python script within C# program?

2009-02-10T04:13:00.000-08:00

http://groups.google.com/group/comp.lang.python/browse_frm/thread/da2a675da29b0bd/197b6a89095ef930?lnk=st&q&rnum=4&pli=1

Cross-thread operation not valid: Control accessed from a thread other than the thread it was created on.

2009-01-21T06:12:00.000-08:00

http://stackoverflow.com/questions/142003/cross-thread-operation-not-valid-control-accessed-from-a-thread-other-than-the

Multithreading in .NET With the BackgroundWorker

2009-01-21T06:11:00.000-08:00

http://amgadhs.com/index.php/2008/11/multithreading-in-net-with-the-backgroundworker/

Convert Image to Icon in .Net

2008-12-30T04:35:00.000-08:00

///
/// Converts an image into an icon.
///
/// The image that shall become an icon
/// The width and height of the icon. Standard
/// sizes are 16x16, 32x32, 48x48, 64x64.
/// Whether the image should be squashed into a
/// square or whether whitespace should be put around it.
/// An icon!!
private Icon MakeIcon(Image img, int size, bool keepAspectRatio) {
Bitmap square = new Bitmap(size, size); // create new bitmap
Graphics g = Graphics.FromImage(square); // allow drawing to it
int x, y, w, h; // dimensions for new image
if(!keepAspectRatio || img.Height == img.Width) {
// just fill the square
x = y = 0; // set x and y to 0
w = h = size; // set width and height to size
} else {
// work out the aspect ratio
float r = (float)img.Width / (float)img.Height;
// set dimensions accordingly to fit inside size^2 square
if(r > 1) { // w is bigger, so divide h by r
w = size;
h = (int)((float)size / r);
x = 0; y = (size - h) / 2; // center the image
} else { // h is bigger, so multiply w by r
w = (int)((float)size * r);
h = size;
y = 0; x = (size - w) / 2; // center the image
}
}
// make the image shrink nicely by using HighQualityBicubic mode
g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;
g.DrawImage(img, x, y, w, h); // draw image with specified dimensions
g.Flush(); // make sure all drawing operations complete before we get the icon
// following line would work directly on any image, but then
// it wouldn't look as nice.
return Icon.FromHandle(square.GetHicon());
}

Creating CSV file using Oledb object in C#.net

2008-12-28T23:14:00.000-08:00

OleDbConnection OleDbCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strCSVPath + ";Extended Properties=\"text;HDR=Yes;FMT=Delimited(,)\"");
OleDbCmd = new OleDbCommand("Create table BundlesTable.csv(ProjectName varchar(250),BundleName varchar(250),BundleItems varchar(250))", OleDbCon);
OleDbCmd.ExecuteNonQuery();

Visual Studio Windows Application Setup Project

2008-12-08T03:19:00.000-08:00

http://www.codeproject.com/KB/dotnet/Win_App_Setup_Project.aspx

Converting numbers into formatted strings in VB.NET

2008-12-05T01:15:00.000-08:00

http://articles.techrepublic.com.com/5100-10878_11-6103431.html

Opening the link or website in .net windows application

2008-11-21T01:02:00.000-08:00

System.Diagnostics.Process.Start("http://decidyn.com");

Converting String to Datetime in C#.net 2.0

2008-11-04T06:02:00.000-08:00

System.Globalization.CultureInfo culture = new System.Globalization.CultureInfo("en-US", false);
culture.DateTimeFormat.DateSeparator = string.Empty;
culture.DateTimeFormat.ShortDatePattern = "yyyyMMdd";

dateime dt=DateTime.ParseExact(startEndDate[1].ToString(),"yyyyMMdd", culture)

Exporting a DataGridView to an Excel/PDF/image file by using Reporting Services report generation

2008-10-31T23:37:00.000-07:00

www.codeproject.com/KB/reporting-services/ReportExporters_WinForms.aspx

Getting Unique field of a column form a table( also get distinct count of column of datatable)

2008-10-16T05:24:00.000-07:00

public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
{
DataTable dt = new DataTable(TableName);
dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

object LastValue = null;
foreach (DataRow dr in SourceTable.Select("", FieldName))
{
if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
{
LastValue = dr[FieldName];
dt.Rows.Add(new object[] { LastValue });
}
}
//if (ds != null)
// ds.Tables.Add(dt);
return dt;
}
private bool ColumnEqual(object A, object B)
{

// Compares two values to see if they are equal. Also compares DBNULL.Value.
// Note: If your DataTable contains object fields, then you must extend this
// function to handle them in a meaningful way if you intend to group on them.

if (A == DBNull.Value && B == DBNull.Value) // both are DBNull.Value
return true;
if (A == DBNull.Value || B == DBNull.Value) // only one is DBNull.Value
return false;
return (A.Equals(B)); // value type standard comparison
}

Using Generic lists with crystal reports in .net

2008-09-14T22:49:00.000-07:00

Public Sub ReportDocumentListDetails(ByVal rptPath As String, ByVal reportname As String, ByVal rptdoc As CrystalDecisions.CrystalReports.Engine.ReportDocument, _

ByVal arListName As System.Collections.ArrayList, ByVal arListValue As System.Collections.ArrayList)

Dim rptViewer As New CrystalDecisions.Windows.Forms.CrystalReportViewer()

Dim ds As DatasetReport = New DatasetReport

If (arListName.Count = arListValue.Count) Then

For i As Integer = 0 To arListName.Count - 1

rptdoc.SetParameterValue(arListName(i).ToString, arListValue(i))

End If

Dim objExOpt As CrystalDecisions.Shared.ExportOptions

Dim objDiskOpt As New CrystalDecisions.Shared.DiskFileDestinationOptions

objDiskOpt.DiskFileName = "F:\nath\" & reportname & ".pdf"

objExOpt = rptdoc.ExportOptions

objExOpt.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile

objExOpt.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat

objExOpt.DestinationOptions = objDiskOpt

rptdoc.Export(objExOpt)

rptdoc.Close()

End Sub

Export options of crystal reports and passing parameters to crystal reports in .net

2008-09-14T22:45:00.000-07:00

Public Sub ReportDocumentDetails(ByVal rptPath As String, ByVal reportname As String, ByVal dtReport As DataTable, _

ByVal arListName As System.Collections.ArrayList, ByVal arListValue As System.Collections.ArrayList)

Dim rptViewer As New CrystalDecisions.Windows.Forms.CrystalReportViewer()

Dim ds As DatasetReport = New DatasetReport

Dim rptdoc As CrystalDecisions.CrystalReports.Engine.ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument

rptdoc.Load(rptPath)

rptdoc.SetDataSource(CType(dtReport, DataTable))

If (arListName.Count = arListValue.Count) Then

For i As Integer = 0 To arListName.Count - 1

rptdoc.SetParameterValue(arListName(i).ToString, arListValue(i))

End If

Dim objExOpt As CrystalDecisions.Shared.ExportOptions

Dim objDiskOpt As New CrystalDecisions.Shared.DiskFileDestinationOptions

objDiskOpt.DiskFileName = "F:\nath\" & reportname & ".pdf"

objExOpt = rptdoc.ExportOptions

objExOpt.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile

objExOpt.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat

objExOpt.DestinationOptions = objDiskOpt

rptdoc.Export(objExOpt)

rptdoc.Close()

End Sub

Working with Attachments in Outlook Email using .net 2.0

2008-09-08T02:59:00.000-07:00

Dim olApp As Microsoft.Office.Interop.Outlook.Application = New ApplicationClass()
Dim oNameSpace As Microsoft.Office.Interop.Outlook.NameSpace = olApp.GetNamespace("MAPI")
Dim oOutL As New Outlook.Application
Dim oMail As Outlook.MailItem

Dim objFolder As MAPIFolder = oNameSpace.GetDefaultFolder(OlDefaultFolders.olFolderInbox)
Dim oItems As Items = objFolder.Items

Dim twmp As Integer = 15
Do While (twmp >= 0)
oMail = CType(objFolder.Items((objFolder.Items.Count - twmp)), MailItem)
If (oMail.UnRead = True) Then
If (oMail.Subject = "Document") Then
oMail.Attachments(1).SaveAsFile("E:\VBABonds\kaushik\" & oMail.Attachments(1).FileName)
End If
End If
twmp = (twmp - 1)
Loop

Open word file using vb.net 2,0 in windows application

2008-09-05T03:05:00.000-07:00

To open ms word file using vb.net 2.0 in windows application

Add reference microsoft.word 12.o object from com tab.

If (System.IO.File.Exists(txtBrowse.Text)) Then

Dim test As Microsoft.Office.Interop.Word.Application

test = New Microsoft.Office.Interop.Word.Application

test.Documents.Open(txtBrowse.Text)

test.OpenAttachmentsInFullScreen = True

test.Visible = True

End If

Storing and Downloading ms word file in ms access file using VB.NET 2.0

2008-09-04T04:00:00.000-07:00

Storing a ms word file in access database using VB.NET 2.0

Dim objcon As System.Data.OleDb.OleDbConnection = CommonFunctions.clsCommonFunctions.OleDbConnections()

Dim objcmd As System.Data.OleDb.OleDbCommand = New OleDb.OleDbCommand("", objcon)

Dim par As OleDb.OleDbParameter = New OleDb.OleDbParameter("@Attachment", OleDb.OleDbType.LongVarBinary)

Dim strText As String = "update bonds set Attachment=@Attachment where bondsid=" & txtDBaseRefNo.Text

objcon.Open()

Dim fs As New System.IO.FileStream _

(TextBox1.Text, System.IO.FileMode.OpenOrCreate, _

System.IO.FileAccess.Read)

Dim MyData(fs.Length) As Byte

fs.Read(MyData, 0, fs.Length)

fs.Close()

par.Value = MyData

objcmd.CommandText = strText

objcmd.Parameters.Add(par)

Dim i As Integer = objcmd.ExecuteNonQuery()

fs = Nothing

objcon.Close()

Downloading ms word file from ms access database using VB.NET 2.0

Dim objcon As System.Data.OleDb.OleDbConnection = CommonFunctions.clsCommonFunctions.OleDbConnections()

Dim command As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand( _

"select Attachment from bonds where bondsid=" & txtDBaseRefNo.Text, objcon)

' Writes the BLOB to a file (*.bmp).

Dim stream As System.IO.FileStream

' Streams the binary data to the FileStream object.

Dim writer As System.IO.BinaryWriter

' The size of the BLOB buffer.

Dim bufferSize As Integer = 100

' The BLOB byte() buffer to be filled by GetBytes.

Dim outByte(bufferSize - 1) As Byte

' The bytes returned from GetBytes.

Dim retval As Long

' The starting position in the BLOB output.

Dim startIndex As Long = 0

' The publisher id to use in the file name.

Dim pubID As String = ""

' Open the connection and read data into the DataReader.

objcon.Open()

Dim reader As System.Data.OleDb.OleDbDataReader = command.ExecuteReader(CommandBehavior.SequentialAccess)

Do While reader.Read()

' Get the publisher id, which must occur before getting the logo.

'pubID = reader.GetString(0)

' Create a file to hold the output.

stream = New System.IO.FileStream( _

"C:\Documents and Settings\Gopinath\Desktop\doccc1111.doc", System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write)

writer = New System.IO.BinaryWriter(stream)

' Reset the starting byte for a new BLOB.

startIndex = 0

' Read bytes into outByte() and retain the number of bytes returned.

retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize)

' Continue while there are bytes beyond the size of the buffer.

Do While retval = bufferSize

writer.Write(outByte)

writer.Flush()

' Reposition start index to end of the last buffer and fill buffer.

startIndex += bufferSize

retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize)

Loop

' Write the remaining buffer.

writer.Write(outByte, 0, retval - 1)

writer.Flush()

' Close the output file.

writer.Close()

stream.Close()

Loop

' Close the reader and the connection.

reader.Close()

objcon.Close()

Run Executable file in ASP.NET

2008-09-02T06:02:00.000-07:00

i will provide a code snippet of how to run a process (in this case it is WindowsMediaPlayer.exe) which exists in your application root folder.

// Create An instance of the Process class responsible for starting the newly process.

System.Diagnostics.Process process1 = new System.Diagnostics.Process();

// Set the directory where the file resides

process1.StartInfo.WorkingDirectory = Request.MapPath("~/");

// Set the filename name of the file you want to open

process1.StartInfo.FileName = Request.MapPath("WindowsMediaPlayer.exe");

// Start the process

process1.Start();

Run a .EXE from a VB.NET application?

2008-09-02T06:01:00.000-07:00

To run a .EXE file from a VB.NET application you need to import the System.Diagnostics namespace. The following sample shows how to run Notepad from a VB.NET application.


Imports System
Imports System.Diagnostics
Dim program As New Process()
program.StartInfo.FileName = "Notepad.exe"
program.StartInfo.Arguments = " "
program.Start()

Validating Email id using Regular Expression in vb.net

2008-08-28T05:35:00.000-07:00

Dim m As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex("^((?>[a-zA-Z\d!#$%&'*+\-/=?^_`{|}~]" & _

"+\x20*|""((?=[\x01-\x7f])" & _

"[^""\\]|\\[\x01-\x7f])*""\x20*)*(?<))?((?!\.)" & _ "(?>\.?[a-zA-Z\d!#$%&'*+\-/=?^_`{|}~]+)+|""((?=[\x01-\x7f])[^""\\]" & _

"|\\[\x01-\x7f])*"")@(((?!-)[a-zA-Z\d\-]+(? & _

"{4}|[a-zA-Z\d\-]*[a-zA-Z\d]:((?=[\x01-\x7f])[^\\\[\]]|\\[\x01-\x7f])+)\])(?(angle)>)$")

Dim isMatch As Boolean = m.IsMatch(txtBrokerEmail.Text) 'INPUT

MsgBox(isMatch.ToString())

Excel Reports in ASP.NET 2.0

2008-08-26T00:56:00.000-07:00

In this article you will learn how to read data from Excel, generate report using the Response objects and to play with the Microsoft Excel 11.0 objects involved. Indeed, it is impossible to cover all features in this tutorial but many important techniques were covered to help you down the line. It is up to you to be creative and adapt the techniques to your scenarios.

Analyst Real Team System (ARTS) 7.0

Analyst Real Team System (ARTS) provides a complete set of tools for modeling the various artifact types of system development. ARTS provides a uniform approach to modeling use cases, test cases, business rules, bugs, tasks, and more. The result is a unique and coherent framework for successful system development by anyone using the software.

Introduction

Excel is such a powerful tool that almost all companies make extensive use of it for analyzing data. Moreover, the internet is not as it was 10 years ago. Almost all pages that are out there on the internet are dynamic ones, that is, interacts with a database in backend to produce results. Sometimes, data that are displayed, if made available on in an Excel file, proper analysis of the data often helps in making more accurate decisions by using Excel features. In this tutorial, we will learn how to interact with Excel files, both reading and writing.

Excel - ASP.NET Scenarios

For the purpose of this tutorial, an Excel sheet had been prepared that holds record of students and their respective marks in subjects. Please note that these records are fictitious and had been input randomly.

StudentId	StudentName
10001	John Thomas
10002	Terry Lane
10003	Anne Marie
10004	Tom Sawyer
10005	Derek Ince
10006	Emerson Boyce
10007	Thommas Kolka
10008	Edison Hall
10009	Teddy Harewood
10010	Williams Yorkshire

Fig 1. Table holding information for students

StudentId	Marks
10001	50
10002	60
10003	75
10004	80
10005	90
10006	95
10007	100
10008	20
10009	48
10010	66

Fig 2. Table holding student marks in Mathematics subject

StudentId	Marks
10001	30
10002	25
10003	65
10004	78
10005	95
10006	64
10007	32
10008	88
10009	95
10010	99

Fig 3. Table holding student marks in Geography subject

Interacting with Excel

Fortunately, there are COM objects that are available to interact with the Excel file. Also, we can make use of the OleDB class to treat the Excel file as a simple table. In this way, we can fire SQL queries to the Excel file so that we can retrieve data from the sheets. An analogy can be made to a database. The Excel Workbook is considered as the Database while the Sheets are considered as tables. Microsoft Excel 11.0 Object Library should be added as Reference to get the features for interacting with Excel.

Fig 4. Adding Microsoft Excel 11.0 Object Library as Reference

The Excel file is then added to the DataSource folder in ASAP.NET to be interacted with. To connect to a certain database, a connection string is needed. The same thing applies for Excel. The following is a key added in the Web.Config file for future reference.

Note that our Excel file which contains the necessary data will be SchoolMgt.xls, as written in the connection string.

Fig 5. Excel file illustrating Excel Book containing data sheets with data

Reading data from Excel sheets

Let us build an interface for getting data from the Excel file.

Fig 6. Interface to search records per table selected.

It is important to import the following:

Imports System.Data.OleDb
Imports System.Data

The following code creates the connection and builds the query based on the selected table.

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String

strSQL = "SELECT * FROM [" & cmbSheets.SelectedItem.ToString() & "$]"

dbConn.Open()

Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)

daExcel.Fill(dsExcel)

dgResults.DataSource = dsExcel
dgResults.DataBind()
Catch ex As Exception
Throw ex
End Try
End Sub

Please note that the table name in the SELECT query should be in the format [TableName$], which the square brackets and the dollar sign at the end.

Playing with SELECT statements

As you have guessed, the Excel file has turned out to be a normal database. The transparency that the OleDB connection had made is exceptional. Also, note that the first row is taken to contain field names for the columns.

Now, let us get the marks of the students in Mathematics, Geography and Total, sorted in Descending order.

Protected Sub btnGenerateReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerateReport.Click
Try

Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String

strSQL = "SELECT S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total "& _
"FROM [Students$] S, [Mathematics$] M, [Geography$] G "& _
"WHERE(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " & _
"ORDER BY (M.Marks+G.Marks) DESC"
dbConn.Open()

Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)

daExcel.Fill(dsExcel)

dgReports.DataSource = dsExcel
dgReports.DataBind()

Catch ex As Exception
Throw ex
End Try
End Sub

Fig 7. Report generating the students' marks and total, sorted in descending order.

Generating Excel Reports

Two ways for generating Excel report will be discussed in this tutorial. The first one is using the "Response" class and the second one by manipulating the Excel objects that "Microsoft Excel 11.0" Objects provide.

The following piece of code illustrates how this is done

Protected Sub btnToExcelByResponse_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByResponse.Click
' Variables declaration
Dim dsExport As New DataSet()
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim dgGrid As New DataGrid()

dgGrid.DataSource = getData()

' Report Header
hw.WriteLine(" Student Marking Report ")

' Get the HTML for the control.
dgGrid.HeaderStyle.Font.Bold = True
dgGrid.DataBind()
dgGrid.RenderControl(hw)

' Write the HTML back to the browser.
Response.ContentType = "application/vnd.ms-excel"
Me.EnableViewState = False
Response.Write(tw.ToString())
Response.End()
End Sub

First, we create the dataset that will hold the records. Then, a StringWriter object (tw) is also created along with an HtmlTextWriter (hw) which takes as parameter the text writer object. They are important for rendering purposes; i.e. html tags can be applied like bold, italic etc. to the resulting Excel report.
Also, a datagrid object which will store the dataset of records.
Note that function getData() in the code snippet only contains the codes in the previous example which returns a dataset of records.
hw.WriteLine(" Student Marking Report ") shows that you can also combine HTML codes with the output for proper display. In our example, the font size will be 5, underlined and bold.
dgGrid.RenderControl(hw) renders the HTML object with the data that the datagrid contains. The result is normally formatted in a tabular format.
Response.ContentType = "application/vnd.ms-excel" makes the Reponse object output to Excel.
Response.Write(tw.ToString()) outputs the formatted object to Excel.

Figure 8. Output result showing the report that is generated in Excel

The second way for generating Excel reports from ASP.NET is to use the Microsoft Excel 11.0 Objects. This method offers more fallibility in terms of manipulating the features that Excel provides and formatting the report.

The following code snippet makes use of the various Excel objects that Excel provides to generate the report.

Imports System.Reflection
Imports Excel = Microsoft.Office.Interop.Excel

Protected Sub btnToExcelByObjects_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByObjects.Click
Try
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)
xlWorkBook.Application.Visible = True
xlWorkSheet = xlWorkBook.ActiveSheet

' Gets the dataset containing the data
Dim dsData As DataSet = getData()
Dim i As Integer = 2

' Outputting the fieldnames in pink bold color
xlWorkSheet.Cells(1, 1) = "Student ID"
xlWorkSheet.Cells(1, 2) = "Student Name"
xlWorkSheet.Cells(1, 3) = "Mathematics"
xlWorkSheet.Cells(1, 4) = "Geography"
xlWorkSheet.Cells(1, 5) = "Total"

xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1
xlWorkSheet.Range("$A1:$E1").Font.Bold = True

' Outputting the data
For Each dr As DataRow In dsData.Tables(0).Rows
xlWorkSheet.Cells(i, 1) = dr(0)
xlWorkSheet.Cells(i, 2) = dr(1)
xlWorkSheet.Cells(i, 3) = dr(2)
xlWorkSheet.Cells(i, 4) = dr(3)

' Building the formula for calculating the sum
xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString())

' Going to the next row
i = i + 1
Next

' Auto fit the columns
xlWorkSheet.Columns.AutoFit()

' Generating the graph
Dim chart As Excel.Chart
chart = xlWorkBook.Charts.Add()

With chart
.ChartType = Excel.XlChartType.xlColumnClustered
.SetSourceData(xlWorkSheet.Range("A1:E11"), 2)

.HasTitle = True
.ChartTitle.Characters.Text = "Students' marks"

.Axes(1, Excel.XlAxisGroup.xlPrimary).HasTitle = True
.Axes(1, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Students"
.Axes(2, Excel.XlAxisGroup.xlPrimary).HasTitle = True
.Axes(2, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Marks"
End With
Catch ex As Exception
Throw ex
End Try
End Sub

An Excel workbook is first created. Then the active sheet is accessed by xlWorkSheet = xlWorkBook.ActiveSheet. After that, we get the data into the dataset, ready to be processed. Using the Font property, the title is displayed in Pink bold colour. Note how the Formula that Excel provides is properly used to calculate the SUM of the marks for each student xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString()).

Figure 9. Excel Report having output the list of students and their respective marks.

A chart is also generated using the objects. The x-axis consists of the names and id of each student. The y-axis, the marks for the different students. The chart is always handy for analysis of data.

Figure 10. Chart that is generated using Microsoft Excel 11.0 Objects

Open and Edit Cells in an Excel 2007 file in VB.NET

2008-08-26T00:54:00.000-07:00

http://vb.net-informations.com/excel-2007/vb.net_excel_2007_open_file.htm

Open an Excel File in .net

2008-08-26T00:53:00.000-07:00

Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open(strpath)
xlWorkSheet = xlWorkBook.Worksheets("sheet1")
xlWorkBook.Application.Visible = True

Sub reports in crystal reports

2008-08-21T00:56:00.000-07:00

http://aspalliance.com/1673_Creating_SubReports_Using_Crystal_Reports.all

Removing duplicate records from datatable in ADO.NET

2008-08-08T01:03:00.000-07:00

private DataTable RemoveDuplicates(DataTable dt)
{
DataTable dtUnique = new DataTable();
dtUnique = dt.Clone();
DataColumn[] coln = new DataColumn[dt.Columns.Count];
dtUnique.Columns.CopyTo(coln, 0);
dtUnique.PrimaryKey = coln;
DataRow[] drs = new DataRow[dt.Rows.Count];
dt.Rows.CopyTo(drs, 0);

foreach (DataRow dr1 in drs)
{
if (dtUnique.Rows.Contains(dr1.ItemArray))
dt.Rows.Remove(dr1);
else
dtUnique.Rows.Add(dr1.ItemArray);
}
dt.Dispose();
drs = null;
coln = null;

return dtUnique;
}

sudagn

Add and Remove HTML elements dynamically with Javascript

Encrypt and Decrypt Connection String

Make fancy buttons using CSS sliding doors technique

Nested GridView in ASP.NET

Nested GridView in ASP.NET

Microsoft Anti-Cross Site Scripting Library V1.5: Protecting the Contoso Bookmark Page

Export GridView To Word/Excel/PDF/CSV in ASP.Net

Adding QueryString Parameters to the SiteMapNode

SiteMapProvider doesn't take QueryString into consideration

GridView with Thumbnail Images

Hidden Columns with values: asp:GridView (access the value of the hidden columns in gridview )

How to add link to a css file in content page

How To Validate a Serial Number During an Installation Created with VSI

How To Validate a Serial Number During an Installation Created with VSI

SUMMARYIn a Visual Studio Installer (VSI) project, the developer has the ability to ad...

MORE INFORMATIONCreate an empty installer project with VSI.Add a Customer Information dialog box...

Deploy your Application or Component Using Windows Installer and serial key

Introduction

About Microsoft Windows Installer

Run-Time Requirements

Features of Windows Installer:

Five Easy Steps:

Step 1

Step 2

Step 3

Step 4

We can use the following editors during the deployment of our application

File System Editor:

Registry Settings Management Editor:

File Types Management Editor :

Custom Actions Management Editor

Launch Condition Management Editor :

User Interface Management Editor

Conclusion

Javascript function to make asp.net control invisible

Auto-Update as a Simple Custom Control

Run a .BAT file from ASP.NET

Export to Excel in ASP.Net 2.0 – Gridview to Excel, DataTable to Excel

It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level

ASP.Net 2.0: Export GridView to Excel - Part II

ASP.Net 2.0: Export GridView to Excel

Java script for button control in code behind in asp.net

GridView Sorting

Practical threaded programming with Python

Creating Setup and Deployment Projects in VS.NET && Set a password textbox for Visual Studio .NET setup project

50 Ways to Kill Excel using .net

Disabling Close Button on Forms using .net 2.0

Using the Code

How to implement a simple filewatcher Windows service in C#

How can I run some Python script within C# program?

Cross-thread operation not valid: Control accessed from a thread other than the thread it was created on.

Multithreading in .NET With the BackgroundWorker

Convert Image to Icon in .Net

Creating CSV file using Oledb object in C#.net

Visual Studio Windows Application Setup Project

Converting numbers into formatted strings in VB.NET

Opening the link or website in .net windows application

Converting String to Datetime in C#.net 2.0

Exporting a DataGridView to an Excel/PDF/image file by using Reporting Services report generation

Getting Unique field of a column form a table( also get distinct count of column of datatable)

Using Generic lists with crystal reports in .net

Export options of crystal reports and passing parameters to crystal reports in .net

Working with Attachments in Outlook Email using .net 2.0

Open word file using vb.net 2,0 in windows application

Storing and Downloading ms word file in ms access file using VB.NET 2.0

Run Executable file in ASP.NET

Run a .EXE from a VB.NET application?

Validating Email id using Regular Expression in vb.net

Excel Reports in ASP.NET 2.0

Introduction

Excel - ASP.NET Scenarios

Interacting with Excel

Reading data from Excel sheets

Playing with SELECT statements

Generating Excel Reports

Open and Edit Cells in an Excel 2007 file in VB.NET

Open an Excel File in .net

Sub reports in crystal reports

Removing duplicate records from datatable in ADO.NET

SUMMARY
In a Visual Studio Installer (VSI) project, the developer has the ability to ad...

MORE INFORMATION
Create an empty installer project with VSI.Add a Customer Information dialog box...