Export Html Tables to Multiple Excel Sheet using C#

Question

How to export multiple tables to multiple excel sheet using c#? Below code is working but only for 1 html table.

    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();

Below are tables in *.aspx page. And i want to both tables in one excel with multiple sheet.

for Example : Table 1 is in worksheet 1 table 2 is in worksheet 2

  <table id="tbl" border="1" runat="server" >
                <tr>
                    <td>Product</td>
                    <td>Price</td>
                    <td>Available</td>
                    <td>Count</td>
                </tr>
                <tr>
                    <td>Bred</td>
                    <td>1
                    </td>
                    <td>2
                    </td>
                    <td>3
                    </td>
                </tr>
                <tr>
                    <td>Butter</td>
                    <td>4
                    </td>
                    <td>5
                    </td>
                    <td>6
                    </td>
                </tr>
            </table>

     <table id="tbl2" border="1" runat="server" >
                <tr>
                    <td>KD</td>
                    <td>Dabhi</td>
                    <td>Qnil</td>
                    <td>Dabhi</td>
                </tr>
                <tr>
                    <td>Bred</td>
                    <td>1
                    </td>
                    <td>2
                    </td>
                    <td>3
                    </td>
                </tr>
                <tr>
                    <td>Butter</td>
                    <td>4
                    </td>
                    <td>5
                    </td>
                    <td>6
                    </td>
                </tr>
            </table>


     <asp:Button runat="server" ID="ExportToExcelButton" OnClick="ExportToExcelButton_Click"
        Text="Export To Excel" />

score 2 · Answer 1 · answered Apr 20 '15 at 11:41

2

In the example you mentioned, you are NOT creating an Excel (or Excel WorkSheets), instead you are trying to open HTML content in Excel format. That is the reason, even if you have TWO Tables, they will show up in single sheet.

Use Microsoft's OpenXml SDK, through which you can create any number of Sheets and inject data into sheets. Even OpenXml SDK got some minor issues when it comes to Date Type (especially while reading data from Excel sheets). In such a case you can go to any third party Excel automation providers like SpreadSheetGear.

answered Apr 20 '15 at 11:41

ramiramilu

17,044
6
49
66

Thanks, There is no any other way to solve ? either using C# or other language ? – KD. Apr 20 '15 at 12:43
Using C#, you can use OpenXml SDK. There is one other way to solve without using OpenXml (or third party tools), that is by constructing you own XML and sending it in response (http://www.codeproject.com/Articles/532589/ExportplustoplusExcelplus-e-plusMultipleplus). I dont think this is a generic approach, so I wouldn't suggest it. – ramiramilu Apr 21 '15 at 08:43

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

Working solution for GridViews is here:

http://www.aspsnippets.com/Articles/Exporting-Multiple-GridViews-To-Excel-SpreadSheet-in-ASP.Net.aspx

You may want to look also here for some advise:

Add table to second sheet of Excel using C#

You may want to use EPPLUS library (free) instead of interop because it has a lot of functionality:

http://epplus.codeplex.com/

edited May 23 '17 at 11:50

Community

1
1

answered Apr 20 '15 at 11:44

demonplus

5,613
12
49
68

Thanks for your revert. But, I am looking for with HTML Tables not grid-views. :) I really appreciated your efforts here. – KD. Apr 20 '15 at 12:26
No problem, then using some library like EPPLUS may be a solution for you instead of Interop, it will have more flexibility. – demonplus Apr 20 '15 at 12:28
I have no idea about EPPLUS, Is it possible to convert HTML tables to excel using this library? If yes, How? – KD. Apr 20 '15 at 12:40
I added the link to it to my post – demonplus Apr 20 '15 at 12:42

Export Html Tables to Multiple Excel Sheet using C#

2 Answers2