Excel in a .NET Application

It has been a really hard work week after too many hours of coding. Thankfully I can get a whole week rest after tomorrow.

Last week, after a meeting with my project manager and my users, I had been assigned to build an application which includes import/export data from/to an excel spreadsheet functionality.

VS .NET allows us to add Excel reference. Once Excel reference is added into an application, you can take control of every aspect of Excel by using a rich library of objects provided by Excel itself, e.g. add formatting, additional worksheets and so on.

Previously, I had written a few posts related with import and export data from/to excel file, and I will summarized all of them in this post by using C#.

Connection String
To read data from Excel file, you will need a connection string to connect to the Excel file, as following:

string sfile = "C:\Book1.xls";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sfile + ";" + "Extended Properties=Excel 8.0;";

sfile represents the excel file path that you wish to read from.

Reading Data from Excel into DataSet
Here is a sample of reading an Excel spreadsheet by using OleDb and DataSet.

System.Data.DataTable dbSchema = new System.Data.DataTable();
OleDbConnection conn;
OleDbDataAdapter da;
DataSet ds;

string sfile = "C:\Book1.xls";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sfile + ";" + "Extended Properties=Excel 8.0;";

conn = new OleDbConnection(strConn);
conn.Open();

// Get all sheetnames from an excel file into data table
dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dbSchema != null || dbSchema.Rows.Count > 0)
{
    // Loop through all worksheets
    for (int i = 0; i < dbSchema.Rows.Count; i++)
    {
        string sheetname = dbSchema.Rows[i]["TABLE_NAME"].ToString();

        da = new OleDbDataAdapter("SELECT * FROM [" + sheetname + "]", strConn);
        da.TableMappings.Add("Table", "dt_Excel");

        ds = new DataSet();
        da.Fill(ds);

        if (ds.Tables != null || ds.Tables[0].Rows.Count > 0)
        {
            // your codes here…
        }
    }
}

conn.Close();

Create an instance of Excel and Open a New Excel Spreadsheet
Excel.Application xlsApp = new Excel.ApplicationClass();
Excel.Workbook xlsWorkbook;
Excel.Worksheet xlsWorksheet;

// Create new workbook
xlsWorkbook = xlsApp.Workbooks.Add(true);

// Get the first worksheet
xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

// Activate current worksheet
xlsWorksheet.Activate();

Here, we have our first worksheet ready for insert data.

Write Table Headers and Data from DataSet into Excel
To add column headers into worksheet, we need to use Cells property of the worksheet to set a value into a specific row and column in the spreadsheet. The Cells property lets you treat both rows and columns as numbers start with index 1.

// Write Column Headers into first row of Excel Worksheet
xlsWorksheet.Cells[1, 1] = "Name";
xlsWorksheet.Cells[1, 2] = "NRIC";
xlsWorksheet.Cells[1, 3] = "Address";
xlsWorksheet.Cells[1, 4] = "MonthSalary";
xlsWorksheet.Cells[1, 5] = "YearSalary";

int row = 1;
// Write data to Excel worksheet from dataset
foreach (DataRow dr in dt.Rows)
{
row += 1;
xlsWorksheet.Cells[row, 1] = dr["Name"];
xlsWorksheet.Cells[row, 2] = dr["NRIC"];
xlsWorksheet.Cells[row, 3] = dr["Address"];
xlsWorksheet.Cells[row, 4] = dr["MonthSalary"];
xlsWorksheet.Cells[row, 5] = dr["YearSalary"];
}

Column and Row Format in Excel
Formatting spreadsheet is quite important in certain circumstances. Below I will show you a few formatting examples.

// Freeze the columm headers (first row)
xlsWorksheet.get_Range("A2", "E2").Select();
xlsApp.ActiveWindow.FreezePanes = true;

// Set the font name of the worksheet through the EntireColumn property of the cell
xlsWorksheet.get_Range("A1", "E1").Cells.EntireColumn.Font.Name = "Calibri";

// Bolding the entire row of header (first row) through the EntireRow property of the cell
xlsWorksheet.get_Range("A1", "E1").EntireRow.Font.Bold = true;

// Auto fit the entire worksheet
xlsWorksheet.get_Range("A1", "E1").EntireColumn.AutoFit();

// Set column of MonthSalary and YearSalary to 2 decimal positions
xlsWorksheet.get_Range("D2", "E2").Cells.EntireColumn.NumberFormat = "##0.00";
// or for date format -> …Cells.EntireColumn.NumberFormat = "dd-mmm-yy";

Clean Up Excel Objects
// Save the file and clean up all Excel object
string filename = "C:\test1.xls";
xlsApp.DisplayAlerts = false;
xlsWorkbook.Close(true, filename, null);
xlsApp.Quit();
xlsWorksheet = null;
xlsWorkbook = null;
xlsApp = null;

Hope this can help you. Cheers!


Other related posts:
1. Export Data to Excel (Not HTML Tables)
2. Import data from CSV file into data set
3. Import Data from MS Access to Excel File
4. Import Data from Excel File (.cont)
5. A Simple Solution for Export ASP .NET Data Grid to Excel
6. How to Import Data from Excel File

ASP .NET DataGrid with fixed headers

When dealing with data grid in ASP .NET, a very common design request for it is having a data grid which has a horizontal scrollbar and fixed column header.

Adding a Scrollbar
A vertical scrollbar is very useful when your data grid contains of many rows that cannot fit into a web page. This can be done by using DIV tag. Enclose your data grid into a <div> tag and set the overflow style to scroll or auto. Look at the example as below:

<DIV id=”div-datagrid” style=”OVERFLOW: auto;”>
    <asp:datagrid runat=server….> </asp:datagrid>
</DIV>

Fixed Header
A fixed header will stick on the top of your data grid and never scroll out of view, in this case, users will not get confuse which column represent which field.

To achieve this, add a style as below in your CSS file.

<style type="text/css">
<!--
.DataGridFixedHeader {
    FONT: menu;
    COLOR: black;
    background-color: #CCFFCC;
    position:relative;
    top:expression(this.offsetParent.scrollTop);
}
-->
</style>

Add this CSS class in your HeaderStyle’s CssClass. And now you can see the header is always on the top of your data grid.

But if the header moves a little bit down when you scroll the data grid (which will show something (text or line) on the top of the header), you can add “-2” after the scrollTop.

position:relative;
top:expression(this.offsetParent.scrollTop-2);

But this only works fine in IE but not Firefox. So if you are using Firefox, you have to find another way to implement it.

Hope you will find this helpful.

DoCmd.HourGlass Method in MS Access

In MS Access, when a macro action takes a long time to run, you can use DoCmd.HourGlass to change your mouse pointer to an image of an hourglass or any icon that you like, by using the code as below:

DoCmd.HourGlass True

or

DoCmd.HourGlass False

It has the following argument: True/False, or 0/-1.

Export Data to Excel (Not HTML Tables)

Recently I working with export data to an excel file, but had faced a lot of problems. The main problem of it: The exported file is saved as HTML tables with XLS extension, not the actual XLS format. In this way, I can’t re-import the data using the OleDbConnection.

I have been searching around the net, but no exact solution that suit my situation. Finally, I had found another way to export data into the actual XLS format, by using Excel.Application.

First thing that you need to do is to add Excel dll (Microsoft Excel 11.0 Object Library) as a reference.

Here are the codes.

Private Function ExportToExcel(ByVal dt As System.Data.DataTable)
    Dim xlsApp As New Excel.Application
    Dim xlsWorkbook As Excel.Workbook
    Dim xlsWorksheets As Excel.Worksheets
    Dim xlsWorksheet As Excel.Worksheet
    Dim strhdr As String
    Dim row As Integer
    Dim drow As DataRow
    Dim strFile As String = "test.xls"
    Dim filename As String = Server.MapPath("Doc") & "\" & strFile

    If dt.Rows.Count > 0 Then
        'Create new workbook
        xlsWorkbook =
        xlsApp.Workbooks.Add

        'Get the first worksheet
        xlsWorksheet = CType(xlsWorkbook.Worksheets(1), Excel.Worksheet)

        'Activate current worksheet
        xlsWorksheet.Activate()

        'Set header row to row 1
        row = 1

        'Add table headers to worksheet
        xlsWorksheet.Cells(row,1).Value = "NAME"
        xlsWorksheet.Cells(row, 2).Value = "JOB POSITION"
        xlsWorksheet.Cells(row, 3).Value = "ORIGIN"

        'Format header row (bold, extra row height, autofit
width)
        With xlsWorksheet.Range("A" & row, "C" & row)
            .Font.Bold = True
            .Rows(row).RowHeight = 1.5 * xlsWorksheet.StandardHeight
            .EntireRow.AutoFit()
        End With

        'Freeze the column headers
        With xlsWorksheet.Range("A" & row + 1, "C" & row + 1).Select
            xlsApp.ActiveWindow.FreezePanes = True
        End With

        'Write data to Excel worksheet
        For Each drow In dt.Rows
            row += 1
            If Not IsDBNull(dr.Item("NAME")) Then xlsWorksheet.Cells(row, 1).Value = dr.Item("NAME")
            If Not IsDBNull(dr.Item("JOB POSITION")) Then xlsWorksheet.Cells(row, 2).Value = dr.Item("JOB POSITION")
            If Not IsDBNull(dr.Item("ORIGIN")) Then xlsWorksheet.Cells(row, 3).Value =
dr.Item("ORIGIN")
        Next

        'Format data rows (align to top, autofit width and height)
        With xlsWorksheet.Range("A2", "C" & row)
            .VerticalAlignment = CType(XlVAlign.xlVAlignCenter, Excel.XlVAlign)
            .HorizontalAlignment = CType(XlHAlign.xlHAlignLeft, Excel.XlHAlign)
            .EntireColumn.AutoFit()
            .EntireRow.AutoFit()
        End With

        'Make excel workbook visible to user after all data has been added to worksheet
        xlsApp.DisplayAlerts = False
        xlsWorkbook.Close(True, filename)

        'Export data to client machine
        strhdr = "attachment;filename=" & strFile
        With Response
            .Clear()
            .ContentType = "application/vnd.ms-excel"
            .ContentEncoding = System.Text.Encoding.Default
            .AppendHeader("Content-Disposition", strhdr)
            .WriteFile(filename)
            .Flush()
            .Clear()
            .Close()
        End With
    End If
End Function

If you got any question, feel free to ask me.


Update on 07 Aug 2008:
For C# .NET example, please refer to Export Data to Excel (Not HTML Table) in C# .NET

Simple error log files in ASP .NET

In some circumstances, you may not be able to debug your web application to determine what exception had occurred in it. So to create an error log file is very important to keep track of the occurred exception.

In this post, I will show you how to write an error log files by using a class file (.cs).

First of all, a class called CreateLogFiles needs to be created. In this class file, add in the codes as following:

using System;
using System.IO;
using System.Text;

namespace CSharpWeb
{
    public class CreateLogFiles
    {
        private string sFormat;
        private string sTime;

        public CreateLogFiles()
        {
            sFormat = DateTime.Now.ToShortDateString().ToString()+" "+DateTime.Now.ToLongTimeString().ToString()+" --- ";
            sTime = DateTime.Now.ToString("yyyyMMdd");
        }

         public void CreateErrorLog(string strPath, string sErr)
        {
            StreamWriter sw = new StreamWriter(strPath+sTime+".txt",true);
            sw.WriteLine(sFormat + sErr);
            sw.Flush();
            sw.Close();
         }
     }
}

In your own web page, you can use this class to create error log files, namely whenever an exception is being caught, it will create an error log file with the exception error message.

The code below is an example of mine, which I will call the CreateErrorLog function when a Checking button is clicked.

private void btnCheck_Click(object sender, System.EventArgs e)
{
    try
    {
        // ...The rest of your codes here
    }
    catch(Exception ex)
    {
         CreateLogFiles Error = new CreateLogFiles();
         Error.CreateErrorLog(Server.MapPath("Logs/ErrorLog"), ex.Message);
    }
}

Fro the codes as above, I am using Try…Catch method. Whenever an exception occurs, it will get caught by the Exception method. In there, an error log files will be created and saved as a keep track.

That’s it. If you have any curious feel free to ask me. Cheers！

Can I use .NET Framework 2.0 from VS .NET 2003?

Currently I am working with VS .NET 2003 with .NET framework 1.1. But recently I found out that some features that only available in .NET framework 2.0 is needed in my project.

I had downloaded the .NET framework 2.0 package and think of want to use it within the VS .NET IDE. Problem arose. I do not know how I can achieve to it. And after a few searches on net, I found out that each release of Visual Studio is only tied to a specific version of the .NET framework.

I found the following comment on the VS 2005 Express FAQ:

Can I develop application using the Visual Studio Express Editions to target the .NET Framework 1.1?
No, each release of Visual Studio is tied to a specific version of the .NET Framework. The Express Edition can only be used to create applications that run on the .NET Framework 2.0

VS .NET 2002 => .NET 1.0
VS .NET 2003 => .NET 1.1
VS .NET 2005 => .NET2.0
VS .NET 2007 (codenamed Orcas) => .NET3.0

Sending mail in ASP .NET

Sending e-mails from a website is very common nowadays, especially while registering on a website, an automated e-mail will be sent to your mailbox for verification and confirmation purpose.

ASP .NET simplified the work of developers with the introduction of a namespace called System.Web.Mail. By using this namespace, developers can easily sending a plain text mail, HTML mail, or sending attachments.

Sending Plain Text Mail
Let’s start with a simple plain text mail first. See the codes below.

using System.Web.Mail;
.
.
.
string To = "youremailaddress@yahoo.com";
string From = "webmail@mail.com";
string Subject = "Hi";
string Body = "Hello World!";

SmtpMail.Send(From, To, Subject, Body);

The output of this will look like the screenshot below.


This is very simple since it only uses SmtpMail.Send method in the System.Web.Mail namespace. SmtpMail.Send method contains 4 parameters: From, To, Subject and BodyText.

Sending HTML Mail
In sending a HTML mail, we need to use MailMessage class, which provides a lot of useful properties. Here is a list of the properties:

1. Attachments - Used for sending e-mails with attachments
2. From - Sender’s email address
3. To - Recipient’s email address
4. Cc - Recipient’s email address (Carbon Copy)
5. Bcc - Recipient’s email address (Blind Carbon Copy)
6. Body - Text of the email address
7. BodyFormat - Format of an email (Text or HTML)
8. Priority - Priority of an email (High, Low, or normal)
9. Subject - Title of an email
10. Headers - Denotes a collection of acceptable headers (E.g. Reply-To)
11. BodyEncoding - Method of encoding an e-mail message (Base64 or UUEncode)

Below is a sample code for sending a simple HTML mail.

using System.Web.Mail;
.
.
.
MailMessage mail = new MailMessage();

mail.To = "youremailaddress@yahoo.com";
mail.Cc = "ccemailaddress@yahoo.com";
mail.From = "webmail@mail.com";
mail.Subject = "Hi, another mail from me";
mail.BodyFormat = MailFormat.Html;

string strBody = "<html><body><b>Hello World</b><br>" +
" <font color=\"red\">A HTML mail</font></body></html>";
mail.Body = strBody;

SmtpMail.Send(mail);

The output of this will look like the screenshot below.


Sending Attachments
As you already seen in the list of MailMessage properties, by sending an attachment, you can use the Attachments property. See the codes below.

using System.Web.Mail;
.
.
.
MailMessage mail = new MailMessage();

mail.To = "youremailaddress@yahoo.com";
mail.From = "webmail@mail.com";
mail.Subject = "Attachment from me";

mail.BodyFormat = MailFormat.Text;
mail.Body = "There is an attachment!";
mail.Attachments.Add(new MailAttachment("c:\\Test Message.doc"));

SmtpMail.Send(mail);

The output of this will look like the screenshot below.

SQL Server function for datetime - DATEADD

DATEADD is a date function that will return a datetime value based on the number interval add to the particular date part of the specified date. Here is the syntax for DATEADD:

DATEADD(date_part, number_interval, specified_date)

date_part is the parameter that specifies on which part of the date to be manipulated with the number interval. You can add month, year, day and etc. You can use

MONTH, MM or M for month
YEAR, YYYY, YY for year
DAY, DD, D for day
HH for hour
SS, S for Second

For example :
SELECT DATEADD(D, -1, GETDATE())AS [Yesterday]
SELECT DATEADD(MM, 3, GETDATE())AS [FourMonthsFromNow]
SELECT DATEADD(YEAR, -2, GETDATE())AS [TwoYearsAgo]

How to use the LIKE clause in SQL Query in MS Access

This article will show you how to use the LIKE clause in SQL to search all the related records in a table that begin with, end with or contain a certain letter.

Normally for LIKE statement, the wildcard character is “%”, but please take note that, in MS Access, you have to use the wildcard “*” instead of “%”, else it is not working.

Here are some examples using the LIKE clause:

LIKE [First character that you want to search] & “*”
LIKE “*” & [Certain character that you want to search] & “*”

Stored Procedures in MySQL 5.0

Some people resisted to use MySQL because it had no support for stored procedures. But with MySQL 5.0, it has its new functionality for Stored Procedures. What are stored procedures actually? They are collections of SQL query and procedures that stored and run on the database server.

Before MySQL provides this new feature, MySQL developers need to store their procedures on the application, there hasn’t been an option anyway. And now, this new feature is available on version 5.0, and I am going to discuss why we would want to use stored procedures and place logic on the database server, instead of just put the procedures on the application.


Why use Stored Procedures?
§ They can run in all environments. Since stored procedures are stored in database server, so it doesn’t matter what application environment is used – the logic just remains in one place.

§ Less network traffic. For complex application, it may require complex queries or looped queries to get the desired result; it may generate more network traffic than it should be. By using Stored Procedures, they can cut down the unnecessary long queries being sent back and forth from application and database server, since they are done on the database server.

§ You can fix problems in your application by simply edit the related stored procedures on your database server, instead of changing your application code.

§ Speed of execution. Data manipulation that is done directly on the database server is faster than your application to handle it.

Page.RegisterHiddenField Method

This article explains the Page.RegisterHiddenField Method.

From MSDN library’s explanation, Page.RegisterHiddenField method allows server controls to automatically register a hidden field on the form. The field will be sent to the page when the HTML Form servers control is rendered.

This method takes 2 parameters:
hiddenFieldName – The unique name of the hidden field to be rendered
hiddenFieldInitialValue – The value to be emitted in the hidden form

Please take note that if you use this method twice with the same id with different value in your page, it will only register the first one.

For example:
Page.RegisterHiddenField(“field_1”, “value_1”);
Page.RegisterHiddenField(“field_1”, “value_2”);

Value_1 will be rendered while the second call is ignored.

On postback, you can use Request. Form(“field_1”) to get the data of the string result.

Cheers!