/// <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); } }
/// <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); } }
/// <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}"; } }