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

 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 ExcelHelper11 {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 Conn21 ????{22 ????????get23 ????????{24 ????????????return conn;25 ????????}26 27 ????????set28 ????????{29 ????????????conn = value;30 ????????}31 ????}32 }

 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 MysqlHelper10 {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 Conn19 ????{20 ????????get21 ????????{22 ????????????return conn;23 ????????}24 25 ????????set26 ????????{27 ????????????conn = value;28 ????????}29 ????}30 }

 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 ExcelDao12 {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 }

 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 MysqlDao12 {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 }

 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>

 ?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 ????????????mhelper.Conn.Close(); 76 ????????} 77 ????????if (i > 1) 78 ????????????Response.Write("导入成功"); 79 ????????else 80 ????????????Response.Write("导入失败"); 81 ????????ehelper.Conn.Close(); ???82 ????} 83 ????/// <summary> 84 ????/// 导出 85 ????/// </summary> 86 ????/// <param name="sender"></param> 87 ????/// <param name="e"></param> 88 ????protected void Button3_Click(object sender, EventArgs e) 89 ????{ 90 ????????//1. 复制一份模板 91 ????????string oldPath = Server.MapPath("Down/学生.xlsx"); 92 ????????string newPath = Server.MapPath("Down/学生2.xlsx"); 93 ????????if (System.IO.File.Exists(newPath)) 94 ????????{ 95 ????????????System.IO.File.Delete(newPath); 96 ????????} 97 ????????System.IO.File.Copy(oldPath, newPath); 98 ????????//2. 查询数据表 99 ????????string sql = "select * from students";100 ????????mhelper = new MysqlHelper();101 ????????mhelper.Conn.Open();102 ????????MysqlDao dao = new MysqlDao();103 ????????DataTable dt = dao.FindAll(mhelper.Conn, sql);104 ????????// 3. 将数据插入到表格中105 ????????int i = 0;106 ????????foreach (DataRow item in dt.Rows)107 ????????{108 ????????????if (item.IsNull(0)) continue;109 ????????????string esql = "insert into [Sheet1$] values(@a,@b,@c)";110 ????????????ehelper.Conn.Open();111 ????????????ExcelDao dao1 = new ExcelDao();112 ????????????dao1.Insert(ehelper.Conn, esql, item, i);113 ????????????ehelper.Conn.Close();114 ????????}115 ????????mhelper.Conn.Close();116 ????????// 将数据导出到Excel文件后下载117 ????????Down(newPath);//下载操作118 ????}119 ????/// <summary>120 ????/// 下载导出的文件121 ????/// </summary>122 ????/// <param name="newPath"></param>123 ????protected void Down(string newPath)124 ????{125 ????????Response.ContentType = "application / vnd.ms - excel";126 ????????Response.AddHeader("content-disposition", "attchment;filename=学生信息.xlsx");127 ????????FileStream fs = new FileStream(newPath, FileMode.Open, FileAccess.Read);128 ????????Stream st = Response.OutputStream;129 ????????byte[] bt = new byte[102400];//100k位单位减轻服务器压力130 ????????while (true)131 ????????{132 ????????????int len = fs.Read(bt, 0, bt.Length);133 ????????????if (len == 0) break;134 ????????????st.Write(bt, 0, len);135 ????????????Response.Flush();136 ????????}137 ????????st.Close();138 ????????fs.Close();139 ????????Response.End();140 ????}141 }