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

01//使用jxls报表生成工具，把java实体类导出生成 Excel文件或导入 Excel 插入数据库 02  03//读取 04  05public class ReadExcel { 06    private final static String xmlConfig="student.xml"; 07    public List 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(new FileInputStream(path)); 15                Student stu=new Student(); 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                return students; 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        return null; 30    } 31      32    public static void main(String[] args) { 33        ReadExcel re=new ReadExcel(); 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 class WriteExcel { 03      04    public static 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    public List getStudetns(){ 21        List<Student> list=new ArrayList<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=new Student(); 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        return list; 49    } 50    public static void main(String[] args) { 51        WriteExcel w=new WriteExcel(); 52        w.write(w.getStudetns()); 53    } 54} 1//sql 2create table STUDENT 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)

Excel模板文件：

对应Excel文件

1个评论 3个牛币 jxls框架excel导入excel导出 文本解析和文件处理 文件名:jxls-poi.rar,文件大小:2850.34K 下载 / /jxls-poi /jxls-poi/.classpath/jxls-poi/.mymetadata/jxls-poi/.project /jxls-poi/WebRoot /jxls-poi/WebRoot/Excel /jxls-poi/WebRoot/Excel/stu.xls/jxls-poi/WebRoot/Excel/student.xls /jxls-poi/src /jxls-poi/src/org /jxls-poi/src/org/terry /jxls-poi/src/org/terry/jxls

/jxls-poi/src/org/terry/jxls/DBConector.java

/jxls-poi/src/org/terry/jxls/ReadExcel.java

/jxls-poi/src/org/terry/jxls/Student.java

/jxls-poi/src/org/terry/jxls/WriteExcel.java

/jxls-poi/src/org/terry/jxls/student.xml