MVC 导出

最新推荐文章于 2021-11-28 20:39:39 发布

长江

最新推荐文章于 2021-11-28 20:39:39 发布

阅读量1.9k

点赞数

分类专栏： C#WEB VS2010.net MVC+EXTJS4 文章标签： mvc string exception excel function

本文链接：https://blog.csdn.net/lonestar555/article/details/7362394

版权

C#WEB 同时被 2 个专栏收录

34 篇文章 0 订阅

订阅专栏

VS2010.net MVC+EXTJS4

29 篇文章 0 订阅

订阅专栏

,
                                {
                                    xtype: 'button',
                                    width: 54,
                                    cls: 'index-btn-custom',
                                    overCls: 'index-btn-custom-over',
                                    focusCls: 'index-btn-custom-focus', 
                                    style: { border: 0 },
                                    text:  ButtonCss_Export,
                                    //导出按钮事件
                                    handler: function () { 
                                        var code = Ext.getCmp('condition1').getValue();
                                        
                                        if (code == "") {
                                            ShowInfoMsg("提示", "条件不能为空！");
                                            return;
                                        }
 
                                        //debugger 
                                        var myPanel = new Ext.Panel({
                                            layout: 'fit',
                                            html: '<iframe src="/Account/Export?AAA=' + AA + '&BBBBStart + '&CC=' + CC + '&DD=1"  width=100% height=100%></iframe>',
                                            frame: true
                                        });
                                        var win = new Ext.Window({
                                            title: '导出',
                                            width: 500,
                                            height: 300,
                                            resizable: false,
                                            closable: true,
                                            draggable: true,
                                            resizable: false,
                                            layout: 'fit',
                                            modal: false,
                                            plain: true,  
                                            bodyStyle: 'padding:5px;',
                                            items: [myPanel],
                                            buttonAlign: 'center',
                                            buttons: [{
                                                text: '关闭',
                                                type: 'button',
                                                handler: function () {
                                                    win.close();
                                                }
                                            }]
                                        });
                                        win.show();

                                    }
                                }

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<Controllers.AccountController.ExportCondition>" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title> 信息导出</title>
    <style type="text/css">
        body
        {
            color: #333333;
            font: 12px/20px "宋体";
        }
        .content
        {
            -moz-border-bottom-colors: none;
            -moz-border-image: none;
            -moz-border-left-colors: none;
            -moz-border-right-colors: none;
            -moz-border-top-colors: none;
            border-style: none solid solid;
            border-bottom: 1px solid #BDD2ED;
            border-top: 1px solid #BDD2ED;
            border-right: 1px solid #BDD2ED;
            border-width: medium 1px 1px;
            margin-bottom: 10px;
        }
    </style>
</head>
<%if (ViewData["ErrorMsg"] != null)
  { %>
<body>
    <div class="content" style="color: Red">
        <%=ViewData["ErrorMsg"].ToString()%></div>
    <div>
        请重新导入</div>
    <%}
  else
  {%>
    <body οnlοad="submitForm();">
        <%} %>
        <script type="text/javascript">
            var paracode = '';
            var paradtstart = '';
            var paradtend = '';
            function QueryString() {
                //构造参数对象并初始化
                var name, value = '', i;
                var str = location.href.toString().toLowerCase();  //获得浏览器地址栏URL串   //别忘了全是小写了
                //alert(str);
                var num = str.indexOf("?")
                str = str.substr(num + 1);  //截取“?”后面的参数串
                var arrtmp = str.split("&"); //将各参数分离形成参数数组
                for (i = 0; i < arrtmp.length; i++) {
                    num = arrtmp[i].indexOf("=");
                    if (num > 0) {
                        name = arrtmp[i].substring(0, num); //取得参数名称
                        value = arrtmp[i].substr(num + 1); //取得参数值
                        this[name] = value;    //定义对象属性并初始化

                    }
                }
            }

            function submitForm() {
                var Request = new QueryString();    //使用new运算符创建参数对象实例
                if (Request["username"] != null) {
                    document.getElementById('txtusername').value = Request["username"];
                }
                else {
                    document.getElementById('txtusername').value = '';
                }
                document.getElementById('formDownload').submit();
            }
        </script>
        <form action="/Account/Export" enctype="multipart/form-data" method="post"
        id="formDownload">
        <div class="content">
            <label>
                若浏览器长时间无反应，请点击导出按钮：</label><br />
            <div>
                <label>
                    导出用户名：</label>
                <input type="text" style="background-color: #ccc8bf" readonly="readonly" name="username"
                    id="txtusername" />
            </div>
            <div style="visibility: hidden">
                <input type="text" style="background-color: #ccc8bf; visibility: hidden;" readonly="readonly"
                    name="isfiverecord" id="txtisfiverecord" /></div>
            <div>
                <input type="submit" id="Submit3" value="导出" />
            </div>
        </div>
        </form>
        <div>
        </div>
    </body>
</html>

        /// <summary>
        /// 导出用户信息查询条件
        /// </summary>
        public class ExportCondition
        {
            /// <summary>
            /// 用户名
            /// </summary>
            public string UserName { get; set; }
        }

        /// <summary>
        /// 导出公司用户信息 aciton方法
        /// </summary>
        /// <returns>返回 登录 视图</returns>
        [RequiresAuthenticationAttribute]
        public ActionResult Export()
        {
            return View();
        }

        /// <summary>
        /// 导出公司用户信息
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [RequiresAuthenticationAttribute]
        public ActionResult Export(ExportCondition condition)
        {
            string msg = "";
            bool result = false;

            string UserName ;
            UserName = condition.UserName;
             
            string path = AppDomain.CurrentDomain.BaseDirectory + "TempData\\";
            string filepathModel = AppDomain.CurrentDomain.BaseDirectory + "Content\\导出用户信息_模板.xls";
            
            string newFileName = DateTime.Now.Ticks.ToString() + ".xls";
            string newFilePath = path + newFileName;
            try
            {
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                List<XH_UserInfo> listInfo = new List<XH_UserInfo>();
                 
                    listInfo = GetUserInfo(UserName );
                
                if (listInfo != null && listInfo.Count > 0)
                {
                    if (listInfo.Count > 500)
                    {
                        msg = "导出数据不能大于500条";
                    }
                    else
                    {
                        if (System.IO.File.Exists(filepathModel))
                        {
                            System.IO.File.Copy(filepathModel, newFilePath);
                            System.IO.File.SetAttributes(newFilePath, FileAttributes.Normal);

                            result = WriteExcelByOledb(newFilePath, listInfo, out  msg);
                        }
                        else
                        {
                            msg = "服务器上'导出用户信息_模板.xls'不存在";
                        }
                    }
                }
                else
                {
                    msg = "查询无数据";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                LogHelper.WriteError(ex.Message, ex);
            }
            if (result)
            {
                return File("/TempData/" + newFileName           //文件路径 
                , "application/vnd.ms-excel"                     //文件类型 
                , "导出用户信息.xls"
                );
            }
            else
            {
                ViewData["ErrorMsg"] = msg;
                return View();
            }
        }


        #region Oledb方式写入EXCEL


        /// <summary>
        /// Oledb方式写入EXCEL
        /// </summary>
        /// <param name="fileNamePath">文件路径</param>
        /// <returns></returns>
        private bool WriteExcelByOledb(string fileNamePath, List<XH_UserInfo> listInfo, out string msg)
        {
            msg = "";
            bool result = false;
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + fileNamePath;
            OleDbConnection oledbconn1 = new OleDbConnection(connStr);
            try
            {
                oledbconn1.Open();
                DataTable _table = oledbconn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                string strTableName = string.Empty;
                if (_table.Rows.Count > 0)
                {
                    strTableName = _table.Rows[0]["TABLE_NAME"].ToString().Trim();
                    //string sql = string.Format("SELECT * FROM [{0}]", strTableName);
                    string strValue ;
                    string sql = "";

                    OleDbCommand oCmd = new OleDbCommand(); 
                    foreach (var item in listInfo)
                    {
                        strValue = "'" + item.UserName + "'" + ",";     // 用户名 
                        strValue += "'" + item.XX.ToString() + "'";       //XXX
                        sql = string.Format("INSERT INTO [{0}]  VALUES ({1})", strTableName, strValue);
                        oCmd = new OleDbCommand(sql, oledbconn1);
                        oCmd.CommandType = CommandType.Text;
                        try
                        {
                            int r = oCmd.ExecuteNonQuery();
                            if (r > 0)
                            {
                                result = true;
                            }
                        }
                        catch (Exception ex)
                        {
                            msg = "写入EXCEL文件错误";
                            LogHelper.WriteError(ex.Message, ex);
                        }
                        finally
                        {
                        }
                    }
                }
                else
                {
                    msg = "模板文件不正确";
                }
            }
            catch (Exception ex)
            {
                msg = "连接EXCEL错误";
                LogHelper.WriteError(ex.Message, ex);
            }
            finally
            {
                if (oledbconn1.State != ConnectionState.Closed) { oledbconn1.Close(); }
            }
            return result;
        }

        #endregion