c#匯出Excel

標籤：

 1  protected void btnExecl_Click(object sender, EventArgs e) 2     { 3         DataSet myds = new DataSet(); 4         string ConnectionString = WebConfigurationManager.ConnectionStrings["TMSDatabase"].ConnectionString; 5         string actionid = GetParameter("id"); 6         using (SqlConnection conn = new SqlConnection(ConnectionString)) 7         { 8             conn.Open(); 9             string sqlstr = " SELECT b.UserName 人員姓名,s.[Score_Teammate] 互評得分合計,s.[Score_Leader] 處長打分,s.[Score_Summary] 最終得分 FROM [PA_Score] s inner join BD_BaseUserInfo b on s.Assess_UserID = b.UserID "10                       + "where  s.[Assess_Action_ID]=‘" + actionid + "‘ and s.IsHistory=‘true‘ order by s.[Score_Summary] desc";11             SqlDataAdapter myda = new SqlDataAdapter(sqlstr, conn);12             myda.Fill(myds, "ExportExcel");13         }14         if (myds.Tables[0].Rows.Count > 0)15         {16             CreateExcel(myds);17         }18         else19         {20             Response.Write("<script type=‘text/javascript‘>alert(‘沒有要倒出的資料!‘);</script>");21         }22     }

View Code

 1   public void CreateExcel(DataSet dsProductListSearch) 2     { 3         try 4         { 5             //匯出 6             XlsDocument xls = new XlsDocument(); 7             //xls.FileName = "workstat.xls"; 8             xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss", System.Globalization.DateTimeFormatInfo.InvariantInfo); 9 10             Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");11             //填充表頭   12             foreach (DataColumn col in dsProductListSearch.Tables[0].Columns)13             {14                 string[] columnNames = col.ColumnName.Split(‘#‘);15 16                 Cell cell = sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);17                 cell.Font.FontFamily = FontFamilies.Roman; //字型   18                 cell.Font.Bold = true;  //字型為粗體 19             }20             //填充內容   21             string strvare = "";22             for (int i = 0; i < dsProductListSearch.Tables[0].Rows.Count; i++)23             {24                 for (int j = 0; j < dsProductListSearch.Tables[0].Columns.Count; j++)25                 {26                     if (j == 15)27                     {28                         if (dsProductListSearch.Tables[0].Rows[i][j].ToString() == "-1")29                         {30                             strvare = "逾時";31                         }32                         if (dsProductListSearch.Tables[0].Rows[i][j].ToString() == "1")33                         {34                             strvare = "";35                         }36                     }37                     else38                     {39                         strvare = dsProductListSearch.Tables[0].Rows[i][j].ToString();40                     }41                     sheet.Cells.Add(i + 2, j + 1, strvare);42                 }43             }44 45             xls.Send();46         }47         catch48         {49             Response.Write("<script language=javascript>‘rztj->serarch.aspx.cs->Button_ToExcel_Click匯出excel錯誤‘</script>");50         }51 52     }

View Code

需要用到的dll

http://files.cnblogs.com/files/ithuo/%E5%AF%BC%E5%87%BAExcel%E7%94%A8%E5%88%B0%E7%9A%84dll.rar

c#匯出Excel