java通过jxls框架实现导入导出excel

来源：互联网发布：阿里大数据平台建成编辑：程序博客网时间：2024/06/10 17:19

java通过jxls框架实现导入导出excel

01//使用jxls报表生成工具，把java实体类导出生成 Excel文件或导入 Excel 插入数据库
02 
03//读取
04 
05public classReadExcel {
06    privatefinal static String xmlConfig="student.xml";
07    publicList read(){
08        InputStream inputXML =new BufferedInputStream(ReadExcel.class.getResourceAsStream(xmlConfig));
09        XLSReader mainReader;
10        String path=ReadExcel.class.getResource("/").getPath();
11        path=path.substring(1,path.indexOf("/WebRoot")+1)+"WebRoot/Excel/stu.xls";
12        try{
13            mainReader = ReaderBuilder.buildFromXML(inputXML );
14             InputStream inputXLS =new BufferedInputStream(newFileInputStream(path));
15                Student stu=newStudent();
16                List students =new ArrayList();
17                Map beans =new HashMap();
18                beans.put("stu", stu);
19                beans.put("students", students);
20                XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
21                returnstudents;
22        } catch (IOException e) {
23            // TODO Auto-generated catch block
24            e.printStackTrace();
25        } catch (SAXException e) {
26            // TODO Auto-generated catch block
27            e.printStackTrace();
28        }
29        returnnull;
30    }
31     
32    publicstatic void main(String[] args) {
33        ReadExcel re=newReadExcel();
34        List<Student> list=re.read();
35        System.out.println("ID\t  name\t  subject\t  score");
36        for(Student stu:list){
37            System.out.println(stu.getIdname()+"\t  "+stu.getName()+"\t  "+stu.getSubject()+"\t  "+stu.getScorename());
38        }
39    }
40}

01//写入
02public classWriteExcel {
03     
04    publicstatic void write(List list){
05        List students =new ArrayList();
06        Map beans =new HashMap();
07        beans.put("students", list);
08        XLSTransformer transformer =new XLSTransformer();
09        String path=ReadExcel.class.getResource("/").getPath();
10        path=path.substring(1,path.indexOf("/WebRoot")+1)+"WebRoot/Excel/";
11        try{
12            transformer.transformXLS(path+"/student.xls", beans, path+"/stus.xls");
13        } catch (ParsePropertyException e) {
14            e.printStackTrace();
15        } catch (IOException e) {
16            e.printStackTrace();
17        }
18 
19    }
20    publicList getStudetns(){
21        List<Student> list=newArrayList<Student>();
22        Student stu=null;
23        PreparedStatement pre=null;
24        ResultSet re=null;
25        try{
26            pre=DBConector.getCon().prepareStatement("select * from student");
27            re=pre.executeQuery();
28            while(re.next()){
29                stu=newStudent();
30                stu.setId(re.getLong(1));
31                stu.setName(re.getString(2));
32                stu.setSubject(re.getString(3));
33                stu.setScore(re.getLong(4));
34                list.add(stu);
35            }
36        }catch(Exception e){
37            e.printStackTrace();
38        }finally{
39            try{
40                if(re!=null)
41                    re.close();
42                if(pre!=null)
43                    pre.close();
44            }catch(Exception e){
45            }
46             
47        }
48        returnlist;
49    }
50    publicstatic void main(String[] args) {
51        WriteExcel w=newWriteExcel();
52        w.write(w.getStudetns());
53    }
54}

1//sql
2create tableSTUDENT
3(
4  ID      NUMBER(8)not null primary key,
5  NAME   VARCHAR2(50) not null,
6  SUBJECT VARCHAR2(50)not null,
7  SCORE   NUMBER(8)
8)