将Table表格导出到Excel

1.导出当前页

效果如下:

前台代码:

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <style>
        DIV.viciao {
            MARGIN-TOP: 20px;
            MARGIN-BOTTOM: 10px;
        }

            DIV.viciao A {
                BORDER-RIGHT: #8db5d7 1px solid;
                PADDING-RIGHT: 5px;
                BORDER-TOP: #8db5d7 1px solid;
                PADDING-LEFT: 5px;
                PADDING-BOTTOM: 2px;
                BORDER-LEFT: #8db5d7 1px solid;
                COLOR: #000;
                MARGIN-RIGHT: 2px;
                PADDING-TOP: 2px;
                BORDER-BOTTOM: #8db5d7 1px solid;
                TEXT-DECORATION: none;
            }

                DIV.viciao A:hover {
                    BORDER-RIGHT: red 1px solid;
                    PADDING-RIGHT: 5px;
                    BORDER-TOP: red 1px solid;
                    PADDING-LEFT: 5px;
                    PADDING-BOTTOM: 2px;
                    BORDER-LEFT: red 1px solid;
                    MARGIN-RIGHT: 2px;
                    PADDING-TOP: 2px;
                    BORDER-BOTTOM: red 1px solid;
                }

                DIV.viciao A:active {
                    BORDER-RIGHT: red 1px solid;
                    PADDING-RIGHT: 5px;
                    BORDER-TOP: red 1px solid;
                    PADDING-LEFT: 5px;
                    PADDING-BOTTOM: 2px;
                    BORDER-LEFT: red 1px solid;
                    MARGIN-RIGHT: 2px;
                    PADDING-TOP: 2px;
                    BORDER-BOTTOM: red 1px solid;
                }

            DIV.viciao SPAN.current {
                BORDER-RIGHT: #e89954 1px solid;
                PADDING-RIGHT: 5px;
                BORDER-TOP: #e89954 1px solid;
                PADDING-LEFT: 5px;
                FONT-WEIGHT: bold;
                PADDING-BOTTOM: 2px;
                BORDER-LEFT: #e89954 1px solid;
                COLOR: #000;
                MARGIN-RIGHT: 2px;
                PADDING-TOP: 2px;
                BORDER-BOTTOM: #e89954 1px solid;
                BACKGROUND-COLOR: #ffca7d;
            }

            DIV.viciao SPAN.disabled {
                BORDER-RIGHT: #ccc 1px solid;
                PADDING-RIGHT: 5px;
                BORDER-TOP: #ccc 1px solid;
                PADDING-LEFT: 5px;
                PADDING-BOTTOM: 2px;
                BORDER-LEFT: #ccc 1px solid;
                COLOR: #ccc;
                MARGIN-RIGHT: 2px;
                PADDING-TOP: 2px;
                BORDER-BOTTOM: #ccc 1px solid;
            } 

    </style>
</head>
<body>
    <input type="button" name="excelBut" value="导出Excel" onclick="exportExcel()" class="sgbtn" /> 
    
        <table id="OrderInfo">
            <thead>
                <tr>
                    <th>编号</th>
                    <th>姓名</th>
                    <th>备注</th>
                </tr>
            </thead>
            <tbody id="tdContent"></tbody>
        </table>
        <div id="nav" class="viciao"></div>
      <!--导出Excel处理隐藏域-->
        @using (Html.BeginForm("ExportExcel1", "Home", FormMethod.Post, new { name = "myForm" }))
                                {
                                    @Html.Hidden("hHtml")
                                }
    
    <script src="~/Scripts/jquery-1.8.2.min.js"></script>
    <script>
        //分页内容开始
        $(function () {
            pageData(1,5);
        })
        pageData = function (pIndex, pSize) {
            $("#tdContent").empty();
            $("#nav").children().empty();
            $.getJSON('@Url.Action("GetList","Home")?id='+Math.random()+'', { pageIndex: pIndex,pageSize:pSize}, function (_data) {
                for (var i = 0; i < _data.data.length; i++) {
                    var html = "<tr><td name='majorId'>" + _data.data[i].MajorID + "</td><td>" + _data.data[i].Name + "</td><td>" + _data.data[i].Remark + "</td></tr>";
                    $("#tdContent").append(html);
                }
                $("<p id='pNav'>" + _data.strPage + "</p>").appendTo("#nav");
                $("#pNav a").click(clickShow);
            })           
        }
        function clickShow() {
         
            var url = this.href;
            var navData = url.substring(url.lastIndexOf('?') + 1);
            var pSize = navData.split('&')[0].split('=')[1];
            var pIndex = navData.split('&')[1].split('=')[1];            
            pageData(pIndex, pSize);
            return false;
        }
        //分页内容结束-------<<

        //导出Excel文件开始
        function exportExcel() {        
            var sHtml =htmlEncode($("#OrderInfo")[0].outerHTML);//做html编码                
            $("input[name='hHtml']").val(sHtml);
          
            //表单提交
            $("form[name='myForm']").submit();
        }
       
        function htmlEncode(value) {
            return $('<div/>').text(value).html();
        }
        //导出Excel文件结束
    </script>
</body>
</html>

View Code

后台代码:

using Entity.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using System.IO;
using System.Text.RegularExpressions;

namespace 分页练习.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/

        public ActionResult Index()
        {

            return View();
        }
        public ActionResult GetList()
        {
            int pageIndex = string.IsNullOrEmpty(Request["pageIndex"]) ? 1:int.Parse(Request["pageIndex"]);
            int pageSize = string.IsNullOrEmpty(Request["pageSize"]) ? 10 : int.Parse(Request["pageSize"]);
            DbContext db = new InfernoContext();
            int recordCount=db.Set<Major>().Count();
            int pageCount = Convert.ToInt32(Math.Ceiling((recordCount*1.0/pageSize)));
            List<Major> list=db.Set<Major>().OrderBy(c => c.MajorID).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            string strPage = PageHelper.strPage(recordCount, pageSize, pageCount, pageIndex,"../Home/Index?pageSize="+pageSize+"&pageIndex=");
           var sendData = new{data=list,strPage=strPage };
           return Json(sendData, JsonRequestBehavior.AllowGet);
        }
        /// <summary>
        /// 导出Excel
        /// </summary>    
        [HttpPost]
        public ActionResult ExportExcel1(FormCollection form)
        {
            string strHtml = form["hHtml"]; 
            strHtml = HttpUtility.HtmlDecode(strHtml);//Html解码
            byte[] b = System.Text.Encoding.UTF8.GetBytes(strHtml);//字串转byte阵列    

            return File(b, "application/vnd.ms-excel", "订单详情.xls");//输出档案给Client端          
        }
      

    }
}

View Code

导出Excel动态链接库下载:http://pan.baidu.com/s/1o7ZDxwu

PS：上面是导出【当前页】的内容，如果想导出【所有的】。原理一样，添加个隐藏域就行了，然后将所有查询到的值加载到这个隐藏域里面。之后就可以按照上面的方法继续啦~

关于将Excel导入数据库可以参考博文

终结版参考博文