Monday, February 13, 2012

Export Html Table to Excel in Asp.net MVC

Export Html Table to Excel in Asp.net

function Export()   {  
    $
('#yourHiddenFieldId').val() = $('#yourTable').table2CSV({delivery:'value'});
    __doPostBack
('#yourExportBtnId', '');   }
 
// c#   if(Page.IsPostBack)   {
   
if(!String.IsNullOrEmpty(Request.Form[yourHiddenField.UniqueId]))
   
{
       
Response.Clear();
       
Response.ContentType = "text/csv";
       
Response.AddHeader("Content-Disposition", "attachment; filename=TheReport.csv");
       
Response.Flush();
       
Response.Write(Request.Form[yourHiddenField.UniqueID]);
       
Response.End();
   
}   } 


/// using Asp.net MVC
1) Add line in .aspx

<div id="mydiv"></div>

2) Add script to create iframe with a form tag

$(document).ready(function(){
$("#mydiv").hide();
 $("#mydiv").append('<iframe id="iframe1" href="#"> </iframe>');
        function replaceAll(txt, replace, with_this) { return txt.replace(new RegExp(replace, 'g'), with_this); }
        setTimeout(function Func1() {
            $("#iframe1").contents().find("body").append('<form id="exportform" action="ExportToExcel" method="post" target="_self"><input type="hidden" id="area1" name="area1" value=""/> <input type="submit" id="b1" value="submit"/><input type="hidden" id="area2" name="area2" value=""/></form>');
        }, 50);
        });

3)  Add Script to export button

  $("#btnExport").click(function () {
                        
            var arrTop = [];
            arrTop[0] = "Test Center:" + "|" + testCenter + "|" + "Program:" + "|" + program +"|";
            arrTop[1] = "Requestor:" + "|" + requestor + "|" + "Project:" + "|" + project +"|";
            arrTop[2] = "Department:" + "|" + department + "|" + "Project Number:" + "|" + projectNo ;

           
            //code for results grid
            var Columns = jQuery("#tblData thead tr").map(function() {
            var cells = "";
            jQuery(this).find('th').each(function() {
            if (cells.length > 0) {
                cells += ',' + jQuery(this).text();
            } else
            {
                cells += jQuery(this).text();
            }
            });
            return { ColumnNames: cells };
            }).get();
           
           
            //alert(Columns);
           
            var Rows = jQuery("#tblData tbody tr").map(function() {
             var cells = "";
            jQuery(this).find('td').each(function() {
              var celValue = jQuery(this).text();
               if (cells.length > 0) {
                 cells += ',' + celValue.replace(/,/g, ';');
               } else {
                 cells += celValue.replace(/,/g, ';');
               }
             });
             return { row: cells };
            }).get();
            var tableCSV = { "Columns": Columns, "Rows": Rows };
            var array = typeof tableCSV != 'object' ? JSON.parse(tableCSV) : tableCSV;
           
            var str = '';
            for (var i = 0; i < array.length; i++) {
                var line = '';
                for (var index in array[i]) {
                    line += array[i][index] + ',';
                }
                line.slice(0,line.Length-1);
                str += line + '\r\n';
               
            }
          
             var x = JSON.stringify(tableCSV);
            var prog=  "<%= Url.RouteUrl(new { controller = "controllerName", action = "ExportToExcel"}) %>";
                      var x = JSON.stringify(tableCSV);
             $($("#iframe1").contents().find('input')[0]).val(x);
              $($("#iframe1").contents().find('input')[1]).val(arrTop);
            $($("#iframe1").contents().find("input")[2]).trigger("click");
        });

4. Add action method to controller

 [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult ExportToExcel(string exportString)
        {
            exportString = Request.Form.GetValues(0)[0];
            string topTab = Request.Form.GetValues(1)[0];
            string fileName;
            fileName = "test1.xls";
            HtmlTable tempTabParam = new HtmlTable();
            //HtmlTableRow paramRow = null;
            //HtmlTableCell paramCell = null;
            string[] topArr = topTab.Replace(',', ' ').Split('|');
            HtmlTableRow paramRow1 = new HtmlTableRow();
            HtmlTableCell paramCell1 = new HtmlTableCell();
            paramCell1.InnerText = topArr[0].ToString();
            paramRow1.Cells.Add(paramCell1);
            HtmlTableCell paramCell2 = new HtmlTableCell();
            paramCell2.InnerText = topArr[1].ToString();
            paramRow1.Cells.Add(paramCell2);
            HtmlTableCell paramCell3 = new HtmlTableCell();
            paramCell3.InnerText = topArr[2].ToString();
            paramRow1.Cells.Add(paramCell3);
            HtmlTableCell paramCell4 = new HtmlTableCell();
            paramCell4.InnerText = topArr[3].ToString();
            paramRow1.Cells.Add(paramCell4);
            tempTabParam.Rows.Add(paramRow1);
            HtmlTableRow paramRow2 = new HtmlTableRow();
            HtmlTableCell paramCell5 = new HtmlTableCell();
            paramCell5.InnerText = topArr[4].ToString();
            paramRow2.Cells.Add(paramCell5);
            HtmlTableCell paramCell6 = new HtmlTableCell();
            paramCell6.InnerText = topArr[5].ToString();
            paramRow2.Cells.Add(paramCell6);
            HtmlTableCell paramCell7 = new HtmlTableCell();
            paramCell7.InnerText = topArr[6].ToString();
            paramRow2.Cells.Add(paramCell7);
            HtmlTableCell paramCell8 = new HtmlTableCell();
            paramCell8.InnerText = topArr[7].ToString();
            paramRow2.Cells.Add(paramCell8);
            tempTabParam.Rows.Add(paramRow2);
            HtmlTableRow paramRow3 = new HtmlTableRow();
            HtmlTableCell paramCell9 = new HtmlTableCell();
            paramCell9.InnerText = topArr[8].ToString();
            paramRow3.Cells.Add(paramCell9);
            HtmlTableCell paramCell10 = new HtmlTableCell();
            paramCell10.InnerText = topArr[9].ToString();
            paramRow3.Cells.Add(paramCell10);
            HtmlTableCell paramCell11 = new HtmlTableCell();
            paramCell11.InnerText = topArr[10].ToString();
            paramRow3.Cells.Add(paramCell11);
            HtmlTableCell paramCell12 = new HtmlTableCell();
            paramCell12.InnerText = topArr[11].ToString();
            paramRow3.Cells.Add(paramCell12);
            tempTabParam.Rows.Add(paramRow3);
           
            tempTabParam.BorderColor = ConsoleColor.DarkRed.ToString(); ;
           
           
           
            System.Web.HttpContext context = System.Web.HttpContext.Current;
            HtmlTable tempTable = new HtmlTable();
            tempTable.Border = 1;
            HtmlTableRow headRow = new HtmlTableRow();
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            TableData data = serializer.Deserialize<TableData>(exportString);
            HtmlTableCell cell = null;
            columns[] col = data.Columns;
            string[] arr = col[0].ColumnNames.Replace("\n", "").Replace(" ", "").Split(',');
            for (int i = 0; i <= arr.Length - 1; i++)
            {
                cell = new HtmlTableCell();
                cell.InnerText = arr[i].ToString();
                cell.BgColor = ConsoleColor.DarkRed.ToString();
                headRow.Cells.Add(cell);
            }
            tempTable.Rows.Add(headRow);
           
            rows[] arr1 = data.Rows;
            HtmlTableRow tabRow = null;
            HtmlTableCell tbCell = null;
            string[] rowArr = null;
            foreach (rows dataRow in data.Rows)
            {
                tabRow = new HtmlTableRow();
                rowArr = dataRow.row.Replace("\n", "").Replace(" ", "").Split(',');
                for (int j = 0; j <= rowArr.Length - 1; j++)
                {
                    tbCell = new HtmlTableCell();
                    tbCell.InnerText = rowArr[j].ToString();
                    tabRow.Cells.Add(tbCell);
                }
                tempTable.Rows.Add(tabRow);
            }
          
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            tempTabParam.RenderControl(hw);
            tempTable.RenderControl(hw);
           
            string filePath = HttpContext.Server.MapPath("~/content/");
            System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
            return File(encoding.GetBytes(hw.InnerWriter.ToString()), "application/excel", fileName);
           
        }

Updated :

public class columns
{
public string ColumnNames;
}

public class rows
{
public string row;
}

public class TableData
{
public columns[] Columns;
public rows[] Rows;
}

4 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
    Replies
    1. I know this is very large code to understand, but i also know this is the only way to export a HTML table to pdf.

      Delete
  2. Can please mention the assembly for TableData object?

    ReplyDelete
    Replies
    1. Following classes i missed to add.

      public class columns
      {
      public string ColumnNames;
      }

      public class rows
      {
      public string row;
      }

      public class TableData
      {
      public columns[] Columns;
      public rows[] Rows;
      }

      Delete