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