.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模板，合计总计会自动计算出来！