.net数据导出到excel【Update20111009】

     昨天临时要做一个把dataset中的数据导出到excel的功能，同事给了一段代码，功能实现，现分享：

#region 普通的Excel导出 /// <summary> /// DataTable To Excel /// </summary> /// <param name="dt">DataTable Name</param> /// <param name="typeid">1，Excel 2，XML</param> /// <param name="FileName">文件名</param> public void CreateExcel(System.Data.DataTable dt, string typeid, string FileName) { HttpResponse resp; resp = Page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.ContentType = "application/ms-excel"; resp.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls"); this.EnableViewState = false; string colHeaders = "", Is_item = ""; int i = 0; //定义表对象与行对象，同时使用DataSet对其值进行初始化 DataRow[] myRow = dt.Select(""); //typeid=="1"时导出为Excel格式文件;typeid=="2"时导出为XML文件 if (typeid == "1") { //取得数据表各列标题，标题之间以/t分割，最后一个列标题后加回车符 for (i = 0; i < dt.Columns.Count; i++) { colHeaders += dt.Columns[i].Caption.ToString() + "/t"; } colHeaders += "/n"; resp.Write(colHeaders); //逐行处理数据 foreach (DataRow row in myRow) { //在当前行中，逐列取得数据，数据之间以/t分割，结束时加回车符/n for (i = 0; i < dt.Columns.Count; i++) { Is_item += row[i].ToString() + "/t"; } Is_item += "/n"; resp.Write(Is_item); Is_item = ""; } } else { if (typeid == "2") { //从DataSet中直接导出XML数据并且写到HTTP输出流中 resp.Write(dt.DataSet.GetXml()); } } //写缓冲区中的数据到HTTP头文件中 resp.End(); } #endregion

在网上又看到一篇更全面的文章，分享地址：http://www.cnblogs.com/xiaotao823/archive/2008/09/26/1299364.html

现在常用的方式是参考：http://excelpackage.codeplex.com/

 实例方法：

  string sql = string.Format("select * from ...{0}}", SearchTerms);            string name = "Search" + System.DateTime.Now.ToString("yyMMddHHmmss") + ".xlsx";//文件名            DataTable dt = Biz.csFormDateReport(sql);//数据源            FileInfo template = new FileInfo(Server.MapPath("/Controls/CustomerService/Report/TemplateExcel/MonthFormTemplate.xlsx"));//模板文件            if (dt.Rows.Count > 0)            {              if (!template.Exists) throw new Exception("Template file does not exist!");              using (ExcelPackage pck = new ExcelPackage(template, true))              {                  ExcelWorksheet ws = pck.Workbook.Worksheets["FormList"];                  if (ws != null)                  {                      const int startRow = 3;//开始插入数据的行                      int row = startRow;                      foreach (DataRow dr in dt.Rows)                      {                          //产品名称工单属性类别结案未结案详细描述个案总结                          if (row > startRow) ws.InsertRow(row, 1);                          ws.Cells[row, 1].Value = dr["ProjectName"].ToString();                          ........//填充数据    row++;                      }                      //delete the two spare rows we have in the template                       ws.DeleteRow(row, 1);                      row--;                      ws.Cells[row + 1, 4].CreateArrayFormula("=SUM(D" + startRow + ":D" + row + ")");//添加计算公式                      ws.Cells[row + 1, 5].CreateArrayFormula("=SUM(E" + startRow + ":E" + row + ")");                      Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");                      Response.ContentType = "Application/vnd.ms-Excel";                      Response.AddHeader("content-disposition", "attachment;  filename=" + System.Web.HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8) + "");                      Response.BinaryWrite(pck.GetAsByteArray());                      Response.End();                  }              }            }            else            { this.labInfo.Text = "无数据！"; return; }

Excel模板，合计总计会自动计算出来！