-
How to export datatable to excel in asp.net?
almost 8 years ago
To export datatable in excel, First create an excel file at any location on my computer. In my example, I have created a blank excel file in D drive named as "DataTableToExcel.xls".
First, we will add some data in datatable, Either we can have static data or dynamic. We can bind our datatable to database.
To export datatable in excel we use StreamWriter Class for which "System.IO" namespace is used. This class contains all the methods to write text to file. So StreamWriter helps to implement a Textwriter for writing text to the file in particular file format.
Implementation: First you have to add an aspx page in your application and then add a button on this page.
Include the following code in your design part: It will add a button which will export data to excel sheet.
<!DOCTYPE html> <html> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button runat="server" ID="Btn_Export" Text="Export" OnClick="Btn_DtToExeclExport_Click" /> </div> </form> </body> </html>Now include the following code at code behind page(aspx.cs)
protected void Btn_DtToExeclExport_Click(object sender, EventArgs e) { //DataTable, Filename with full path ExportToExcel(BindDatatable(), "D:\\DataTableToExcel.xls"); } protected DataTable BindDatatable() { DataTable dt = new DataTable(); dt.Columns.Add("UserId", typeof(Int32)); dt.Columns.Add("UserName", typeof(string)); dt.Columns.Add("EmailId", typeof(string)); dt.Columns.Add("UserMobile", typeof(string)); dt.Rows.Add(1, "shikha", "shikha.evon@gmail.com", "9876556523"); dt.Rows.Add(2, "mayuri", "mayuri.evond@gmail.com", "9988774455"); dt.Rows.Add(3, "deepika", "deepika.evon@gmail.com", "9966563211"); dt.Rows.Add(4, "mona", "mona.evon@gmail.com", "9988995522"); return dt; } private void ExportToExcel(DataTable table, string filePath) { StreamWriter Strwriter = new StreamWriter(filePath, false); Strwriter.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">"); Strwriter.Write("<font style='font-size:15.0pt; font-family:TimesNewRoman;'>"); Strwriter.Write("<BR><BR><BR>"); Strwriter.Write("<Table border='2' bgColor='#ffffff' borderColor='#000000' cellSpacing='0' cellPadding='0' style='font-size:15.0pt; font-family:TimesNewRoman; background:white;'> <TR>"); int dtcolumncount = table.Columns.Count; for (int j = 0; j < dtcolumncount; j++) { Strwriter.Write("<Td>"); Strwriter.Write("<B>"); Strwriter.Write(table.Columns[j].ToString()); Strwriter.Write("</B>"); Strwriter.Write("</Td>"); } Strwriter.Write("</TR>"); foreach (DataRow row in table.Rows) { Strwriter.Write("<TR>"); for (int i = 0; i < table.Columns.Count; i++) { Strwriter.Write("<Td>"); Strwriter.Write(row[i].ToString()); Strwriter.Write("</Td>"); } Strwriter.Write("</TR>"); } Strwriter.Write("</Table>"); Strwriter.Write("</font>"); Strwriter.Close(); }Debug the code and click on Export Button. When the program will be successfully executed then you can check your excel file at your given location. you will get all data table details in excel.
See Screenshot for reference:
0 Comment(s)