数据库导出Excel报表

aspx文件部分代码:

aspx

1    <div style="width: 84px; height: 30px; text-align: left; float: left">
2                         <asp:Button ID="bt_Export" runat="server" Height="23px"    Text="导出" 
3                             Width="59px" onclick="bt_Export_Click" />
4                     </div>

aspx.cs文件部分代码:

aspx.cs

  1         static DataTable dt = null;
  2 
  3 
  4         protected void bt_Search_Click(object sender, EventArgs e)
  5         {
  6             string strTemp = "";
  7             whereString = "1>0 ";
  8             //编制类型
  9 
 10             foreach (System.Web.UI.WebControls.ListItem item in cbl_bzlx.Items)
 11             {
 12                 if (item.Selected)
 13                 {
 14                     if (strTemp == "")
 15                     {
 16                         strTemp += " gb_formation_type='" + item.Text.Trim() + "' ";
 17                     }
 18                     else
 19                     {
 20                         strTemp += " OR gb_formation_type='" + item.Text.Trim() + "' ";
 21                     }
 22                 }
 23             }
 24 
 25             if (strTemp != "")
 26             {
 27                 whereString += " AND (";
 28                 whereString += strTemp;
 29                 whereString += ")  ";
 30             }
 31             //现任职务
 32             strTemp = "";
 33             foreach (System.Web.UI.WebControls.ListItem item in cbl_xrzw.Items)
 34             {
 35                 if (item.Selected)
 36                 {
 37                     if (strTemp == "")
 38                     {
 39                         strTemp += " gb_position_now='" + item.Text.Trim() + "' ";
 40                     }
 41                     else
 42                     {
 43                         strTemp += " OR gb_position_now='" + item.Text.Trim() + "' ";
 44                     }
 45                 }
 46             }
 47 
 48             if (strTemp != "")
 49             {
 50                 whereString += " AND (";
 51                 whereString += strTemp;
 52                 whereString += ")  ";
 53             }
 54 
 55             if (nf_zjYear.Text.Trim() != "")
 56             {
 57                 //现任职级年长
 58                 whereString += @"AND  datediff( month, [gb_position_level_date],'" + date_deadline.SelectedDate.ToString() + "'" + ") >=12*" + nf_zjYear.Text.ToString().Trim() + "  ";
 59             }
 60             if (nf_workYear.Text.Trim() != "")
 61             {
 62                 //工作时间
 63                 whereString += @"AND  datediff( month, [gb_inwork_date],'" + date_deadline.SelectedDate.ToString() + "'" + ") >=12*" + nf_workYear.Text.ToString().Trim() + "  ";
 64             }
 65             //学历
 66             strTemp = "";
 67             foreach (System.Web.UI.WebControls.ListItem item in cbl_xl.Items)
 68             {
 69                 if (item.Selected)
 70                 {
 71                     if (strTemp == "")
 72                     {
 73                         strTemp += " gb_edu_bgd='" + item.Text.Trim() + "' ";
 74                     }
 75                     else
 76                     {
 77                         strTemp += " OR gb_edu_bgd='" + item.Text.Trim() + "' ";
 78                     }
 79                 }
 80             }
 81             if (strTemp != "")
 82             {
 83                 whereString += " AND (";
 84                 whereString += strTemp;
 85                 whereString += ")  ";
 86             }
 87 
 88             // 年度考核
 89             strTemp = "";
 90             foreach (System.Web.UI.WebControls.ListItem item in cbl_ndkh.Items)
 91             {
 92                 if (item.Selected)
 93                 {
 94                     if (strTemp == "")
 95                     {
 96                         strTemp += " rsc_check_hire='" + item.Text.Trim() + "' ";
 97                     }
 98                     else
 99                     {
100                         strTemp += " OR rsc_check_hire='" + item.Text.Trim() + "' ";
101                     }
102                 }
103             }
104             if (strTemp != "")
105             {
106                 if (nf_khYear.Text.Trim() == "")
107                 {
108                     MessageBox("提示", "请填写年度考核年长");
109                     return;
110                 }
111                 else if (nf_khYear.Text.Trim() != "")
112                 {
113                     whereString += " AND (";
114                     whereString += strTemp;
115                     whereString += ")  ";
116                     //考核年长
117                     whereString += "AND  datediff( month, [rsc_date], getdate()) <=12*" + nf_khYear.Text.ToString().Trim() + "  ";
118                 }
119             }
120 
121 
122             if (nf_salaryLevel.Text.Trim() != "")
123             {
124                 //薪级
125                 whereString += "AND  cast([salary_level] as int) >=" + nf_salaryLevel.Text.ToString().Trim() + "  ";
126             }
127            // LogAction(whereString);
128             dt = bll.GetListForSelect_GB(whereString).Tables[0];
129             if (dt.Rows.Count==0)
130             {
131                 bt_Export.Visible = false;
132                 panel_dataList.Visible = false;
133                 MessageBox("提示", "没有符合所选择条件的数据");
134             }
135             else
136             {
137                 Store1.DataSource = dt;
138                 Store1.DataBind();
139                 bt_Export.Visible = true;
140                 panel_dataList.Visible = true;
141                 MessageBox("提示", "一共有"+dt.Rows.Count.ToString()+"条数据");
142             }
143 
144             // whereString +=" gb_formation_type='"+
145         }
146 
147 
148      protected void bt_Export_Click(object sender, EventArgs e)
149         {
150             bt_Search_Click(sender, e);
151             if (dt.Rows.Count!=0)
152             {
153                 //创建临时xls文件
154                 string FileName = @"TempFile/干部选拔" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
155                 string sNewFullFile = Server.MapPath(FileName);
156                 string formatFilePath=Server.MapPath(@"TempFile/format.xls");
157               
158                 SZLtbIM.BLL.Export ebll = new SZLtbIM.BLL.Export();
159                 if (ebll.ExportByRSGL(sNewFullFile,formatFilePath,dt))
160                 {
161                     Response.Redirect(FileName);
162                 }
163                 else
164                 {
165                     MessageBox("提示", "导出失败，请重新导出");
166                 }
167 
168             }
169         }
170

SQLDAL层文件部分代码

GetListForSelect_GB

 1   /// <summary>
 2         /// 干部选拔获得数据列表
 3         /// </summary>
 4         public DataSet GetListForSelect_GB(string strWhere)
 5         {
 6             StringBuilder strSql = new StringBuilder();
 7             strSql.Append("select   [dept_name],[gb_name],[gb_sex],[gb_birthday],[gb_hometown],[gb_inwork_date],[gb_political_status],[gb_edu_bgd],[gb_position_now],[gb_position_level_date],[gb_formation_type],[gb_position_now]");
 8             strSql.Append(" FROM v_GB_Check ");
 9             if (strWhere.Trim() != "")
10             {
11                 strSql.Append(" where " + strWhere);
12             }
13             strSql.Append("  order by gb_id desc ");
14             /*
15             DataSet ds = SQLServerHelper.ExecuteDataset(strSql.ToString());
16             foreach(DataTable dt in ds.Tables)
17             {
18                 foreach (DataRow row in dt.Rows)
19                  {
20                     try
21                     { 
22                         row["gb_birthday"]=Convert.ToDateTime(row["gb_birthday"]).ToString("yyyy-MM-dd");
23                     }
24                     catch (System.Exception )
25                     {
26                         continue;
27                     }
28                      
29                  }
30             }  
31              return ds;
32             */
33         
34                 
35          return SQLServerHelper.ExecuteDataset(strSql.ToString());
36         }

ExportByRSGL

 1  /// <summary>
 2         /// 人事管理按格式导出信息
 3         /// </summary>
 4         /// <param name="sNewFullFile">文件的绝对路径</param>
 5         /// <param name="formatFilePath">格式文件的绝对路径</param>
 6         /// <param name="dt">数据表datatable</param>
 7         /// <returns>true成功， false失败</returns>
 8         public bool ExportByRSGL(string sNewFullFile,string formatFilePath, DataTable dt)
 9         {
10 
11             bool bRet = false;
12             if (dt.Rows.Count != 0)
13             {
14                 //创建临时xls文件          
15                 try
16                 {
17                     if (File.Exists(sNewFullFile))
18                     {
19                         File.Delete(sNewFullFile);
20                     }
21                     else
22                         System.IO.File.Copy(formatFilePath, sNewFullFile);
23                 }
24                 catch (Exception)
25                 {                   
26                     return false;
27                 }
28                 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties=Excel 8.0;";
29                 OleDbConnection conn = new OleDbConnection(strConn);
30                 OleDbCommand cmd = null;
31 
32                 try
33                 {
34                     conn.Open();
35                     cmd = new OleDbCommand("CREATE table [Sheet1$]([序号] Text,[单位] Text,[姓名] Text,[性别] Text,[出生年月] Text,[籍贯] Text,[参加工作时间] Text,[政治面貌] Text,[学历] Text,[现任职务] Text,[任现职级时间] Text)", conn);
36                     cmd.ExecuteNonQuery();
37                     string strSQL = @"INSERT INTO [Sheet1$] ([序号],[单位],[姓名],[性别],[出生年月],[籍贯],[参加工作时间],[政治面貌],[学历],[现任职务],[任现职级时间]) VALUES (?,?, ?, ?,?, ?, ?,?, ?, ?,?)";
38 
39                     cmd = new OleDbCommand(strSQL, conn);
40                     for (int i = 0; i < 11; i++)
41                     {
42                         cmd.Parameters.Add(i.ToString(), OleDbType.VarChar);
43                     }
44                     System.Data.DataView dv = dt.DefaultView;
45 
46                     foreach (DataRowView row in dv)
47                     {
48                         cmd.Parameters[0].Value = dt.Rows.IndexOf(row.Row) + 1;
49                         for (int i = 1; i < 11; i++)
50                         {
51                             if (i==4||i==6 || i==10)
52                             {
53                                 try
54                                 {
55                                     cmd.Parameters[i].Value =Convert.ToDateTime(row[i-1]).ToString("yyyy年MM月dd日");
56                                 }
57                                 catch (System.Exception)
58                                 {
59                                     cmd.Parameters[i].Value = row[i-1].ToString();
60                                 }
61                             }
62                             else
63                             cmd.Parameters[i].Value = row[i-1].ToString();
64                         }
65                         cmd.ExecuteNonQuery();
66                     }
67                     bRet = true;
68                 }
69                 catch (Exception e)
70                 {
71                     System.Console.WriteLine(e.Message);
72                     bRet = false;
73                 }
74                 finally
75                 {
76                     if (cmd != null)
77                     {
78                         cmd.Dispose();
79                     }
80                     conn.Dispose();
81                 }
82             }
83             return bRet;
84         }
85 
86

思路:

上篇的逆过程

posted on 2012-07-03 15:34 小y同学阅读(154) 评论(0) 收藏举报

刷新页面返回顶部

小y同学

数据库导出Excel报表

导航

公告