java excel jxls_java通过jxls框架实现导入导出excel文件实例

//使用jxls报表生成工具，把java实体类导出生成 Excel文件或导入 Excel 插入数据库

//读取

public class ReadExcel {

private final static String xmlConfig="student.xml";

public List read(){

InputStream inputXML = new BufferedInputStream(ReadExcel.class.getResourceAsStream(xmlConfig));

XLSReader mainReader;

String path=ReadExcel.class.getResource("/").getPath();

path=path.substring(1,path.indexOf("/WebRoot")+1)+"WebRoot/Excel/stu.xls";

try {

mainReader = ReaderBuilder.buildFromXML(inputXML );

InputStream inputXLS = new BufferedInputStream(new FileInputStream(path));

Student stu=new Student();

List students = new ArrayList();

Map beans = new HashMap();

beans.put("stu", stu);

beans.put("students", students);

XLSReadStatus readStatus = mainReader.read(inputXLS, beans);

return students;

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SAXException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return null;

}

public static void main(String[] args) {

ReadExcel re=new ReadExcel();

List list=re.read();

System.out.println("ID\t name\t subject\t score");

for(Student stu:list){

System.out.println(stu.getIdname()+"\t "+stu.getName()+"\t "+stu.getSubject()+"\t "+stu.getScorename());

}

}

}

//写入

public class WriteExcel {

public static void write(List list){

List students = new ArrayList();

Map beans = new HashMap();

beans.put("students", list);

XLSTransformer transformer = new XLSTransformer();

String path=ReadExcel.class.getResource("/").getPath();

path=path.substring(1,path.indexOf("/WebRoot")+1)+"WebRoot/Excel/";

try {

transformer.transformXLS(path+"/student.xls", beans, path+"/stus.xls");

} catch (ParsePropertyException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

}

public List getStudetns(){

List list=new ArrayList();

Student stu=null;

PreparedStatement pre=null;

ResultSet re=null;

try{

pre=DBConector.getCon().prepareStatement("select * from student");

re=pre.executeQuery();

while(re.next()){

stu=new Student();

stu.setId(re.getLong(1));

stu.setName(re.getString(2));

stu.setSubject(re.getString(3));

stu.setScore(re.getLong(4));

list.add(stu);

}

}catch(Exception e){

e.printStackTrace();

}finally{

try{

if(re!=null)

re.close();

if(pre!=null)

pre.close();

}catch(Exception e){

}

}

return list;

}

public static void main(String[] args) {

WriteExcel w=new WriteExcel();

w.write(w.getStudetns());

}

}//sql

create table STUDENT

(

ID NUMBER(8) not null primary key,

NAME VARCHAR2(50) not null,

SUBJECT VARCHAR2(50) not null,

SCORE NUMBER(8)

)

Excel模板文件：

对应Excel文件