Want to download HTML table in Excel Sheet using ASP.NET c#

Question

I have created an .aspx page where I am displaying summary report in two different tables and retrieving values from database.

This is my aspx code:

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<div style="text-align:center;" ><asp:Label ID="Label3" runat="server" Text="Summary Report" style="font-size:x-large;"></asp:Label></div>    
<br />
<table id="tbl1" style="width: 100%;" border="1">
    <tr>
        <th class="auto-style1" colspan="11" style="text-align:center;padding:10px 10px 10px 10px;">Total Players: <asp:Label ID="lblTotPlayers" runat="server" Text="Label"></asp:Label></th>
    </tr>
    <tr>
        <% string connString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
           System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connString);
           System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
           con.Open();
           cmd = new System.Data.SqlClient.SqlCommand("select vchSportName from UserSportMapping left outer join tblPWP_Sports on UserSportMapping.SportsID = tblPWP_Sports.intSportId WHERE vchSportName IN (vchSportName)  group by vchSportName", con);
            System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {%>
            <th class="auto-style1" style="padding:10px 10px 10px 10px;"><%=dr["vchSportName"] %></th>
            <%}
                dr.Close(); %>
    </tr>
    <tr>
        <% cmd = new System.Data.SqlClient.SqlCommand("select count(vchSportName) as Counts from UserSportMapping left outer join tblPWP_Sports on UserSportMapping.SportsID = tblPWP_Sports.intSportId WHERE vchSportName IN (vchSportName)  group by vchSportName", con);
            System.Data.SqlClient.SqlDataReader dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {%>
            <td style="padding:10px 10px 10px 10px;"><%=dr1["Counts"] %></td>
            <%}
            dr1.Close();%>
    </tr>
</table>

<br /><br /><br />

<table id="tbl2" style="width: 100%;" border="1">
    <tr>
        <th class="auto-style1" colspan="12" style="text-align:center;padding:10px 10px 10px 10px;">Total Business Partners: <asp:Label ID="lblTotBPs" runat="server" Text="Label"></asp:Label></th>
    </tr>
    <tr>
        <% cmd = new System.Data.SqlClient.SqlCommand("select UserType, COUNT(CompanyName) as Counts from tblUserDetails full outer join Mst_UserType on tblUserDetails.ServiceId = Mst_UserType.UserTypeId where UserType in (UserType) and UserTypeId <> 1 and IsActive=1 group by UserType order by UserType", con);
            System.Data.SqlClient.SqlDataReader dr2 = cmd.ExecuteReader();
            while (dr2.Read())
            {
            %>
            <th class="auto-style1" style="padding:10px 10px 10px 10px;"><%=dr2["UserType"] %></th>
            <%}
            dr2.Close(); %>
    </tr>
    <tr>
        <% cmd = new System.Data.SqlClient.SqlCommand("select UserType, COUNT(CompanyName) as Counts from tblUserDetails full outer join Mst_UserType on tblUserDetails.ServiceId = Mst_UserType.UserTypeId where UserType in (UserType) and UserTypeId <> 1 and IsActive=1 group by UserType order by UserType", con);
            System.Data.SqlClient.SqlDataReader dr3 = cmd.ExecuteReader();
            while (dr3.Read())
            {
            %>
        <td style="padding:10px 10px 10px 10px;"><%=dr3["Counts"] %></td>
        <%}
            dr3.Close(); %>
    </tr>
</table>
<br /><br />
<asp:Button ID="Button1" runat="server" Text="Back" OnClick="Button1_Click" /> &nbsp; <asp:Button ID="Button2" runat="server" Text="Download as Excel" OnClick="DownExcel"  />

One thing to note that I am using <% %> on .aspx page to write c# code.

Now I want to download this table as Excel file with same table format. I am unable to find any specific solution, please help me.

Thanks in advance.

save the values in a datatable and then export it to excel (e.g. with using EPPlus) — Kᴀτᴢ, Mar 23 '16 at 07:22
I have read the name EPPlus, but don't know how to use it, and it is worth to use third party apps in projects? If yes, please explain about it. — Azeem Shaikh, Mar 23 '16 at 07:25
http://stackoverflow.com/questions/9364107/export-html-table-to-excel-using-asp-net — Vikas, Mar 23 '16 at 07:30
Thanks @Vikas I have seen that thread before also, but as I am using html table, it's not detecting my table id in c# code. — Azeem Shaikh, Mar 23 '16 at 07:46
I do not want to criticize your approach, but i personally did the same thing other way. aspx have a wonderfull separation of concern: aspx for markup and aspx.cs for code. With OnPageLoad (etc) events to bind your data to the html. If you can afford to write the page with the codebehind part(where the %get data from database% thing happens) so you could easily utilize EPPlus. It is great lib and is very easy to use, if you have raw collection data (for example List thing) — Lorin, Mar 23 '16 at 10:12

score 0 · Answer 1 · answered Mar 23 '16 at 07:31

0

You can use a csv export class which has built in methods to export data from table to excel sheet.

https://github.com/jitbit/CsvExport

In the readme file, you will find the steps.

answered Mar 23 '16 at 07:31

It's a trap

1,333
17
39

I have to display header and data cells both from database, as you can see in my above code. How would CSV help me in that? – Azeem Shaikh Mar 23 '16 at 07:49
Csv is just a file format supported by Excel. Its best to use csv format to export tables as they can be used to restore them easily by just feeding to a right tool. – It's a trap Mar 23 '16 at 08:30
@AzeemShaikh just export the data you get from the database, not the html table. You do not need this step to export table. – Lorin Mar 23 '16 at 10:15
thanks All for help, just now I have achieved my goal using stringwriter. :) – Azeem Shaikh Mar 23 '16 at 10:29

Want to download HTML table in Excel Sheet using ASP.NET c#

1 Answers1