GridView导入导出Excel表

实现在GridView中将数据导入导出Exce表（需要有Excel模板在文件中）

后台代码：

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GridView();
        }
    }
    //数据绑定
    private void GridView()
    {
        string sql = "select * from Article";
        GridView1.DataSource = SqlHelper.GetSqlTable(sql, null);
        GridView1.DataBind();
    }
    //导入
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (FileUpload1.PostedFile.FileName != "")
        {
            string path = Server.MapPath("upfiles/") + DateTime.Now.ToString("yyyyMMddhhmm") + FileUpload1.FileName;
            FileUpload1.SaveAs(path);
            string fileName = FileUpload1.PostedFile.FileName.ToString();// 获取带有完整路径的文件名
            if ((fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls"))// 判断是不是标准excel表
            {
                Response.Write("当前只支持后缀名为【.xls】的Excel文件，请重新选择正确的文件导入！");
                return;
            }
            //读取表
            DataSet ds = ExcelHelper.ExcelToData(Server.MapPath("upfiles/") + DateTime.Now.ToString("yyyyMMddhhmm") + FileUpload1.FileName);
            if (ds == null)
            {
                Response.Write("读取Excel表失败。请检查!");
                return;
            }
            //检测是否为该表
            DataTable data = ds.Tables["data"];
            string erro = "";
            string[] column = new string[] { "标题", "内容", "时间" }; //列名
            foreach (string col in column)
            {
                if (!data.Columns.Contains(col))
                {
                    erro += "【" + col + "】";
                }
            }
            if (error != "")
            {
                Response.Write("该Excel文件不存在以下列:" + "n" +errorMsg);
                return;
            }
            try
            {

                SqlConnection connStr = new SqlConnection(ConfigurationManager.ConnectionStrings["gzshConnectionString"].ConnectionString);
                connStr.Open();
                
                int rows = ds.Tables["data"].Rows.Count; // Excel表中数据行数
                int cols = ds.Tables["data"].Columns.Count; // Excel表中数据列数
                int n = 0;
                string repeat = ""; // 重复的主键
                for (int i = 0; i < rows; i++)
                {
                    string sql_getpk = "select * from Article where Atitle ='" + ds.Tables["data"].Rows[i][0] + "'";
                    SqlDataAdapter myAdapter = new SqlDataAdapter(sql_getpk, connStr);
                    if (myAdapter.Fill(ds) != 0)
                    {
                        n++;
                        repeat += ds.Tables["data"].Rows[i][0] + ", ";
                    }
                    connStr.Close();
                }

                if (n > 0)
                {
                    Response.Write("导入失败：当前Excel表中共有" + n + "条记录与数据库中记录重复。<br />重复的主键为：" + repeat);
                }
                else
                {
                    for (int j = 0; j < data.Rows.Count; j++)
                    {
                        string Atitle = data.Rows[j]["标题"].ToString();
                        string Acontent = data.Rows[j]["内容"].ToString();
                        string ApubDate = data.Rows[j]["时间"].ToString();
                        string sqlinsert = "insert into Article (Atitle,Acontent,ApubDate)values(@Atitle ,@Acontent ,@ApubDate)";
                        int result = SqlHelper.ExecuteNonQuery(sqlinsert, new SqlParameter("@Atitle", Atitle), new SqlParameter("@Acontent", Acontent), new SqlParameter("@ApubDate", ApubDate));
                    }
                    Response.Write("成功导入" + rows + "条");
                    GridView();
                }
                connStr.Close();
            }
            catch
            {
                
                Response.Write("导入失败");
            }
           
        }
        else
            {
                Response.Write("请选择需导入的excel表");
            }
    }
    //导出
    protected void Button2_Click(object sender, EventArgs e)
    {

        //如果有分页，则默认导出当前页
        ExcelHelper.DataToExcel(GridView1);

        //导出全部的方法，让其导出的时候不分页，导出完毕后恢复分页
        GridView1.AllowPaging = false;
        GridView();
        ExcelHelper.DataToExcel(GridView1);
        GridView1.AllowPaging = true;
        GridView();

    //导出check选中行，将未勾选中的行隐藏
        GridView1.AllowPaging = false;
        GridView1.AllowSorting = false;
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            CheckBox ckb = GridView1.Rows[i].FindControl("chk_Del") as CheckBox;
            if (ckb.Checked)
            {
                GridView1.Rows[i].Visible = true;
            }
            else
            {
                GridView1.Rows[i].Visible = false;
            }
        }
        GridView1.Columns[0].Visible = false; //去掉不需要导出的列
        ExcelHelper.DataToExcel(GridView1);
        GridView1.AllowPaging = true;
        GridView1.AllowSorting = true;
        GridView1.Columns[0].Visible = true;
        GridView1.DataBind();
    }

   //这个空方法必须要有
    public override void VerifyRenderingInServerForm(Control control)
    {
    // Confirms that an HtmlForm control is rendered for   
    }



ExcelHelper类代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Web.UI;
using System.IO;

/// <summary>
///ExcelHelper 的摘要说明
/// </summary>
public class ExcelHelper
{
    public ExcelHelper()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }
    /// <summary>
    /// 以Excel导入
    /// </summary>
    /// <param name="path">返回web服务器上指定的虚拟路径的相对应物理路径</param>
    /// <returns></returns>
    public static DataSet ExcelToData(string path)
    {
        try
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            string strExcel = "select * from  [Sheet1$] ";
            DataSet ds = new DataSet();
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn))
                    adapter.Fill(ds, "data");
                conn.Close();
                return ds;
            }
        }
        catch (Exception)
        {
            return null;

        }
    }
    /// <summary>
    /// 以Excel导出
    /// </summary>
    /// <param name="ctl">控件Id</param>
    /// <returns></returns>
    public static DataSet DataToExcel(Control ctl)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
        HttpContext.Current.Response.Charset = "gb2312";
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                ctl.RenderControl(htw);
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
        return null;
    }

    /// <summary>
    /// 标准Excel表格模板下载
    /// </summary>
    /// <param name="filepath">下载Excel模板的物理路径</param>
    /// <returns></returns>
    public static DataSet ExcelDownLoad(string filepath)
    {
        //下载文件路径
        System.IO.FileInfo file = new System.IO.FileInfo(filepath);
        //判断文件是否存在
        if (file.Exists)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" +HttpContext.Current.Server.UrlEncode(file.Name));                         
            HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
            HttpContext.Current.Response.ContentType = "application/octet-stream";
            HttpContext.Current.Response.WriteFile(file.FullName);//将文件写入HTTP响应流中
            HttpContext.Current.Response.End();
        }
        else
        {
            HttpContext.Current.Response.Write("<script> alert('指定模板文件不存在！');</script>");
        }
        return null;
    }
}