//后台方法 /// <summary> /// 生存xls文件 /// </summary> /// <param name="Date">统计月份</param> /// <param name="type">数据源</param> /// <param name="filePath">文件路径</param> public void getContractImportDataScoure(string Date, string type, string filePath) { string importsql = returnSql(Date, type); try { using (SqlConnection connection = new SqlConnection(DbHelperSQL.connectionString)) { int index = 0; connection.Open(); SqlCommand command = new SqlCommand(importsql, connection); command.CommandType = CommandType.Text; SqlDataReader retReader = command.ExecuteReader(CommandBehavior.CloseConnection); StringBuilder strhtm = new StringBuilder(); strhtm.Append("<table border=1>"); if (type.Equals("合同明细")) { while (retReader.Read()) { strhtm.Append("<tr>"); strhtm.AppendFormat("<td>{0}</td>", retReader["统计日期"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["分行号"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["机构号"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["借据号"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["合同编号"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["基准利率"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["浮动利率"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["执行利率"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["放款日期"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["到期日期"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["贷款期限"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["贷款期限"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["贷款金额"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["还款方式"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["抽取日期"].ToString()); index++; if (index == 200000)//当记时器等于20万时,进行数据写入,然后关闭,在开启 { StreamWriter sw = new StreamWriter(filePath, true, System.Text.Encoding.UTF8); sw.Write(strhtm.ToString()); sw.Close(); strhtm = new StringBuilder(); index = 0;//记时器清"0” } } } else if (type.Equals("还款明细")) { while (retReader.Read()) { strhtm.Append("<tr>"); strhtm.AppendFormat("<td>{0}</td>", retReader["统计日期"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["分行号"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["机构号"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["借据号"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["合同编号"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["还款期次"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["当期约定还款日期"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["当期实际还款日期"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["当期实际还款本金"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["本次还款后贷款余额"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["下期计划应还本金"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["贷款状态"].ToString()); strhtm.AppendFormat("<td>{0}</td>", retReader["抽取日期"].ToString()); index++; if (index == 200000)//当记时器等于20万时,进行数据写入,然后关闭,在开启 { StreamWriter sw = new StreamWriter(filePath, true, System.Text.Encoding.UTF8); sw.Write(strhtm.ToString()); sw.Close(); strhtm = new StringBuilder(); index = 0;//记时器清"0” } } } retReader.Close(); strhtm.Append("</table>"); StreamWriter sws = new StreamWriter(filePath, true, System.Text.Encoding.UTF8); sws.Write(strhtm); sws.Close(); strhtm = null; } } catch (Exception ex) { new Exception(ex.Message); } } //前台调用 /// <summary> /// 导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnExport_Click(object sender, EventArgs e) { if (CheckIsNull()) { string name = this.DropDMonths.SelectedValue.ToString() + this.DropDDataScoure.SelectedValue.ToString() + "." + this.DropDFormat.SelectedValue.ToString(); string FilePath = Server.MapPath(string.Format("~/DpExecl/{0}", name)); try { if (this.DropDFormat.SelectedValue == "xls")//以"xls"格式导出 { icb.getContractImportDataScoure(this.DropDMonths.SelectedValue.ToString(), this.DropDDataScoure.SelectedValue.ToString(), FilePath); } else if (this.DropDFormat.SelectedValue == "csv")//以"csv"格式导出 { icb.getContractImportCsv(this.DropDMonths.SelectedValue.ToString(), this.DropDDataScoure.SelectedValue.ToString(), FilePath); } else if (this.DropDFormat.SelectedValue == "txt")//以"txt"格式导出 { icb.getContractImportTxt(this.DropDMonths.SelectedValue.ToString(), this.DropDDataScoure.SelectedValue.ToString(), FilePath); } } catch (Exception es) { ClientScript.RegisterStartupScript(GetType(), "", "<mce:script type="text/javascript"><!-- alert('" + es.Message + "'); // --></mce:script>"); return; } Response.Clear(); Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); Response.ContentType = "application/octet-stream"; Response.AppendHeader("Content-Disposition", "attachment;filename= " + HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8)); System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true); if (File.Exists(FilePath))//是否存在所指定的文件,如果存在进行下载 { FileInfo file = new FileInfo(FilePath); Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); //解决中文乱码 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); //解决中文文件名乱码 Response.AddHeader("Content-length", file.Length.ToString()); Response.ContentType = "appliction/octet-stream"; Response.WriteFile(file.FullName); Response.Flush(); if (file.Exists) { file.Delete(); } Response.End(); } } }