POI3.10 根据Excel模版导出数据测试
1:所需jar包
2:Mysql数据库表内容如下:
3:代码结构如下:
(1)User.java
public class User { private int id; private String name; private String no; private String nativePlace; private String edu; private Double math; private Double computer; private Double english; private Double sumcount; private Double avgcount; //setter-getter ... }
(2)JdbcUtil.java
public class JdbcUtil { private static final String URL = "jdbc:mysql://localhost:3306/test"; private static final String USER = "root"; private static final String PASSWORD = "mysql"; private JdbcUtil() { } static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException ex) { Logger.getLogger(JdbcUtil.class.getName()).log(Level.SEVERE, null, ex); } } public static Connection getConnection() throws Exception {//建立连接 return DriverManager.getConnection(URL, USER, PASSWORD); } public static void free(ResultSet rs, Statement st, Connection conn) {//释放资源 try { if (rs != null) { rs.close(); } } catch (SQLException ex) { Logger.getLogger(JdbcUtil.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (st != null) { st.close(); } } catch (SQLException ex) { Logger.getLogger(JdbcUtil.class.getName()).log(Level.SEVERE, null, ex); } finally { if (conn != null) { try { conn.close(); } catch (SQLException ex) { Logger.getLogger(JdbcUtil.class.getName()).log(Level.SEVERE, null, ex); } } } } } }
(3)UserService.java
public class UserService { public static List<User> getUserList(){ User user; List<User> list = new ArrayList<>(); String sql = "select id,name,no,nativeplace,edu,math,computer,english,sumcount,avgcount from t_user"; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtil.getConnection(); ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while(rs.next()){ user = new User(); user.setId(rs.getInt("id")); user.setName(rs.getString("name")); user.setNo(rs.getString("no")); user.setNativePlace(rs.getString("nativeplace")); user.setEdu(rs.getString("edu")); user.setMath(rs.getDouble("math")); user.setComputer(rs.getDouble("computer")); user.setEnglish(rs.getDouble("english")); user.setSumcount(rs.getDouble("sumcount")); user.setAvgcount(rs.getDouble("avgcount")); list.add(user); } } catch (Exception ex) { Logger.getLogger(UserService.class.getName()).log(Level.SEVERE, null, ex); }finally{ JdbcUtil.free(rs, ps, conn); } return list; } }
(4)Poitest.java
public class Poitest { /** * @param args the command line arguments */ public static void main(String[] args) { InputStream inputStream = null; OutputStream outputStream =null; try { inputStream = new FileInputStream(new File("E:\\hello_temp.xls")); outputStream = new FileOutputStream(new File("E:\\hello1.xls")); writeToExcelByTemp2(inputStream,outputStream); System.out.println("成功生成"); } catch (FileNotFoundException ex) { Logger.getLogger(Poitest.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Poitest.class.getName()).log(Level.SEVERE, null, ex); }finally{ if(null!=outputStream){ try { outputStream.close(); } catch (IOException ex) { Logger.getLogger(Poitest.class.getName()).log(Level.SEVERE, null, ex); } } if(null!=inputStream){ try { inputStream.close(); } catch (IOException ex) { Logger.getLogger(Poitest.class.getName()).log(Level.SEVERE, null, ex); } } } } /** * 根据模版生成Excel * @param inputStream * @param outputStream * @throws IOException */ public static void writeToExcelByTemp2(InputStream inputStream, OutputStream outputStream) throws IOException{ List<User> list = UserService.getUserList(); int length = list.size(); //New Workbook Workbook wb = new HSSFWorkbook(inputStream); //New Sheet Sheet sheet = wb.getSheetAt(0); int curRowIndex = 0; for(int rowIndex=1; rowIndex<=length;rowIndex++){ curRowIndex = rowIndex; //获取一行,如果为空则新建 Row row = sheet.getRow(rowIndex); if(row == null){ row = sheet.createRow(rowIndex); } User user = list.get(rowIndex-1); //根据user的对象个数创建列数 for(int cellNum=0; cellNum<10;cellNum++){ Cell cell = row.getCell(cellNum); if(cell==null){ cell = row.createCell(cellNum); } switch(cellNum){ case 0: cell.setCellValue(user.getId()); break; case 1: cell.setCellValue(user.getName()); break; case 2: cell.setCellValue(user.getNo()); break; case 3: cell.setCellValue(user.getNativePlace()); break; case 4: cell.setCellValue(user.getEdu()); break; case 5: cell.setCellValue(user.getMath()); break; case 6: cell.setCellValue(user.getComputer()); break; case 7: cell.setCellValue(user.getEnglish()); break; case 8: cell.setCellFormula("SUM(F"+(rowIndex+1)+":H"+(rowIndex+1)+")"); break; case 9: cell.setCellFormula("I" + (rowIndex+1) +"/3"); break; } } } curRowIndex++; Row row = sheet.createRow(curRowIndex); Cell cell0 = row.createCell(0); cell0.setCellValue("总计"); Cell cell5 = row.createCell(5); cell5.setCellFormula("SUM(F2:"+"F"+(curRowIndex)+")"); Cell cell6 = row.createCell(6); cell6.setCellFormula("SUM(G2:"+"G"+(curRowIndex)+")"); Cell cell7 = row.createCell(7); cell7.setCellFormula("SUM(H2:"+"H"+(curRowIndex)+")"); Cell cell8 = row.createCell(8); cell8.setCellFormula("SUM(I2:"+"I"+(curRowIndex)+")"); Cell cell9 = row.createCell(9); cell9.setCellFormula("AVERAGE(J2:"+"J"+(curRowIndex)+")"); wb.write(outputStream); } }
Excel模版:
生成结果: