.net将Excel格式的xml导入到数据库
来源：互联网 发布：英剧推荐知乎 编辑：程序博客网 时间：2024/06/05 05:33
以下是导入的文件样板：
因为是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; }- .net将Excel格式的xml导入到数据库
- .net从将数据库中的数据导出到Excel格式的xml
- 以xml格式将dataset导入到excel
- 将Excel导入到数据库
- 将excel导入到数据库
- 将Excel导入到数据库
- 将EXCEL导入到数据库
- asp.net怎么将Excel表导入到sql数据库
- 用asp.net将Excel导入到数据库并显示
- .net excel导入到数据库
- .net excel导入到数据库
- 导入excel格式的文件并写入到数据库中
- vba 将xml导入到excel
- vb.net 将DataGridView数据导入到Excel的方式
- 将Excel的数据导入到Sql数据库
- 将数据库的数据导入到excel中
- JXL将excel的数据导入到数据库
- c#将Excel数据导入到数据库的实现代码
- hdu1088
- ssh login without passwd
- 可以直接拿来用的15个jQuery代码片段
- tree命令自动生成目录树
- HDU 2138 How many prime numbers（米勒拉宾素数测试算法）
- .net将Excel格式的xml导入到数据库
- block session
- POJ 1065 Wooden Sticks （排序）
- 数据库锁机制
- sendmail 配置SMTP服务器
- C++语法基础--复制控制--复制构造函数 ，赋值操作符 ，析构函数
- 【deep learning学习笔记】注释yusugomori的LR代码 --- LogisticRegression.h
- Spring使用
- Shell脚本语法
