POI 实现Excel 导入导出

自己到apache 下载poi.jar 这里不说了
先写 JDBC 链接语句吧(用Hibernate 好久没写了 复习下)

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;

}

-----------------
搞定。。