poi的读写

自己创建student类

 

//将数据库的数据写入到excel2007

public class CreateStudent {

    public static Connection conn = null;

//获得数据库中的结果

    public static ResultSet getStudentInformation() {

//JDBC为自己创建的数据库连接的工具类

        conn = JDBC.getConnection();
        if (conn != null) {
            try {
                Statement state = conn.createStatement();
                String sql = "select * from dbo.Student";
                ResultSet rs = state.executeQuery(sql);
                return rs;
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }

    /**

     * 将获得的数据写入到excel2007
     * @param args
     */
    public static void main(String[] args) {
        String path = "E://Student2007.xlsx";
        ResultSet resultSet = getStudentInformation();
        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet();
        try {
            int i = 0;
            while (resultSet.next()) {
                XSSFRow row = sheet.createRow(i);
                row.createCell(0).setCellValue(resultSet.getString("name"));
                row.createCell(1).setCellValue(resultSet.getString("sex"));
                row.createCell(2).setCellValue(resultSet.getString("age"));
                row.createCell(3).setCellValue(resultSet.getString("sclass"));
                i++;
            }
            File file = new File(path);
            FileOutputStream os = new FileOutputStream(file);
            book.write(os);
            System.out.println("学生信息导出成功");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            System.out.println("学生信息导出失败" + e);
            // e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

 

 

//从现存的excel2007中读取数据

public class ReadStudents {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        String path = "E://Student2007.xlsx";
        try {
            FileInputStream is = new FileInputStream(new File(path));
            XSSFWorkbook book = new XSSFWorkbook(is);
            XSSFSheet sheet = book.getSheetAt(0);

             //一共三行数据，sheet.getLastRowNum()为2
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                // System.out.println("row" + sheet.getLastRowNum());
                XSSFRow row = sheet.getRow(i);
                if (row == null) {
                    break;
                }
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    // System.out.println("cell" + row.getLastCellNum());
                    XSSFCell cell = row.getCell(j);
                    String cellStr = null;
                    if (XSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
                        cellStr = String.valueOf(cell.getBooleanCellValue());
                    } else if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                        cellStr = String.valueOf(cell.getNumericCellValue());
                    } else {
                        cellStr = cell.getStringCellValue();
                    }

                    if (j == 0) {
                        System.out.print("姓名： " + cellStr);
                    } else if (j == 1) {
                        System.out.print("  性别： " + cellStr);
                    } else if (j == 2) {
                        System.out.print("  年龄： " + cellStr);
                    } else if (j == 3) {
                        System.out.println("  班级： " + cellStr);
                    }

                }

            }
            System.out.println("  学生信息读取成功 ");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            // e.printStackTrace();
            System.out.println("  学生信息读取失败 " + e);
        }

    }
}