场景
SpringBoot中使用POI实现Excel导入到数据库（图文教程已实践）：
在Excel导入到数据库后进行格式验证，如果有错误则将错误的Cell标红然后验证完成后
将标红的Excel下载到客户端。
实现
js中发送请求
//解析Excel操作
function parseExcel(){
var url = "/wmsReceiveOrder/parseExcel";
$.ajax({
type: 'POST',
url: url,
cache: false, //禁用缓存
contentType: false,
dataType: "json",
processData:false,
success: function (result) {
//250则为验证不通过则下载不正确的excel
if(result.statusCode=="250"){
alert(result.message)
window.location.href="/wmsReceiveOrder/downloadMistakeExcel";
}else{
alert(result.message)
if(result.statusCode=="200"){
window.location.reload()
}
}
}
})
return false;
}
后台解析Excel的方法
@Description("解析Excel")
@ResponseBody
@RequestMapping("/parseExcel")
@Transactional
public Map<String, Object> parseExcel(HttpServletRequest request) {
return receiveOrderService.parseExcel(request);
}serviceImpl
/***
* 解析excel
* @param request
* @return
*/
@Override
@Transactional
public Map<String, Object> parseExcel(HttpServletRequest request) {
Map<String, Object> result = new HashMap<String, Object>();
Workbook workbook = null;
//验证标识
Boolean isValidatePass = true;
//获取文件路径
String path = (String)request.getSession().getAttribute("currFilePath");
if(path==null||path==""){
result.put("statusCode", "300");
result.put("message", "请先上传excel文件再导入");
}else{
//获取文件格式
String fileType = path.substring(path.lastIndexOf(".") + 1, path.length());
try {
InputStream stream = new FileInputStream(path);
//如果后缀名为xls，使用HSSF
if (fileType.equals("xls")) {
workbook = new HSSFWorkbook(stream);
//如果后缀名是xlsx，使用XSSF
}else if (fileType.equals("xlsx")){
workbook = new XSSFWorkbook(stream);
}
Sheet sheet= workbook.getSheet("sheet1");
//获取行数
int rows=sheet.getPhysicalNumberOfRows();
WmsReceiveOrder receiveOrder =new WmsReceiveOrder();
//获取第一行数据
Row row1 =sheet.getRow(0);
if(row1!=null){
//获取采购订单号
row1.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String purchaseCode =row1.getCell(1).getStringCellValue();
receiveOrder.setPurchaseCode(purchaseCode);
}
//获取第二行数据
Row row2 =sheet.getRow(1);
if(row2!=null){
//获取供应商送货单号
row2.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String deliveryCode =row2.getCell(1).getStringCellValue();
receiveOrder.setDeliveryCode(deliveryCode);
//日期格式加校验
Cell deliveryTimeCell = row2.getCell(3);
if(deliveryTimeCell!=null){
//如果是数值类型
if(deliveryTimeCell.getCellType()==0){
if(HSSFDateUtil.isCellDateFormatted(deliveryTimeCell)){
//获取送货日期
Date deliveryTime =deliveryTimeCell.getDateCellValue();
receiveOrder.setDeliveryTime(deliveryTime);
}else{
//设置送货时间为红色
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
deliveryTimeCell.setCellStyle(style);
isValidatePass=false;
}
}else{
//设置送货时间为红色
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
deliveryTimeCell.setCellStyle(style);
isValidatePass=false;
}
}
//获取供应商编号
row2.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
String supplierCode =row2.getCell(5).getStringCellValue();
receiveOrder.setSupplierCode(supplierCode);
//获取供应商名称
row2.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
String supplierName =row2.getCell(6).getStringCellValue();
receiveOrder.setSupplierName(supplierName);
}
//获取第三行数据
Row row3 =sheet.getRow(2);
if(row3!=null){
//获取ERP入货单号
row3.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String erpInCode =row3.getCell(1).getStringCellValue();
receiveOrder.setErpInCod(erpInCode);
Cell inTimeCell= row3.getCell(3);
//如果是数值类型
if(inTimeCell!=null&&inTimeCell.getCellType()==0){
if(HSSFDateUtil.isCellDateFormatted(inTimeCell)){
//获取入库日期
Date inTime =row3.getCell(3).getDateCellValue();
receiveOrder.setInTime(inTime);
}else{
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
inTimeCell.setCellStyle(style);
isValidatePass=false;
}
}else{
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
inTimeCell.setCellStyle(style);
isValidatePass=false;
}
}
receiveOrder.setType(1);
receiveOrder.setStatus("1");
//校验子表各条的数量以及生产日期以及物料编号是否存在
for (int currentRow=4;currentRow<rows;currentRow++) {
WmsReceiveOrderDetails wmsReceiveOrderDetails =new WmsReceiveOrderDetails();
//获取物料编号
sheet.getRow(currentRow).getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String materielNumber = sheet.getRow(currentRow).getCell(1).getStringCellValue();
//获取生产日期
Cell productDateCell= sheet.getRow(currentRow).getCell(4);
//如果是数值类型
if(productDateCell!=null&&productDateCell.getCellType()==0){
if(!HSSFDateUtil.isCellDateFormatted(productDateCell)) {
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
productDateCell.setCellStyle(style);
isValidatePass = false;
}
}else{
//设置生产日期为红色
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
productDateCell.setCellStyle(style);
isValidatePass=false;
}
//对数量进行验证
Cell numCell =sheet.getRow(currentRow).getCell(5);
//如果是数值类型
if(numCell!=null&&numCell.getCellType()==0){
if(HSSFDateUtil.isCellDateFormatted(numCell)){
//设置数量单元格为红色
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
numCell.setCellStyle(style);
isValidatePass=false;
}else{
if(!String.valueOf(numCell.getNumericCellValue()).contains(".0")){
//设置数量单元格为红色
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
numCell.setCellStyle(style);
isValidatePass=false;
}
}
}else{
//设置为红色
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
numCell.setCellStyle(style);
isValidatePass=false;
}
//对物料编号进行验证
QueryWrapper<BusMaterielInfo> busMaterielInfoQueryWrapper =new QueryWrapper<BusMaterielInfo>();
busMaterielInfoQueryWrapper.eq("materiel_number",materielNumber);
BusMaterielInfo busMaterielInfo = busMaterielInfoMapper.selectOne(busMaterielInfoQueryWrapper);
Cell materialNumberCell=sheet.getRow(currentRow).getCell(1);
if(busMaterielInfo==null){
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
materialNumberCell.setCellStyle(style);
isValidatePass=false;
}
}
//验证完成
if(!isValidatePass){
//将错误文件下载
result.put("statusCode", "250");
result.put("message", "Excel存在错误(红色部分)!");
FileOutputStream fileOut = new FileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
}else {
//查询条件
Map<String, Object> map = new HashMap<String, Object>();
map.put("delivery_code", receiveOrder.getDeliveryCode());
map.put("supplier_code", receiveOrder.getSupplierCode());
List<WmsReceiveOrder> wmsReceicveOrderList = wmsReceiveOrderMapper.selectByMap(map);
if (wmsReceicveOrderList.size() == 0 || wmsReceicveOrderList == null) {
boolean isSaveReceiveOrder = false;
//如果前面主表验证通过则插入主表
if (isValidatePass) {
//插入receiveOrder表数据
isSaveReceiveOrder = this.save(receiveOrder);
}
List<WmsReceiveOrderDetails> receiveOrderDetailsList = new ArrayList<WmsReceiveOrderDetails>();
if (isSaveReceiveOrder) {
//如果插入表头成功，获取插入数据的ID并插入详情表
Long receiveId = receiveOrder.getId();
for (int currentRow = 4; currentRow < rows; currentRow++) {
WmsReceiveOrderDetails wmsReceiveOrderDetails = new WmsReceiveOrderDetails();
wmsReceiveOrderDetails.setReceiveId(receiveId);
//获取物料编号
sheet.getRow(currentRow).getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String materielId = sheet.getRow(currentRow).getCell(1).getStringCellValue();
wmsReceiveOrderDetails.setMaterielNumber(materielId);
//获取物料名称
sheet.getRow(currentRow).getCell(2).setCellType(Cell.CELL_TYPE_STRING);
String materielName = sheet.getRow(currentRow).getCell(2).getStringCellValue();
wmsReceiveOrderDetails.setMaterielName(materielName);
//获取供应商批次
sheet.getRow(currentRow).getCell(3).setCellType(Cell.CELL_TYPE_STRING);
String supplierBatch = sheet.getRow(currentRow).getCell(3).getStringCellValue();
wmsReceiveOrderDetails.setSupplierBatch(supplierBatch);
//获取生产日期
Date productDate = sheet.getRow(currentRow).getCell(4).getDateCellValue();
wmsReceiveOrderDetails.setProductDate(productDate);
//获取数量
sheet.getRow(currentRow).getCell(5).setCellType(Cell.CELL_TYPE_STRING);
Long num = Long.parseLong(sheet.getRow(currentRow).getCell(5).getStringCellValue());
wmsReceiveOrderDetails.setNum(num);
//获取托盘编号
String salverCode = sheet.getRow(currentRow).getCell(6).getStringCellValue();
wmsReceiveOrderDetails.setSalverCode(salverCode);
wmsReceiveOrderDetails.setStatus("0");
receiveOrderDetailsList.add(wmsReceiveOrderDetails);
}
if (receiveOrderDetailsList != null) {
for (WmsReceiveOrderDetails wmsReceiveOrderDetails : receiveOrderDetailsList
) {
boolean isSaveReceiveOrderDetails = wmsReceiveOrderDetailsService.save(wmsReceiveOrderDetails);
if (!isSaveReceiveOrderDetails) {
result.put("statusCode", "300");
result.put("message", "导入物料编号为：" + wmsReceiveOrderDetails.getMaterielNumber() + "出错了!");
} else {
if (isValidatePass == true) {
result.put("statusCode", "200");
result.put("message", "导入收货单成功!");
} else {
result.put("statusCode", "300");
result.put("message", "导入收货单失败!");
}
}
}
}
} else {
result.put("statusCode", "300");
result.put("message", "导入收货单失败!");
}
} else {//是否已经导入过判断结束
result.put("statusCode", "300");
result.put("message", "此收货单已经导入，请勿重复导入！！");
}
}//验证通过后的插入数据库
} catch (FileNotFoundException e) {
e.printStackTrace();
result.put("statusCode", "300");
result.put("message", e.toString());
} catch (IOException e) {
e.printStackTrace();
result.put("statusCode", "300");
result.put("message", e.toString());
}
}//判断是否已经上传文件
return result;
}
错误excel文件下载方法
@Description("错误Excel下载")
@RequestMapping("/downloadMistakeExcel")
public String downloadMistakeExcel(HttpServletRequest request, HttpServletResponse response) {
String path = (String)request.getSession().getAttribute("currFilePath");
if (path != null) {
//设置文件路径
File file = new File(path);
if (file.exists()) {
//时间格式化格式
SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyyMMddHHmmss");
//获取当前时间并作为时间戳
String timeStamp=simpleDateFormat.format(new Date());
response.setContentType("application/force-download");// 设置强制下载不打开
response.addHeader("Content-Disposition", "attachment;fileName="+timeStamp+"mistake.xlsx" );// 设置文件名
byte[] buffer = new byte[1024];
FileInputStream fis = null;
BufferedInputStream bis = null;
try {
fis = new FileInputStream(file);
bis = new BufferedInputStream(fis);
OutputStream os = response.getOutputStream();
int i = bis.read(buffer);
while (i != -1) {
os.write(buffer, 0, i);
i = bis.read(buffer);
}
System.out.println("success");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
return null;
}流程图
