Esempio n. 1
0
        /// <summary>
        /// 根據用戶輸入的查詢語句,匯出查詢結果
        /// </summary>
        /// <returns>Super Excel匯出</returns>
        public DataTable SuperExportExcel(SuperQuery query, out int totalCount)//GetPersonMsg查询方法,dao層有方法重載
        {
            try
            {
                DataTable dt = new DataTable();
                dt = _superDao.SuperExportExcel(query, out totalCount);

                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception("SuperMgr-->SuperExportExcel-->" + ex.Message);
            }
        }
Esempio n. 2
0
        /// <summary>
        /// 根據用戶輸入的查詢語句,匯出查詢結果
        /// </summary>
        /// <returns>Super Excel匯出</returns>
        public DataTable SuperExportExcel(SuperQuery query, out int totalCount)//  數據總數 ,mgr層也有此方法,
        {
            StringBuilder str = new StringBuilder();

            totalCount = 0;
            try
            {

                if (!string.IsNullOrEmpty(query.superSql))
                {
                    str.Append(query.superSql);
                }
                DataTable myDataTable = _access.getDataTable(str.ToString());
                totalCount = myDataTable.Rows.Count;

                return myDataTable;
            }
            catch (Exception ex)
            {
                throw new Exception("SuperDao-->SuperExportExcel-->" + ex.Message);

            }
        }
Esempio n. 3
0
        /// <summary>
        /// 根據用戶輸入的查詢語句,匯出查詢結果
        /// </summary>
        /// <returns>Super Excel匯出</returns>
        public void SuperExportExcel()
        {
            SuperQuery query = new SuperQuery();
            int totalCount = 0;
            string json = string.Empty;
            DataTable _dt = new DataTable();

            string excelPath = String.Empty;

            try
            {
                try
                {
                    excelPath = ConfigurationManager.AppSettings["ImportUserIOExcel"].ToString();
                }
                catch (Exception ex)
                {
                    throw new Exception("找不到路徑" + excelPath + ex.Message);
                }

                //檢查Sql語句是否為查詢語句,不是則拋出錯誤
                //檢查Sql語句是否為空,為空則拋出錯誤
                if (!string.IsNullOrEmpty(Request.Params["superSql"]))
                {
                    query.superSql = Request.Params["superSql"];
                    query.superSql = query.superSql.Replace(",", ", ").Replace("\r", " ").Replace("\n", " ");
                    string[] sqlArray = query.superSql.Split(' ', ',', '(', ')');
                    if (sqlArray[0].ToLower() != "select")
                    {
                        Response.Write("Sql語句不是查詢語句,請輸入查詢語句 ");                      
                    }
                }
                else
                {
                    Response.Write("查詢語句為空!");                   
                }

                //檢查Sql語句是否錯誤
                try
                {
                    _dt = _superMgr.SuperExportExcel(query, out totalCount);
                }
                catch (Exception ex)
                {
                    Response.Write("Sql語句有錯誤" + ex.Message);
                    throw new Exception(ex.Message);                  
                }

                if (totalCount >= 60000)
                {
                    Response.Write("查詢到的數據多於60000條,請重新輸入查詢語句!");
                    
                }
                //返回与 Web 服务器上的指定虚拟路径相对应的物理文件路径。
                //Exists确定给定路径是否引用磁盘上的现有目录。
                if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))//如果目錄不存在,添加目錄   ,在派生类中重写时,
                {
                    //CreateDirectory 按 path 的指定创建所有目录和子目录。
                    //Directory目錄 
                    System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
                }


                if (_dt.Rows.Count > 0)
                {
                    string fileName = DateTime.Now.ToString("報表_yyyyMMddHHmmss") + ".xls";
                    MemoryStream ms = ExcelHelperXhf.ExportDT(_dt, "");
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);//Disposition:处置、部署
                    Response.BinaryWrite(ms.ToArray());//BinaryWrite(byte[] buffer)在派生类中重写时,将二进制字符的字符串写入 HTTP 输出流。
                    //要写入当前响应的二进制字符。 ToArray将流内容写入字节数组,而与 System.IO.MemoryStream.Position 属性无关。
                }
                else
                {
                    Response.Write("匯出數據不存在");
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false}";
            }
        }