//后台方法 /// <summary> /// 生存CSV文件 /// </summary> /// <param name="Date">统计月份</param> /// <param name="type">数据源</param> /// <param name="filePath">文件路径</param> public void getContractImportCsv(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); System.IO.StringWriter Ends = new System.IO.StringWriter(); if (type.Equals("合同明细")) { while (retReader.Read()) { Ends.Write(retReader["统计日期"].ToString() + ","); Ends.Write(retReader["分行号"].ToString() + ","); Ends.Write(retReader["机构号"].ToString() + ","); Ends.Write(retReader["借据号"].ToString() + ","); Ends.Write(retReader["合同编号"].ToString() + ","); Ends.Write(retReader["基准利率"].ToString() + ","); Ends.Write(retReader["浮动利率"].ToString() + ","); Ends.Write(retReader["执行利率"].ToString() + ","); Ends.Write(retReader["放款日期"].ToString() + ","); Ends.Write(retReader["到期日期"].ToString() + ","); Ends.Write(retReader["贷款期限"].ToString() + ","); Ends.Write(retReader["贷款期限"].ToString() + ","); Ends.Write(retReader["贷款金额"].ToString() + ","); Ends.Write(retReader["还款方式"].ToString() + ","); Ends.Write(retReader["抽取日期"].ToString() + ","); Ends.Write(Ends.NewLine); index++; if (index == 200000)//当记时器等于20万时,进行数据写入,然后关闭,在开启 { Ends.Close(); StreamWriter sw = new StreamWriter(filePath, true, System.Text.Encoding.GetEncoding("GB2312")); sw.Write(Ends); sw.Close(); Ends = new System.IO.StringWriter(); index = 0;//记时器清"0” } } } else if (type.Equals("还款明细")) { while (retReader.Read()) { Ends.Write(retReader["统计日期"].ToString() + ","); Ends.Write(retReader["分行号"].ToString() + ","); Ends.Write(retReader["机构号"].ToString() + ","); Ends.Write(retReader["借据号"].ToString() + ","); Ends.Write(retReader["合同编号"].ToString() + ","); Ends.Write(retReader["还款期次"].ToString() + ","); Ends.Write(retReader["当期约定还款日期"].ToString() + ","); Ends.Write(retReader["当期实际还款日期"].ToString() + ","); Ends.Write(retReader["当期实际还款本金"].ToString() + ","); Ends.Write(retReader["本次还款后贷款余额"].ToString() + ","); Ends.Write(retReader["下期计划应还本金"].ToString() + ","); Ends.Write(retReader["贷款状态"].ToString() + ","); Ends.Write(retReader["抽取日期"].ToString() + ","); Ends.Write(Ends.NewLine); index++; if (index == 200000)//当记时器等于20万时,进行数据写入,然后关闭,在开启 { Ends.Close(); StreamWriter sw = new StreamWriter(filePath, true, System.Text.Encoding.GetEncoding("GB2312")); sw.Write(Ends); sw.Close(); Ends = new System.IO.StringWriter(); index = 0;//记时器清"0” } } } retReader.Close(); Ends.Close(); StreamWriter sws = new StreamWriter(filePath, true, System.Text.Encoding.GetEncoding("GB2312")); sws.Write(Ends); sws.Close(); Ends = null; } } catch (Exception ex) { new Exception(ex.Message); } } /// <summary> /// 生存txt文件 /// </summary> /// <param name="Date">统计月份</param> /// <param name="type">数据源</param> /// <param name="filePath">文件路径</param> public void getContractImportTxt(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(); if (type.Equals("合同明细")) { while (retReader.Read()) { strhtm.AppendFormat("{0}", retReader["统计日期"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["分行号"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["机构号"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["借据号"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["合同编号"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["基准利率"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["浮动利率"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["执行利率"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["放款日期"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["到期日期"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["贷款期限"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["贷款期限"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["贷款金额"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["还款方式"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["抽取日期"].ToString() + "/t"); strhtm.Append("/r/n"); 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.AppendFormat("{0}", retReader["统计日期"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["分行号"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["机构号"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["借据号"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["合同编号"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["还款期次"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["当期约定还款日期"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["当期实际还款日期"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["当期实际还款本金"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["本次还款后贷款余额"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["下期计划应还本金"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["贷款状态"].ToString() + "/t"); strhtm.AppendFormat("{0}", retReader["抽取日期"].ToString() + "/t"); strhtm.Append("/r/n"); 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(); 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(); } } }