小弟是新手,试着做点东东,现在遇到一个问题,通过asp.net把固定的excel导入到oracle表中。不知道如何实现,在网上搜到一段代码数据库中表设计CREATE TABLE CCOP (
A CHAR(20) NULL,
B CHAR(20) NULL,
C CHAR(20) NULL,
D CHAR(20) NULL
)EXCEL的第一行前四列列名为 A B C Dusing System;
using System.Collections;
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.OracleClient;
using System.Data.OleDb;public partial class TestExcel : System.Web.UI.Page
{
CommonClass com = new CommonClass();
protected void Page_Load(object sender, EventArgs e)
{ }
public DataSet ExecleDs(string filenameurl,string table)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" +filenameurl+ "";
//Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=\"Excel 8.0;IMEX=1\";" + "data source=" + filenameurl; OleDbConnection conn = new OleDbConnection(connStr);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]",conn);
DataSet ds = new DataSet();
odda.Fill(ds,table);
return ds;
}
protected void Button1_Click(object sender, EventArgs e)
{ if (FileUpload1.HasFile == false)
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls=System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
string error = null;
OracleConnection cn = com.GetConnection();
cn.Open();
string strpath = FileUpload1.PostedFile.FileName.ToString(); //获取Execle文件路径
string filename = FileUpload1.FileName; //获取Execle文件名
DataSet ds = ExecleDs(strpath,filename);
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
string A = dr[i]["A"].ToString();
string B = dr[i]["B"].ToString();
string C = dr[i]["C"].ToString();
string D = dr[i]["D"].ToString();
string sqlcheck = "select count(*) from CCOP where A='" + A + "'And B='" + B + "'"; //检查用户是否存在
bool ch = check(sqlcheck);
if (ch == true)
{
string insertstr = "insert into CCOP(A,B,C,D) values('" + A + "','" + B + "','" + C + "','" + D + "')";
OracleCommand cmd = new OracleCommand(insertstr,cn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('创建用户:"+ex.Message+"')</script>");
}
}
else
{
error += "<em style='color:red;font-sixe:25px'>"+A+"</em>用户已存在,此行记录无法插入!请修改用户再进行插入 <br>"; //若用户存在,将已存在用户信息打出,并提示此用户无法插入
continue;
}
}
Response.Write("<script>alert('Excle表导入成功!')</script>");
Label1.Text = error;
}
cn.Close();
}
protected bool check(string sqlcheck) {
bool g; DataSet ds= com.GetDataSet(sqlcheck,"checkuss");
string tmp = ds.Tables["checkuss"].Rows[0].ItemArray[0].ToString(); if (tmp == "0")
{
g = true;
}
else
{
g = false;
}
return g;
}
}但是执行过程中提示 CommonClass com = new CommonClass();出错,缺少using或是不存这个命令空间,请各位指点一下报错行是何意,另外,有可用的完整实例源码,小弟更是感激不尽。
A CHAR(20) NULL,
B CHAR(20) NULL,
C CHAR(20) NULL,
D CHAR(20) NULL
)EXCEL的第一行前四列列名为 A B C Dusing System;
using System.Collections;
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.OracleClient;
using System.Data.OleDb;public partial class TestExcel : System.Web.UI.Page
{
CommonClass com = new CommonClass();
protected void Page_Load(object sender, EventArgs e)
{ }
public DataSet ExecleDs(string filenameurl,string table)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" +filenameurl+ "";
//Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=\"Excel 8.0;IMEX=1\";" + "data source=" + filenameurl; OleDbConnection conn = new OleDbConnection(connStr);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]",conn);
DataSet ds = new DataSet();
odda.Fill(ds,table);
return ds;
}
protected void Button1_Click(object sender, EventArgs e)
{ if (FileUpload1.HasFile == false)
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls=System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
string error = null;
OracleConnection cn = com.GetConnection();
cn.Open();
string strpath = FileUpload1.PostedFile.FileName.ToString(); //获取Execle文件路径
string filename = FileUpload1.FileName; //获取Execle文件名
DataSet ds = ExecleDs(strpath,filename);
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
string A = dr[i]["A"].ToString();
string B = dr[i]["B"].ToString();
string C = dr[i]["C"].ToString();
string D = dr[i]["D"].ToString();
string sqlcheck = "select count(*) from CCOP where A='" + A + "'And B='" + B + "'"; //检查用户是否存在
bool ch = check(sqlcheck);
if (ch == true)
{
string insertstr = "insert into CCOP(A,B,C,D) values('" + A + "','" + B + "','" + C + "','" + D + "')";
OracleCommand cmd = new OracleCommand(insertstr,cn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('创建用户:"+ex.Message+"')</script>");
}
}
else
{
error += "<em style='color:red;font-sixe:25px'>"+A+"</em>用户已存在,此行记录无法插入!请修改用户再进行插入 <br>"; //若用户存在,将已存在用户信息打出,并提示此用户无法插入
continue;
}
}
Response.Write("<script>alert('Excle表导入成功!')</script>");
Label1.Text = error;
}
cn.Close();
}
protected bool check(string sqlcheck) {
bool g; DataSet ds= com.GetDataSet(sqlcheck,"checkuss");
string tmp = ds.Tables["checkuss"].Rows[0].ItemArray[0].ToString(); if (tmp == "0")
{
g = true;
}
else
{
g = false;
}
return g;
}
}但是执行过程中提示 CommonClass com = new CommonClass();出错,缺少using或是不存这个命令空间,请各位指点一下报错行是何意,另外,有可用的完整实例源码,小弟更是感激不尽。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货