依赖包
<!-- POI包 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
Controller 层定义
@RestController
public class StdandardDirectoryController {
/**
* Excel批量导入标准化字典数据
* @param catalogId 标准数据主键
* @param importStdDicFile 导入文件
* @return Object 状态码
*/
@PostMapping("/standardDictionary/importStdDictData")
@ResponseBody
public Object importStdDictData(@RequestParam int catalogId,
@RequestParam("importStdDicFile") MultipartFile importStdDicFile) throws Exception{
log.info("调用/standardDictionary/importStdDictData");
log.info("参数：catalogId="+catalogId);
String fileName = importStdDicFile.getOriginalFilename();
/**
* 判断文件类型
*/
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new BusinessParamCheckingException(ExceptionConstants.DEVOPS_SERVICE_FILE_TYPE_ERROR_CODE,
ExceptionConstants.DEVOPS_SERVICE_FILE_TYPE_ERROR_MSG);
}
/**
*获取用户phone
*/
String modifyPhone = userInfoService.getUserPhone();
/**
* 表格数据导入 业务处理
*/
JSONObject data = standardDirectoryService.importExcelData(importStdDicFile, modifyPhone, catalogId);
/**
* 返回结果
*/
JSONObject result = ExceptionConstants.standardSuccess();
result.put(ExceptionConstants.GLOBAL_RETURNS_DATA,data);
return result;
}
}
service 层接口定义
public interface IStandardDirectoryService {
/**
* 导入Excel表格数据到便准数据字典biao
* @param importStdDicFile 导入文件
* @param modifyUser 修改人手机号
* @param catalogId 目录id
* @return JSONObject 成功条数，失败条数，失败数据id；
*/
JSONObject importExcelData(MultipartFile importStdDicFile,String modifyUser,int catalogId) throws Exception;
}
service 层定义
@Service
public class StandardDirectoryServiceImpl implements IStandardDirectoryService {
/**
* 导入Excel表格数据到标准数据字典表
* @param importStdDicFile 导入文件
* @param modifyUser 修改人手机号
* @param catalogId 目录id
* @return JSONObject 成功条数，失败条数，失败数据id；
*/
public JSONObject importExcelData(MultipartFile importStdDicFile,String modifyUser,int catalogId) throws Exception{
/**
* 获取输入流
*/
InputStream inputStream = importStdDicFile.getInputStream();
Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheetAt(0);
if(sheet==null){
//未找到工作簿
throw new BusinessRunTimeException(ExceptionConstants.DEVOPS_SERVICE_NOT_FOUND_SHEET_CODE,
ExceptionConstants.DEVOPS_SERVICE_NOT_FOUND_SHEET_MSG);
}
/**
* successMap 放置验证成功的标准数据
* errorMap 放置失败的标准数据
* errorDatalist 放置错误数据list
* regxDictCode,regxDictName 正则验证表达式
*/
HashMap<String, String> successMap = new HashMap<>();
HashMap<String, Integer> errorKeyMap = new HashMap<>();
ArrayList<String> errorDatalist = new ArrayList<>();
String regxDictCode = BusinessConstants.DICT_CODE_REGULAR_VERIFICATION_EXPRESSION;
JSONArray errorDataJson;
String errorData;
/**
* 读取文件内容
*/
List<String[]> list = new ArrayList<String[]>();
for (int r = 1; r < sheet.getLastRowNum()+1; r++) {
Row row = sheet.getRow(r);
if (row == null){
continue;
}
//设置读取转String类型
String excelDictCode = "";
String excelDictName = "";
if(row.getCell(0) != null){
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
excelDictCode = row.getCell(0).getStringCellValue();
}
if(row.getCell(1) != null){
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
excelDictName = row.getCell(1).getStringCellValue();
}
/**
* 数据验证
* 1--code，name不为空
* 2--code，name正则验证
* 3--表格code重复验证
*/
if( StringUtil.isEmpty(excelDictCode) ){
errorDataJson = new JSONArray();
errorDataJson.add("");
if(StringUtil.isEmpty(excelDictName)){
errorDataJson.add("");
}else {
errorDataJson.add(excelDictName);
}
errorDataJson.add(BusinessConstants.DICT_CODE_CANNOT_BE_EMPTY);
errorData = errorDataJson.toString();
errorDatalist.add(errorData);
continue;
}
if( StringUtil.isEmpty(excelDictName) ){
errorDataJson = new JSONArray();
errorDataJson.add(excelDictCode);
errorDataJson.add("");
errorDataJson.add(BusinessConstants.DICT_NAME_CANNOT_BE_EMPTY);
errorData = errorDataJson.toString();
errorDatalist.add(errorData);
continue;
}
if(!Pattern.matches(regxDictCode, excelDictCode)){
errorDataJson = new JSONArray();
errorDataJson.add(excelDictCode);
errorDataJson.add(excelDictName);
errorDataJson.add(BusinessConstants.DICT_CODE_VERIFICATION_FAILED);
errorData = errorDataJson.toString();
errorDatalist.add(errorData);
errorKeyMap.put(excelDictCode,1);
continue;
}
if(excelDictName.length()<1 || excelDictName.length()>30){
errorDataJson = new JSONArray();
errorDataJson.add(excelDictCode);
errorDataJson.add(excelDictName);
errorDataJson.add(BusinessConstants.DICT_NAME_LENGTH_FAILED);
errorData = errorDataJson.toString();
errorDatalist.add(errorData);
errorKeyMap.put(excelDictCode,1);
continue;
}
if(errorKeyMap.containsKey(excelDictCode)){
errorDataJson = new JSONArray();
errorDataJson.add(excelDictCode);
errorDataJson.add(excelDictName);
errorDataJson.add(BusinessConstants.EXCEL_DICT_CODE_REPETITION);
errorData = errorDataJson.toString();
errorDatalist.add(errorData);
continue;
}
if(successMap.containsKey(excelDictCode)){
errorDataJson = new JSONArray();
errorDataJson.add(excelDictCode);
errorDataJson.add(excelDictName);
errorDataJson.add(BusinessConstants.EXCEL_DICT_CODE_REPETITION);
errorData = errorDataJson.toString();
errorDatalist.add(errorData);
errorDataJson = new JSONArray();
errorDataJson.add(excelDictCode);
errorDataJson.add(successMap.get(excelDictCode));
errorDataJson.add(BusinessConstants.EXCEL_DICT_CODE_REPETITION);
errorData = errorDataJson.toString();
errorDatalist.add(errorData);
successMap.remove(excelDictCode);
errorKeyMap.put(excelDictCode,1);
continue;
}
successMap.put(excelDictCode,excelDictName);
}
/**
* 将上一步筛选出的数据code与mysql数据库中进行比对，获取数据库中已存在的code集合
*/
if (!successMap.isEmpty()){
Set<String> stdCodeSet = successMap.keySet();
//排除其他目录下的标准数据编码
List<String> dbExistDictCodes = standardDirectoryMapper.findByDictCodes(stdCodeSet,catalogId);
/**
*将数据库中存在的数据从successMap中移除，添加到errorDatalist
*/
if(!dbExistDictCodes.isEmpty()){
for (String dbExistDictCode :dbExistDictCodes) {
errorDataJson = new JSONArray();
errorDataJson.add(dbExistDictCode);
errorDataJson.add(successMap.get(dbExistDictCode));
errorDataJson.add(BusinessConstants.EXCEL_AND_MYSQL_DICT_CODE_REPETITION);
errorDatalist.add(errorDataJson.toString());
successMap.remove(dbExistDictCode);
}
}
}
StandardDirectory standardDirectory = null;
ArrayList<StandardDirectory> stdDictInsertlist = new ArrayList<>();
if( !successMap.isEmpty() ){
for(Map.Entry<String, String> entry: successMap.entrySet()){
/**
* 组装参数
*/
standardDirectory = new StandardDirectory();
standardDirectory.setCatalogId(catalogId);
standardDirectory.setModifyUser(modifyUser);
standardDirectory.setDictCode(entry.getKey());
standardDirectory.setDictName(entry.getValue());
standardDirectory.setUpdateTime(new Date());
standardDirectory.setCreateTime(new Date());
stdDictInsertlist.add(standardDirectory);
}
}
if( !stdDictInsertlist.isEmpty() ){
int result = standardDirectoryMapper.insertByList(stdDictInsertlist);
if( result == 0 ){
throw new BusinessRunTimeException(ExceptionConstants.DEVOPS_SERVICE_MYSQL_WRITE_FAIL_CODE,
ExceptionConstants.DEVOPS_SERVICE_MYSQL_WRITE_FAIL_MSG);
}
}
/**
*将错误数据写入redis，设置过期时间，正确数据批量插入mysql数据库
*/
String errorDataId = null;
if (errorDatalist.size()!=0) {
errorDataId = UUID.randomUUID().toString().replace("-","");
stringRedisTemplate.opsForList().rightPushAll(RedisConstants.PREFIX_ERROR_DATA + errorDataId,errorDatalist);
stringRedisTemplate.expire(RedisConstants.PREFIX_ERROR_DATA + errorDataId + errorDataId,RedisConstants.VALIDITY_PERIOD_ERROR_DATA, TimeUnit.SECONDS);
}
int successCount = stdDictInsertlist.size();
int failCount = errorDatalist.size();
JSONObject jsonObject = new JSONObject();
jsonObject.put(BusinessConstants.DEVOPS_RETURNS_SUCCESS_COUNT,successCount);
jsonObject.put(BusinessConstants.DEVOPS_RETURNS_FAIL_COUNT,failCount);
jsonObject.put(BusinessConstants.DEVOPS_RETURNS_ERROR_DATA_ID,errorDataId);
jsonObject.put("catalogId",catalogId);
inputStream.close();
return jsonObject;
}
}
