DAL:
//产品信息导出——LPH
public DataTable ExportRelease(string type)
{
string sql = "SELECT [ProductID],[BankName],[ProductName] ,[IsServiceEnt] ,[IsServiceTrade],[IsServiceIndividual],[IsServiceOT] ,[ServiceOTDesc] ,[IsIndividual],[IsEnterpriseMF],[IsEnterpriseWS],[IsEnterpriseSR],[IsEnterpriseST],[IsEnterpriseOT],[EnterpriseOTDesc] ,[AgeLimit] ,[RegisterAddrType],[BusinessIncome] ,[TotalAssets] ,[NoGuaranty] ,[IsMortgageHS] ,[IsMortgageFB],[IsMortgageME] ,[IsMortgageOT] ,[MortgageOTDesc] ,[IsHypoDR] ,[IsHypoWR],[IsHypoIP],[IsHypoAR],[IsHypoITP],[IsHypoOT],[HypoDesc],[IsGuaranteeInd] ,[IsGuaranteeEnt] ,[IsGuaranteeCom] ,[IsGuaranteeOT] ,[GuaranteeOTDesc] ,[LoanAmountUpper] ,[LoanAmountLower] ,[LoanLimitUpper] ,[LoanLimitLower] ,[RepaymentType] ,[ReviewDate] ,[ProductDesc],[ProductDoc],[ProductURL] FROM [LoanProducts] where IsDeleted=0 and CheckStatus=3";
return base.SelectData(sql);
}
BLL:
//产品信息导出——LPH
protected DataTable ExportRelease(string type)
{
LP_CustomerDetailDAL dal = new LP_CustomerDetailDAL(CurrentTransaction);
return dal.ExportRelease(type);
}
protected void btnExcel_Click(object sender, EventArgs e)
{
System.Data.DataTable dt = (System.Data.DataTable)EventSubmit("TonkNet.BDA.LoanProduct.BLL.LoanProduct.LP_CustomerDetailBLL", "ExportRelease", new object[] { "" });
DataTableToExcel(dt, "产品信息导出列表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
//ExportExcel(dt, 100);
}
/// <summary>
/// 把datatable导入到excel中——LPH
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="defaultFileName">默认保存文件名</param>
protected void DataTableToExcel(System.Data.DataTable dt, string defaultFileName)
{
DataGrid dgd = new DataGrid();
dgd.AutoGenerateColumns = false;
//添加字段
BoundColumn ProductID = new BoundColumn();
BoundColumn BankName = new BoundColumn();
BoundColumn ProductName = new BoundColumn();
BoundColumn IsServiceEnt = new BoundColumn();
BoundColumn IsServiceTrade = new BoundColumn();
BoundColumn IsServiceIndividual = new BoundColumn();
//将多余的一行隐藏掉
ProductID.HeaderStyle.Height = 0;
BankName.HeaderStyle.Height = 0;
ProductName.HeaderStyle.Height = 0;
IsServiceEnt.HeaderStyle.Height = 0;
IsServiceTrade.HeaderStyle.Height = 0;
IsServiceIndividual.HeaderStyle.Height = 0;
//ProductID.HeaderText = "序号";
//BankName.HeaderText = "银行名称";
//ProductName.HeaderText = "产品名称";
//IsServiceEnt.HeaderText = "企业流动资金贷款";
//IsServiceTrade.HeaderText = "贸易融资";
//IsServiceIndividual.HeaderText = "个人经营性贷款";
ProductID.DataField = "ProductID";
BankName.DataField = "BankName";
ProductName.DataField = "ProductName";
IsServiceEnt.DataField = "IsServiceEnt";
IsServiceTrade.DataField = "IsServiceTrade";
IsServiceIndividual.DataField = "IsServiceIndividual";
dgd.Columns.Add(ProductID);
dgd.Columns.Add(BankName);
dgd.Columns.Add(ProductName);
dgd.Columns.Add(IsServiceEnt);
dgd.Columns.Add(IsServiceTrade);
dgd.Columns.Add(IsServiceIndividual);
dgd.DataSource = dt.DefaultView;
dgd.DataBind();
if (dgd.Items.Count == 0)
{
throw (new Exception("there is no data in dateTable"));
}
else
{
string elxStr = "<table border='1px' style='text-align:center;font-weight :bold ;'><tr> <td rowspan='4'>序号</td> <td rowspan='4'>银行名称</td> <td rowspan='4'>产品名称</td> <td rowspan='3' colspan='4'>业务实质</td> <td colspan='6'>贷款主体</td> <td rowspan='4'>企业成立最短年限(不限选0)</td> <td rowspan='4'>企业注册地</td><td rowspan='4'>营业收入(万元)</td><td rowspan='4'>企业总资产(万元)</td><td colspan='15'>担保方式</td><td rowspan='3' colspan='2'>额度（万元）</td><td rowspan='3' colspan='2'>贷款期限（月）</td><td rowspan='4'>还款方式</td><td rowspan='4'>审批时间</td><td rowspan='4'>产品特点（50字以内，内容通俗易懂）</td><td rowspan='4'>申请所需特殊文件（50字以内）</td><td rowspan='4'>产品链接或热线电话</td></tr><tr><td rowspan='3'>个人</td><td rowspan='2' colspan='5'>企业类型</td><td rowspan='3'>不需要</td><td colspan='14'>需要</td></tr><tr><td colspan='4'>抵押</td><td colspan='6'>质押</td><td colspan='4'>保证</td> </tr><tr><td>企业流动资金贷款</td><td>贸易融资</td><td>个人经营性贷款</td><td>其他(请注明)</td><td>制造业</td><td>批发、零售</td><td>服务业</td><td>科技型</td><td>其他(请说明)</td><td>商业房产</td><td>厂房</td><td>机器设备</td><td>其他(请注明)</td><td>存单</td><td>仓单</td><td>保单</td><td>应收账款</td><td>知识产权</td><td>其他(请注明)</td><td>个人连带责任担保</td><td>企业担保</td><td>担保公司担保</td><td>其他(请注明)</td><td>下限</td><td>上限</td><td>下限</td><td>上限</td></tr></table>";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "gb2312";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + defaultFileName);
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GBK");
//HttpContext.Current.Response.ContentEncoding = Encoding.UTF7;
//设置输出流为简体中文,试了GBK会出现乱码
HttpContext.Current.Response.ContentType = "application/ms-excel";
//设置输出文件类型为excel文件。
CultureInfo myCItrad = new CultureInfo("ZH-CN", true);
StringWriter oStringWriter = new StringWriter(myCItrad);
Response.Write(elxStr);
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
dgd.RenderControl(oHtmlTextWriter);
HttpContext.Current.Response.Write(oStringWriter.ToString());
HttpContext.Current.Response.End();
}
}