Springboot 之 使用POI读取解析Excel文件

1、引入依赖jar包。

在pom.xml中引入两个依赖的包即可：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>RELEASE</version>
</dependency>
 
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>RELEASE</version>
</dependency>

 

2、读取xxx.xlsx

@GetMapping(value = "/impPriceRecord")
   public InvokeResult impPriceRecord() throws Exception {
       try {
           List<AmoycarClue> infos = new ArrayList<AmoycarClue>();
           InputStream is = new FileInputStream("D:/xxx.xlsx");
           XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
           XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
           XSSFRow titleCell = xssfSheet.getRow(0);
           for (int i = 1; i <= xssfSheet.getLastRowNum(); i++) {
               XSSFRow xssfRow = xssfSheet.getRow(i);
               int minCell = xssfRow.getFirstCellNum();
               int maxCell = xssfRow.getLastCellNum();
               XSSFCell bidCode = xssfRow.getCell(0);
               XSSFCell owerName = xssfRow.getCell(1);
               XSSFCell ownersex = xssfRow.getCell(2);
               XSSFCell owerMobile = xssfRow.getCell(3);
               XSSFCell basePrice = xssfRow.getCell(4);
               XSSFCell bidType = xssfRow.getCell(5);
               XSSFCell bidDealerId = xssfRow.getCell(6);
               XSSFCell bidDealerName = xssfRow.getCell(7);
               XSSFCell bidName = xssfRow.getCell(8);
               XSSFCell bidMobile = xssfRow.getCell(9);
               XSSFCell carNumber = xssfRow.getCell(10);
               XSSFCell autoNumber = xssfRow.getCell(11);
               XSSFCell carUnifiedNumber = xssfRow.getCell(12);
               XSSFCell curBid = xssfRow.getCell(13);
               XSSFCell bidStatus = xssfRow.getCell(14);
               XSSFCell maxBid = xssfRow.getCell(15);
               XSSFCell priceGap = xssfRow.getCell(16);
               XSSFCell bidCreateTime = xssfRow.getCell(17);
               XSSFCell expectPrice = xssfRow.getCell(18);
               XSSFCell roundStatus = xssfRow.getCell(19);
 
               AmoycarClue model = new AmoycarClue();
               model.setBidCode(getValue(bidCode));
               model.setBizoppCode("");
               model.setOriginalCode("");
               model.setWorkNo("");
               model.setOwnerName(getValue(owerName) + "");
               model.setOwnerSex(Double.valueOf(getValue(ownersex)).intValue());
               model.setOwnerMobile(getValue(owerMobile));
               model.setBasePrice(Double.valueOf(getValue(basePrice)));
               model.setBidName(getValue(bidName));
               model.setBidMobile(getValue(bidMobile));
               model.setBidType(Double.valueOf(getValue(bidType)).intValue());           
               model.setCarCode(code);
               model.setMaxBid(Double.valueOf(getValue(maxBid)));
               model.setCurBid(Double.valueOf(getValue(curBid)));
               model.setPriceGap((Double.valueOf(getValue(priceGap))));
               model.setBidStatus(Double.valueOf(getValue(bidStatus)).intValue());
 
               SimpleDateFormat pat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
               if (bidCreateTime!= null) {
                   try {
                       String sDate=getValue(bidCreateTime);
                       java.util.Date uDate = pat.parse(sDate);
                       model.setBidCreatetime(uDate);
                   } catch (ParseException ex) {
                       ex.printStackTrace();
                   }
               }
               model.setRoundStatus(Double.valueOf(getValue(roundStatus)).intValue());
               model.setExpectPrice(Double.parseDouble(getValue(expectPrice)));
               model.setBidDealerId(getValue(bidDealerId));
               model.setBidDealerName(getValue(bidDealerName));
              
 
             
               try {
                   auctionClient.syncAmoycarClue(model);
                   Thread.sleep(1000);
               } catch (Exception ex) {
                   ex.printStackTrace();
                   return InvokeResult.failure(500, "impPriceRecord:插入错误BidCode:{" + model.getBidCode() +"}"+ ex.getMessage());
               }
           }
           return InvokeResult.success(true);
       } catch (Exception e) {
 
           return InvokeResult.failure(500,"impPriceRecord:历史数据导入错误"+e.getMessage());
       }
 
 
   }

 

3、格式方法

private String getValue(XSSFCell xssfRow) {
        if (xssfRow != null) {
//            if (xssfRow != null) {
//                xssfRow.setCellType(xssfRow.CELL_TYPE_STRING);
//            }
            if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
                return String.valueOf(xssfRow.getBooleanCellValue());
            } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
                String result = "";
                if (xssfRow.getCellStyle().getDataFormat() == 22) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    double value = xssfRow.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                    result = sdf.format(date);
                } else {
                    double value = xssfRow.getNumericCellValue();
                    CellStyle style = xssfRow.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();
                    // 单元格设置成常规
                    if (temp.equals("General")) {
                        format.applyPattern("#");
                    }
                    result = format.format(value);
                }
                return result;
            } else {
                return String.valueOf(xssfRow.getStringCellValue());
            }
        } else
            return "0";
    }

Java操作Excel中XSSFCell.CELL_TYPE_BOOLEAN、BOOLEAN、NUMERIC无定义解决方法

错误原因：jar包版本更新，官方改动；

解决方法：

导入CellType包import org.apache.poi.ss.usermodel.CellType
使用CellType.BOOLEAN代替XSSFCell.CELL_TYPE_BOOLEAN

使用CellType.NUMBERIC代替XSSFCell.CELL_TYPE_NUMBERIC

 

转载于：https://www.cnblogs.com/lizp/p/9008103.html