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