struts2将excel文件内容导入数据库 ----- POI

原理： 先将文件导入，再读取文件内容，提取数据，生成对象，保存到数据库。

jar包：POI中poi-3.9-20121203.jar，poi-ooxml-3.9-20121203.jar这两个个为必须。实验结果。

           xbean.jar必须。

在导入的基础上操作：

public String uploadFile(){String directory = "/upload";          String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);          //生成上传的文件对象          File target = new File(targetDirectory,uploadFileFileName);          //如果文件已经存在，则删除原有文件          if(target.exists()){              target.delete();          }          //复制file对象，实现上传          try {              FileUtils.copyFile(uploadFile, target);          } catch (IOException e) {              e.printStackTrace();          }        //从导入的文件中读取数据        loadUserInfo(uploadFileFileName);  return SUCCESS;}private void loadUserInfo(String uploadFileFileName) {        //读取刚才上传文件，确保路径相同        String directory = "/upload";        String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);        File target = new File(targetDirectory,uploadFileFileName);        // HSSFWorkbook只能用来读取2003前（含）的版本， .xls  读取Excel2007时发生如下异常：                 org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML.                       You are calling the part of POI that deals with OLE2 Office Documents.                       You need to call a different part of POI to process this data (eg XSSF instead of HSSF)        //XSSFWorkbook 只能读取2007版本 .xlsx  读取Excel2003以前（包括2003）的版本时却发生了如下新异常：                 org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: '*.xls'               //XSSF和HSSF虽然在不同的包里，但却引用了同一接口Workbook，可以用下面判断        Workbook wb = null;        try{            FileInputStream fi = new FileInputStream(target);              if (uploadFileFileName.toLowerCase().endsWith("xls")) {                 wb = new HSSFWorkbook(fi);            }else if(uploadFileFileName.toLowerCase().endsWith("xlsx")) {                 wb = new XSSFWorkbook(fi);            }             Sheet sheet = wb.getSheetAt(0);                            int rowNum = sheet.getLastRowNum()+1;              userList = new ArrayList<User>();            //i 从1开始表示第一行为标题 不包含在数据中            for(int i=1;i<rowNum;i++){                user = new User();                Row row = sheet.getRow(i);                  int cellNum = row.getLastCellNum();                  for(int j=0;j<cellNum;j++){                      Cell cell = row.getCell(j);                      String cellValue = null;                      switch(cell.getCellType()){ //判断excel单元格内容的格式，并对其进行转换，以便插入数据库                          case 0 : cellValue = String.valueOf((int)cell.getNumericCellValue()); break;                          case 1 : cellValue = cell.getStringCellValue(); break;                          case 2 : cellValue = String.valueOf(cell.getDateCellValue()); break;                          case 3 : cellValue = ""; break;                          case 4 : cellValue = String.valueOf(cell.getBooleanCellValue()); break;                          case 5 : cellValue = String.valueOf(cell.getErrorCellValue()); break;                      }                                            switch(j){//通过列数来判断对应插如的字段                          //数据中不应该保护ID这样的主键记录                                              //case 0 : user.setId(Integer.valueOf(cellValue));break;                          case 0 : user.setUserName(cellValue);break;                          case 1 : user.setPassword(cellValue);break;                          case 2 : user.setFirstName(cellValue);break;                        case 3 : user.setLastName(cellValue);break;                        case 4 : user.setAddress(cellValue);break;                    }                  }                  userList.add(user);            }             userIntoDB(userList);         }catch(IOException e){              e.printStackTrace();        }      }    private void userIntoDB(List<User> userList) {        // TODO Auto-generated method stub        int num = userList.size();        for(int i=0; i<num; i++){            userService.add(userList.get(i));        }    }