java利用POI操作excel

POI操作excel,保存数据到数据库流程：

1.获取工作簿（内含多个工作表）

2.获取该工作簿的所有工作表

3.对工作表进行取值操作，遍历每一行的每一个单元格，取值放到对应的model存储器中

4.编写保存方法，保存model对象到MySQL

1.控制器代码，执行读表或者保存数据到数据库

public class Controler {  public static void main(String[] args) throws FileNotFoundException, IOException {  String path = "E:/2015级OOP实训材料/附件1：2013级2015—2016学年度第一学期教材计划【完整格式数据】.xlsx";  Read.start(path);  //Insert.start(path);  }}

2.然后先看读表的代码：

public class Read { public Read(){   } public static void start(String path) throws FileNotFoundException, IOException{  Workbook book = getWorkBook(path);  Sheet sheet = getSheets(book);  SheetIterator(sheet); }    private static void SheetIterator(Sheet sheet){      Iterator<Row> iterator = sheet.iterator();   while(iterator.hasNext()){    Row nextRow = iterator.next();    if(nextRow.getRowNum()<3){              continue;             }             Iterator<Cell> cellIterator = nextRow.cellIterator();             while (cellIterator.hasNext()) {                 Cell cell = cellIterator.next();                                   switch (cell.getCellType()) {                     case Cell.CELL_TYPE_STRING:                         System.out.print(cell.getStringCellValue());                         break;                     case Cell.CELL_TYPE_BOOLEAN:                         System.out.print(cell.getBooleanCellValue());                         break;                     case Cell.CELL_TYPE_NUMERIC:                         System.out.print(cell.getNumericCellValue());                         break;                 }                 System.out.print("   ");             }             System.out.println("   ");   }    System.out.println("   "); }  private static Sheet getSheets(Workbook book) {  return(Sheet) book.getSheetAt(0); } public static Workbook getWorkBook(String path) throws FileNotFoundException, IOException{  return path.endsWith(".xls")?(new HSSFWorkbook(new FileInputStream(new File(path)))):(path.endsWith(".xlsx")?(new XSSFWorkbook(new FileInputStream(new File(path)))):(null)); }}

3.保存到数据库的代码，与读表类似

public class Insert { public Insert() { } public static void start(String path) throws FileNotFoundException, IOException {  Workbook book = getWorkBook(path);  Sheet sheet = getSheets(book);  System.out.println("sheet name:" + sheet.getSheetName());  SheetIterator(sheet); } private static void SheetIterator(Sheet sheet) {  Iterator<Row> iterator = sheet.iterator();  while (iterator.hasNext()) {   Row nextRow = iterator.next();   if (nextRow.getRowNum() < 3) {    continue;   }   Iterator<Cell> cellIterator = nextRow.cellIterator();   StringBuffer course_str = new StringBuffer("");   while (cellIterator.hasNext()) {    Cell cell = cellIterator.next();    nextRow.getCell(cell.getColumnIndex()).setCellType(Cell.CELL_TYPE_STRING);    if (cell.getColumnIndex() < 8) {     Object course_obj = cell.getStringCellValue();     course_str.append(course_obj + "-");    }       }   Object[] course_obj = new Object[20];   String c_str = new String(course_str);   System.out.println(c_str);   course_obj = c_str.split("-");   Course course = new Course();   course.setSerial(Integer.parseInt(course_obj[0].toString()));   course.setDept(course_obj[1].toString());   course.setMajor(course_obj[2].toString());   course.setCourse_name(course_obj[3].toString());   course.setCredit(course_obj[4].toString());   course.setNatrue(course_obj[5].toString());   course.setTest_way(course_obj[6].toString());   course.setTeacher_in_charge(course_obj[7].toString());   CourseImp c = new CourseImp();      try {    c.save(course);//调用你的方法保存数据   } catch (Exception e) {    e.printStackTrace();   }   System.out.println(" ");  }  System.out.println("   "); } private static Sheet getSheets(Workbook book) {  return (Sheet) book.getSheetAt(0); } public static Workbook getWorkBook(String path) throws FileNotFoundException, IOException {  return path.endsWith(".xls") ? (new HSSFWorkbook(new FileInputStream(new File(path))))    : (path.endsWith(".xlsx") ? (new XSSFWorkbook(new FileInputStream(new File(path)))) : (null)); }}

3，存储器model

public class Course implements Serializable { /**  *   */ private static final long serialVersionUID = -993930170402063910L; private Integer serial;  private String dept;  private String major;  private String course_name;  private String credit;  private String natrue;  private String test_way;  private String teacher_in_charge; public Course(){  super(); } public Integer getSerial() {  return serial; } public void setSerial(Integer serial) {  this.serial = serial; } public String getDept() {  return dept; } public void setDept(String dept) {  this.dept = dept; } public String getMajor() {  return major; } public void setMajor(String major) {  this.major = major; } public String getCourse_name() {  return course_name; } public void setCourse_name(String course_name) {  this.course_name = course_name; } public String getCredit() {  return credit; } public void setCredit(String credit) {  this.credit = credit; } public String getNatrue() {  return natrue; } public void setNatrue(String natrue) {  this.natrue = natrue; } public String getTest_way() {  return test_way; } public void setTest_way(String test_way) {  this.test_way = test_way; } public String getTeacher_in_charge() {  return teacher_in_charge; } public void setTeacher_in_charge(String teacher_in_charge) {  this.teacher_in_charge = teacher_in_charge; }}

4,save方法

public class CourseImp implements ICourse {  private static final String sql_save = " INSERT INTO course(序号,系,专业名称,课程名称,学分,课程性质,考核方式,责任教师) VALUES(?,?,?,?,?,?,?,?)";  @Override public Integer save(Course course) throws Exception {    Connection conn = null;  PreparedStatement prep = null;        int i = 0;  try {   conn = DBUtil.getConnection();   prep = conn.prepareStatement(sql_save);   prep.setInt(1, course.getSerial());   prep.setString(2, course.getDept());   prep.setString(3, course.getMajor());   prep.setString(4, course.getCourse_name());   prep.setString(5, course.getCredit());   prep.setString(6, course.getNatrue());   prep.setString(7, course.getTest_way());   prep.setString(8, course.getTeacher_in_charge());      i  = prep.executeUpdate();    if(i>0){    System.out.println("1 row affected...");    }else if(i==0){     System.out.println("insert failed...");    }  } catch (SQLException e) {   // TODO Auto-generated catch block   e.printStackTrace();  }finally{   DBUtil.release( prep, conn);  }  return i; } }