Export HTML Table to Excel using ASP.NET

Question

I have an html table (Not Gridview) and that has no proper Header and rows. Instead it has customized structure and data. I want to export this table to Excel. How can I do using ASP.NET? enter image description here

The labels are fixed text and the integer values are coming from database. So the table structure is fixed only the integer/decimal values change.

this has to be a repeatable process? or cut and paste from your web page once? — Randy, Feb 20 '12 at 16:00
Unlike someone else, I'll explain my down-vote: This is a poor question without enough detail to elicit an actual answer of any value. Provide more detail, or I suspect this question will be closed — Didaxis, Feb 20 '12 at 16:45

score 9 · Accepted Answer · edited Dec 21 '16 at 15:03

9

You want Export HTML table (Not Gridview) customized structure and data to Excel using ASP.NET.

Try the following Approach

Provide the ID and add runat="server" attribute

<table id="tbl" runat="server" >

Add the following code

Response.ContentType = "application/x-msexcel"; 
Response.AddHeader("Content-Disposition", "attachment;
filename=ExcelFile.xls");
Response.ContentEncoding = Encoding.UTF8; 
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
tbl.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();

edited Dec 21 '16 at 15:03

Davidm176

163
1
12

answered Mar 21 '12 at 18:42

Arun Singh

1,538
4
19
43

1

Very good. How if I want to put an image on the excel file, or fill color the cell, etc. What I want to say is, formatting the excel file after exported. – Haminteu Aug 08 '14 at 07:37
1

Check this http://stackoverflow.com/questions/7172282/give-background-color-to-excel-sheet-in-asp-net/9858841#9858841 and http://stackoverflow.com/questions/9410401/adding-custom-header-to-the-excel-file/9653194#9653194 – Arun Singh Aug 20 '14 at 09:22
1

I am trying to use the export function from a Visual Web Part in my SP site. `Response` is giving me an error. – SearchForKnowledge Jan 06 '15 at 16:54

score 4 · Answer 2 · answered Feb 18 '14 at 08:07

You can use below code:

Response.ContentType = "application/force-download";
Response.AddHeader("content-disposition", "attachment; filename=Print.xls");
Response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
Response.Write("<head>");
Response.Write("<META http-equiv=\"Content-Type\" content=\"text/html; charset=utf-     8\">");
Response.Write("<!--[if gte mso 9]><xml>");
Response.Write("<x:ExcelWorkbook>");
Response.Write("<x:ExcelWorksheets>");
Response.Write("<x:ExcelWorksheet>");
Response.Write("<x:Name>Report Data</x:Name>");
Response.Write("<x:WorksheetOptions>");
Response.Write("<x:Print>");
Response.Write("<x:ValidPrinterInfo/>");
Response.Write("</x:Print>");
Response.Write("</x:WorksheetOptions>");
Response.Write("</x:ExcelWorksheet>");
Response.Write("</x:ExcelWorksheets>");
Response.Write("</x:ExcelWorkbook>");
Response.Write("</xml>");
Response.Write("<![endif]--> ");
StringWriter tw = new StringWriter(); 
HtmlTextWriter hw = new HtmlTextWriter(tw);      
tbl.RenderControl(hw);
Response.Write(tw.ToString());
Response.Write("</head>");
Response.flush();

Also it is advicable to give inline css if you want the exported output look exactly same as your UI. If you apply css classes to the table then it would not be displayed in exported excel.

not necessarily you need to put inline CSS you can add the style classes in the head and it will work normally — Kronass, Feb 27 '14 at 06:41

score 1 · Answer 3 · answered Mar 21 '14 at 17:59

If dtReport contains the table(i.e data to be exported) then we can export the table to excel by using the following LOC and also we can format the header

    if (dtReports != null && dtReports.Rows.Count > 0 && !string.IsNullOrEmpty(formName))
            {
                string filename = formName.ToUpper() + ParsConstant.XLS_EXTENSION;
                StringWriter tw = new StringWriter();

                using (HtmlTextWriter hw = new HtmlTextWriter(tw))
                {

                    //Binding Datatable to DataGrid.
                    DataGrid dgGrid = new DataGrid();
                    dgGrid.DataSource = dtReports;
                    dgGrid.DataBind();

                    //Some Properties for the Header
                    dgGrid.HeaderStyle.Font.Bold = true;
                    dgGrid.HeaderStyle.Font.Size = 13;

                    //Get the HTML for the control.
                    dgGrid.RenderControl(hw);


                    Response.ContentType = "application/vnd.ms-excel";
                    Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
                    //Response.Write("<style> TD { mso-number-format:\\@; } </style>");


                    Response.Write(tw.ToString());
                    Response.End();
                }
            }

using MSO Format will not be avoiding leading zero's,but it will convert the text to string which is not advisable for doing operations.

Jonathan Wood · Answer 4 · 2012-02-20T16:55:20.343

0

There's no automated way. But you could use the same code to create the table and write it to the output instead. If you write it as a simple CSV file, then the user can load it into Excel by simply clicking the downloaded file.

By setting the correct headers, you can direct the browser to treat the content as a download instead of a web page. I posted the code to do this in this article.

edited Feb 20 '12 at 16:55

answered Feb 20 '12 at 16:22

Jonathan Wood

65,341
71
269
466

1

Who's the bozo voting down answers without an explanation? – Jonathan Wood Feb 20 '12 at 16:25
I'm not the down-voting "bozo" :), but perhaps your answer was down-voted because the OP explicitly states this is not in a GridView -nor has the OP hinted at where this data comes from. For all we know, this is a HTML table on a site the OP has no control over. – Didaxis Feb 20 '12 at 16:39
@ErOx: Yes, point taken. :) Since the OP said he's using ASP.NET, I'm going to assume he's doing this from his own site. But I agree there is ambiguity on this matter. – Jonathan Wood Feb 20 '12 at 16:56

Export HTML Table to Excel using ASP.NET

4 Answers4

Linked