封装类 导出gridview为excel

/*支持一般gridview和模板内容为textbox的导出*/

    可以通过ColumnCount调节要导出的列 比如最后一列模板为checkbox，那么可以设置ColumnCount=GridView1.Columns.Count-1; 

 

using System;
using System.Data;
using System.Configuration;
//using System.Linq;
using System.IO;
using System.Text;
using System.Net;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
//using System.Xml.Linq;

/// <summary>
///BB 的摘要说明
/// </summary>
public class BB
{
 public BB()
 {
  //
  //TODO: 在此处添加构造函数逻辑
  //
 }

 

   /// <summary>
   /// 导出gridview 支持textbox 和 非textbox
   /// </summary>
   /// <param name="page">当前gridview所在页面</param>
   /// <param name="gv">gridview</param>
   /// <param name="Title">excel标题</param>
   /// <param name="ColumnCount">导出的列数：从第一列开始数</param>
    public static void Export_Excel(Page page,GridView gv,string Title,int ColumnCount)
    {
        System.Reflection.Missing miss = System.Reflection.Missing.Value;
        Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
        m_objExcel.Visible = false;
        Microsoft.Office.Interop.Excel.Workbooks m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
        Microsoft.Office.Interop.Excel.Workbook m_objBook = (Microsoft.Office.Interop.Excel.Workbook)(m_objBooks.Add(miss));
        Microsoft.Office.Interop.Excel.Worksheet m_objSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet;

        /*注意excel   的起始行列是1，1*/
        /*注意gridview的起始行列是0，0*/

        #region 表名
        m_objSheet.Cells[1, 1] = Title;
        m_objSheet.Columns.AutoFit();
        Microsoft.Office.Interop.Excel.Range mergeRange = (Microsoft.Office.Interop.Excel.Range)m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, ColumnCount]);
        mergeRange.Merge((bool)false);
        ((Microsoft.Office.Interop.Excel.Range)m_objSheet.Cells[1, 1]).Font.Size = 16;
        ((Microsoft.Office.Interop.Excel.Range)m_objSheet.Cells[1, 1]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
        ((Microsoft.Office.Interop.Excel.Range)m_objSheet.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        #endregion

        #region 列名

        for (int i = 1; i <= ColumnCount; i++)
        {
            m_objSheet.Cells[2, i] = gv.HeaderRow.Cells[i-1].Text;
            ((Microsoft.Office.Interop.Excel.Range)m_objSheet.Cells[2, i]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            ((Microsoft.Office.Interop.Excel.Range)m_objSheet.Cells[2, i]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        }
        #endregion

        #region 填充内容

        for (int i = 0; i < gv.Rows.Count; i++)
            for (int j = 0; j < ColumnCount; j++)
            {
                try
                {
                    m_objSheet.Cells[(i + 3), (j + 1)] = ((TextBox)(gv.Rows[i].Cells[j].Controls[1])).Text;
                }
                catch(Exception e)
                {
                    try
                    {
                        m_objSheet.Cells[(i + 3), (j + 1)] = gv.Rows[i].Cells[j].Text;
                    }
                    catch
                    {
                        m_objSheet.Cells[(i + 3), (j + 1)] = "0";
                    }
                    //catch
                    //{
                    //    throw new ArgumentNullException();
                    //}
                }
              
                m_objSheet.Columns.AutoFit();
                ((Microsoft.Office.Interop.Excel.Range)m_objSheet.Cells[(i + 3), (j+1)]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                ((Microsoft.Office.Interop.Excel.Range)m_objSheet.Cells[(i + 3), (j+1)]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            }
        #endregion

        #region 保存
        string path = page.Server.MapPath(".");
        string filename= Title + ".xls";
        string filepath = path + "//" + filename;

        m_objBook.SaveAs(filepath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
        m_objBook.Close(false, miss, miss);
        m_objBooks.Close();
        m_objExcel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
        GC.Collect();
        #endregion

        #region 下载

        //HttpWebRequest request = null;
        //HttpWebResponse response = null;
        //Stream stream = null;
        //StreamReader reader = null;
        FileInfo fileInfo = new FileInfo(filepath);

        HttpResponse Response = page.Response;
        Response.Clear();
        Response.ClearContent();
        Response.ClearHeaders();
        Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8).ToString());
        Response.AddHeader("Content-Length", fileInfo.Length.ToString());
        Response.AddHeader("Content-Transfer-Encoding", "binary");
        Response.ContentType = "application/octet-stream";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
        Response.WriteFile(fileInfo.FullName);
        Response.Flush();

        System.IO.FileInfo file = new System.IO.FileInfo(filepath);
        if (file.Exists)
        {
            file.Delete();
        }
        Response.End();
       

        #endregion

    }

 
}

 

 

例子调用：BB.Export_Excel(this.Page, GridView1, "水井日报表", GridView1.Columns.Count);

本人将继续封装 导出gridview为word