Export DataTable to Excel Using HTML Text in C#

Introduction

We have various ways to export a DataTable to Excel. The following are common ways:

Export to Excel using Interop objects
Export to Excel by changing the content type of the HTTP response (applicable in web only)
Creating CSV or TSV (comma separated or tab separated file )

At this time I want to introduce an alternative way to create an Excel file for a required DataTable.

Let us first discuss some basics of how to export HTML text to an Excel file.

Please use the following procedure to understand this technique.

1. Create sample HTML file

<html>  
<table bgcolor='lightgrey' color='Darkblue' border='1px' style="font-family:Calibri">  
    <tr>   
        <td> Col 1 </td>  
        <td> Col 2 </td>  
       <td> Col 3 </td>  
       <td> Col 4 </td>  
       <td> Col 5 </td>  
       <td> Col 6 </td>  
</tr>    
<tr>   
    <td> MANJU GUPTA </td>  
    <td> DEVESH  </td>  
    <td> Manish</td>  
    <td> KB GUPTA </td>  
    <td> MONA</td>  
    <td> Adhyayan </td>  
</tr>  
<tr>   
    <td> ROLI GUPTA </td>  
    <td> ROLI GUPTA </td>  
    <td> ROLI GUPTA</td>  
    <td> ROLI GUPTA </td>  
    <td> MONA</td>  
   <td> Adhyayan </td>  
</tr>  
</table>  
</html>

Output

Here we can see the HTML in the web browser.

Now we will open this file in Excel.

Export DataTable to Excel Using HTML Text in C#

The following will be the screen if we open the HTML file in Excel.

Export DataTable to Excel Using HTML Text in C#

Here we created a HTML file and opened it in an Excel file. So the table row and table cell of the HTML file fits into rows and cells of the Excel file with proper formatting and borders and background color and so on.

So we will create HTML text for the data table and then save it as a .xls file, it would work as expected.

We will use the following approach to export the DataTable to Excel.

This code can be used for reporting purposes where sometimes the client needs to output the data in HTML/Excel format.

A. Binding DataGridView using DataTable

Procedure

Create DataTable and Define Columns

DataTable table = new DataTable();  
table.Columns.Add("ID", typeof(int));  
table.Columns.Add("NAME", typeof(string));  
table.Columns.Add("CITY", typeof(string));

Add Rows

table.Rows.Add(111, "Devesh", "Ghaziabad");  
table.Rows.Add(222, "ROLI", "KANPUR");  
table.Rows.Add(102, "ROLI", "MAINPURI");  
table.Rows.Add(212, "DEVESH", "KANPUR");

Bind DataGridView
```
dataGridView1.DataSource=table; 
```
Running the code the following will be the screen.

B. Exporting DataTable to HTML.

I have written generic code that could create HTML text for every DataTable.

You can use this code directly in your project for reporting purposes.

protected string ExportDatatableToHtml(DataTable dt)  
{  
    StringBuilder strHTMLBuilder = new StringBuilder();  
    strHTMLBuilder.Append("<html >");  
    strHTMLBuilder.Append("<head>");  
    strHTMLBuilder.Append("</head>");  
    strHTMLBuilder.Append("<body>");  
    strHTMLBuilder.Append("<table border='1px' cellpadding='1' cellspacing='1' bgcolor='lightyellow' style='font-   family:Garamond; font-size:smaller'>");  
    strHTMLBuilder.Append("<tr >");  
    foreach (DataColumn myColumn in dt.Columns)  
    {  
       strHTMLBuilder.Append("<td >");  
       strHTMLBuilder.Append(myColumn.ColumnName);  
       strHTMLBuilder.Append("</td>");  
    }  
    strHTMLBuilder.Append("</tr>");  
    foreach (DataRow myRow in dt.Rows)  
    {  
       strHTMLBuilder.Append("<tr >");  
       foreach (DataColumn myColumn in dt.Columns)  
       {  
          strHTMLBuilder.Append("<td >");  
          strHTMLBuilder.Append(myRow[myColumn.ColumnName].ToString());  
          strHTMLBuilder.Append("</td>");  
      }  
      strHTMLBuilder.Append("</tr>");  
   }  
   //Close tags.  
   strHTMLBuilder.Append("</table>");  
   strHTMLBuilder.Append("</body>");  
   strHTMLBuilder.Append("</html>");  
   string Htmltext = strHTMLBuilder.ToString();  
   return Htmltext;  
}

C. Understanding the code

We created a generic function that takes a DataTable as the parameter
We are using StringBuilder to create dynamic HTML text.
Here the output would contain an equal number of rows and columns as we have in the DataGridView.

Creating columns in HTML:

foreach (DataColumn myColumn in dt.Columns)  
{  
    strHTMLBuilder.Append("<td >");  
    strHTMLBuilder.Append(myColumn.ColumnName);  
    strHTMLBuilder.Append("</td>");  
}

Copy the data. The following code would create an equal number of rows as we have in the DataTable and copy the data to HTML rows.

foreach (DataRow myRow in dt.Rows)  
{  
    strHTMLBuilder.Append("<tr >");  
    foreach (DataColumn myColumn in dt.Columns)  
    {  
        strHTMLBuilder.Append("<td >");  
        strHTMLBuilder.Append(myRow[myColumn.ColumnName].ToString());  
        strHTMLBuilder.Append("</td>");  
    }  
    strHTMLBuilder.Append("</tr>");  
}

D. Output

After executing the code above we would get the following HTML:

<html>
    <head>
      </head>
        <body>
          <table border='1px' cellpadding='1' cellspacing='1' bgcolor='lightyellow' style='font-          family:Garamond; font-size:smaller'><tr ><td >ID</td><td >NAME</td><td >CITY</td></tr>
        <tr >
          <td >111</td>
          <td >Devesh</td>
          <td >Ghaziabad</td>
       </tr>
       <tr >
          <td >222</td>
          <td >ROLI</td>
          <td >KANPUR</td>
       </tr>
       <tr>
          <td>102</td>
          <td >ROLI</td>
          <td >MAINPURI</td>
      </tr>
      <tr>
        <td >212</td>
        <td >DEVESH</td>
        <td >KANPUR</td>
     </tr>
   </table>
  </body>
</html>

E. Creating HTML file

string HtmlBody = ExportDatatableToHtml(table)
System.IO.File.WriteAllText(@"c:\abc.xls", HtmlBody);

Important: We are saving the output file in .xls format and we have written HTML text to this Excel file.

HTML Output

Export DataTable to Excel Using HTML Text in C#

Excel Output

Export DataTable to Excel Using HTML Text in C#

Conclusion

We have learned how to create an export of a DataTable to Excel using HTML text.

References

Another approach to exporting DataTable to Excel is below:

Exporting DataTable to Excel in C# Using Interop

Export DataTable to Excel Using HTML Text in C#

Reactions

4 Comments