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:
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:
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)
{
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>";
System.IO.StreamWriter file = new System.IO.StreamWriter("c:\\test.xls");
file.WriteLine(HtmlStream);
file.Close();
Process.Start("c:\\test.xls");
}
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:
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%'>";
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>";
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>";
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%'>";
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>";
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>";
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)
{
DataSet Records = new DataSet();
Records.ReadXml(Server.MapPath("books.xml"));
gvRecords.DataSource = Records;
gvRecords.DataBind();
excl.CreateExcelSheet(this.gvRecords, "C:\\Test.xls");
}
| |
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 14th December, 2011
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
