c#导出Excel操作

 1     //导出为Excel
 2     protected void btnExecl_Click(object sender, EventArgs e)
 3     {
 4         DataSet myds = new DataSet();
 5         string ConnectionString = WebConfigurationManager.ConnectionStrings["TMSDatabase"].ConnectionString;
 6         string actionid = GetParameter("id");
 7         using (SqlConnection conn = new SqlConnection(ConnectionString))
 8         {
 9             conn.Open();
10             string sqlstr = " SELECT b.UserName 人员姓名,s.[Score_Teammate] 互评得分合计,s.[Score_Leader] 处长打分,s.[Score_Summary] 最终得分 FROM [P
11                       + "where  s.[Assess_Action_ID]='" + actionid + "' and s.IsHistory='true' order by s.[Score_Summary] desc";
12             SqlDataAdapter myda = new SqlDataAdapter(sqlstr, conn);
13             myda.Fill(myds, "ExportExcel");
14         }
15         if (myds.Tables[0].Rows.Count > 0)
16         {
17             CreateExcel(myds);
18         }
19         else
20         {
21             Response.Write("<script type='text/javascript'>alert('没有要倒出的数据!');</script>");
22         }
23     }
24     #region 导出EXCEl事件
25     public void CreateExcel(DataSet dsExportExcel)
26     {
27         try
28         {
29             //导出
30             XlsDocument xls = new XlsDocument();
31             //xls.FileName = "workstat.xls";
32             xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss", System.Globalization.DateTimeFormatInfo.InvariantInfo);
33 
34             Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");
35             //填充表头   
36             foreach (DataColumn col in dsExportExcel.Tables[0].Columns)
37             {
38                 string[] columnNames = col.ColumnName.Split('#');
39 
40                 Cell cell = sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);
41                 cell.Font.FontFamily = FontFamilies.Roman; //字体   
42                 cell.Font.Bold = true;  //字体为粗体 
43             }
44             //填充内容   
45             string strvare = "";
46             for (int i = 0; i < dsExportExcel.Tables[0].Rows.Count; i++)
47             {
48                 for (int j = 0; j < dsExportExcel.Tables[0].Columns.Count; j++)
49                 {
50                     if (j == 15)
51                     {
52                         if (dsExportExcel.Tables[0].Rows[i][j].ToString() == "-1")
53                         {
54                             strvare = "超时";
55                         }
56                         if (dsExportExcel.Tables[0].Rows[i][j].ToString() == "1")
57                         {
58                             strvare = "";
59                         }
60                     }
61                     else
62                     {
63                         strvare = dsExportExcel.Tables[0].Rows[i][j].ToString();
64                     }
65                     sheet.Cells.Add(i + 2, j + 1, strvare);
66                 }
67             }
68 
69             xls.Send();
70         }
71         catch
72         {
73             Response.Write("<script language=javascript>'导出excel错误'</script>");
74         }
75 
76     }
77     #endregion