POI 实现Excel 导入导出

 

POI 实现Excel 导入导出【转载】

自己到apache 下载poi.jar 这里不说了 
先写 JDBC 链接语句吧

 

public class DBConnection {  
  
    private String classString="com.mysql.jdbc.Driver";  
    private String username="root";  
    private String password="123";  
    private String url="jdbc:mysql://localhost:3306/books?&characterEncoding=utf-8";  
    private Connection con=null;  
  
    public Connection getConnection(){  
       try {  
        Class.forName(classString);  
        con=DriverManager.getConnection(url,username,password);  
       } catch (ClassNotFoundException e) {  
        e.printStackTrace();  
       } catch (SQLException e) {  
        e.printStackTrace();  
       }  
       return con;  
    }  
}  

 --------------------------------------------- 

导入数据库： 
我这里数据库表很简单  字段（id,userName,password）。 
注意： *.xls 文件 数据必须从B2开始 。下面的写法我也没去多判断了，只写核心。呵呵。 

 

private Connection con;  
    private DBConnection db;  
    private PreparedStatement pst;  
    private String filePath="d:\\abcdef.xls";  
      
    public boolean insertDB() throws java.text.ParseException{  
            
           boolean flag=true;  
           db=new DBConnection();  
           con=db.getConnection();  
           try {  
            //文件流指向excel文件  
            FileInputStream fin=new FileInputStream(filePath);  
            HSSFWorkbook workbook=new HSSFWorkbook(fin); //创建工作薄  
            HSSFSheet sheet=workbook.getSheetAt(0); //得到工作表  
            HSSFRow row=null; //对应excel的行  
            HSSFCell cell=null; //对应excel的列  
             
            int totalRow=sheet.getLastRowNum(); //得到excel的总记录条数  
            //以下的字段一一对应数据库表的字段  
            String userName="";  
            String password="";  
            Date bookDate=null;  
            
            String sql="insert into user(userName,password) values(?,?)";   
             
            for(int i=1;i<=totalRow;i++){  
             row=sheet.getRow(i);  
             cell=row.getCell((short) 1);  
             userName=cell.getStringCellValue().toString();  
               
             cell=row.getCell((short) 2);  
             password=cell.getStringCellValue().toString();  
               
             cell=row.getCell((short) 3);  
             //格式化字符串时间  
           //  SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");  
           //  bookDate=new //Date((format.parse(cell.getStringCellValue().toString())).getTime());  
              
             pst=con.prepareStatement(sql);  
             pst.setString(1,userName);  
             pst.setString(2,password);  
              
             pst.execute();  
            }  
           } catch (FileNotFoundException e) {  
            flag=false;  
            e.printStackTrace();  
           } catch(IOException ex){  
            flag=false;  
            ex.printStackTrace();  
           } catch(SQLException exx){  
            flag=false;  
            exx.printStackTrace();  
           } catch(ParseException exxx){  
            exxx.printStackTrace();  
           }finally{  
            try {  
                if(pst !=null){  
                    pst.close();  
                    con.close();  
                }  
            } catch (SQLException e) {  
             e.printStackTrace();  
            }  
           }  
           return flag;  
        }  

 ----------------------------------------------------- 

从数据库导出到Excel： 
这里我直接在内存中创建一个输出流对象。省去读写硬盘的麻烦！

 

public InputStream getInputStream(){  
  HSSFWorkbook wb = new HSSFWorkbook();  
  HSSFSheet sheet = wb.createSheet("sheet1");  
    
                HSSFRow row = sheet.createRow(0);  
        HSSFCell cell = row.createCell((short) 0);  
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);  
        cell.setCellValue("序号");  
  
        cell = row.createCell((short) 1);  
        cell.setEncoding(HSSFCell.ENCODING_UTF_16); //设置字符编码  
        cell.setCellValue("姓名");  
          
        cell = row.createCell((short) 2);  
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);  
        cell.setCellValue("密码");  
          
        List<Entity> listrepot=dao.getAllEntity(); //从数据库中获取数据  
  
        for (int i = 1; i <= listrepot.size(); ++i){  
          Entity  rep= listrepot.get(i-1);  
            
           row = sheet.createRow(i + 1);  
            cell = row.createCell((short) 0);  
            cell.setEncoding(HSSFCell.ENCODING_UTF_16);  
            cell.setCellValue(i);  
  
            cell = row.createCell((short) 1);  
            cell.setEncoding(HSSFCell.ENCODING_UTF_16);  
            cell.setCellValue(rep.getUserName());  
  
            cell = row.createCell((short) 2);  
            cell.setEncoding(HSSFCell.ENCODING_UTF_16);  
                    cell.setCellValue(rep.getPassword());  
  
               }  
            //这是核心了 直接在内存中创建 返回OK  
            ByteArrayOutputStream os=new ByteArrayOutputStream();  
        try {  
            wb.write(os);  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        byte[] content=os.toByteArray();  
        InputStream is=new ByteArrayInputStream(content);  
          
        return is;  
  
}