java读取excel 支持所有版本

最新推荐文章于 2022-02-23 17:20:50 发布

原创最新推荐文章于 2022-02-23 17:20:50 发布 · 3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #excel

本文介绍了一个使用SpringMVC实现的简单示例，该示例能够从上传的Excel文件(xls/xlsx格式)中读取数据，并将数据保存到数据库。示例中详细展示了如何解析Excel文件中的每一行数据并映射到Java对象。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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";
   }