Official Blog of Kunsh Technologies

How to generate Excel Report Content from HTML Data Table Using ASP.NET MVC

6/8/2018

A very common need of any reporting solution is to give user ability to export the report content as Excel or PDF once he/she has viewed the report in the browser as HTML. It is a not a new thing to deal with the HTML tables in ASP.NET. However, usage of MVC makes the things a bit complex and hence, it is required by the ASP.NET Developer to consider it seriously.

You may have an idea earlier about reading Microsoft Excel data files using Excel Data Reader library and the ways to display that data in ASP.NET MVC based web application. In this post, I am going to suggest how that data can be exported to Excel file using same application with the help of ASP.NET Framework. So, check out the coding examples displayed below.

Create excel or Export Excel from Html Table using MVC (asp.net MVC)

1) First Create HTML or CSHTML extension File:-

  1. There Is No Required html body etc.
  2. If You Are Using Asp.net MVC Partial Page Your Page Extension Is ".CSHTML"

  Example:-

<div style="margin-top:10px;"></div>
    <table style="border:1px solid black;padding:5px;font-weight:100">
        <tbody style="border:1px solid black;padding:5px;font-weight:100"><tr style="border:1px solid black;padding:5px;font-weight:100">
        <td colspan = "5" style ="text-align:center;border:1px solid black">
            <h1 style="color:red;text-decoration:underline">Your Invoice</h1></td>
        </tr>

        <tr>
            <td colspan="5" style="border:1px solid black;padding:5px;font-weight:100">Searching criteria</td>
        </tr>

        <tr>
            <td colspan="2" style="border:1px solid black;padding:5px;font-weight:100">Search Param 1</td>
            <td colspan="3" style="border:1px solid black;padding:5px;font-weight:100">8821650318629</td>
        </tr>
        <tr>
            <td colspan="2" style="border:1px solid black;padding:5px;font-weight:100">Period</td>
            <td colspan="3" style="border:1px solid black;padding:5px;font-weight:100">04/04/2017 - 03/05/2017</td>
        </tr>
        <tr>
            <td colspan="2" style="border:1px solid black;padding:5px;font-weight:100">Search Param 2</td>
            <td colspan="3" data-xls-col-index="2">Call Details</td>
        </tr>
        <tr>
            <td colspan="3" style="border:1px solid black;padding:5px;font-weight:100"> GPS Location Information</td>
            <td colspan="2" style="border:1px solid black;padding:5px;font-weight:100"> -</td>
        </tr>
        <tr>
            <td colspan="2" style="border:1px solid black;padding:5px;font-weight:100">Search Param 3</td>
            <td colspan="3" style="border:1px solid black;padding:5px;font-weight:100">MS - ORIGINATING</td>
        </tr>
        <tr>
            <td colspan="3" style="border:1px solid black;padding:5px;font-weight:100">MS - TERMINATING</td>
            <td colspan="2" style="border:1px solid black;padding:5px;font-weight:100"> -</td>
        </tr>
        <tr>
            <td colspan="5" style="border:1px solid black;padding:5px;font-weight:100">Summary Report</td>
        </tr>
        <tr>
            <td colspan="5" style="border:1px solid black;padding:5px;font-weight:100">Originating Call Details</td>
        </tr>
        <tr>
            <td style="border:1px solid black;padding:5px;font-weight:100">Number calling</td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Number called</td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Date(YYYY/MM/DD HH:MM:SS) </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Call Duration(sec) </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Country</td>
        </tr>
        <tr>
            <td style="border:1px solid black;padding:5px;font-weight:100">8821650318629 </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">393662555630 </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">2017/05/02 16:31:17 </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">0 </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Mediterranean Sea</td>
        </tr>
        <tr>
            <td style="border:1px solid black;padding:5px;font-weight:100">
                8821650318629 </td >
                <td style="border:1px solid black;padding:5px;font-weight:100"> 393662555630 </td >
                <td style="border:1px solid black;padding:5px;font-weight:100" > 2017 / 05 / 02 03:40:30
            </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">
                0 </td >
                <td style="border:1px solid black;padding:5px;font-weight:100"> Italy </td >
                </tr >
                <tr >
                <td colspan= "5" style="border:1px solid black;padding:5px;font-weight:100">Terminating Call Details
            </td>
        </tr>
        <tr>
            <td style="border:1px solid black;padding:5px;font-weight:100">Number calling</td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Number called</td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Date(YYYY/MM/DD HH:MM:SS) </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Call Duration(sec) </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Country</td>
        </tr>
        <tr>
            <td  colspan="5" style="border:1px solid black;padding:5px;font-weight:100">No Record found.</td>
        </tr>
        <tr>
            <td colspan="5" style="border:1px solid black;padding:5px;font-weight:100">GPS Location Information</td>
        </tr>
        <tr>
            <td style="border:1px solid black;padding:5px;font-weight:100">IMEI</td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Date(YYYY/MM/DD HH:MM:SS) </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Country</td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Latitude</td>
            <td style="border:1px solid black;padding:5px;font-weight:100">Longitude</td>
        </tr>
        <tr>
            <td style="border:1px solid black;padding:5px;font-weight:100">3560130010789500 </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">2017/05/03 16:02:51 </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">ITALY</td>
            <td style="border:1px solid black;padding:5px;font-weight:100">45.220586 </td>
            <td style="border:1px solid black;padding:5px;font-weight:100">12.282395 </td>
        </tr>

        </tbody>
    </table>

 

2) Second We Need To Create Action Method From Which We Use:- 

It is a Get Action Request Action Method Write This Code in Your Controller

 

using System;

Using System.Collections.Generic;

Using System.Data.Entity;

using System.Data.Entity.Validation;

Using System.IO;

Using System.Linq;

Using System.Text;

Using System.Web;

Using System.Web.Mvc;

Using AngularFirstWithMVC.DataEntity;

Using AngularFirstWithMVC.Filter;

using AngularFirstWithMVC.Models;

 

namespace AngularFirstWithMVC.Controllers

{

    [Authorize]

    public class EmployeeController : Controller

    {

      

 public ActionResult Export()

        {

            return View();

           

        }

    }

}

 

3) Third We Need To Create View of Export Action Method:- 

For User View We need Create View of Export Action Method View by Following Steps

1).Right Click on Export Action Method

2).Choose Create View Option 

3). finally create Empty View like This 

         A). View name: Export (already Inserted When You Create View from Action Method)

         b). Template: Select Empty (Without model) Option because we do note any model in this action method

         c). Model class: set To Be Empty

         d). Data Context Class: set To Be Empty

         e). Select Option

                           1). Create as a Partial View (leave unchecked the Check box)

                           2). Render Script Library (leave unchecked the Check box)

                           3). Use a Layout Page (Checked the Check box), and select path of layout Page

         e). finally click on add Button

 

.net development images

 

4) Fourth We Need To Design View of Export Action Method:- 

You need to write Your Code of View Manfully because you select template type is Empty

This Is View design Code 

 

@{

    ViewBag.Title = "Employee List";

    Layout = "~/Views/Shared/_Layout.cshtml";

}

@using (Html.BeginForm())

{

  

    @Html.Partial("invoice")

 

    <input type="submit" value="Convert To Excel" />

}

 

In Above Code we use Layout Page and Also Used HTml.BeginForm for Submitting on Post Method of Export

 

5) Fifth We Need To Post Verb Type of Export Action Method:- 

Here the Code of Post Method Of Export Action Method With Get Action Method

 

using System;

using System.Collections.Generic;

using System.Data.Entity;

using System.Data.Entity.Validation;

using System.IO;

using System.Linq;

using System.Text;

using System.Web;

using System.Web.Mvc;

using AngularFirstWithMVC.DataEntity;

using AngularFirstWithMVC.Filter;

using AngularFirstWithMVC.Models;

 

namespace AngularFirstWithMVC.Controllers

{

    [Authorize]

    public class EmployeeController : Controller

    {

//Get httpVerb Method      

 public ActionResult Export()

        {

            return View();

           

        }

//Post httpVerb Method

        [HttpPost]

        [ValidateInput(false)]

        public FileResult Export(FormCollection frm)

        {

            string body = string.Empty;

 

            using (StreamReader reader = new StreamReader(Server.MapPath("~/Views/Shared/invoice.cshtml")))

            {

                body = reader.ReadToEnd();

            }       

 

            return File(Encoding.ASCII.GetBytes(body), "application/vnd.ms-excel", "Grid.xls");

        }

    }

}

 

Important Notes:

1).here We Used [validate Input (false)] Attribute to allow a String input that contain Tags if we Do not use this attribute Server Not allow to input html string into the post request 

2).FormCollection Class Indicate The Entire Form Element from Where the post request Come 

3).We Are Using StreamReader Class to read string from Html or partial Cshtml file (which content Table Data

4).We Use Action Result type file because we return file type content which extension is .xls

 

Hope You like it. If you guys have any query regarding this than let me know at my mail I'd: parth.bari@kunshtech.com, I will definitely try to solve it.

Kunsh Technologies Kunsh Technologies