求救 excel批量导入 循环插入数据 oracle

Question

求救 excel批量导入 循环插入数据并删除重复的数据  oracle  
  已有的代码如下         舍弃以下代码亦可  求段循环插入数据的源代码
 public static void photo(DataTable zh)
｛
  int count = 0;
  string strfields = "";//列
  string strvalues = ""; //值
  string filed = ""; 
  string strvalue = "";  for (int i = 0; i < zh.Columns.Count; i++)   //循环列名 并插入数据  列名较多
  {
  string strfiled = zh.Columns[i].ColumnName;
  strfields += strfiled + ",";
  strvalues += "@" + strfiled + ",";
  }
  if (strfields != "")
  {
  strfields = strfields.Substring(0, strfields.LastIndexOf(","));
  strvalues = strvalues.Substring(0, strvalues.LastIndexOf(","));
  }
  try
  {
  OracleConnection sqlconn = new OracleConnection(ConfigurationManager.AppSettings[connectdate.getconnect()]);
  sqlconn.Open();
  OracleDataAdapter da = new OracleDataAdapter();
  OracleCommand cmd = new OracleCommand();
  //建立InsertCommand
  da.InsertCommand = cmd;
  StringBuilder sb = new StringBuilder("");
  cmd.CommandText = sb.ToString();
  sb.Append("INSERT" + casemessage + ", " + strfields + " VALUES()");
  sb.Append(strvalues + ")");for (int i = 0; i < zh.Columns.Count; i++)
  {
  filed = zh.Columns[i].ColumnName;
  strvalue = "@" + filed;
  OracleParameter oparam = new OracleParameter();
  oparam.ParameterName = strvalue;
  oparam.OracleType = DbTypeList[i];  oparam.SourceVersion = DataRowVersion.Current;
  oparam.SourceColumn = filed;
  da.InsertCommand.Parameters.Add(oparam);
  }
  count = da.Update(zh);
    
  OracleOperate con = new OracleOperate();
  //删除掉重复记录,在casemessage 表中当 casenumber 相同时则认为该数据重复......
  string sql = "delete from casemessage where tid not in (select min(tid) from casemessage group by casenumber)";
  con.
    
  sqlconn.Close();
  }  catch (Exception ex)
  {
  count = 0;
  }
    
  return count;
    
  ｝

Answer 1

http://www.cnblogs.com/wuhuacong/archive/2010/04/28/1723142.html

Answer 2

http://topic.csdn.net/t/20050724/19/4164861.html

Answer 3

 try
            {
                if (!string.IsNullOrEmpty(FileUpload1.PostedFile.FileName))
                {
                    if (!(".xls,.xlsx".Contains(Path.GetExtension(this.FileUpload1.PostedFile.FileName))))
                    {
                        this.ShowMessage("上传的文件不是EXCEL文件！请重新上传！");
                    }
                    else
                    {
                        fileName = Path.GetTempFileName();
                        this.FileUpload1.PostedFile.SaveAs(fileName);
                        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + "Extended properties=Excel 12.0;";
                        var sql = "Select * from [Sheet1$]";
                        DataSet ds = new DataSet();
                        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, connectionString);
                        dataAdapter.Fill(ds, "data");
                        this.GvReport.DataSource = ds.Tables["data"];
                        this.GvReport.DataBind();                        List<ContactMsg> list = new List<ContactMsg>();                        for (int i = 0; i < ds.Tables["data"].Rows.Count; i++)
                        {
                            ContactMsg perModel = new ContactMsg();                            string PersonNO = ds.Tables[0].Rows[i]["人员编号"].ToString().Replace(" ", "");
                            if (!String.IsNullOrEmpty(PersonNO))
                            {
                                string PersonName = ds.Tables[0].Rows[i]["姓名"].ToString().Replace(" ", "");
                                string IsContact = !String.IsNullOrEmpty(ds.Tables[0].Rows[i]["是否签约"].ToString().Replace(" ", "")) ? ds.Tables[0].Rows[i]["是否签约"].ToString().Replace(" ", "") : "0";
                                ContactMsg model = new ContactMsg()
                                {
                                    PersonNO = PersonNO,
                                    PersonName = PersonName,
                                    IsContact = IsContact
                                };
                                list.Add(model);
                            }
                        }
                        ViewState["ContactMsg"] = list;
                    }                }
                else
                {
                    this.ShowMessage("请选择需要导入的住宿信息excel表！");
                }            }
            catch (Exception e)
            {
                if (e.Message.Equals("外部表不是预期的格式。"))
                {
                    var reader = XmlReader.Create(fileName);
                    var dt = new DataTable();
                    var row = 0;
                    var isColumn = false;
                    var col = 0;
                    while (reader.Read())
                    {
                        if (reader.Name == "Row" && reader.NodeType == XmlNodeType.Element)
                        {
                            row = row + 1;
                            col = 0;
                            if (row > 1)
                            {
                                var dr = dt.NewRow();
                                dt.Rows.Add(dr);
                            }
                        }                        if (reader.Name == "Cell" && reader.NodeType == XmlNodeType.Element)
                        {
                            isColumn = true;
                            if (reader.HasAttributes && reader.GetAttribute("ss:Index") != null)
                            {
                                col = ConvertToInt(reader.GetAttribute("ss:Index"));
                            }
                            else
                            {
                                col = col + 1;
                            }
                        }                        if (reader.NodeType == XmlNodeType.Text)
                        {
                            if (isColumn)
                            {
                                if (row == 1)
                                {
                                    dt.Columns.Add(reader.Value);
                                    isColumn = false;
                                }
                                else
                                {
                                    dt.Rows[dt.Rows.Count - 1][col - 1] = reader.Value;
                                }
                            }
                        }
                        if (reader.Name == "Table" && reader.NodeType == XmlNodeType.EndElement)
                        {
                            break;
                        }
                    }