excel导入数据库sql中
public partial class Default2 : System.Web.UI.Page{
string strConn = "Data Source=.;DataBase=Database;Uid=sa;Pwd=sa"; //链接SQL数据库
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from suserss", cn);
DataSet ds = new DataSet();
sda.Fill(ds, "suserss");
this.GridView1.DataSource = ds.Tables["suserss"];
this.GridView1.DataKeyNames = new string[] { "gasId" };
this.GridView1.DataBind();
}
/// <summary>
/// 查询EXCEL电子表格添加到DATASET
/// </summary>
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'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from ", conn);
odda.Fill(ds, table);
return ds;
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls=System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName; //获取Execle文件名DateTime日期函数
// string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
string savePath = Server.MapPath(("upfiles/") + filename);
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExecleDs(savePath, filename); //调用自定义方法
DataRow[] dr = ds.Tables.Select(); //定义一个DataRow数组
int rowsnum = ds.Tables.Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
string gasId = dr["车辆ID"].ToString();
string carName = dr["车辆"].ToString();//日期 excel列名【名称不能变,否则就会出错】
string gasBeginTime = dr["起始加油时间"].ToString();//编号 列名 以下类似
decimal beginLc = Convert.ToDecimal(dr["起始里程"].ToString());
string gasEndTime = dr["最后加油时间"].ToString();
decimal endLc = Convert.ToDecimal(dr["最后里程"].ToString());
decimal gLs = Convert.ToDecimal(dr["公里数"].ToString());
decimal gasNum = Convert.ToDecimal(dr["加油量"].ToString());
decimal gasHao = Convert.ToDecimal(dr["油耗量"].ToString());
string yearMonth = dr["年月"].ToString();
string sqlcheck = "select count(*) from suserss where carName='" + carName + "'And yearMonth='" + yearMonth + "'";//检查车辆是否存在
SqlCommand sqlcmd = new SqlCommand(sqlcheck,cn);
int count = Convert.ToInt32(sqlcmd.ExecuteScalar());
if (count < 1)
{
string insertstr = "insert into suserss (gasId,carName, gasBeginTime, beginLc, gasEndTime, endLc, gLs, gasNum, gasHao, yearMonth) values('" + gasId + "','" + carName + "','" + gasBeginTime + "'," + beginLc + ",'" + gasEndTime + "'," + endLc + "," + gLs + "," + gasNum + "," + gasHao + ",'" + yearMonth + "')";
SqlCommand cmd = new SqlCommand(insertstr, cn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
else
{
Response.Write("<script>alert('内容重复！禁止导入');location='default.aspx'</script></script> ");
continue;
}
}
Response.Write("<script>alert('Excle表导入成功!');location='default.aspx'</script>");
}
cn.Close();
}
}
请大家帮忙一下，这个代码导入增加了的数据的excel，数据可以导入到excel中，但是跳出的对话框是'内容重复！禁止导入'。请问这个能不能帮忙改一下。 车辆不存在， 就是你excel中有这个车 但是在数据库中没有 将excel中的数据导入到数据库中是可以导入的，但是在同一个excel中增添数据再次导入时，数据也是可以导入到，数据库中的数据都是存在的，但是就是再次导入时跳出的对话框是'内容重复！禁止导入'。这个一概怎么修改一下？
页:
[1]