这个暑假接到一个任务将一个含有学生信息的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一起使用,快去试试吧