Asp.net导出Excel方法

方法一：

直接导出控件的数据源

 

 /// <summary>        /// 从控件导出        /// </summary>        /// <param name="title">标题</param>        public void outputExcle1(string title)        {                      string fileName = string.Empty;            HttpContext.Current.Response.Buffer = true;            HttpContext.Current.Response.ClearContent();            HttpContext.Current.Response.ClearHeaders();                       //HttpContext.Current.Response.Charset = "utf-8";            //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");            HttpContext.Current.Response.Charset = "GB2312"; //设置了类型为中文防止乱码的出现             fileName = string.Format("Export-File {0:yyyy-MM-dd_HH_mm}.xls", DateTime.Now);            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文            HttpContext.Current.Response.AppendHeader("Content-Type", "text/html; charset=gb2312");            HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。                         StringWriter tw = new System.IO.StringWriter();            HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);            this.Repeater1.RenderControl(hw); //将服务器控件中的内容输出到hw中 有RenderControl方法的控件应该都支持                      if (!string.IsNullOrEmpty(title))            {                HttpContext.Current.Response.Write("<b><center><font size=3 face=Verdana color=#0000FF>" + title + "</font></center></b>");            }                       HttpContext.Current.Response.Write(tw.ToString());            HttpContext.Current.Response.Flush();            HttpContext.Current.Response.Close();            HttpContext.Current.Response.End();        }

 

方法二，直接将DataTable导出

 

     public void outputExcle2(System.Data.DataTable dt)        {            StringWriter stringwriter = new StringWriter();            HtmlTextWriter htmlwriter = new HtmlTextWriter(stringwriter);            DataGrid excel = new DataGrid();            System.Web.UI.WebControls.TableItemStyle alternatingstyle = new TableItemStyle();            System.Web.UI.WebControls.TableItemStyle headerstyle = new TableItemStyle();            System.Web.UI.WebControls.TableItemStyle itemstyle = new TableItemStyle();            alternatingstyle.BackColor = System.Drawing.Color.LightGray;            headerstyle.BackColor = System.Drawing.Color.White;            headerstyle.Font.Bold = true;            headerstyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;            itemstyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; ;            excel.AlternatingItemStyle.MergeWith(alternatingstyle);            excel.HeaderStyle.MergeWith(headerstyle);            excel.ItemStyle.MergeWith(itemstyle);            excel.GridLines = GridLines.Both;            excel.HeaderStyle.Font.Bold = true;            excel.DataSource = dt;   //输出datatable的内容            excel.DataBind();            excel.RenderControl(htmlwriter);            string filepath = "aaa.xls";            string filestr = Server.MapPath(filepath);  //filepath是文件的路径            int pos = filestr.LastIndexOf("\\");            string file = filestr.Substring(0, pos);            if (!Directory.Exists(file))            {                Directory.CreateDirectory(file);            }            System.IO.StreamWriter sw = new StreamWriter(filestr);            sw.Write(stringwriter.ToString());            sw.Close();                     }

 

 

方法三：

直接导出DataTable 要引用：Microsoft.Office.Interop.Excel.dll 

   public void outputExcle2(System.Data.DataTable dt)        {            Application xlApp = new Application();            Workbooks w = xlApp.Workbooks;            Workbook workbook = w.Add(XlWBATemplate.xlWBATWorksheet);            Worksheet worksheet = (Worksheet)workbook.Worksheets[1];            //写入字段            for (int i = 0; i < dt.Columns.Count; i++)            {                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;            }            //写入数值            for (int r = 0; r < dt.Rows.Count; r++)            {                for (int i = 0; i < dt.Columns.Count; i++)                {                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];                }            }            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。            workbook.Saved = true;            workbook.SaveCopyAs(Server.MapPath("test.xls"));                        xlApp.Quit();            GC.Collect();//强行销毁            Response.Redirect("test.xls");        }