读取Excel,并把某几列合并起来存入XElement 类型放入数据库

 

1.读取excel,放入datatable里面:
                OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + sheename + "]", OLEDBConnStr);
                DataTable sourceDataTable = new DataTable();
                oda.Fill(sourceDataTable);
                oda.Dispose();

2.new一个XElement 类型的Rooot,并循环datatable:row和column

                XElement xe = new XElement("Root");

                foreach (DataRow item in sourceDataTable.Rows)
                {
                    tbOBCustList tb = new tbOBCustList();

                    foreach (DataColumn dcs in sourceDataTable.Columns)
                    {

                        if (dcs.ColumnName == "客户编号" || dcs.ColumnName == "旺旺号" || dcs.ColumnName == "手机1" || dcs.ColumnName == "手机2" || dcs.ColumnName == "固话1" || dcs.ColumnName == "固话2" || dcs.ColumnName == "拨打结果" || dcs.ColumnName == "拨打人" || dcs.ColumnName == "名单批次号"
                            ||dcs.ColumnName == "电子邮箱" 
                            )
                        {
                            if (tb.cust_id == Convert.ToInt32(item["客户编号"]))// 因为第一次把上面所有的列都读取了一次所有次判断是为了防止重复.
                            {
                                continue;
                            }
                            tb.cust_id = Convert.ToInt32(item["客户编号"]);
                            tb.ww_user = Convert.ToString(item["旺旺号"]);
                            tb.mobile1 = Convert.ToString(item["手机1"]);
                            tb.mobile2 = Convert.ToString(item["手机2"]);
                            tb.phone1 = Convert.ToString(item["固话1"]);
                            tb.phone2 = Convert.ToString(item["固话2"]);
                            tb.case_result = Convert.ToString(item["拨打结果"]);
                            tb.id_cro = Convert.ToString(item["拨打人"]);
                            tb.batch_no = Convert.ToString(item["名单批次号"]);
                            try
                            {
                                tb.email = Convert.ToString(item["电子邮箱"]);
                            }
                            catch
                            {}

                        }
                        else//如果读完了其他的列,那么把剩下的几个列都存入XElement 中
                        {
                            xe.SetElementValue(dcs.ColumnName, item[dcs.ColumnName].ToString());

                        }
                    }

                    tb.surveymain_id = Convert.ToInt32(this.DropDownList1.SelectedItem.Value);
                    tb.remark = xe;
                    tb.modify_dt = BnbCRM.Models.DTimes.MinDate;
                    tb.project_id = project_id;
                    //int custid = Convert.ToInt32(tb.cust_id);
                    //delete(custid, project_id);

                    dc.tbOBCustLists.InsertOnSubmit(tb);
                    dc.SubmitChanges();
                }

                sConn.Close();

                Page.ClientScript.RegisterStartupScript(GetType(), "", "<script>alert('成功！')</script>");

            }
            catch (Exception)
            {
                throw;
                Page.ClientScript.RegisterStartupScript(GetType(), "", "<script>alert('失败！')</script>");
            }

 

存入数据库中如图:

点开链接之后如图: