导出excel表方法汇总

1.不存放到服务器，直接给浏览器输出文件流
public bool ExportByDataTable(DataTable dt)
        {

            try
            {
                StringWriter sw = new StringWriter();
                string colstr="";
                foreach (DataColumn col in dt.Columns)
                {
                    colstr +=col.ColumnName + "\t";
                }
                sw.WriteLine(colstr);
                //同样方法处理数据
               
                foreach (DataRow row in dt.Rows)
                {
                    colstr = "";
                    foreach (DataColumn col in dt.Columns)
                    {
                           colstr +=  row[col.ColumnName].ToString()+"\t";
                    }
                    sw.WriteLine(colstr);
                }
                sw.Close();
                System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=Station.xls");
                System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
                System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                System.Web.HttpContext.Current.Response.Write(sw);
                System.Web.HttpContext.Current.Response.End();
            }
            catch (Exception err)
            {
                return false;
            }
            return true;
        }


2.在服务器上生成文件
 public bool ExportToFileByDataTable(DataTable dt)
        {

            try
            {
                Excel.Application excel = new Excel.ApplicationClass();
                int rowIndex = 1;
                int colIndex = 0;
                excel.UserControl = false;
                Excel.WorkbookClass wb = (Excel.WorkbookClass)excel.Workbooks.Add(System.Reflection.Missing.Value);

                //将所得到的表的列名,赋值给单元格
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    excel.Cells[1, colIndex] = col.ColumnName;
                }

                //同样方法处理数据
                foreach (DataRow row in dt.Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in dt.Columns)
                    {
                        colIndex++;
                        if (col.DataType.ToString() == "System.Int32")
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        else
                            //在Excel中，如果某单元格以单引号“’”开头，表示该单元格为纯文本
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                    }
                }

                //不可见,即后台处理
                excel.Visible = false;
                wb.Saved = true;
                excel.ActiveWorkbook.SaveCopyAs(System.Web.HttpContext.Current.Server.MapPath(fileName));
                excel.Quit();
                System.GC.Collect();

            }
            catch (Exception err)
            {
                return false;
            }
            return true;
        }

3、 Gridview中的内容导出到Excel

我们首先将gridview绑定到指定的数据源中，然后在button1的按钮（用来做导出到EXCEL的）的事件中，写入相关的代码。这里使用Response.AddHeader("content-disposition","attachment;filename=exporttoexcel.xls");中的filename来指定将要导出的excel的文件名，这里是exporttoexcel.xls。要注意的是，由于gridview的内容可能是分页显示的，因此，这里在每次导出excel时，先将gridview的allowpaging属性设置为false,然后通过页面流的方式导出当前页的gridview到excel中，最后再重新设置其allowpaging属性。另外要注意的是，要写一个空的VerifyRenderingInServerForm方法（必须写），以确认在运行时为指定的ASP.NET 服务器控件呈现HtmlForm 控件。

protected void Page_Load(object sender, EventArgs e)
{
　if (!Page.IsPostBack)
　{
　　BindData();
　}
}
private void BindData()
{
　string query = "SELECT * FROM customers";
　SqlConnection myConnection = new SqlConnection(ConnectionString);
　SqlDataAdapter ad = new SqlDataAdapter(query, myConnection);
　DataSet ds = new DataSet();
　ad.Fill(ds, "customers");
　GridView1.DataSource = ds;
　GridView1.DataBind();
}

public override void VerifyRenderingInServerForm(Control control)
{
　// Confirms that an HtmlForm control is rendered for
}

protected void Button1_Click(object sender, EventArgs e)
{
　Response.Clear();
　Response.AddHeader("content-disposition","attachment;filename=FileName.xls");
　Response.Charset = "gb2312";
　Response.ContentType = "application/vnd.xls";
　System.IO.StringWriter stringWrite = new System.IO.StringWriter();
　System.Web.UI.HtmlTextWriter htmlWrite =new HtmlTextWriter(stringWrite);

　GridView1.AllowPaging = false;
　BindData();
　GridView1.RenderControl(htmlWrite);

　Response.Write(stringWrite.ToString());
　Response.End();
　GridView1.AllowPaging = true;
　BindData();
}
protected void paging(object sender,GridViewPageEventArgs e)
{
　GridView1.PageIndex = e.NewPageIndex;
　BindData();
}