MVC导出Excel

一、客户端js生成excel

因为excel能够解析html代码，所以此思路就是直接将html中间的table标签原样输出，然后直接粘贴到excel里面就ok了

1、页面代码：

<div id="MyDiv" runat="server">
    <table id="MyTable" border="1" cellpadding="0" cellspacing="0">
        <thead>
            <tr>
                <th>编号</th>
                <th>名称</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>1</td>
                <td>测试1</td>
            </tr>
            <tr>
                <td>2</td>
                <td>测试2</td>
            </tr>
            <tr>
                <td>3</td>
                <td>测试3</td>
            </tr>
            <tr>
                <td>4</td>
                <td>测试4</td>
            </tr>
        </tbody>
    </table>
</div>
<br />
<input id="btnExport" value="js导出" type="submit" onclick="PrintTableToExcel()" />

<script type="text/javascript">
    function PrintTableToExcel() {
        exportTable = document.getElementById("MyTable");
        oApplication = new ActiveXObject('Excel.Application');
        oApplication.visible = true;
        oApplication.DisplayAlerts = false;
        xlBook = oApplication.Workbooks.Add;
        oActiveSheet = xlBook.ActiveSheet;
        window.clipboardData.setData('text', exportTable.outerHTML);
        oActiveSheet.Paste;
    }
</script>

此方法只适合IE浏览器用，其他浏览器不能下载

二、服务器端生成Excel

此导出理论主要是当点击页面上的导出按钮的时候，服务器端会生成一Excel文件，然后利用Access数据库来将查询到的数据插入到此Excel文件；这样的话就需要在服务器端安装一下AccessDatabaseEngine，可以到官方的网站来下载对应的32位的还是64位的（官方网站：http://www.microsoft.com/zh-cn/download/details.aspx?id=13255），具体需要在服务器端安装32位还是64位可以上网搜一下（参考：http://blog.csdn.net/sundacheng1989/article/details/17925431）。

下面来编写代码：

1、在导出数据的页面添加一个导出按钮：

<a class="btn exportExcelJs" href="javascript:void(0);">导出Excel</a>

2、给该按钮添加一点击事件：

           //导出excel
            $('.exportExcelJs').on('click', function () {
                var filefolder = $('#filefolder').val();
                var filename = $('#filename').val();
                $.post('@Url.Action("ExportExcel", "Recharge", new { area = string.Empty })', { fileFolder: filefolder, fileName: filename }, function (a) {
                    var url = 'http://' + location.host + '/' + filefolder + '/' + filename + '.xlsx';
                    window.open(url);
                });
            })

3、此时需要编写Controller方法：

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public ActionResult ExportExcel(string fileFolder, string fileName)
        {
            try
            {
                string fullPath = string.Format("{0}{1}", Server.MapPath("~/"), fileFolder);

                if (!Directory.Exists(fullPath))
                {
                    Directory.CreateDirectory(fullPath);
                }

                fullPath += string.Format("/{0}.xlsx", fileName);

                IList<Recharge> recharge = RechargeService.QueryList();
                var list = new List<Models.Setting.Recharge>();
                // 格式化数据
                foreach (Recharge v in recharge)
                {
                    var recharge = new Models.Setting.Recharge
                    {
                        充值编号 = v.SerialNumber,
                        交易号 = v.PayNumber,
                        充值金额 = v.RechargeMoney.ToString(),
                        充值时间 = v.DateTime.ToString()
                    };
                    switch (v.State)
                    {
                        case 0:
                            recharge.充值状态 = "未支付";
                            break;
                        case 1:
                            recharge.充值状态 = "支付成功";
                            break;
                        case 2:
                            recharge.充值状态 = "支付失败";
                            break;
                    }
                    list.Add(recharge);
                }
                bool result = ExcelHelper.ExportExcel(list, fullPath);

                if (result)
                {
                    return JRSuccess("导出成功");
                }

            }
            catch (Exception)
            {
                return JRFaild("all", "导出失败");
            }

            return JRFaild("all", "导出失败");
        }

4、编写ExcelHelper类

        /// <summary>
        /// 将数据导出至Excel
        /// </summary>
        /// <param name="list">数据</param>
        /// <param name="excelFilePath">Excel文件路径</param>
        /// <returns></returns>
        public static bool ExportExcel<T>(IList<T> list, string excelFilePath)
        {
            PropertyInfo[] propertyInfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            //连接字符串
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath +
                             ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=0\"";

            // SQL语句
            var tableStructBuilder = new StringBuilder(@"Create Table Sheet1 (");
            var insertSqlBuilder = new StringBuilder(@"Insert into Sheet1 (");
            var insertSqlValueBuilder = new StringBuilder(@"Values (");

            var paras = new Dictionary<string, OleDbParameter>();

            foreach (PropertyInfo pi in propertyInfos)
            {
                paras.Add(pi.Name, new OleDbParameter("@" + pi.Name, OleDbType.VarChar));
                tableStructBuilder.Append("[" + pi.Name + "] varchar,");

                insertSqlBuilder.Append("[" + pi.Name + "],");
                insertSqlValueBuilder.Append("@" + pi.Name + ",");
            }

            // 创建Excel文件
            using (var conn = new OleDbConnection(connString))
            {
                try
                {
                    conn.Open();
                    using (var cmd = new OleDbCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandType = CommandType.Text;
                        // 创建表结构
                        cmd.CommandText = tableStructBuilder.ToString().TrimEnd(',') + ")";
                        cmd.ExecuteNonQuery();

                        // 插入数据
                        cmd.CommandText = insertSqlBuilder.ToString().TrimEnd(',') + ")" +
                                          insertSqlValueBuilder.ToString().TrimEnd(',') + ")";
                        cmd.Parameters.AddRange(paras.Select(p => p.Value).ToArray());

                        foreach (T entity in list)
                        {
                            foreach (PropertyInfo pi in propertyInfos)
                            {

                                paras[pi.Name].Value = Convert.ToString(pi.GetValue(entity, null) ?? "");
                            }
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception)
                {
                    return false;
                }
            }

            return true;
        }