一、应用场景
系统中经常有导入功能,如果导入数据量比较大,那么应该是先上传文件,然后后台异步的进行导入数据。
二、代码
@RequestMapping(value="/upfile", method = RequestMethod.POST)
public ResultVo uploadFile(@RequestParam MultipartFile file,HttpServletRequest request) throws Exception{
if(file==null)
return ResultVo.error("1", "上传文件不能为空");
String fileName = file.getOriginalFilename();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
return ResultVo.error("1", "上传文件格式错误,请上传后缀为.xls或.xlsx的文件");
}
String filePath = request.getSession().getServletContext().getRealPath("upload/");
String path = filePath+fileName;
try {
File targetFile = new File(filePath);
if(!targetFile.exists()){
targetFile.mkdirs();
}
BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(path));
out.write(file.getBytes());
out.flush();
out.close();
this.addFileTask(path);
} catch (Exception e) {
e.printStackTrace();
ResultVo.error("1", "上传失败");
}
return ResultVo.success();
}
public void addFileTask(String filepath) {
ImFileTask imFileTask = new ImFileTask();
imFileTask.setFileId(CommonUtil.getKeyId());
imFileTask.setStatus("1");
imFileTask.setComplRate("");
imFileTask.setFilePath(filepath);
imFileTask.setConsumeTm("");
imFileTask.setCreateTm(new Date());
imFileTaskService.addFileTask(imFileTask);
asyncTaskService.asyncInvokeWithParameter(filepath); //开启线程执行导入数据
}
/**
* @author: william
* @Description: 异步线程任务类
* @date: 2018年5月29日 下午8:56:24
* @version: v1.0.0
*/
@Component
public class AsyncTaskService {
private Logger logger = LoggerFactory.getLogger(AsyncTaskService.class);
@Autowired
private ExcelService excelservice;
@Async
public void asyncInvokeWithParameter(String filepath) {
excelservice.importExcelDate(filepath);
}
@Async
public Future<String> asyncInvokeReturnFuture(int i) {
logger.info("asyncInvokeReturnFuture, parementer={}", i);
Future<String> future;
try {
Thread.sleep(1000);
future = new AsyncResult<String>("success:" + i);
} catch (InterruptedException e) {
future = new AsyncResult<String>("error");
}
return future;
}
}
/**
* @author: chaiz
* @Description: TODO
* @date: 2018年5月29日 下午8:33:37
* @version: v1.0.0
*/
@Service
public class ExcelServiceImpl implements ExcelService {
@Autowired
private ImDevInfoService imDevInfoService;
@Override
public JSONObject importExcelDate(String filepath) {
try {
File file = new File(filepath);
InputStream is = new FileInputStream(file);
String fileName = file.getName();
Workbook hssfWorkbook = null;
if (fileName.endsWith("xlsx")){
hssfWorkbook = new XSSFWorkbook(is); //Excel 2007
}else if (fileName.endsWith("xls")){
hssfWorkbook = new HSSFWorkbook(is); //Excel 2003
}
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
Row row = hssfSheet.getRow(rowNum);
if (row == null) {
continue;
}
try {
ImDevInfo devInfo = new ImDevInfo();
Cell cell0 = row.getCell(0);
String devuid = CommonUtil.transformIMEI(getValue(cell0));
devInfo.setDevUid(devuid);
Cell cell1 = row.getCell(1);
devInfo.setSn(getValue(cell1));
Cell cell2 = row.getCell(2);
devInfo.setBrand(getValue(cell2));
Cell cell3 = row.getCell(3);
devInfo.setModel(getValue(cell3));
Cell cell4 = row.getCell(4);
String olstate = getValue(cell4);
devInfo.setSdkVer((int)Double.parseDouble(olstate));
Cell cell5 = row.getCell(5);
devInfo.setOs(getValue(cell5));
Cell cell6 = row.getCell(6);
devInfo.setBrandOs(getValue(cell6));
Cell cell7 = row.getCell(7);
devInfo.setReso(getValue(cell7));
Cell cell8 = row.getCell(8);
devInfo.setRemarks(getValue(cell8));
devInfo.setMftId((Integer) CommonUtil.getSessionAttr(Constants.SESSION_MFT_ID));
devInfo.setOlState(1);
devInfo.setDevState(1);
devInfo.setAppKey("appkey_undefine01");
devInfo.setUpdTm(new Date());
devInfo.setCreateTm(new Date());
imDevInfoService.insert(devInfo);
} catch (Exception e) {
e.printStackTrace();
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private static String getValue(Cell hssfCell) {
if(hssfCell!=null) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_STRING) {
return String.valueOf(hssfCell.getStringCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN){
return String.valueOf(hssfCell.getBooleanCellValue());
} else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_FORMULA){
return String.valueOf(hssfCell.getCellFormula());
} else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_BLANK){
return String.valueOf(hssfCell.getStringCellValue());
}
}
return "";
}