通过Microsoft.Jet.OLEDB.4.0 方式可以实现使用ADO.NET访问Excel的目的,其转换流程为:
Web.config文件
- <?xml version="1.0"?>
- <!--
- 注意: 除了手动编辑此文件以外,您还可以使用
- Web 管理工具来配置应用程序的设置。可以使用 Visual Studio 中的
- “网站”->“Asp.Net 配置”选项。
- 设置和注释的完整列表在
- machine.config.comments 中,该文件通常位于
- /Windows/Microsoft.Net/Framework/v2.x/Config 中
- -->
- <configuration>
- <appSettings/>
- <connectionStrings>
- <add name="ExcelConnection" connectionString="Data Source=C:/Inetpub/wwwroot/ExcelToOracle/ExcelToOracle.xls;Extended Properties=Excel 8.0;Provider=Microsoft.Jet.OLEDB.4.0"/>
- <add name="OracleConnection" connectionString="Data Source=orcl_202.201.244.121;Persist Security Info=True;User ID=scott;Pwd=sde;Unicode=True" providerName="System.Data.OracleClient"/>
- </connectionStrings>
- <system.web>
- <!--
- 设置 compilation debug="true" 将调试符号插入
- 已编译的页面中。但由于这会
- 影响性能,因此只在开发过程中将此值
- 设置为 true。
- -->
- <compilation debug="true">
- <assemblies>
- <add assembly="System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/></assemblies></compilation>
- <!--
- 通过 <authentication> 节可以配置 ASP.NET 使用的
- 安全身份验证模式,
- 以标识传入的用户。
- -->
- <authentication mode="Windows"/>
- <!--
- 如果在执行请求的过程中出现未处理的错误,
- 则通过 <customErrors> 节可以配置相应的处理步骤。具体说来,
- 开发人员通过该节可以配置
- 要显示的 html 错误页
- 以代替错误堆栈跟踪。
- <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
- <error statusCode="403" redirect="NoAccess.htm" />
- <error statusCode="404" redirect="FileNotFound.htm" />
- </customErrors>
- -->
- </system.web>
- </configuration>
前台代码:
- <%@ 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 runat="server">
- <title>将Excel数据写入到Oracle数据库中</title>
- </head>
- <body>
- <form id="form1" runat="server">
- <div align="center">
- <table align="center" cellpadding="0" cellspacing="0" border="1" bordercolor="honeydew">
- <tr>
- <td style="width: 600px" colspan="2" bgcolor="lime">
- <strong>将EXCEL中的数据导入到Oracle数据库中示例 </strong>
- </td>
- </tr>
- <tr>
- <td>
- 测试Excel中的数据是否读取成功
- <asp:DataGrid ID="DataGrid1" runat="server">
- </asp:DataGrid>
- </td>
- <td style="width: 100px; height: 252px;">
- 测试Excel中的数据是否成功导入到Oracle数据中
- <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
- <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="width: 100px; height: 19px;">
- <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Excel数据写入Oracle中" />
- <asp:Label ID="Label1" runat="server" Width="232px" Visible="False">没有成功导入所有数据或是有部分数据重复!</asp:Label></td>
- <td style="width: 100px; height: 19px;">
- <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="Oracle中显示Excel数据" /></td>
- </tr>
- </table>
- </div>
- </form>
- </body>
- </html>
后台代码:
- using System;
- using System.Data;
- using System.Configuration;
- using System.Web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Web.UI.HtmlControls;
- using System.Data.OracleClient;
- using System.Data.OleDb;
- public partial class _Default : System.Web.UI.Page
- {
- protected void Page_Load(object sender, EventArgs e)
- {
- }
- public void LoadData(string StyleSheet)
- {
- string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["ExcelConnection"].ConnectionString;//Excel的连接字符串
- OleDbConnection myConn = new OleDbConnection(strCon);//创建一个OLEDBC的链接
- myConn.Open();//打开数据库的连接,得到一个数据集
- DataSet myDataSet = new DataSet();
- string StrSql = "Select * From [" + StyleSheet + "$]";
- OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn);
- myCommand.Fill(myDataSet, "[" + StyleSheet + "$]");
- myCommand.Dispose();
- DataTable dt = myDataSet.Tables["[" + StyleSheet + "$]"];
- myConn.Close();//关闭数据库连接
- myCommand.Dispose();
- DataGrid1.DataSource = dt.DefaultView;//用来测试Excel中的数据是否读取成功
- DataGrid1.DataBind();
- string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString;
- OracleConnection conn = new OracleConnection(strConn);
- for (int j = 0; j < dt.Rows.Count; j++)
- {
- conn.Open();
- OracleCommand mycom = new OracleCommand("Select count (*) from Usersdb where UserID='" + dt.Rows[j][0].ToString() + "'", conn);
- int count = Convert.ToInt32(mycom.ExecuteScalar());
- if (count > 0)
- {
- Response.Write("<script>alert('对不起!您已经转换了相同的数据!');</script>");
- }
- else
- {
- string UserID = dt.Rows[j][0].ToString();
- string UserName = dt.Rows[j][1].ToString();
- string Title = dt.Rows[j][2].ToString();
- string Sal = dt.Rows[j][3].ToString();
- string strSql = "Insert Into Usersdb(UserID,UserName,Title,Sal) Values('" + UserID + "','" + UserName + "','" + Title + "','" + Sal + "')";
- OracleCommand comm = new OracleCommand(strSql, conn);
- comm.ExecuteNonQuery();
- if (j == dt.Rows.Count - 1)
- {
- Label1.Visible = true;
- Label1.Text = "所有数据成功导入!";
- }
- else
- {
- Label1.Visible = true;
- }
- }
- conn.Close();
- }
- }
- protected void Button1_Click(object sender, EventArgs e)
- {
- string StyleSheet = "Sheet1";
- LoadData(StyleSheet);
- }
- protected void Button2_Click(object sender, EventArgs e)
- {
- string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString;
- string sqlstr = "Select * From Usersdb";
- OracleConnection conn = new OracleConnection(strConn);
- OracleDataAdapter myda = new OracleDataAdapter(sqlstr, strConn);
- DataSet ds = new DataSet();
- conn.Open();
- myda.Fill(ds, "Usersdb");
- GridView1.DataSource = ds;
- GridView1.DataBind();
- conn.Close();
- }
- }
注:要添加System.Data.OleDB和System.Data.OracleClient命名空间