ASP.NETExcel文件导入与导出实例

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

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

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

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using System.Data.OleDb;
 6 
 7 /// <summary>
 8 /// ExcelHelper 的摘要说明
 9 /// </summary>
10 public class ExcelHelper
11 {
12     private OleDbConnection conn;
13     private string connStr = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'";
14     public ExcelHelper(string fileName)
15     {
16         connStr = string.Format(connStr, fileName);//操作的文件路径早调用对象时给定
17         Conn = new OleDbConnection(connStr);
18     }
19 
20     public OleDbConnection Conn
21     {
22         get
23         {
24             return conn;
25         }
26 
27         set
28         {
29             conn = value;
30         }
31     }
32 }

ExcelHelper

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using MySql.Data.MySqlClient;
 6 /// <summary>
 7 /// MysqlHelper 的摘要说明
 8 /// </summary>
 9 public class MysqlHelper
10 {
11     private MySqlConnection conn;
12     private string connection = "Data Source = 127.0.0.1;User ID = root;Password=123;Database=students;Charset=utf8";
13     public MysqlHelper()
14     {
15         Conn = new MySqlConnection(connection);      
16     }
17 
18     public MySqlConnection Conn
19     {
20         get
21         {
22             return conn;
23         }
24 
25         set
26         {
27             conn = value;
28         }
29     }
30 }

MysqlHelper

数据操作类

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using System.Data.OleDb;
 6 using System.Data;
 7 
 8 /// <summary>
 9 /// ExcelDao 的摘要说明
10 /// </summary>
11 public class ExcelDao
12 {
13     public ExcelDao()
14     {
15         
16     }
17     /// <summary>
18     /// 查询 Excel 中的数据并存入数据表中
19     /// </summary>
20     /// <param name="connStr">连接字符串</param>
21     /// <param name="sql">sql命令</param>
22     /// <returns>返回一个数据表</returns>
23     public DataTable FindAll(OleDbConnection ocon, string sql)
24     {
25         OleDbCommand ocmd = new OleDbCommand(sql, ocon);
26         OleDbDataAdapter da = new OleDbDataAdapter(ocmd);
27         DataSet ds = new DataSet();
28         da.Fill(ds, "aa");
29         DataTable dt = ds.Tables["aa"];
30         return dt;
31     }
32     /// <summary>
33     /// 将数据插入 Excel 中
34     /// </summary>
35     /// <param name="sql">sql命令</param>
36     public void Insert(OleDbConnection coon,string sql,DataRow item,int i)
37     {
38         OleDbParameter[] pms = new OleDbParameter[3];
39         OleDbCommand ocmd = new OleDbCommand(sql, coon);
40         string id = item["学号"].ToString();
41         string name = item["姓名"].ToString();
42         string sex = item["性别"].ToString();
43 
44         pms[0] = new OleDbParameter("@a", id);
45         pms[1] = new OleDbParameter("@b", name);
46         pms[2] = new OleDbParameter("@c", sex);
47 
48         foreach(OleDbParameter iteme in pms)
49         {
50             ocmd.Parameters.Add(iteme);
51         }
52         i += ocmd.ExecuteNonQuery();
53     }
54 }

ExcelDao

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using System.Data;
 6 using MySql.Data.MySqlClient;
 7 
 8 /// <summary>
 9 /// MysqlDao 的摘要说明
10 /// </summary>
11 public class MysqlDao
12 {
13     public MysqlDao()
14     {
15         //
16         // TODO: 在此处添加构造函数逻辑
17         //
18     }
19     /// <summary>
20     /// 查询数据库中的数据并存入数据表中
21     /// </summary>
22     /// <param name="conn"></param>
23     /// <param name="sql"></param>
24     /// <returns>返回一个数据表</returns>
25     public DataTable FindAll(MySqlConnection conn,string sql)
26     {
27         MySqlCommand cmd = new MySqlCommand(sql, conn);
28         MySqlDataAdapter da = new MySqlDataAdapter(cmd);
29         DataSet ds = new DataSet();
30         da.Fill(ds, "bb");
31         DataTable dt = ds.Tables["bb"];
32         return dt;
33     }
34     /// <summary>
35     /// 将数据表插入到数据库中
36     /// </summary>
37     /// <param name="conn"></param>
38     /// <param name="sql"></param>
39     /// <param name="item"></param>
40     /// <param name="i"></param>
41     /// <returns></returns>
42     public int Insert(MySqlConnection conn,string sql,DataRow item,int i)
43     {
44         string id = item["学号"].ToString();
45         string name = item["姓名"].ToString();
46         string sex = item["性别"].ToString();
47         MySqlCommand cmd = new MySqlCommand(sql, conn);
48         MySqlParameter[] pm = new MySqlParameter[3];
49 
50         pm[0] = new MySqlParameter("@a", id);
51         pm[1] = new MySqlParameter("@b", name);
52         pm[2] = new MySqlParameter("@c", sex);
53 
54         foreach (MySqlParameter item1 in pm)
55         {
56             cmd.Parameters.Add(item1);
57         }
58         i += cmd.ExecuteNonQuery();
59         return i;
60     }
61     public MySqlDataReader FindAllReader(string sql,MySqlConnection conn)
62     {
63         MySqlCommand cmd = new MySqlCommand(sql, conn);
64         MySqlDataReader dr = cmd.ExecuteReader();
65         return dr;
66     }
67 }

MysqlDao

前端页面

 1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Excel.aspx.cs" Inherits="Excel" %>
 2 
 3 <!DOCTYPE html>
 4 
 5 <html xmlns="http://www.w3.org/1999/xhtml">
 6 <head runat="server">
 7 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
 8     <title></title>
 9     <style type="text/css">
10         .auto-style1 {
11             width: 100%;
12         }
13         .auto-style2 {
14             width: 372px;
15         }
16     </style>
17 </head>
18 <body>
19     <form id="form1" runat="server">
20     <div>
21     
22         <table class="auto-style1">
23             <tr>
24                 <td class="auto-style2">Excel文件：<asp:FileUpload ID="FileUpload1" runat="server" />
25                     <asp:Button ID="Button1" runat="server" Text="导入" OnClick="Button1_Click" />
26                 </td>
27                 <td>&nbsp;</td>
28             </tr>
29             <tr>
30                 <td class="auto-style2">
31                     <asp:Button ID="Button2" runat="server" Text="预览数据库中的数据" Width="224px" OnClick="Button2_Click" />
32                     <asp:Button ID="Button3" runat="server" Text="导出" OnClick="Button3_Click" />
33                 </td>
34                 <td>&nbsp;</td>
35             </tr>
36         </table>
37     
38     </div>
39         <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="4" Width="262px" ForeColor="Black" GridLines="Horizontal">
40             <FooterStyle BackColor="#CCCC99" ForeColor="Black" />
41             <HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
42             <PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
43             <SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
44             <SortedAscendingCellStyle BackColor="#F7F7F7" />
45             <SortedAscendingHeaderStyle BackColor="#4B4B4B" />
46             <SortedDescendingCellStyle BackColor="#E5E5E5" />
47             <SortedDescendingHeaderStyle BackColor="#242121" />
48         </asp:GridView>
49     </form>
50 </body>
51 </html>

View Code

后台实现代码

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 using System.Web.UI;
  6 using System.Web.UI.WebControls;
  7 using MySql.Data.MySqlClient;
  8 using System.Data.OleDb;
  9 using System.Data;
 10 using System.IO;
 11 
 12 public partial class Excel : System.Web.UI.Page
 13 {
 14     ExcelHelper ehelper;
 15     MysqlHelper mhelper;
 16     protected void Page_Load(object sender, EventArgs e)
 17     {
 18         GridDataSource();
 19         string fileName = Server.MapPath("Down/学生.xlsx");
 20         ehelper = new ExcelHelper(fileName);
 21     }
 22     /// <summary>
 23     /// 预览数据库中的数据
 24     /// </summary>
 25     /// <param name="sender"></param>
 26     /// <param name="e"></param>
 27     protected void Button2_Click(object sender, EventArgs e)
 28     {
 29         GridDataSource();
 30     }
 31     /// <summary>
 32     /// 加载数据源
 33     /// </summary>
 34     protected void GridDataSource()
 35     {
 36         mhelper = new MysqlHelper();
 37         mhelper.Conn.Open();
 38         string sql = "select * from students";
 39         MysqlDao dao = new MysqlDao();
 40         MySqlDataReader dr = dao.FindAllReader(sql, mhelper.Conn);
 41         GridView1.DataSource = dr;
 42         GridView1.DataBind();
 43         dr.Close();
 44         mhelper.Conn.Close();
 45     }
 46     /// <summary>
 47     /// 导入
 48     /// </summary>
 49     /// <param name="sender"></param>
 50     /// <param name="e"></param>
 51     protected void Button1_Click(object sender, EventArgs e)
 52     {
 53         //1. 选择文件上传到服务器的文件夹
 54         string type = Path.GetExtension(FileUpload1.FileName);
 55         string filePath = "Down/"+FileUpload1.FileName;
 56         //fileName = filePath;
 57         FileUpload1.SaveAs(Server.MapPath(filePath));
 58 
 59         //2. 把刚上传的excel文件中的内容查询出来
 60         
 61         int i = 0;
 62         string sql = "select * from [Sheet1$]";
 63         ehelper.Conn.Open();
 64         ExcelDao dao = new ExcelDao();
 65         DataTable dt = dao.FindAll(ehelper.Conn, sql);
 66         if(dt.Equals(null)) Response.Write("dt = null");
 67         foreach(DataRow item in dt.Rows)
 68         {//将Excel中的内容存入缓存中，一条条插入mysql数据库中
 69             if (item.IsNull(0)) continue;
 70             string msql = "insert into students values(@a,@b,@c)";
 71             mhelper = new MysqlHelper();
 72             mhelper.Conn.Open();
 73             MysqlDao dao1 = new MysqlDao();
 74             i = dao1.Insert(mhelper.Conn, msql, item, i);
 75  
                       
                    
                     
                      



鲜花




握手




雷人




路过




鸡蛋