动态选择导出Excel字段
1.获取数据，自动生成字段，绑定CheckBoxList
DAL
/// <summary> /// 获取学生导出信息 /// </summary> /// <param name="strWhere"></param> /// <returns></returns> public DataSet sao_p_GetStudent_DKSQ_print(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT e.xh as 学号,d.xs_name as 姓名,e.rxnf as 入学年份,pycc.pycc_name as 培养层次,zyxy.xy_name as 专业学院,"); strSql.Append(" zsxy.xy_name as 住宿书院,zy.zy_name as 专业,e.zynd_no as 年级,e.ldap_id+'@stu.edu.cn' as 邮箱,"); strSql.Append(" d.xb as 性别,lxdz_sf+lxdz_cs+lxdz_xq+lxdz as 地址,"); strSql.Append(" grlxfs as 个人联系方式,jg as 籍贯,sfzh_id as 身份证,"); strSql.Append(" csrq_str as 出生日期,zzmm as 政治面貌,mz as 民族,sfzh_id as 身份证号,e.vid ,e.ldap_id as 校园账号,"); strSql.Append(" d.yzbm as 邮政编码,lxr as 联系人,d.lxdh as 联系电话,e.byxx as 毕业学校,"); strSql.Append(" A.xn_key1 as 贷款学年1,A.xfzsf1 as 申请学费住宿费1,A.shf1 as 申请生活费1,"); strSql.Append(" A.xn_key2 as 贷款学年2,A.xfzsf2 as 申请学费住宿费2,A.shf2 as 申请生活费2,"); strSql.Append(" A.xn_key3 as 贷款学年3,A.xfzsf3 as 申请学费住宿费3,A.shf3 as 申请生活费3,"); strSql.Append(" A.xn_key4 as 贷款学年4,A.xfzsf4 as 申请学费住宿费4,A.shf4 as 申请生活费4,"); strSql.Append(" A.xn_key5 as 贷款学年5,A.xfzsf5 as 申请学费住宿费5,A.shf5 as 申请生活费5,"); strSql.Append(" A.hdkzh as 还款帐户,dkqx_ks as 贷款期限开始,dkqx_js as 贷款期限结束,"); strSql.Append(" sqrdh as 申请人电话,jtrks as 家庭人口,rjsr as 家庭人均月收入,jtlxr as 联系人姓名,lxr_gx as 联系人关系,gzdw as 联系人工作单位,"); strSql.Append(" B.lxdh as 联系人电话,fqxm as 父亲姓名,fqsfzh as 父亲身份证号码,fqgzdw as 父亲工作单位,fqysr as 父亲月收入,fqlxdh as 父亲联系电话,"); strSql.Append(" mqxm as 母亲姓名,mqsfzh as 母亲身份证号码,mqgzdw as 母亲工作单位,mqysr as 母亲月收入,mqlxdh as 母亲联系电话,"); strSql.Append(" c.yhshrq as 学院审核日期,case A.zt_flg when 0 then '填表中' when 1 then '待学院审核'"); strSql.Append(" when 2 then '待学校审核' when 3 then '待银行审核' when 4 then '审核通过'"); strSql.Append(" when 5 then '审核不通过' "); strSql.Append(" end as 审核状态,CONVERT(char(10), hzdyrq, 20) as 打印时间"); strSql.Append(" FROM dkb_dksqb A"); strSql.Append(" INNER JOIN dkb_dksqjb B ON A.dksq_key=B.dksq_key "); strSql.Append(" INNER JOIN dkb_dksqsh c ON A.dksq_key=c.dksq_key"); strSql.Append(" INNER JOIN dkb_dkht on a.vid=dkb_dkht.vid"); strSql.Append(" INNER JOIN dkb_dkhtje ON dkb_dkht.dkht_key=dkb_dkhtje.dkht_key "); strSql.Append(" inner join xsb_grxx d on a.vid=d.vid"); strSql.Append(" inner join xsb_rxjbxx e on a.vid=e.vid"); strSql.Append(" left join dmb_pycc pycc on e.pycc_key=pycc.pycc_key"); strSql.Append(" left join dmb_xy zyxy on e.zyxy_key=zyxy.xy_key"); strSql.Append(" left join dmb_xy glxy on e.glxy_key=glxy.xy_key"); strSql.Append(" left join dmb_xy zsxy on e.zsxy_key=zsxy.xy_key"); strSql.Append(" left join dmb_zy zy on b.zy_key=zy.zy_key"); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return DbHelperSQL_SAO.Query(strSql.ToString()); }
HTML
<asp:CheckBoxList ID="cblCol" runat="server" RepeatColumns="6" RepeatDirection="Horizontal" CssClass="tbCbl"></asp:CheckBoxList>
.cs文件
private void BindData() { DAL_SAO_NEW_ZXDK.Controller.dkb_dksqb C_dkb_dksqb = new DAL_SAO_NEW_ZXDK.Controller.dkb_dksqb(); DataTable dt = C_dkb_dksqb.sao_p_GetStudent_DKSQ_print(where).Tables[0]; ViewState["XsxxExport"] = dt; cblCol.Items.Clear(); //清空选项 for (int i = 0; i < dt.Columns.Count; i++) { cblCol.Items.Add(new ListItem(dt.Columns[i].ColumnName)); } cblCol.Items.FindByText("姓名").Selected = true; cblCol.Items.FindByText("性别").Selected = true; cblCol.Items.FindByText("学号").Selected = true; cblCol.Items.FindByText("专业学院").Selected = true; cblCol.Items.FindByText("专业").Selected = true; cblCol.Items.FindByText("年级").Selected = true; cblCol.Items.FindByText("身份证号").Selected = true; }
2.遍历CheckBoxList，去除DataTable中未选中的字段，绑定GridView，导出Excel
HTML
<asp:GridView ID="gvwXsxxExport" runat="server" Visible="false"></asp:GridView> <asp:Label ID="lb_total_xsxx" runat="server" Text="0" Visible="false"></asp:Label>
.cs文件
/// <summary> /// 导出学生信息 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnXsxxExport_Click(object sender, EventArgs e) { DataTable dt = (DataTable)ViewState["XsxxExport"]; for (int i = 0; i < cblCol.Items.Count; i++) { if (cblCol.Items[i].Selected == false) { //去除未选中字段 dt.Columns.Remove(cblCol.Items[i].Text); } } if (dt.Columns.Count < 1) { Common_SAO_NEW.Tools.ShowAlert("请选中要导出的列！"); return; } gvwXsxxExport.DataSource = dt; gvwXsxxExport.DataBind(); foreach (GridViewRow x in gvwXsxxExport.Rows) { for (int i = 0; i < x.Cells.Count; i++) { //设置单元格样式，防止出现科学计数法问题 x.Cells[i].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); } } lb_total_xsxx.Text = "总计-" + gvwXsxxExport.Rows.Count.ToString() + "-项"; if (gvwXsxxExport.Rows.Count > 0) { gvwXsxxExport.Visible = true; lb_total_xsxx.Visible = true; string ExcelFile = "XSDKXX_" + System.DateTime.Now.ToString("yyyyMMddHHmmss"); string header = @"<table><tr><td colspan=" + dt.Columns.Count + "><center><b><font size='3'>学生申请贷款信息</font></b></center></td></tr>" + "<tr><td colspan=" + (dt.Columns.Count - 1) + "></td><td>导出时间:" + DateTime.Now.ToShortDateString() + "</td></tr></table>"; string bottomer = ""; Common_SAO_NEW.Tools.OutputExcel(gvwXsxxExport, ExcelFile, header, bottomer, lb_total_xsxx); gvwXsxxExport.Visible = false; lb_total_xsxx.Visible = false; } else { Common_SAO_NEW.Tools.ShowAlert("暂无可供导出信息！"); return; } }
操作DataTable
DataTable dt = new DataTable("tt"); dt.Columns.Add("cc1"); dt.Columns.Add("cc2"); dt.Columns.Add("cc3"); dt.Rows.Add("r1","r1","r1"); dt.Rows.Add("r1", "r1", "r1"); dt.Rows.Add("r1", "r1", "r1"); dt.Rows.Add("r1", "r1", "r1"); DataSet ds = new DataSet(); ds.Tables.Add(dt);//添加到ds中，以便和你的情况一致，排除ds的影响 DataTable dt2 = ds.Tables["tt"];//重新获取tt dt2.Columns.Remove("cc2");//删除列 int s = dt2.Columns.Count;//结果是2，删除成功 Response.Write(s);
浙公网安备 33010602011771号