Rofans.NET

Recently at work, I encountered an assignment to export DataTable objects into .xls file in C#. I conducted quite a research which revealed myself few alternatives to achieve this task.

Using HtmlTextWriter and StringWriter Objects

//make sure these two are imported

using System.IO;

using System.Text;

protected void ExportToExcel()

{

//Get the data from database into datatable
string strQuery = “select CustomerID, ContactName, City, PostalCode” +” from customers”;
SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);
//Create a dummy GridView
GridView GridView1 = new GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = dt;
GridView1.DataBind();

Response.Clear();

Response.Buffer = true;

Response.AddHeader(“content-disposition”,”attachment;filename=New.xls”);
Response.Charset = “”;
Response.ContentType = “application/vnd.ms-excel”;

StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);

for (int i = 1; i < gvGridView.HeaderRow.Cells.Count; i++)

{

gvGridView.HeaderRow.Cells[i].Text = Table.Columns[i - 1].ColumnName;

}

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

{

//Apply text style to each Row
gvGridView.Rows[i].Attributes.Add(“class”, “textmode”);

}
gvGridView.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();

}

The good thing about this method is it’s very simple to be understood. What it basically does is creating an HTML table and tell our computer that it’s an .xls file. Yeah, it’s that simple. Problem is .xls file resulted from this process won’t be available to be accessed reversibly. If you would like to import back this .xls file into DataTable, you won’t be able to. Because this is basically a conned file.

Using XmlTextWriter Objects

//ensure that this is imported

using System.Xml;

public class ExcelHelper

{

//private static readonly ExcelHelper _instance = new ExcelHelper();
private static readonly ExcelHelper _instance = new ExcelHelper();

public static ExcelHelper Instance
{
get { return _instance; }
}

///
/// Create one Excel-XML-Document with SpreadsheetML from a DataTable
///
/// Datasource which would be exported in Excel
/// Name of exported file

public void Create(DataTable dtSource, string strFileName)
{

// Create XMLWriter
using (XmlTextWriter xtwWriter = new XmlTextWriter(strFileName, Encoding.UTF8))
{

//Format the output file for reading easier
xtwWriter.Formatting = Formatting.Indented;

//
xtwWriter.WriteStartDocument();

//
xtwWriter.WriteProcessingInstruction(“mso-application”, “progid=\”Excel.Sheet\”");

//
xtwWriter.WriteStartElement(“DocumentProperties”, “urn:schemas-microsoft-com:office:office”);

// Write document properties
xtwWriter.WriteElementString(“Author”, “Rofans Manao”);
xtwWriter.WriteElementString(“LastAuthor”, “Rofans Manao”);
xtwWriter.WriteElementString(“Created”, DateTime.Now.ToString(“u”) + “Z”);
xtwWriter.WriteElementString(“Company”, “Siemens Malaysia Sdn Bhd”);
xtwWriter.WriteElementString(“Version”, “12″);

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteStartElement(“ExcelWorkbook”, “urn:schemas-microsoft-com:office:excel”);

// Write settings of workbook
xtwWriter.WriteElementString(“WindowHeight”, “8010″);
xtwWriter.WriteElementString(“WindowWidth”, “14805″);
xtwWriter.WriteElementString(“WindowTopX”, “240″);
xtwWriter.WriteElementString(“WindowTopY”, “105″);
xtwWriter.WriteElementString(“ProtectStructure”, “False”);
xtwWriter.WriteElementString(“ProtectWindows”, “False”);

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteStartElement(“Styles”);

//
xtwWriter.WriteStartElement(“Style”);
xtwWriter.WriteAttributeString(“ss”, “ID”, null, “Default”);
xtwWriter.WriteAttributeString(“ss”, “Name”, null, “Normal”);

//
xtwWriter.WriteStartElement(“Alignment”);
xtwWriter.WriteAttributeString(“ss”, “Vertical”, null, “Bottom”);
xtwWriter.WriteEndElement();

// Write null on the other properties
xtwWriter.WriteElementString(“Borders”, null);
xtwWriter.WriteElementString(“Font”, null);
xtwWriter.WriteElementString(“Interior”, null);
xtwWriter.WriteElementString(“NumberFormat”, null);
xtwWriter.WriteElementString(“Protection”, null);

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteStartElement(“Style”);
xtwWriter.WriteAttributeString(“ss”, “ID”, null, “s16″);
xtwWriter.WriteStartElement(“Font”);
xtwWriter.WriteAttributeString(“ss”, “Bold”, null, “1″);
xtwWriter.WriteAttributeString(“ss”, “Size”, null, “11″);
xtwWriter.WriteAttributeString(“ss”, “Underline”, null, “Single”);
xtwWriter.WriteEndElement();

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteStartElement(“Worksheet”);
xtwWriter.WriteAttributeString(“ss”, “Name”, null, dtSource.TableName);

//
xtwWriter.WriteStartElement(“Table”);
xtwWriter.WriteAttributeString(“ss”, “ExpandedColumnCount”, null, dtSource.Columns.Count.ToString());
xtwWriter.WriteAttributeString(“ss”, “ExpandedRowCount”, null, (dtSource.Rows.Count + 1).ToString());
xtwWriter.WriteAttributeString(“x”, “FullColumns”, null, “1″);
xtwWriter.WriteAttributeString(“x”, “FullRows”, null, “1″);
//xtwWriter.WriteAttributeString(“ss”, “DefaultColumnWidth”, null, “60″);

// Run through all rows of data source

//
xtwWriter.WriteStartElement(“Row”);

foreach (DataColumn Header in dtSource.Columns)

{

//
xtwWriter.WriteStartElement(“Cell”);
xtwWriter.WriteAttributeString(“ss”, “StyleID”, null, “s16″);

// xxx
xtwWriter.WriteStartElement(“Data”);
xtwWriter.WriteAttributeString(“ss”, “Type”, null, “String”);
// Write content of cell
xtwWriter.WriteValue(Header.ColumnName);

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteEndElement();

}

xtwWriter.WriteEndElement();

foreach (DataRow row in dtSource.Rows)

{

//
xtwWriter.WriteStartElement(“Row”);

// Run through all cell of current rows

foreach (object cellValue in row.ItemArray)

{

//
xtwWriter.WriteStartElement(“Cell”);
//if (cnt == 0)
// xtwWriter.WriteAttributeString(“ss”, “StyleID”, null, “s16″);

// xxx
xtwWriter.WriteStartElement(“Data”);
xtwWriter.WriteAttributeString(“ss”, “Type”, null, “String”);
// Write content of cell
string strcellValue = (cellValue == System.DBNull.Value ? string.Empty : (string)cellValue);
xtwWriter.WriteValue(strcellValue);

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteEndElement();

}

//
xtwWriter.WriteEndElement();

}

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteStartElement(“WorksheetOptions”, “urn:schemas-microsoft-com:office:excel”);

// Write settings of page
xtwWriter.WriteStartElement(“PageSetup”);
xtwWriter.WriteStartElement(“Header”);
xtwWriter.WriteAttributeString(“x”, “Margin”, null, “0.4921259845″);
xtwWriter.WriteEndElement();
xtwWriter.WriteStartElement(“Footer”);
xtwWriter.WriteAttributeString(“x”, “Margin”, null, “0.4921259845″);
xtwWriter.WriteEndElement();
xtwWriter.WriteStartElement(“PageMargins”);
xtwWriter.WriteAttributeString(“x”, “Bottom”, null, “0.984251969″);
xtwWriter.WriteAttributeString(“x”, “Left”, null, “0.78740157499999996″);
xtwWriter.WriteAttributeString(“x”, “Right”, null, “0.78740157499999996″);
xtwWriter.WriteAttributeString(“x”, “Top”, null, “0.984251969″);
xtwWriter.WriteEndElement();
xtwWriter.WriteEndElement();

//
xtwWriter.WriteElementString(“Selected”, null);

//
xtwWriter.WriteStartElement(“Panes”);

//
xtwWriter.WriteStartElement(“Pane”);

// Write settings of active field
xtwWriter.WriteElementString(“Number”, “1″);
xtwWriter.WriteElementString(“ActiveRow”, “1″);
xtwWriter.WriteElementString(“ActiveCol”, “1″);

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteEndElement();

// False
xtwWriter.WriteElementString(“ProtectObjects”, “False”);

// False
xtwWriter.WriteElementString(“ProtectScenarios”, “False”);

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteEndElement();

//
xtwWriter.WriteEndElement();

// Write file on hard disk
xtwWriter.Flush();
xtwWriter.Close();

}

}

}

To use: ExcelHelper.Instance.Create(DataTable,strFileName). This method is perfect. Only one problem it’s caused. It doesn’t use objects instantiated from any System.IO classes. So when you execute this method, there is no popup asking you where to save downloaded .xls file as the first method produced. What it will do (this really scares me) it will write straight away your .xls file in the directory you defined. Off course in normal environment this will result in no problem. But imagine the situation where you don’t have admin rights or you have to store your files in mainframe/server. You basically get my point right?

Using Interop Objects

//Make sure you add this reference and have it imported

Using Excel = Microsoft.Office.Interop.Excel;

protected void xlsWorkBook()
{

Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
Excel.Range oRange;

// Start Excel and get Application object.
oXL = new Excel.Application();

// Set some properties
oXL.Visible = true;
oXL.DisplayAlerts = false;

// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);

// Get the active sheet
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oSheet.Name = “Customers”;

// Process the DataTable
// BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
//DataTable dt = Customers.RetrieveAsDataTable();//commented

DataTable dt = Table;//added
Session["dt"] = dt;//added

int rowCount = 1;

foreach (DataRow dr in dt.Rows)

{

rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)

{

// Add the header the first time through
if (rowCount == 2)

{

oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;

}

oSheet.Cells[rowCount, i] = dr[i - 1].ToString();

}

}

// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[rowCount, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();

// Save the sheet and close
oSheet = null;
oRange = null;
oWB.SaveAs(“test.xls”, Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

}

This method is good enough. So far, I found no problem. There is potential memory leak if you process giant files, but so far I haven’t found any.

Well I hope these helps. Let me know if you find any problems with this code.

Even though ASP.NET is the next version of ASP, it is more than just a next version. ASP.NET is completely re-designed from the ground up and it provides a neat object oriented programming model. An ASP.NET page is an object derived from the .NET Page class. We all know that ASP.NET provides several new features and one of the important features is the separation of code and content.

In today’s ASP application, we have a mix of HTML and Scripts making the code difficult to read, debug and maintain. ASP.NET relieves us from this problem by promoting the separation of code and content. That is, the user interface and the user interface programming logic need not necessarily be written in a single page. There are two ways in which you can separate the code and content:

1.) By using Code-behind files that are pre-compiled modules written in any of the Microsoft .NET runtime compliant languages.
2.) By developing the frequently used page logic in to separate files called Pagelets (also known as page controls) and by using them several times in your ASP.NET pages.

As the title of the article indicates, I will explain the first mechanism that is used to neatly encapsulate the UI functionality in to a separate pre-compiled module. You will typically follow the steps below to develop an ASP.NET page that uses code-behind pages:

1.) Create the User Interface using the HTML and/or Web controls and store them in an ASP.NET page with extension “.aspx”.
2.) Create the code-behind file using the .NET runtime classes that mimic the entire ASP.NET page with the member variables that correspond to the HTML and/or Web controls used in the ASP.NET page. The extension of code behind files will vary depending on the language you use. Typical extensions will be .cs for C#, .vb for VB7 and .js for JScript .NET

Now, we know that there are two separate files: one for the UI and the other for the UI logic. But, how do we relate these two files so that the ASP.NET page compiler will locate the code-behind file for the ASP.NET page. The glue between these two files is provided through the Page Directives, which are used to specify optional settings at the page level.

Creating the “Content”
Enough theory said about code-behind files. Let us get our hands on to the actual coding of a code-behind file. The ASP.NET page that displays the Web UI can be seen below. It uses a post-back form to query the user for username and information:

<%@ Page language=”c#” Codebehind=”CodeBehind.cs” Inherits=”ASPPlus.CodeBehind” %>

<HTML>

<BODY>

<form action=”CodeBehind.aspx” method=post runat=”server

<p align=center>Demonstration of Code Behind File</P>

<p align=center><asp:Label id=Message runat=”server”></asp:Label></P>

<p>

<h5>Sign-In Form</h5>

Login: <asp:TextBox id=txtLogin Width=200 Runat=server />

<asp:RequiredFieldValidator ControlToValidate=”txtLogic”

Display=”Static” Font-Name=”verdana,arial,sans-serif”

Font-Size=”9pt” ErrorMessage=”You must enter a Login.”

runat=”server” ID=”RFVLogin” />

<P>

Password: <asp:TextBox id=txtPassword Width=200 Runat=server

TextMode=”Password” />

<asp:RequiredFieldValidator ControlToValidate=”txtPassword”

Display=”Static” Font-Name=”verdana,arial,sans-serif”

Font-Size=”9pt” ErrorMessage=”You must enter a password.”

runat=server ID=”RFVPassword”> </asp:RequiredFieldValidator>

<P>

<asp:Button id=btnSignIn Runat=”server”

Text=”Sign In” onClick=”btnSignIn_Click” />

</FORM>

</body>

</html>

The first line of the ASP.NET page is the page directive that specifies the name of the code behind file and the actual class name inside that file. In our example, the file name is CodeBehind.cs and the class name is ASPPlus.CodeBehind.

<%@ Page language=”c#” Codebehind=”CodeBehind.cs” Inherits=”ASPPlus.CodeBehind” %>

The namespace that I used for this code behind page is ASPPlus. Namespaces provide the naming scope for the classes and is very convenient way to represent hierarchy of classes. The rest of the code consists of HTML tags and Web Control declaration tags. The main thing to note down in this page is the ID properties of the controls. I will explain the importance of these IDs when we discuss the code-behind file.

Read more: http://www.4guysfromrolla.com/webtech/100500-1.2.shtml 

Last time around we installed all the prerequisites and finally even ASP.NET. Now that that’s done, you can start writing ASP.NET pages of your very own. This time around, we walk you through your first one.

Hello World

Way back when programming was new, someone decided that the first thing you should do whenever you start programming in a new environment or language is print out “Hello World.” I have no idea who it was that came up with this idea or why it stuck, but it has. Not being one to fight the system without reason, we’ll be using this incredibly simple idea as the basis for our first ASP.NET page.

Hello World in HTML

For your reference, I’m going to start with a sample of what we’re trying to accomplish done completely in static HTML:

<head>

<title>ASP.NET Hello World</title>

</head>

<body bgcolor=”#FFFFFF”>

<p>Hello World!</p>

Ok so that’s the goal… now let’s try it in ASP.NET.
Hello World in ASP.NET
The Easy Way – Cheating!

Fundamentally, there’s no requirement that an ASP.NET page (sometimes referred to as a Web Form) actually do any processing. As such, the simplest way to get the task at hand accomplished is to take the existing HTML page listed above and simply give it an aspx extension. This results in a perfectly legal and acceptable ASP.NET page. The only thing that happens when you do this is that you tell the web server to pass the aspx file through the ASP.NET runtime, which in turn compiles it and processes any code it finds (in this case none) before it returns the result to the client.
Note: I’ll be highlighting changes from the previous version of the code in red for emphasis and quickly mentioning them after each new listing.

<head>

<title>ASP.NET Hello World</title>

</head>

<body bgcolor=”#FFFFFF”>

<p>Hello World!</p>

The only thing that changed here is the filename. If the ASP.NET runtime doesn’t find any code it understands, it just leaves everything alone and passes it out to the browser.

While that is perfectly legal and runs, it doesn’t really get too much accomplished. In this scenario that’s fine, but that’s not usually the case so now we’ll move on to a version that actually does some processing.
The Classic ASP Way

One of the nice things about ASP.NET is that it will work with a lot of classic ASP style code with only minor changes. Anyone who is familiar with ASP should have no problem making out what is going on in the code listing below. Besides the use of VB instead of VBScript there’s really nothing to distinguish this from a classic ASP script.

<html>

<head>

<title>ASP.NET Hello World</title>

</head>

<body bgcolor=”#FFFFFF”>

<p><%= “Hello World!” %></p>

Read more: http://www.codeguru.com/csharp/.net/net_asp/tutorials/article.php/c19305/Writing-Your-First-ASPNET-Page.htm