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="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>
前台代码:
- <body>
- <form id="form1" runat="server">
- <div>
- <input id="FileUpLoad" type="file" runat="server" />
- <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="提交" Width="72px" /><br />
- 测试是否能读出Excel中的数据<br />
- <asp:GridView ID="GridView1" runat="server">
- </asp:GridView>
- </div>
- </form>
- </body>
后台代码:
- 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)
- {
- }
- protected void Button1_Click(object sender, EventArgs e)
- {
- string filePath="";
- if (FileUpLoad.PostedFile.FileName == "")
- {
- Response.Write("<Script Language=JavaScript>alert('请选择要上传的文件!');</Script>");
- }
- else
- {
- filePath=FileUpLoad.PostedFile.FileName;
- string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["ExcelConnection"].ConnectionString + ";Data Source = "+filePath+";";//Excel的连接字符串
- OleDbConnection myConn = new OleDbConnection(strCon);//创建一个OLEDBC的连接,即建立Excel的连接
- myConn.Open();//打开Excel的连接,得到一个数据集
- DataSet myDataSet = new DataSet();
- string sqlExcel = "Select * From [Sheet1$]";
- //OleDbCommand myCommand = new OleDbCommand(sqlExcel, myConn);
- //OleDbDataAdapter myAdapter = new OleDbDataAdapter();
- //myAdapter.SelectCommand = myCommand;
- OleDbDataAdapter myAdapter = new OleDbDataAdapter(sqlExcel, myConn);
- myAdapter.Fill(myDataSet, "StyleSheet");
- myAdapter.Dispose();
- DataTable dt = myDataSet.Tables["StyleSheet"];
- myConn.Close();//关闭数据库连接
- GridView1.DataSource = dt.DefaultView;//测试代码,用来测试是否能读出Excel上面的数据
- GridView1.DataBind();
- //Oracle数据库连接
- DataView myView = new DataView(dt);
- string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString;
- OracleConnection conn = new OracleConnection();
- conn.ConnectionString = strConn;
- OracleCommand cmd = conn.CreateCommand();
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.CommandText = "INSERT_USERSDB";
- int count = 0;//记录出错的条数
- try
- {
- foreach (DataRowView myDrv in myView)
- {
- count++;//要关闭上一次的Oracle连接
- if (conn.State.ToString() != "Closed")
- conn.Close();
- cmd.Parameters.Clear();//每一次到要清空所有的cmd参数
- //执行存储过程,获得参数
- OracleParameter paraUserID = cmd.Parameters.Add("p_userid", OracleType.VarChar);
- OracleParameter paraUserName = cmd.Parameters.Add("p_username", OracleType.VarChar);
- OracleParameter paraTitle = cmd.Parameters.Add("p_title", OracleType.VarChar);
- OracleParameter paraSal = cmd.Parameters.Add("p_sal", OracleType.VarChar);
- //表示是输出参数
- paraUserName.Direction = ParameterDirection.Input;
- paraUserID.Direction = ParameterDirection.Input;
- paraTitle.Direction = ParameterDirection.Input;
- paraSal.Direction = ParameterDirection.Input;
- //参数赋值
- paraUserID.Value = myDrv[0].ToString().Trim();
- paraUserName.Value = myDrv[1].ToString().Trim();
- paraTitle.Value = myDrv[2].ToString().Trim();
- paraSal.Value = myDrv[3].ToString().Trim();
- conn.Open();
- cmd.ExecuteNonQuery();//写入Oracle数据库
- }
- conn.Close();
- }
- catch
- {
- Page.Response.Write("alert('第"+count.ToString()+"条数据出错!');");
- }
- }
- }
- }
存储过程:
- CREATE OR REPLACE PROCEDURE "SCOTT"."INSERT_USERSDB" (p_userid
- usersdb.userid%type,
- p_username usersdb.username%type,
- p_title usersdb.title%type,
- p_sal usersdb.sal%type)
- as
- begin
- insert into usersdb values(p_userid,p_username,p_title,p_sal);
- end insert_usersdb;
Excel表中的内容:
UserID | UserName | TITLE | SAL |
7369 | SMITH | CLERK | 800 |
7499 | ALLEN | SALESMAN | 1600 |
7521 | WARD | SALESMAN | 1250 |
7566 | JONES | MANAGER | 2975 |
7654 | MARTIN | SALESMAN | 1250 |
7698 | BLAKE | MANAGER | 2850 |