java读取excel 支持所有版本

来源：互联网发布：java中重载的意义编辑：程序博客网时间：2024/06/05 17:38

springmvc 写的一个小例子读取excel里的数据并且保存需要的jar包为dom4j-1.6.1.jar poi-3.9-20121203.jar poi-examples-3.9-20121203.jar poi-excelant-3.9-20121203.jar poi-ooxml-3.9-20121203.jar poi-ooxml-schemas-3.9-20121203.jar poi-scratchpad-3.9-20121203.jar xmlbeans-2.6.0.jar

@RequestMapping(value = "/word/file", method = RequestMethod.POST)
     public String fileWord(@RequestParam(value=("file"),required=false) MultipartFile file) throws IOException {

   try {
    String filepath = "E:\\" + file.getOriginalFilename();

    FileUtils.writeByteArrayToFile(new File(filepath), file.getBytes());
     String fileType = filepath.substring(filepath.lastIndexOf(".")+1);
        Workbook workbook = null;
        InputStream is = new FileInputStream(filepath);
        if (fileType.equalsIgnoreCase("xlsx")) {
          workbook = new XSSFWorkbook(is);
        }else if(fileType.equalsIgnoreCase("xls")){
          workbook = new HSSFWorkbook(is);
        }else {
    throw new Exception("暂时只支持xlsx和xls格式的excel读取");
     }


     // 循环工作表Sheet
     for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
      Sheet sheetAt = workbook.getSheetAt(numSheet);
      if (sheetAt == null) {
       continue;
      }
      // 循环行Row
      for (int rowNum = 1; rowNum <= sheetAt.getLastRowNum(); rowNum++) {
       Row row = sheetAt.getRow(rowNum);
       if (row != null) {
        Words words = new Words();

        words.setMerchantNumber(row.getCell(0)+"");
        words.setMerchantName(row.getCell(1)+"");
        words.setShopNumber(row.getCell(2)+"");
        words.setShopName(row.getCell(3)+"");
        words.setOrderNumber(row.getCell(4)+"");
        words.setMoney(Double.parseDouble(row.getCell(5)+""));
        words.setState(row.getCell(6)+"");
        String parseExcel = parseExcel(row.getCell(7));
        words.setCreateDate(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(parseExcel));
        words.setNeeded(row.getCell(8)+"");

        wordsService.saveWord(words);
       }
      }
     }

     File file2 = new File(filepath);

     file2.delete();
  } catch (Exception e) {
   System.out.println("在读取word格式的文件时出现了未知错位，错位在wordsController类中");
   e.printStackTrace();
  }


   return "forward:/word/words/1";
   }

0 0