* <p>解析表格数据并插入临时表</p>
*
* @param excelFiles 请求参数对象list
* @param request 请求对象
* @return Map
* @apiNote zhiwei.zhang, 2018-1-3
*/
@RequestMapping(value = "/uploadFromExcel", method = RequestMethod.POST)
public Map<String, Object> uploadFromExcel(@RequestParam("excelFiles") List<MultipartFile> excelFiles, HttpServletRequest request) {
IRequest requestContext = createRequestContext(request);
Locale locale = IRequestUtils.getLocale(requestContext.getLocale());
Map<String, Object> importDate = new HashMap<>(7);
// 判断enctype属性是否为multipart/form-data
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
//若enctype属性不为multipart/form-data,则返回错误信息
if (!isMultipart) {
String errorMessage = messageSource.getMessage(PromptConstants.MESSAGE_FILE_TYPE_ERROR, null, locale);
importDate.put("message", errorMessage);
importDate.put("success", false);
return importDate;
}
try {
//用导入入口方法,解析表格,插入临时表
importDate = commonImportService.analysisData(requestContext, excelFiles.get(0));
} catch (FileUploadException e) {
String errorMessage = messageSource.getMessage(PromptConstants.MESSAGE_ERROR_FILE, null, locale);
importDate.put("message", errorMessage);
importDate.put("success", false);
return importDate;
} catch (Exception e) {
String errorMessage = "";
if (e.getCause() != null) {
errorMessage = e.getCause().getMessage();
} else {
errorMessage = e.getMessage();
}
importDate.put("message", errorMessage);
importDate.put("success", false);
return importDate;
}
importDate.put("success", true);
return importDate;
}
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = {Exception.class, RuntimeException.class})
public Map<String, Object> analysisData(IRequest request, MultipartFile file) throws Exception {
Map<String, Object> resultMap = new HashMap<>(3);
InputStream inputStream = null;
//创建新的工作簿对象
Workbook workbook = null;
try {
//获取输入流
inputStream = file.getInputStream();
String originalFileName = file.getOriginalFilename();
if (originalFileName.endsWith(FND_COMMON_IMPORT_FILE_ENDWITH_XLSX) || originalFileName.endsWith(FND_COMMON_IMPORT_FILE_ENDWITH_CSV)) {
//使用XSSF的方式处理Excel文件
workbook = new XSSFWorkbook(inputStream);
} else if (originalFileName.endsWith(FND_COMMON_IMPORT_FILE_ENDWITH_XLS)) {
//使用HSSF的方式处理Excel文件
workbook = new HSSFWorkbook(inputStream);
} else {
//如果不是上述的三种文件格式,抛出异常
throw new IOException(PromptConstants.MESSAGE_IMPORT_FILE_FORMAT_ERROR);
}
} catch (IOException e) {
//捕获异常
throw new IOException(PromptConstants.MESSAGE_IMPORT_FILE_OPEN_ERROR);
} finally {
//关闭输入流
if (inputStream != null) {
inputStream.close();
}
}
//创建临时表集合
List<CommonImpIface> commonImpIfaces = new ArrayList<>();
//ID生成算法获取Group ID
SnowflakeIdWorker idWorker = new SnowflakeIdWorker(0, 0);
Long groupId = idWorker.nextId();
resultMap.put("groupId", groupId.toString());
// 获取sheet页的数量
int sheetNumb = workbook.getNumberOfSheets();
for (int numb = 0; numb < sheetNumb; numb++) {
Sheet sheet = workbook.getSheetAt(numb);
if (sheet.getPhysicalNumberOfRows() == 0) {
continue;
}
// 循环行
for (int numbRow = sheet.getFirstRowNum(); numbRow <= sheet.getLastRowNum(); numbRow++) {
Row row = sheet.getRow(numbRow);
if (null == row) {
continue;
}
CommonImpIface commonImpIface = new CommonImpIface();
commonImpIface.setGroupId(groupId);
int numbCell = row.getFirstCellNum();
int lastNum = row.getLastCellNum();
if (lastNum > 100) {
lastNum = 100;
}
for (; numbCell < lastNum; numbCell++) {
Cell cell = row.getCell(numbCell);
String value = null;
if (null == cell) {
value = "";
continue;
}
// 格式化number
DecimalFormat df = new DecimalFormat("#.#########");
// String字符日期格式化
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
// 格式化数字
DecimalFormat df2 = new DecimalFormat("0.00");
switch (cell.getCellTypeEnum()) {
case STRING:
value = cell.getRichStringCellValue().getString();
break;
case NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
} else if (HSSFDateUtil.isCellDateFormatted(cell)) {
value = sdf.format(cell.getDateCellValue());
} else {
value = df2.format(cell.getNumericCellValue());
}
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
value = "";
break;
default:
value = "";
break;
}
PropertyDescriptor descriptor = getPropertyDescriptor(CommonImpIface.class,
"column" + (numbCell + 1));
Method method = descriptor.getWriteMethod();
method.invoke(commonImpIface, new Object[]{value});
}
commonImpIfaces.add(commonImpIface);
}
}
fndCommonImpInterfaceService.insert(commonImpIfaces.subList(1, commonImpIfaces.size()), request);
resultMap.put("data", commonImpIfaces);
return resultMap;
}