ASP.NET Excel 文件导入与导出实例

话不多说直接来干货。。。。

连接类 Excel 通过 OleDb 类进行操作。

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

/// <summary>
/// ExcelHelper 的摘要说明
/// </summary>
public class ExcelHelper
{
    private OleDbConnection conn;
    private string connStr = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'";
    public ExcelHelper(string fileName)
    {
        connStr = string.Format(connStr, fileName);//操作的文件路径早调用对象时给定
        Conn = new OleDbConnection(connStr);
    }

    public OleDbConnection Conn
    {
        get
        {
            return conn;
        }

        set
        {
            conn = value;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
/// <summary>
/// MysqlHelper 的摘要说明
/// </summary>
public class MysqlHelper
{
    private MySqlConnection conn;
    private string connection = "Data Source = 127.0.0.1;User ID = root;Password=123;Database=students;Charset=utf8";
    public MysqlHelper()
    {
        Conn = new MySqlConnection(connection);      
    }

    public MySqlConnection Conn
    {
        get
        {
            return conn;
        }

        set
        {
            conn = value;
        }
    }
}

数据操作类

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

/// <summary>
/// ExcelDao 的摘要说明
/// </summary>
public class ExcelDao
{
    public ExcelDao()
    {
        
    }
    /// <summary>
    /// 查询 Excel 中的数据并存入数据表中
    /// </summary>
    /// <param name="connStr">连接字符串</param>
    /// <param name="sql">sql命令</param>
    /// <returns>返回一个数据表</returns>
    public DataTable FindAll(OleDbConnection ocon, string sql)
    {
        OleDbCommand ocmd = new OleDbCommand(sql, ocon);
        OleDbDataAdapter da = new OleDbDataAdapter(ocmd);
        DataSet ds = new DataSet();
        da.Fill(ds, "aa");
        DataTable dt = ds.Tables["aa"];
        return dt;
    }
    /// <summary>
    /// 将数据插入 Excel 中
    /// </summary>
    /// <param name="sql">sql命令</param>
    public void Insert(OleDbConnection coon,string sql,DataRow item,int i)
    {
        OleDbParameter[] pms = new OleDbParameter[3];
        OleDbCommand ocmd = new OleDbCommand(sql, coon);
        string id = item["学号"].ToString();
        string name = item["姓名"].ToString();
        string sex = item["性别"].ToString();

        pms[0] = new OleDbParameter("@a", id);
        pms[1] = new OleDbParameter("@b", name);
        pms[2] = new OleDbParameter("@c", sex);

        foreach(OleDbParameter iteme in pms)
        {
            ocmd.Parameters.Add(iteme);
        }
        i += ocmd.ExecuteNonQuery();
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

/// <summary>
/// MysqlDao 的摘要说明
/// </summary>
public class MysqlDao
{
    public MysqlDao()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    /// <summary>
    /// 查询数据库中的数据并存入数据表中
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="sql"></param>
    /// <returns>返回一个数据表</returns>
    public DataTable FindAll(MySqlConnection conn,string sql)
    {
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds, "bb");
        DataTable dt = ds.Tables["bb"];
        return dt;
    }
    /// <summary>
    /// 将数据表插入到数据库中
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="sql"></param>
    /// <param name="item"></param>
    /// <param name="i"></param>
    /// <returns></returns>
    public int Insert(MySqlConnection conn,string sql,DataRow item,int i)
    {
        string id = item["学号"].ToString();
        string name = item["姓名"].ToString();
        string sex = item["性别"].ToString();
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        MySqlParameter[] pm = new MySqlParameter[3];

        pm[0] = new MySqlParameter("@a", id);
        pm[1] = new MySqlParameter("@b", name);
        pm[2] = new MySqlParameter("@c", sex);

        foreach (MySqlParameter item1 in pm)
        {
            cmd.Parameters.Add(item1);
        }
        i += cmd.ExecuteNonQuery();
        return i;
    }
    public MySqlDataReader FindAllReader(string sql,MySqlConnection conn)
    {
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        MySqlDataReader dr = cmd.ExecuteReader();
        return dr;
    }
}

前端页面

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Excel.aspx.cs" Inherits="Excel" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <style type="text/css">
        .auto-style1 {
            width: 100%;
        }
        .auto-style2 {
            width: 372px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <table class="auto-style1">
            <tr>
                <td class="auto-style2">Excel文件：<asp:FileUpload ID="FileUpload1" runat="server" />
                    <asp:Button ID="Button1" runat="server" Text="导入" OnClick="Button1_Click" />
                </td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td class="auto-style2">
                    <asp:Button ID="Button2" runat="server" Text="预览数据库中的数据" Width="224px" OnClick="Button2_Click" />
                    <asp:Button ID="Button3" runat="server" Text="导出" OnClick="Button3_Click" />
                </td>
                <td>&nbsp;</td>
            </tr>
        </table>
    
    </div>
        <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="4" Width="262px" ForeColor="Black" GridLines="Horizontal">
            <FooterStyle BackColor="#CCCC99" ForeColor="Black" />
            <HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
            <SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F7F7F7" />
            <SortedAscendingHeaderStyle BackColor="#4B4B4B" />
            <SortedDescendingCellStyle BackColor="#E5E5E5" />
            <SortedDescendingHeaderStyle BackColor="#242121" />
        </asp:GridView>
    </form>
</body>
</html>

后台实现代码

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

public partial class Excel : System.Web.UI.Page
{
    ExcelHelper ehelper;
    MysqlHelper mhelper;
    protected void Page_Load(object sender, EventArgs e)
    {
        GridDataSource();
        string fileName = Server.MapPath("Down/学生.xlsx");
        ehelper = new ExcelHelper(fileName);
    }
    /// <summary>
    /// 预览数据库中的数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button2_Click(object sender, EventArgs e)
    {
        GridDataSource();
    }
    /// <summary>
    /// 加载数据源
    /// </summary>
    protected void GridDataSource()
    {
        mhelper = new MysqlHelper();
        mhelper.Conn.Open();
        string sql = "select * from students";
        MysqlDao dao = new MysqlDao();
        MySqlDataReader dr = dao.FindAllReader(sql, mhelper.Conn);
        GridView1.DataSource = dr;
        GridView1.DataBind();
        dr.Close();
        mhelper.Conn.Close();
    }
    /// <summary>
    /// 导入
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        //1. 选择文件上传到服务器的文件夹
        string type = Path.GetExtension(FileUpload1.FileName);
        string filePath = "Down/"+FileUpload1.FileName;
        //fileName = filePath;
        FileUpload1.SaveAs(Server.MapPath(filePath));

        //2. 把刚上传的excel文件中的内容查询出来
        
        int i = 0;
        string sql = "select * from [Sheet1$]";
        ehelper.Conn.Open();
        ExcelDao dao = new ExcelDao();
        DataTable dt = dao.FindAll(ehelper.Conn, sql);
        if(dt.Equals(null)) Response.Write("dt = null");
        foreach(DataRow item in dt.Rows)
        {//将Excel中的内容存入缓存中，一条条插入mysql数据库中
            if (item.IsNull(0)) continue;
            string msql = "insert into students values(@a,@b,@c)";
            mhelper = new MysqlHelper();
            mhelper.Conn.Open();
            MysqlDao dao1 = new MysqlDao();
            i = dao1.Insert(mhelper.Conn, msql, item, i);
            mhelper.Conn.Close();
        }
        if (i > 1)
            Response.Write("导入成功");
        else
            Response.Write("导入失败");
        ehelper.Conn.Close();   
    }
    /// <summary>
    /// 导出
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button3_Click(object sender, EventArgs e)
    {
        //1. 复制一份模板
        string oldPath = Server.MapPath("Down/学生.xlsx");
        string newPath = Server.MapPath("Down/学生2.xlsx");
        if (System.IO.File.Exists(newPath))
        {
            System.IO.File.Delete(newPath);
        }
        System.IO.File.Copy(oldPath, newPath);
        //2. 查询数据表
        string sql = "select * from students";
        mhelper = new MysqlHelper();
        mhelper.Conn.Open();
        MysqlDao dao = new MysqlDao();
        DataTable dt = dao.FindAll(mhelper.Conn, sql);
        // 3. 将数据插入到表格中
        int i = 0;
        foreach (DataRow item in dt.Rows)
        {
            if (item.IsNull(0)) continue;
            string esql = "insert into [Sheet1$] values(@a,@b,@c)";
            ehelper.Conn.Open();
            ExcelDao dao1 = new ExcelDao();
            dao1.Insert(ehelper.Conn, esql, item, i);
            ehelper.Conn.Close();
        }
        mhelper.Conn.Close();
        // 将数据导出到Excel文件后下载
        Down(newPath);//下载操作
    }
    /// <summary>
    /// 下载导出的文件
    /// </summary>
    /// <param name="newPath"></param>
    protected void Down(string newPath)
    {
        Response.ContentType = "application / vnd.ms - excel";
        Response.AddHeader("content-disposition", "attchment;filename=学生信息.xlsx");
        FileStream fs = new FileStream(newPath, FileMode.Open, FileAccess.Read);
        Stream st = Response.OutputStream;
        byte[] bt = new byte[102400];//100k位单位减轻服务器压力
        while (true)
        {
            int len = fs.Read(bt, 0, bt.Length);
            if (len == 0) break;
            st.Write(bt, 0, len);
            Response.Flush();
        }
        st.Close();
        fs.Close();
        Response.End();
    }
}

这样就可以达到对 Excel 文件导入与导出的效果了。这只是简单的实现，逻辑不完全，欢迎大家指正！