首先是service层定义一个方法,返回值类型你们自己根据需要定义:
PartImportPartListRes importToTemp(InputStream is, String fileName, String userId, String userName);
serviceImpl实现方法逻辑:
@Override
public PartImportPartListRes importToTemp(InputStream is, String fileName, String userId, String userName) {
ParameterValidator.validateParamString(userId, "optId 不能为空");
ParameterValidator.validateParamString(userName, "optName 不能为空");
PartImportPartListRes importPartListRes = new PartImportPartListRes();
Workbook workbook = null;
if("xlsx".equals(fileName.split("\\.")[1])){
logger.info("xlsx prase data start to insert into partAllThird");
try {
workbook = new XSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
throw new BaseException(ReturnCodeEnum.COMMON_ERROR.getMessage());
}
}else if("xls".equals(fileName.split("\\.")[1])){
logger.info("xls prase data start to insert into partAllThird");
try {
workbook = new HSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
throw new BaseException(ReturnCodeEnum.COMMON_ERROR.getMessage());
}
}else{
throw new BaseException(ReturnCodeEnum.PARAM_ERROR.getMessage(), "文件格式有误,请导入xlsx/xls格式的文档");
}
final Workbook workbookf = workbook;
logger.info("3、将文件中的数据插入到临时表中");
String importMessage = dataHandle(workbookf.getSheetAt(0), fileName, userId, userName, partUUID,partType);
logger.info("importPartListRes---> {}" + importPartListRes.toString());
return importPartListRes;
}
下面的方法dataHandle()是将数据导入到数据库的关键方法:
private String dataHandle(Sheet sheet, String fileName ,String userId, String userName, String uuid ,String partType ){
String importMessage = null;
int succCount = 0,failCount = 0;
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
if (paramCheck(row, partType)) {
/**
* 一堆逻辑
*/
xxxxxMapper.insertSelective(object);
}else{
throw new BaseException("导入文件中缺少必填字段");
}
}
importMessage = " 文件载入成功,将导入正确数据 " + succCount + "条 " + "(错误数据"+ failCount +"条不导入),是否导入?" ;
return importMessage;
}
getValue()是获取单元格中的值,将单元格中的值复制给你定义的bean,循环插入到数据库中:
private String getValue(Cell cell) {
if ( cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
// 返回布尔类型的值
return String.valueOf(cell.getBooleanCellValue());
} else if ( cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
// 返回数值类型的值
DecimalFormat df = new DecimalFormat("0");
return df.format(cell.getNumericCellValue());
} else {
// 返回字符串类型的值
return String.valueOf(cell.getStringCellValue());
}
}
调用方式如下:
String bName = getValue(row.getCell(0));
String fName = getValue(row.getCell(1));
最后是controller层:
/**
* 文件导入
* @param uploadFile
* @return
*/
@RequestMapping(value = "import/partList", method = RequestMethod.POST, produces = "application/json")
@ResponseBody
public DataResult importExcel(@RequestParam(value = "optId", required = false) String optId, @RequestParam(value = "optName", required = false) String optName, @RequestParam("uploadFile") MultipartFile uploadFile) {
DataResult result = new DataResult();
logger.info("导入文件名-->{}", uploadFile.getOriginalFilename());
try {
PartImportPartListRes res = xxxxxService.importToTemp(uploadFile.getInputStream(), uploadFile.getOriginalFilename(), optId, optName);
result.setData(res);
} catch (IOException e) {
e.printStackTrace();
}
logger.info("导入成功 - {}", JSONObject.toJSON(result));
return result;
}
大家注意一点的是:一般文件导入设计到大数据量的时候,导入的过程会很慢,如果在对数据添加校验,和库中的数据对比,可能会更慢一下,一般这样我们直接开辟一个新的线程,去导入,不至于前端页面一直处于加载的状态。