Question

asp.net c# 将excel文件导入到sql中，如果sql中有重复数据，在页面上显示excel重复数据（求源码）

不是用sql的导入导出工具

Answer 1

public static DataTable dt = null;
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
dt = ExcelToDataTable(@"c:\all.xls", "Sheet1");
//Response.Write(dt.Rows.Count.ToString()+"列"+dt.Columns.Count.ToString());
//Response.Write(dt.Rows[0][0].ToString());
labCount.Text = dt.Rows.Count.ToString();
labInfo.Text = dt.Rows[0][0].ToString() + "/"+dt.Rows[0][0].ToString();
}
/// <summary>
/// 从Excel读取数据
/// </summary>
/// <param name="filePath">路径</param>
/// <returns>DataSet</returns>
public DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 5.0;";
string strExcel = string.Format("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
conn.Close();
}
return ds.Tables[strSheetName];
}

protected void bntEap_Click(object sender, EventArgs e)
{
string sql = "";
string tile = "";
int type = 0;
string a = "";
string b = "";
string c = "";
string d = "";
string ee = "";
string f = "";
string rigth = "";
string typeGuid = "";
string kGuid = "0000";
string ok = "";
string uid = "";
string uType = "";
CompanyDataAccess.DataBase db = new CompanyDataAccess.DataBase();
for (int i = 0; i < dt.Rows.Count; i++)
{
//uid=dt.Rows[i][0].ToString();

//sql = string.Format("update t_Course set mc_hot=5 where c_title='{0}'", uid);
//uType = dt.Rows[i][2].ToString();

//sql = string.Format("update t_users set u_type={0} where u_id={1} and u_ca_guid='5510cc5a-2cd5-4468-88f4-071429d76ea6'",uType,uid);
//Response.Write(sql+"<br/>");

////公共分类
//tile = dt.Rows[i][0].ToString();
//a = dt.Rows[i][1].ToString();
//b = dt.Rows[i][2].ToString();
//c=dt.Rows[i][3].ToString();
//d=dt.Rows[i][4].ToString();
//ee=dt.Rows[i][5].ToString();
//f=dt.Rows[i][6].ToString();
//rigth=dt.Rows[i][7].ToString();
//sql = string.Format("insert into T_Video(v_orderName,v_order,v_size,v_kbyte,v_money,v_value,v_address,v_c_guid,v_state,v_ca_guid,v_name,v_count)values('{0}',{1},{2},10,4,4,'{3}','{4}',1,'0','{5}',0)",
// tile,a,c,f,rigth,b);
//Response.Write(sql+"<br/>");

//学员信息
//sql = "insert into T_Users(u_login,u_pwd,u_type,u_ca_guid,u_level,u_trueName,u_pname)values('" + dt.Rows[i][0].ToString() + "','202cb962ac59075b964b07152d234b70'," + dt.Rows[i][3].ToString() + ",'62c27560-94b6-4fdf-8ed1-bb684d551ae4',0,'" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][4].ToString() + "')<br/>";
//sql = "insert into T_Users(u_login,u_pwd,u_occupation,u_periods)values('" + dt.Rows[i][0].ToString() + "','" + Facade.CommonFacade.MD5(dt.Rows[i][1].ToString()) + "','1','1')";
//Response.Write(sql + "<br/>");
#region 综合题库
//string tile = "";
//int type = 0;
//string a = "";
//string b = "";
//string c = "";
//string d = "";
//string rigth = "";
//string typeGuid = "";
//string ok = "";
// CompanyDataAccess.DataBase db = new CompanyDataAccess.DataBase();
typeGuid = dt.Rows[i][1].ToString();
switch (typeGuid.Length)
{
case 1:
typeGuid = "5EB8D599-D585-4C8F-A308-C8C80EC2BFD" + typeGuid;
break;
case 2:
typeGuid = "5EB8D599-D585-4C8F-A308-C8C80EC2BF" + typeGuid;
break;
case 3:
typeGuid = "5EB8D599-D585-4C8F-A308-C8C80EC2B" + typeGuid;
break;
}
tile = dt.Rows[i][2].ToString();
a = dt.Rows[i][3].ToString().Trim();
b = dt.Rows[i][4].ToString().Trim();
c = dt.Rows[i][5].ToString().Trim();
d = dt.Rows[i][6].ToString().Trim();
ee = dt.Rows[i][7].ToString().Trim();
f = dt.Rows[i][8].ToString().Trim();

rigth = dt.Rows[i][9].ToString();
type = int.Parse(dt.Rows[i][10].ToString());

SqlParameter[] Params = new SqlParameter[10] {
new SqlParameter("@title",tile),
new SqlParameter("@type",type),
new SqlParameter("@a",a),
new SqlParameter("@b",b),
new SqlParameter("@c",c),
new SqlParameter("@d",d),
new SqlParameter("@e",ee),
new SqlParameter("@f",f),
new SqlParameter("@rigth",rigth),
new SqlParameter("@upGuid",typeGuid)
};
ok=db.RunProcGetString("stockExamBase",Params);
// create proc stockExamBase
//@title nvarchar(400),
//@type int,
//@a nvarchar(100),
//@b nvarchar(100),
//@c nvarchar(100),
//@d nvarchar(100),
//@rigth varchar(10),
//@upGuid varchar(40)
//as
// insert into t_stockExamBase(seb_title,seb_type,seb_A,seb_B,seb_C,seb_D,seb_right,seb_set_guid)
// values(@title,@type,@a,@b,@c,@d,@rigth,@upGuid)
//go
#endregion
//课程考试
//sql = "insert into t_publicExam(pe_GUID,pe_title,pe_c_guid,pe_values,pe_type,pe_state)values('"+dt.Rows[i][0].ToString()+"','"+dt.Rows[i][2].ToString()+"','"+dt.Rows[i][1].ToString()+"',10,0,1)</br>";
#region 课程考试试题

//typeGuid = dt.Rows[i][0].ToString();
//tile = dt.Rows[i][1].ToString();
//a = dt.Rows[i][2].ToString();
//b = dt.Rows[i][3].ToString();
//c = dt.Rows[i][4].ToString();
//d = dt.Rows[i][5].ToString();
//ee = dt.Rows[i][6].ToString();
//f = dt.Rows[i][7].ToString();
//rigth = dt.Rows[i][8].ToString();
//type = int.Parse(dt.Rows[i][9].ToString());
//SqlParameter[] Params = new SqlParameter[13] {
// new SqlParameter("@Peb_GUID",kGuid),
// new SqlParameter("@Peb_title",tile),
// new SqlParameter("@Peb_a",a),
// new SqlParameter("@Peb_b",b),
// new SqlParameter("@Peb_c",c),
// new SqlParameter("@Peb_d",d),
// new SqlParameter("@Peb_e",ee),
// new SqlParameter("@Peb_f",f),
// new SqlParameter("@Peb_rigth",rigth),
// new SqlParameter("@Peb_type",type),
// new SqlParameter("@Peb_pe_guid",typeGuid),
// new SqlParameter("@Peb_value",5),
// new SqlParameter("@type",1),
// };
//ok = db.RunProcGetString("hh_proc_PublicExamBase_add_update", Params);
//Response.Write(ok + "</br>");
#endregion
//解惑一卡通 问题
//sql = "insert into t_occupationQuestion(oq_guid,oq_title,oq_oc_id,oq_periods,oq_order)values('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "',1,1," + i + ")";
//Response.Write(sql + "<br/>");
//一卡通视频
//sql = "insert into T_Video(v_orderName,v_order,v_name,v_size,v_count,v_address,v_oq_guid)values('" + dt.Rows[i][2].ToString() + "'," + dt.Rows[i][3].ToString() + ",'" + dt.Rows[i][1].ToString() + "'," + dt.Rows[i][4].ToString() + ",0,'" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][0].ToString() + "')";
//Response.Write(sql + "<br/>");

//#region 课程信息
//typeGuid = dt.Rows[i][0].ToString();
//tile = dt.Rows[i][1].ToString();
//a = dt.Rows[i][2].ToString();
//b = dt.Rows[i][3].ToString();
//c = dt.Rows[i][4].ToString();

//d = dt.Rows[i][5].ToString();
//ee = dt.Rows[i][6].ToString();
//f = dt.Rows[i][7].ToString();
////rigth = dt.Rows[i][8].ToString();
////type = int.Parse(dt.Rows[i][9].ToString());
//SqlParameter[] Params = new SqlParameter[9] {
// new SqlParameter("@Pc_guid",typeGuid),
// new SqlParameter("@Pc_title",tile),
// new SqlParameter("@Pc_name",a),
// new SqlParameter("@Pc_hot",int.Parse(b)),
// new SqlParameter("@Pc_img",c),
// new SqlParameter("@Pc_text",f),
// new SqlParameter("@Pc_pct_guid",ee),
// new SqlParameter("@Pc_level",int.Parse(d)),
// new SqlParameter("@type",1),
// };
//ok = db.RunProcGetString("hh_proc_PublicCourse_add_update", Params);
//Response.Write(ok + "</br>");
//#endregion

//try
//{

// #region 视频列表
// typeGuid = dt.Rows[i][0].ToString();
// tile = dt.Rows[i][1].ToString();
// a = dt.Rows[i][2].ToString();
// b = dt.Rows[i][3].ToString();
// c = dt.Rows[i][4].ToString();
// d = dt.Rows[i][5].ToString();
// ee = dt.Rows[i][6].ToString();
// //f = dt.Rows[i][7].ToString();
// //rigth = dt.Rows[i][8].ToString();
// //type = int.Parse(dt.Rows[i][9].ToString());
// SqlParameter[] Params1 = new SqlParameter[11] {
// new SqlParameter("@pv_guid","1111"),
// new SqlParameter("@pv_orderName",typeGuid),
// new SqlParameter("@Pv_order",int.Parse(tile)),
// new SqlParameter("@Pv_name",a),
// new SqlParameter("@Pv_size",float.Parse(b)),
// new SqlParameter("@Pv_money",4.0),
// new SqlParameter("@Pv_value",2.0),
// new SqlParameter("@Pv_url",c),
// new SqlParameter("@Pv_address",d),
// new SqlParameter("@Pv_c_guid",ee),
// new SqlParameter("@type",1)
// };
// ok = db.RunProcGetString("hh_proc_PublicVideo_add_update", Params1);
// Response.Write(ok + "</br>");
//}
//catch(Exception ex)
//{

//}
//#endregion

//综合考试分类 5EB8D599-D585-4C8F-A308-C8C80EC2BFDF
#region
//switch (dt.Rows[i][0].ToString().Length)
//{
// case 1:
// kGuid = "5EB8D599-D585-4C8F-A308-C8C80EC2BFD" + dt.Rows[i][0].ToString();
// break;
// case 2:
// kGuid = "5EB8D599-D585-4C8F-A308-C8C80EC2BF" + dt.Rows[i][0].ToString();
// break;
// case 3:
// kGuid = "5EB8D599-D585-4C8F-A308-C8C80EC2B" + dt.Rows[i][0].ToString();
// break;
//}

//switch (dt.Rows[i][2].ToString().Length)
//{
// case 1:
// rigth = "5EB8D599-D585-4C8F-A308-C8C80EC2BFD" + dt.Rows[i][2].ToString();
// break;
// case 2:
// rigth = "5EB8D599-D585-4C8F-A308-C8C80EC2BF" + dt.Rows[i][2].ToString();
// break;
// case 3:
// rigth = "5EB8D599-D585-4C8F-A308-C8C80EC2B" + dt.Rows[i][2].ToString();
// break;
//}

//sql = "insert into t_stockExamType(set_guid,set_name,set_upGuid,set_type)values('" + kGuid + "','" + dt.Rows[i][1].ToString() + "','" + rigth + "'," + dt.Rows[i][3].ToString() + ")<br/>";
#endregion

// Response.Write(sql);
}
}