Default.aspx
View Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>无标题页</title> <style type="text/css"> .style1 { height: 16px; } .style3 { height: 23px; } </style> </head> <body> <form id="form1" runat="server"> <div> </div> <table align="center" border="1" bordercolor="honeydew" cellpadding="0" cellspacing="0"> <tr> <td style="FONT-SIZE: 9pt; COLOR: #ff0000; BACKGROUND-COLOR: #ff9933; TEXT-ALIGN: center" class="style1"> </td> <td colspan="2" style="FONT-SIZE: 9pt; COLOR: #ffffff; HEIGHT: 16px; BACKGROUND-COLOR: #ff9933; TEXT-ALIGN: center"> 将Excel数据写入Access数据库中</td> </tr> <tr> <td style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center"> </td> <td style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center"> <iframe id="I1" name="I1" scrolling="yes" src="学生成绩.xls" style="WIDTH: 407px; HEIGHT: 280px"></iframe> </td> <td style="WIDTH: 190px; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center"> <asp:GridView ID="GridView1" runat="server" CellPadding="4" Font-Size="9pt" ForeColor="#333333" GridLines="None" Width="228px"> <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#FFFBD6" ForeColor="#333333" /> <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" /> <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" /> <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> </td> </tr> <tr> <td style="BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center" class="style3"> </td> <td style="HEIGHT: 23px; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center" valign="top"> <asp:Button ID="Button3" runat="server" Font-Size="9pt" onclick="Button1_Click" Text="Excel数据写入Access数据库中" /> <asp:Label ID="Label1" runat="server" Text="Label" Visible="False" style="font-size: x-small"></asp:Label> </td> <td style="WIDTH: 190px; HEIGHT: 23px; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center"> <asp:Button ID="Button2" runat="server" Font-Size="9pt" onclick="Button2_Click" Text="数据库中显示Excel数据" /> </td> </tr> <tr> <td> </td> </tr> </table> </form> </body> </html>
Default.aspx.cs
View Code
using System; using System.Configuration; using System.Data; using System.Linq; 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; using System.Data.OleDb; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } public OleDbConnection CreateCon() { string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Server.MapPath("UserScore.mdb") + ";User Id=admin;Password=;"; OleDbConnection odbc = new OleDbConnection(strconn); return odbc; } protected void Button1_Click(object sender, EventArgs e) { //定义Excel列表 string StyleSheet = "Sheet1"; //调用自定义LoadData方法，将Excel文件中数据读到ASPNET页面中 LoadData(StyleSheet); //定义查询的SQL语句 string sql = "select ID,用户姓名,试卷,成绩,考试时间 from Score"; //创建Oledb数据库连接 OleDbConnection con = CreateCon(); con.Open();//打开数据库连接 OleDbCommand com = new OleDbCommand(sql, con); //开始事务 OleDbTransaction tran = con.BeginTransaction(); com.Transaction = tran; //创建适配器 OleDbDataAdapter da = new OleDbDataAdapter(com); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); //创建DataSet数据集 DataSet ds = new DataSet(); //填充数据集 da.Fill(ds); int curIndex = 0; if (ds.Tables[0].Rows.Count > 0) { curIndex = Convert.ToInt32(ds.Tables[0].Rows[0][0]); } //创建一个内存表 DataTable tb = this.getExcelDate(); string selsql = ""; for (int i = 0; i < tb.Rows.Count; i++) { string UserName = tb.Rows[i][0].ToString(); selsql = "select count(*) from Score where 用户姓名='" + UserName + "'"; } //判断Excel文件中是否已经导入到Access数据库中 if (ExScalar(selsql) > 0) { Label1.Visible = true; Label1.Text = "<script language=javascript>alert('该Excle中的数据已经导入数据库中!');location='Default.aspx';</script>"; } else { //循环读取Excel文件中数据，并添加到Access事先创建好的数据库表中 for (int i = 0; i < tb.Rows.Count; i++) { DataRow dr = ds.Tables[0].NewRow(); dr[0] = ++curIndex; dr[1] = tb.Rows[i][0]; dr[2] = tb.Rows[i][1]; dr[3] = tb.Rows[i][2]; dr[4] = tb.Rows[i][3]; ds.Tables[0].Rows.Add(dr); } try { da.Update(ds);//执行插入操作 tran.Commit();//事务提交 Label1.Visible = true; Label1.Text = "<script language=javascript>alert('数据导入成功！');location='Default.aspx';</script>"; } catch { tran.Rollback();//事务回滚 Label1.Visible = true; Label1.Text = "<script language=javascript>alert('数据导入失败！');location='Default.aspx';</script>"; } finally { con.Close();//关闭数据库连接 } } } protected void Button2_Click(object sender, EventArgs e) { string sqlstr = "select * from Score"; OleDbConnection conn = CreateCon(); conn.Open(); OleDbCommand mycom = new OleDbCommand(sqlstr, conn); OleDbDataReader dr = mycom.ExecuteReader(); dr.Read(); if (dr.HasRows) { GetDataSet(sqlstr); } else { Label1.Visible = true; Label1.Text = "<script language=javascript>alert('数据库中没有数据信息，请先导入再查询！');location='Default.aspx';</script>"; } dr.Close(); conn.Close(); } public DataSet GetDataSet(string sqlstr) { OleDbConnection conn = CreateCon(); OleDbDataAdapter myda = new OleDbDataAdapter(sqlstr, conn); DataSet ds = new DataSet(); myda.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); return ds; } public DataTable getExcelDate() { string strExcelFileName = Server.MapPath("学生成绩.xls"); string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; string sql = "select * from [Sheet1$]"; OleDbDataAdapter da = new OleDbDataAdapter(sql, strcon); DataSet ds = new DataSet(); da.Fill(ds); return ds.Tables[0]; } public void LoadData(string StyleSheet) { //定义数据库连接字符串 m string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + Server.MapPath("学生成绩.xls") + ";Extended Properties=Excel 8.0"; //创建数据库连接 OleDbConnection myConn = new OleDbConnection(strCon); //打开数据链接，得到一个数据集 myConn.Open(); //创建DataSet对象 DataSet myDataSet = new DataSet(); //定义查询的SQL语句 string StrSql = "select * from [" + StyleSheet + "$]"; //创建数据库适配器 OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn); //填充数据集中的数据 myCommand.Fill(myDataSet, "[" + StyleSheet + "$]"); //释放占有的资源 myCommand.Dispose(); //关闭数据库连接 myConn.Close(); } public int ExScalar(string sql) { OleDbConnection conn = CreateCon(); conn.Open(); OleDbCommand com = new OleDbCommand(sql, conn); return Convert.ToInt32(com.ExecuteScalar()); conn.Close(); } }
