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