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");
        }