.net将Excel格式的xml导入到数据库

12 篇文章 0 订阅

以下是导入的文件样板:


因为是xml格式的,所以用记事本打开这个文件,可以看到是这种格式:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>zifar-computer</Author>
  <LastAuthor>zifar-computer</LastAuthor>
  <Created>2013-07-08T08:23:29Z</Created>
  <Company>zifar.com</Company>
  <Version>11.5606</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>13680</WindowHeight>
  <WindowWidth>24795</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>60</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Center"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s23" ss:Name="超链接">
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#0000FF"
    ss:Underline="Single"/>
  </Style>
  <Style ss:ID="s21">
   <NumberFormat ss:Format="@"/>
  </Style>
  <Style ss:ID="s22">
   <NumberFormat ss:Format="Short Date"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="23" ss:ExpandedRowCount="1200" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
   <Column ss:Width="57" ss:Span="4"/>
   <Column ss:Index="6" ss:Width="83.25"/>
   <Column ss:Width="33"/>
   <Column ss:Width="57"/>
   <Column ss:Width="33"/>
   <Column ss:Width="57"/>
   <Column ss:Width="33"/>
   <Column ss:Width="57" ss:Span="3"/>
   <Column ss:Index="16" ss:Width="83.25"/>
   <Column ss:Width="33"/>
   <Column ss:Width="57" ss:Span="3"/>
   <Column ss:Index="22" ss:Width="33" ss:Span="1"/>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">用户姓名</Data></Cell>
    <Cell><Data ss:Type="String">人员工号</Data></Cell>
    <Cell><Data ss:Type="String">身证证号</Data></Cell>
    <Cell><Data ss:Type="String">手机号码</Data></Cell>
    <Cell><Data ss:Type="String">是否启用</Data></Cell>
    <Cell><Data ss:Type="String">所属部门</Data></Cell>
    <Cell><Data ss:Type="String">职务</Data></Cell>
    <Cell><Data ss:Type="String">出生日期</Data></Cell>
    <Cell><Data ss:Type="String">年龄</Data></Cell>
    <Cell><Data ss:Type="String">办公电话</Data></Cell>
    <Cell><Data ss:Type="String">职称</Data></Cell>
    <Cell><Data ss:Type="String">文化程度</Data></Cell>
    <Cell><Data ss:Type="String">婚姻状况</Data></Cell>
    <Cell><Data ss:Type="String">政治面貌</Data></Cell>
    <Cell><Data ss:Type="String">电子邮箱</Data></Cell>
    <Cell><Data ss:Type="String">参加工作时间</Data></Cell>
    <Cell><Data ss:Type="String">工龄</Data></Cell>
    <Cell><Data ss:Type="String">毕业学校</Data></Cell>
    <Cell><Data ss:Type="String">所修专业</Data></Cell>
    <Cell><Data ss:Type="String">毕业时间</Data></Cell>
    <Cell><Data ss:Type="String">家庭住址</Data></Cell>
    <Cell><Data ss:Type="String">民族</Data></Cell>
    <Cell><Data ss:Type="String">性别</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">梅锦龙</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">120096</Data></Cell>
    <Cell><Data ss:Type="String">身份证号</Data></Cell>
    <Cell><Data ss:Type="String">手机号码</Data></Cell>
    <Cell><Data ss:Type="String">是</Data></Cell>
    <Cell><Data ss:Type="String">厂部</Data></Cell>
    <Cell><Data ss:Type="String">技术员</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="DateTime">1980-09-09T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">30</Data></Cell>
    <Cell><Data ss:Type="String">办公电话</Data></Cell>
    <Cell><Data ss:Type="String">工程师</Data></Cell>
    <Cell><Data ss:Type="String">大专</Data></Cell>
    <Cell><Data ss:Type="String">已婚 </Data></Cell>
    <Cell><Data ss:Type="String">团员</Data></Cell>
    <Cell ss:StyleID="s23" ss:HRef="mailto:abc@163.com"><Data ss:Type="String">abc@163.com</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2010-08-08T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">3</Data></Cell>
    <Cell><Data ss:Type="String">毕业学校</Data></Cell>
    <Cell><Data ss:Type="String">所修专业</Data></Cell>
    <Cell><Data ss:Type="String">毕业时间</Data></Cell>
    <Cell><Data ss:Type="String">家庭住址</Data></Cell>
    <Cell><Data ss:Type="String">汉</Data></Cell>
    <Cell><Data ss:Type="String">男</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">何**</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">120110</Data></Cell>
    <Cell ss:Index="6"><Data ss:Type="String">厂部</Data></Cell>
    <Cell ss:Index="23"><Data ss:Type="String">男</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">高**</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">120603</Data></Cell>
    <Cell ss:Index="6"><Data ss:Type="String">厂部</Data></Cell>
    <Cell ss:Index="23"><Data ss:Type="String">男</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">曾**</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">120153</Data></Cell>
    <Cell ss:Index="6"><Data ss:Type="String">厂部</Data></Cell>
    <Cell ss:Index="23"><Data ss:Type="String">男</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Unsynced/>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>3</ActiveRow>
     <ActiveCol>11</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Unsynced/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Unsynced/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

导入没什么难度,主要是因为这个xml有N个命名空间,再有就是因为有很多空的单元格,空单元格在这个xml中是看不出来的,跳格是通过cell标记上的ss:Index指向的。下面是解析代码:

/// <summary>
        /// 从excel中导入数据到oracel
        /// </summary>
        /// <param name="filename">要解析的excel文件</param>
        /// <param name="errmsg"></param>
        /// <param name="nofullsucceed">不完全成功提示:如果只是成功了一部分,状态还是返回为true,但是要提示用户</param>
        /// <returns></returns>
        public bool InportFromExcelToDb(string filename,ref string errmsg,ref string nofullsucceed)
        {
            bool r = false;
            if (!File.Exists(filename))
            {
                errmsg = "文件"+filename+"不存在或者没有权限读取";
                return false;
            }
            //附加xml命名空间
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("d", "urn:schemas-microsoft-com:office:spreadsheet");
            dic.Add("o", "urn:schemas-microsoft-com:office:office");
            dic.Add("x", "urn:schemas-microsoft-com:office:excel");
            dic.Add("ss", "urn:schemas-microsoft-com:office:spreadsheet");
            dic.Add("html", "http://www.w3.org/TR/REC-html40");
            XmlNodeList xnl = ConfigHelper.GetXmlNodeList(filename, "descendant::d:Row", dic);
            int max = xnl.Count;//取要导入记录的数目,在最后用的时候,记得要减掉标题 那行
            int succedcount = 0;//没写入成功的记录数
            if (xnl == null || xnl.Count<2)
            {
                //等于1表示只有一行标题
                errmsg = "没有可导的数据";
                return false;
            }

            //数据从第二行开始
            List<string[]> slist = new List<string[]>();//用于保存用户信息
            
            //从1开始,因为第一行是标题 
            for (int count=1;count<xnl.Count;count++)
            {
                string[] arr = new string[23];//创建一个长度为23的数组
                int index = 0;//表示当前实际在第几个节点
                XmlNodeList snl = xnl[count].ChildNodes;//取当前节点的所有子节点,结构不错的话,应该是cell节点
                if (snl == null || snl.Count == 0)
                    continue;
                
                #region 循环取出数据
                for (int j = 0; j < snl.Count; j++)
                {
                    XmlNode xn = snl[j];
                    string at=string.Empty;
                    bool ishas=IsHasAttr(xn,"ss:Index",ref at);
                    if (ishas && ValidHelper.IsIntData(at))
                    {
                        index = Convert.ToInt32(at) - 1;
                    }

                    //总共只有23个列,超过了则进入下一轮
                    if (index < 23)
                    {
                        arr[index] = xn.InnerText.Trim();
                    }
                    index++;
                    if (index >= 23)
                    {
                        index = 0;
                    }
                }
                #endregion
                slist.Add(arr);
            }

            #region 开始往数据库中写入
            bool isSuc = false;
            CUM_UserInfoBM bm = null;
            CUM_UserInfoService bll = new CUM_UserInfoService();
            for (int i = 0; i < slist.Count; i++)
            {
                string[] sarr = slist[i];
                if (TrimOrAddSpace(sarr[0], false) == "" || TrimOrAddSpace(sarr[1], false) == "")
                {
                    continue;//如果员工姓名或者人员工号为空,则下一轮
                }
                //用户姓名,人员工号,身证证号,手机号码,是否启用,所属部门,职务,出生日期,年龄,办公电话,职称
                //文化程度,婚姻状况,政治面貌,电子邮箱,参加工作时间,工龄,毕业学校,所修专业,毕业时间,家庭住址,民族,性别

                bm = new CUM_UserInfoBM();
                bm.User_Name = TrimOrAddSpace(sarr[0]);
                bm.User_No = TrimOrAddSpace(sarr[1]);
                bm.User_IDCard = TrimOrAddSpace(sarr[2]);//为避免空值插入不了数据库,需要对值进行处理
                bm.User_Mobile = TrimOrAddSpace(sarr[3]);
                short valid = 0;
                if (TrimOrAddSpace(sarr[4]) == "是")
                {
                    valid = 1;
                }
                bm.User_ISvalid = valid;
                bm.User_Dept = GetOrgIdByName(sarr[5]);
                bm.User_Job = TrimOrAddSpace(sarr[6]);
                //excel自动加了T和时分秒
                if (!string.IsNullOrEmpty(sarr[7]) && sarr[7].Contains("T"))
                {
                    sarr[7] = sarr[7].Split('T')[0];
                }
                if (ValidHelper.IsDateTime(sarr[7]))
                {
                    bm.User_Born = Convert.ToDateTime(sarr[7]);
                }
                if (ValidHelper.IsIntData(sarr[8]))
                {
                    bm.User_Age = Convert.ToInt32(sarr[8]);
                }
                bm.User_DeptTel = TrimOrAddSpace(sarr[9]);
                bm.User_Title = TrimOrAddSpace(sarr[10]);
                bm.User_Diploma = TrimOrAddSpace(sarr[11]);
                bm.User_Mar = TrimOrAddSpace(sarr[12]);
                bm.User_Polity = TrimOrAddSpace(sarr[13]);
                bm.User_Email = TrimOrAddSpace(sarr[14]);

                if (!string.IsNullOrEmpty(sarr[15]) && sarr[15].Contains("T"))
                {
                    sarr[15] = sarr[15].Split('T')[0];
                }
                if (ValidHelper.IsDateTime(sarr[15]))
                {
                    bm.User_WorkDate = Convert.ToDateTime(sarr[15]);
                }
                if (ValidHelper.IsIntData(sarr[16]))
                {
                    bm.User_WorkYear = Convert.ToInt32(sarr[16]);
                }
                bm.User_School = TrimOrAddSpace(sarr[17]);
                bm.User_Profession = TrimOrAddSpace(sarr[18]);

                if (!string.IsNullOrEmpty(sarr[19]) && sarr[19].Contains("T"))
                {
                    sarr[19] = sarr[19].Split('T')[0];
                }
                if (ValidHelper.IsDateTime(sarr[19]))
                {
                    bm.User_FishDate = Convert.ToDateTime(sarr[19]);
                }
                bm.User_FamAddress = TrimOrAddSpace(sarr[20]);
                bm.User_Nation = TrimOrAddSpace(sarr[21]);
                bm.User_Sex = TrimOrAddSpace(sarr[22],true);
                //bm.User_Sex = "男";
                bm.User_Pwd = SecurityHelper.getUserEncryptPass("123456");
                try
                {
                    if (bll.IsHasUserNo(0, bm.User_No))
                    {
                        continue;
                    }
                    long userid = bll.InsertUserInfo(bm);
                    isSuc = true;
                }
                catch (Exception ex)
                {
                    succedcount++;
                    IOHelper.WriteLog("插入用户" + sarr[0] + "失败," + ex.Message + ex.StackTrace);
                    continue;
                }

                //插入50条后休息30毫秒
                if (i > 0 && i % 50 == 0)
                {
                    Thread.Sleep(30);
                }
            }
            //只要成功插入过一次,就算是成功
            if (isSuc)
            {
                r = true;
            }
            #endregion
            if (succedcount > 0 && succedcount < max)
            {
                nofullsucceed = "共"+succedcount+"条记录导入失败,失败原因请查看根目录下的logs文件夹下的日志";
            }
            return r;
        }

        /// <summary>
        /// 对原值进行trim,如果为空,则赋值空格,如果不为空,则trim掉多余空格
        /// </summary>
        /// <param name="s"></param>
        /// <param name="isSpace">是否要加一个空格,默认加上</param>
        /// <returns></returns>
        private string TrimOrAddSpace(string s,bool isSpace=true)
        {
            string result = " ";
            if (!isSpace)
            {
                result = "";
            }
            if (string.IsNullOrEmpty(s)) return result;
            if (s.Trim() == "")
            {
                return result;
            }
            else
            {
                return s.Trim();
            }
        }

        /// <summary>
        /// 通过名称取部门ID
        /// </summary>
        /// <param name="name"></param>
        private long GetOrgIdByName(string name)
        {
            if (string.IsNullOrEmpty(name)) return 0;
            IList<CUM_OrgBM> list = GetOrgList();
            CUM_OrgBM bm = list.FirstOrDefault(m => m.CUM_OrgName == name);
            if (bm != null)
            {
                return bm.CUM_OrgID;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 判断xmlnode是否有某个节点,有则返回这个节点的值
        /// </summary>
        /// <param name="xn">要判断的某个节点</param>
        /// <param name="ckattr">要判断的那个属性</param>
        /// <param name="index"></param>
        /// <returns></returns>
        private bool IsHasAttr(XmlNode xn,string ckattr, ref string index)
        {
            bool ishas = false;
            XmlAttributeCollection xattr = xn.Attributes;
            if (xattr != null)
            {
                foreach (XmlAttribute attr in xattr)
                {
                    if (attr.Name.Contains(ckattr))
                    {
                        string sy = xn.Attributes[attr.Name].Value;
                        ishas = true;
                        index = sy;
                        break;
                    }
                }
            }
            return ishas;
        }


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值