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){ Entityrep= 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;}
-----------------
搞定。。
页:
[1]