Excel文件转为Xml文件（可处理乱序）

 这个暑假接到一个任务将一个含有学生信息的Excel文件转化为Xml文件，具体的是学生信息每行有小组，题目，个人信息等。在转化中我用到了Jdom和Jxl分别来处理Xml和Excel的读写，废话不说上代码：

代码含有是三个对象和一个处理程序，用到了map和list，对数组不是很熟悉的童鞋，可以先了解下数组的内容：

1. Member.java （底层对象）

package newExcelToXml;

public class Member {
	private String number;
	private String name;
	private String className;
	
	
	
	public Member() {
		super();
	}

	public Member(String number, String name, String className) {
		super();
		this.number = number;
		this.name = name;
		this.className = className;
	}
	
	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getClassName() {
		return className;
	}
	public void setClassName(String className) {
		this.className = className;
	}
}

2. Group.java （中层对象）

package newExcelToXml;

import java.util.ArrayList;
import java.util.List;

public class Group {
	private String number;
	private List<Member> members;
	
	
	public Group() {
		super();
		members = new ArrayList<Member>();
	}
	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	public List<Member> getMembers() {
		return members;
	}
	public void setMembers(List<Member> members) {
		this.members = members;
	}
	
	
	//自定义方法
	public boolean containMember(Member member){
		boolean retVal = false;
		for(Member m : members){
			if(m.getNumber().equals(member.getNumber())){
				return true;
			}
		}
		return retVal;
	}
	
	public void addMember(Member m){
		members.add(m);
	}
}

3.subject.java（顶层对象）

import java.util.HashMap;
import java.util.Map;

public class Subject {
	private String title;
	private String teacherName;
	private Map<String,Group> map;
	
	
	public Subject() {
		super();
		title="";
		teacherName = "";
		map = new HashMap<String,Group>();
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getTeacherName() {
		return teacherName;
	}
	public void setTeacherName(String teachername) {
		this.teacherName = teachername;
	}
	public Map<String, Group> getMap() {
		return map;
	}
	public void setMap(Map<String, Group> map) {
		this.map = map;
	}
}

4.ImportTest.java（处理程序）

package newExcelToXml;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;


import excelToXml.Title;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class ImportTest {
	 public static void main(String[] args){
		 File file=new File("C:\\Users\\pc\\Desktop\\选题数据1.xls");
		 readExcelAddXML(file,"C:\\Users\\pc\\Desktop");
		 System.out.println("success");
	 }
	 public static  void readExcelAddXML(File f,String path){
		/* int sum=0;
		 OutputStreamWriter pw=null;*/
		 try{
			 Map<String,Subject> map=readExcelFileOutList(f);
			//创建根节点
			 Element root=new Element("选课数据");
			 //将根节点添加到文档中
			 Document Doc=new Document(root);
			 Element title;  //二级节点
			 Element xiaozu; //三级节点
			 Iterator<Map.Entry<String,Subject>> itTitle=map.entrySet().iterator();
			 while(itTitle.hasNext()){
				 Map.Entry<String, Subject> entryTitle=itTitle.next();
				 title=new Element("title");
				 Subject ti=entryTitle.getValue();
				 title.setAttribute("教师",""+ti.getTeacherName());
				 title.setAttribute("题目",""+ti.getTitle());
			
				 Map<String, Group> gp=ti.getMap();
				 Iterator<Map.Entry<String, Group>> itGroup=gp.entrySet().iterator();
				 while(itGroup.hasNext()){
					
					 Map.Entry<String, Group> entryGroup=itGroup.next();
					 xiaozu=new Element("xiaozu");
					 Group gp1=entryGroup.getValue();
					 xiaozu.setAttribute("组长学号",""+gp1.getNumber());
					 Element member;
					 for(int j=0;j<gp1.getMembers().size();j++){
						 member=new Element("member");
						 member.setAttribute("studentId",""+gp1.getMembers().get(j).getNumber());
						 member.addContent(new Element("name").setText(gp1.getMembers().get(j).getName()));
						 member.addContent(new Element("classname").setText(gp1.getMembers().get(j).getClassName()));
						 xiaozu.addContent(member);
					 }
					 title.addContent(xiaozu);
					 
				 }
				 root.addContent(title);
				 }
				
		 

			 
			 File file=new File(path,"选题数据1.xml");
			 if(!file.exists()){
				 file.createNewFile();
			 }
			 
			 
			 //格式化
			 Format format=Format.getCompactFormat();
			 format.setEncoding("utf-8");
			 format.setIndent(" ");
			 XMLOutputter XMLOut=new XMLOutputter(format);
			 XMLOut.output(Doc, new FileOutputStream(file));
			 
		 }catch(IOException e){
			 e.printStackTrace();
		 }
	 }
	 public static Map<String, Subject> readExcelFileOutList(File f){
		 System.out.println("进入读取xls程序中");
		 
		 Map<String,Subject> map=new HashMap<String,Subject>();
		 
		 InputStream is=null;
		 try{
			 is=new FileInputStream(f);
			 Workbook rwb=Workbook.getWorkbook(is);
			 Sheet[] sheets=rwb.getSheets();
			 int sheetLen=sheets.length;
			 jxl.Sheet rs=rwb.getSheet(0);
			 int num_row=rs.getRows();//得到行
			 int num_column=rs.getColumns(); //得到列
			 System.out.println("行："+num_row+"列:"+num_column);
			 for(int j=1;j<num_row;j++){
				 Cell[] cell=rs.getRow(j);
				 for(int column_index=1;column_index<num_column-1;column_index++){
					 String titleAndName=cell[column_index++].getContents();
					 String[] tAn=titleAndName.split("-");
					 String title=tAn[0];
					 String teachername=tAn[1];
					 String idnumber=cell[column_index++].getContents();
					 String name=cell[column_index++].getContents();
					 String classname=cell[column_index++].getContents();
					 String groupid=cell[column_index++].getContents();
					 
					 //读取完毕，转成生成模型
					 if(map.containsKey(title)){
						 //放入已存在的对象中
						 Subject subject = map.get(title);
						 
						 //在subject中寻找Group
						 if(subject.getMap().containsKey(groupid)){
							 //获取Group,加入Member
							 Group gp=subject.getMap().get(groupid);
							Member mb=new Member();
							mb.setName(name);
							mb.setNumber(idnumber);
							mb.setClassName(classname);
							gp.addMember(mb);	 
						 }else{
							 Group gp1=new Group();
							 Member mb=new Member();
							 mb.setName(name);
							 mb.setNumber(idnumber);
							 mb.setClassName(classname);
							 gp1.setNumber(groupid);
							 gp1.addMember(mb);
							 subject.getMap().put(groupid, gp1);
						 }
						 
						 
					 }else{
						 //创建一个新的对象并放入
						 Subject subject = new Subject();
						 subject.setTitle(title);
						 subject.setTeacherName(teachername);
						 
						 //创建一个group
						 Group group = new Group();
						 group.setNumber(groupid);
						 
						 Member member = new Member(idnumber,name,classname);
						 
						 //添加关系
						 group.addMember(member);
						 
						 Map<String,Group> mp = new HashMap<String,Group>();
						 mp.put(groupid, group);
						 
						 subject.setMap(mp);
						 subject.setTitle(title);
						 subject.setTeacherName(teachername);
						 
						 map.put(title, subject);
						 
					 }
					 
					 
					 
					 //list.add(new Policy(title,teachername,idnumber,name,classname,zuzhangxuehao));	
					 //map.put(idnumber, new Policy(title,teachername,idnumber,name,classname,groupid));					 
				 }
			 }			 
		 }catch(Exception ex){
			 ex.printStackTrace();
		 }finally{
			 try{
				 if(is !=null)
					 is.close();
			 }catch(IOException e){
				 e.printStackTrace();
			 }
		 }
		 return map; 
	 }
	
	 
}

Excel的大概形式为：

最后的结果为：

这个程序也可以处理乱序排列的问题，稍加改动就能配合很多excel一起使用，快去试试吧