Springboot之使⽤POI读取解析Excel⽂件1、引⼊依赖jar包。
在l中引⼊两个依赖的包即可：
<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 = SheetAt(0);
XSSFRow titleCell = Row(0);
for (int i = 1; i <= LastRowNum(); i++) {
XSSFRow xssfRow = Row(i);
int minCell = FirstCellNum();
int maxCell = LastCellNum();
XSSFCell bidCode = Cell(0);
XSSFCell owerName = Cell(1);
XSSFCell ownersex = Cell(2);
XSSFCell owerMobile = Cell(3);
XSSFCell basePrice = Cell(4);
XSSFCell bidType = Cell(5);
XSSFCell bidDealerId = Cell(6);
XSSFCell bidDealerName = Cell(7);spring怎么读多个文件
XSSFCell bidName = Cell(8);
XSSFCell bidMobile = Cell(9);
XSSFCell carNumber = Cell(10);
XSSFCell autoNumber = Cell(11);
XSSFCell carUnifiedNumber = Cell(12);
XSSFCell curBid = Cell(13);
XSSFCell bidStatus = Cell(14);
XSSFCell maxBid = Cell(15);
XSSFCell priceGap = Cell(16);
XSSFCell bidCreateTime = Cell(17);
XSSFCell expectPrice = Cell(18);
XSSFCell roundStatus = Cell(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:{" + BidCode() +"}"+ 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 (CellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.BooleanCellValue());
} else if (CellType() == xssfRow.CELL_TYPE_NUMERIC) {
String result = "";
if (CellStyle().getDataFormat() == 22) {
// 处理⾃定义⽇期格式：m⽉d⽇(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
double value = NumericCellValue();
Date date = org.apache.poi.ss.JavaDate(value);
result = sdf.format(date);
} else {
double value = NumericCellValue();
CellStyle style = CellStyle();
DecimalFormat format = new DecimalFormat();
String temp = DataFormatString();
/
/ 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
}
return result;
} else {
return String.StringCellValue());
}
} 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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。