Exporting Data From DataGridView or Datatable to ExcelSheet Using HTML

Arif-Imran

3.50/5 (3 votes)

Dec 21, 2011

CPOL

5 min read

36494

918

Exporting data to Excel is an every day requirement for many solution providers. This article shows how to migrate your data to Excel using simple HTML.

Download source - 7.1 KB

Introduction

Almost all financial and other discrete software require powerful reporting tools. While creating a reporting tool, data migration becomes a requirement for many developers everyday. This article introduces an easy technique to perform this migration using simple HTML. For those who are new to HTML, please take a quick tour at the following link:

HTML Tutorial

Background

Microsoft Excel is a versatile platform. Many companies feel comfortable when they manage their data in Excel although there are many other software which are far better, creating an Excel sheet through a web based or a desktop application becomes difficult when it comes to formatting the sheet like merging the cells, changing the cell background color, text size, family and font color of the cell. The solution this article proposes to this problem is to convert the entire data collection in a datatable / dataset or a Gridview into plain HTML stream and write this stream to a file and save it as (.XLS default Excel format). As a result, an Excel sheet is created. I will show how to use the following technique in the below article in two different ways:

Creating a customized Excel Sheet in a defined format.
Creating an Excel Sheet from a data table / datagrid view (Using "ExcelToExport.cs")

Creating a Simple Excel Sheet

I will start by creating a simple Excel sheet, for this you need to create a simple data entry form, mine looks like this:

Export Excel Page

User Interface of Default.aspx when viewed in local Browser

On the click event of the Create Excel Sheet button, but before that, you would need Sytem.Diagnostics namespace so add the following code at the start of the file:

using System.Diagnostics; 
 protected void Btn_Save_Click(object sender, EventArgs e)
    {
        // Creating an HTML Stream, this HTML stream will create the desired 
        // Excel sheet in an HTML format
        String HtmlStream;
        HtmlStream = "<html><body><style type='text/css'>.lblcaptions
    {    font: bold small Verdana;}.mainTable{    
    background: #F7F6F3;    color: #333333;}</style><table class='mainTable' 
    cellpadding='0' cellspacing='0' border='1' width='100%'>";
        HtmlStream += "<tr><td style='text-align:center; 
    background-color:Aqua;' colspan='4'><span class='lblcaptions'>
    CLIENT INFORMATION FORM</span></td></tr>";
        HtmlStream += "<tr><td style='text-align:center; 
    background-color:Aqua;' colspan='4'><span class='lblcaptions'>
    Kindly write / type in 'CAPITAL LETTERS'</span></td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>TCS ACCOUNT #</span></td>";
        HtmlStream += "<td style='text-align:left; width:75%;' colspan='3'>" + 
    txtAccountNumber.Text + "</td>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>TITLE OF ACCOUNT</span></td>";
        HtmlStream += "<td style='text-align:left; width:75%;' colspan='3'>" + 
    txtTitleOfAccount.Text + "</td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>BILLING ADDRESS</span><br /></td>
    <td style='text-align:left; width:75%;' colspan='3'>" + 
    txtbillingaddress.Text + "</td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>REGISTERED OFFICE ADDRESS</span><br /></td>
    <td style='text-align:left; width:75%;' colspan='3'>" + txtofficeadd.Text + 
    "<br /></td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>INDUSTRY</span><br /></td>
    <td style='text-align:left; width:75%;' colspan='3'>" + txtIndustry.Text + 
    "<br /></td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>CONTACT PERSON</span><br /></td>
    <td style='text-align:left; width:25%;'>" + txtcontact.Text + "<br /></td>
    <td style='text-align:left; width:25%;'><span class='lblcaptions'>
    DESIGNATION</span><br /></td><td style='text-align:left; width:25%;'>" + 
    txtDesigation.Text + "<br /></td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>MOBILE #</span><br /></td>
    <td style='text-align:left; width:75%;' colspan='3'>" + txtMobile.Text + 
    "<br /></td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>NTN #</span><br /></td>
    <td style='text-align:left; width:25%;'>" + txtNTN.Text + "<br /></td>
    <td style='text-align:left; width:25%;'><span class='lblcaptions'>NIC#</span>
    <br /></td><td style='text-align:left; width:25%;'>" + txtNIC.Text + 
    "<br /></td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>GST #</span><br /></td>
    <td style='text-align:left; width:75%;' colspan='3'>" + txtgst.Text + 
    "<br /></td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>TELEPHONE #</span><br /></td>
    <td style='text-align:left; width:75%;' colspan='3'>" + txttelephone.Text + 
    "<br /></td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>FAX #</span><br /></td><td style='text-align:left; 
    width:25%;'>" + txtfax.Text + "<br /></td><td style='text-align:left; 
    width:25%;'><span class='lblcaptions'>UAN #</span><br /></td>
    <td style='text-align:left; width:25%;'>" + txtuan.Text + "<br /></td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>EMAIL</span><br /></td>
    <td style='text-align:left; width:75%;' colspan='3'>" + 
    txtEmail.Text + "<br /></td></tr>";
        HtmlStream += "<tr><td style='text-align:left; width:25%;'>
    <span class='lblcaptions'>WEBSITE</span><br /></td>
    <td style='text-align:left; width:75%;' colspan='3'>" + txtwebsite.Text + 
    "<br /></td></tr>";
        HtmlStream += "<tr><td style=' background-color:Aqua;' colspan='4'><br />
    </td></tr>";
        HtmlStream += "</table></body></html>";
    // Creating a file  'test.XLS' as a sample excel sheet
    // You Might get any error regarding Access Path Denied, 
         // as some operating systems dont give access to C:\, so please change the path 
         // here instead of "c:\\test.xls"
    System.IO.StreamWriter file = new System.IO.StreamWriter("c:\\test.xls");    
    file.WriteLine(HtmlStream);// now writing the stream to the file    
    file.Close();// closing the streamwriter
    // Provide the actual path if not using this default path    
    Process.Start("c:\\test.xls");// Opening the excel sheet
    }

Here's a summary of the above code snippet:

Creating an string HTMLStream, this HTMLStream will contain HTML code of the desired Excel sheet.
This HTMLStream is the same as that in the Default.aspx mark-up code file but ASP TEXT BOXES are replaced by SPAN TAG's containing the text of the text boxes.
Creating a Stream Writer for an empty Excel file "test.xls".
Writing the HTMLStream to the file.
Closing the Stream Writer and Opening file recently created "test.xls".

Please follow the comments of the code for more explanation.

The end result will look like this:

Export Excel Page

Excel Sheet "test.xls" displays the Excel sheet created through the given code

By creating this Excel sheet, we managed to perform some simple formatting techniques like merging cell, splitting cells and changing background color through simple HTML hence the document looks attractive and clean and at the same time exporting our data. Now, let's jump to our next example.

Exporting GridView Data Collection or Datatable Data Collection to Excel Sheet

This example will show how to export DataGridView Data Collection or Datatable Data Collection to Excel Sheet. I have tried to make it very simple, for this purpose, I have created a class named as "ExportToExcel.cs". This class contains the code for our Excel exportation. The method CreateExcelSheet is overloaded so that it can be used for gridview and datatable.

public bool CreateExcelSheet(GridView GV, string path)
    {
        String HtmlStream;
        HtmlStream = "<html><body><style type='text/css'>.lblcaptions
        {    font: bold small Verdana;}.mainTable{    
    background: #F7F6F3;    color: #333333;}
        </style><table class='mainTable' width='100%'>";
        // Creating Header Row
        HtmlStream += "<tr>";
        for (int field = 0; field < GV.HeaderRow.Cells.Count; field++)
        {
            HtmlStream += "<td style='text-align:center;'><span class='lblcaptions'>" + 
            GV.HeaderRow.Cells[field].Text + "</span><br /></td>";
        }
        HtmlStream += "</tr>";
        //Creating DataRows
        foreach (GridViewRow row in GV.Rows)
        {
            HtmlStream += "<tr>";
            foreach (TableCell cell in row.Cells)
            {
                HtmlStream += "<td>"+cell.Text+"</td>";
            }
            HtmlStream += "</tr>";
        }        
        HtmlStream += "</table></body></html>";

        // Creating an XLS file and writing the HTML stream the file.
        System.IO.StreamWriter file = new System.IO.StreamWriter(path);
        file.WriteLine(HtmlStream);
        file.Close();
        return true;
    }

Here's a summary of the above code snippet:

The above method CreateExcelSheet takes a GRID VIEW object and a string as path of the Excel file desired to be created.
A String HTMLStream is created for the same purpose as in the previous example, intially head, body, style tags are added to the string.
The ASP GRID is converted into HTML TABLE Tag, by looping through the columns and rows to populate the <td> and <tr> tags.
In the end, the entire data collection is converted into <tr> and <td> tags.
Creating a Stream Writer for an empty Excel file whose path is provided as parameter.
Writing the HTMLStream to the file.
Closing the Stream Writer.

Please follow the comments of the code for more explanation.

public bool CreateExcelSheet(DataTable dt, string path)
    {
        String HtmlStream;
        HtmlStream = "<html><body><style type='text/css'>.lblcaptions
        {    font: bold small Verdana;}.mainTable{    background: #F7F6F3;    
        color: #333333;}</style><table class='mainTable' width='100%'>";
        // Creating Header Row
        HtmlStream += "<tr>";
        for (int field = 0; field < dt.Columns.Count; field++)
        {
            HtmlStream += "<td style='text-align:center;'>
            <span class='lblcaptions'>" + dt.Columns[field].ColumnName.ToString() + 
            "</span><br /></td>";
        }
        HtmlStream += "</tr>";
        //Creating DataRows
        foreach (DataRow row in dt.Rows)
        {
            HtmlStream += "<tr>";
            for (int field = 0; field < dt.Columns.Count; field++)
            {
                HtmlStream += "<td>" + row[field].ToString() + "</td>";
            }
            HtmlStream += "</tr>";
        }
        HtmlStream += "</table></body></html>";
        // Creating an XLS file and writing the HTML stream the file.
        System.IO.StreamWriter file = new System.IO.StreamWriter(path);
        file.WriteLine(HtmlStream);
        file.Close();
        Process.Start(path);
        return true;
    }
}

Here's a summary of the above code snippet:

The above method CreateExcelSheet takes a DATATBLE object and a string as path of the Excel file desired to be created.
A String HTMLStream is created for the same purpose as in the previous example, intially head, body, style tags are added to the string.
The DATABLE is converted into HTML TABLE Tag, by looping through the columns and rows to populate the <td> and <tr> tags.
In the end, the entire data collection is converted into <tr> and <td> tags.
Creating a Stream Writer for an empty Excel file whose path is provided as parameter.
Writing the HTMLStream to the file.
Closing the Stream Writer.

Please follow the comments of the code for more explanation.

Now all we need to do is just create an object of the class and call its relevant method.

(Note: Here I have popluated a gridview with a data collection of an XML File, and used "ExporttoExcel.cs" to create the Excel file.)

ExportToExcel excl = new ExportToExcel();
protected void Page_Load(object sender, EventArgs e)
{
// Creating a Dummy Dataset
DataSet Records = new DataSet();
// Filling the dataset with XML data in books.xml file
Records.ReadXml(Server.MapPath("books.xml"));
// binding the gridview with the dataset
gvRecords.DataSource = Records;
gvRecords.DataBind();
// creating an excel
// You Might get any error regarding Access Path Denied, 
// as some operating systems dont give access to C:\, 
// so please change the path here instead of "c:\\test.xls" 
excl.CreateExcelSheet(this.gvRecords, "C:\\Test.xls");
// This method can also be used like this excel.CreateExcelSheet
// (Records.Tables[0], "C:\\Test.xls") this is because the method has been overloaded
}


DataGridView holding record collection	Excel Sheet holding record collection as in GridView

Suggestions and recommendations are welcome. Please report any bugs if you find them, otherwise have fun.

Conclusion

The core concept which the above examples explain is that, if you want to export your data to Excel in a clean and attractive way, you have to create an HTML for that which is needed to view the same format in a local browser and write this HTML to an Excel file and you are done. For this very purpose, the class file "ExcelToExport.cs" can be used to convert the datatables and gridviews into Excel file without any hassle.

History

Original version submitted on 14^th December, 2011