1、导入见代码分析
@Value("${excel_save_path}")
private String excelSavePath = "/";
//导入
@Override
public JsonResult<Map<String, Object>> previewSave(MultipartFile file, Map<String, Object> params) {
Map<String, Object> dataMap = new HashMap<>(8);
try {
List map = new ArrayList();
String impMonth = String.valueOf(params.get("month"));
Date now = new Date();
String batchNo = DateUtil.format(now, DatePattern.PURE_DATETIME_PATTERN) + RandomUtil.randomNumbers(5);
//多租户调用租户ID
int hosId = userService.getTenantId().intValue();
//excelSavePath:配置了路径
File path = new File(excelSavePath);
if (!path.exists()) {
FileUtil.mkdir(path);
}
log.info(excelSavePath + "/" + batchNo + "." + FileUtil.extName(file.getOriginalFilename()));
FileWriter writer = new FileWriter(excelSavePath + "/" + batchNo + "." + FileUtil.extName(file.getOriginalFilename()));
writer.writeFromStream(file.getInputStream());
ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
//获取动态列
List<TemplateConfigInfo> configList = this.getTemplateConfigList(TemplateType.parse(String.valueOf(params.get("template"))));
if (configList == null || configList.size() == 0) {
throw new CommonException("未配置数据源");
}
//移除不要的字段
Iterator iterator = configList.iterator();
while (iterator.hasNext()) {
TemplateConfigInfo templateConfigInfo = (TemplateConfigInfo) iterator.next();
if (templateConfigInfo.getAliasName().equals("导入时间")) {
iterator.remove();
}
}
int beginRow = configList.get(0).getBeginRow();
reader.setIgnoreEmptyRow(true);
List<List<Object>> readAll = reader.read(beginRow - 1);
List<Object> headRow = readAll.get(0);
Map<Integer, String> indexMap = MapUtil.newHashMap();
Map<String, String> fieldCountType = MapUtil.newHashMap();
//标题映射
Map<String, String> titleMap = dataTitleMappingService.getHospitalTitleMap(String.valueOf(params.get("template")));
Integer itemNameIndex = null;
for (TemplateConfigInfo item : configList) {
for (int i = 0; i < headRow.size(); i++) {
Object v = headRow.get(i);
if (v == null) continue;
fieldCountType.put(item.getMappingName(), item.getFieldType());
String key = String.valueOf(v).trim().replaceAll(" ", "");
if (item.getAliasName().equals(key) || (titleMap != null && titleMap.containsKey(key) && item.getAliasName().equals(titleMap.get(key)))) {
if ("ITEM_NAME".equals(item.getMappingName().toUpperCase())) {
itemNameIndex = i;
}
indexMap.put(i, item.getMappingName());
}
}
}
;
String type = null;
List beanList = null;
beanList = convertToBean(readAll, headRow, fieldCountType, indexMap, TemplateTypeIncome.parse(String.valueOf(params.get("template"))), now, batchNo, null, impMonth, hosId, configList, null);
//使用stream流计算为空的个数
int errorCountFeeType = (int) beanList.stream()
.map(item ->
JSON.parseObject(JSON.toJSONString(item),
ImpIncomeDTO.class)).filter(
user -> ((ImpIncomeDTO) user)
.getFeeType() == null || ((ImpIncomeDTO) user)
.getFeeType()
.equals("")).count();
int errorCountInAmount = (int) beanList.stream()
.map(item ->
JSON.parseObject(
JSON.toJSONString(item),
ImpIncomeDTO.class))
.filter(Objects::nonNull)
.filter(
user -> ((ImpIncomeDTO) user)
.getInAmount() == null || ((ImpIncomes) user)
.getInAmount()
.equals("")).count();
if (errorCountFeeType > 0) map.add("不能为空");
beanList.stream().forEach(item -> {
ImpIncomeDTO impIncomeDTO = JSON.parseObject(JSON.toJSONString(item), ImpIncomeDTO.class);
ImpIncomes impIncom = JSON.parseObject(JSON.toJSONString(item), ImpIncomes.class);
if (impIncomeDTO != null) {
if (impIncomeDTO.getFee_type() != null || !impIncomeDTO.getFee_type().equals("")) {
JsonResult<List<LabelValue>> bill_fee_type = dictionaryApiService.getLabelValueList("XXX");
List<LabelValue> data = bill_fee_type.getData();
List label = new ArrayList();
for (LabelValue datum : data) {
label.add(datum.getLabel());
}
if (!label.contains(impIncomeDTO.getFee_type())) {
map.add("不正确,请输入正确XX");
}
}
String month = String.valueOf(params.get("month"));
SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd yyyy HH:mm:ss 'GMT'Z", Locale.ENGLISH);
try {
Date dd = sdf.parse(month);
String resDate = new SimpleDateFormat("yyyy-MM").format(dd);
impIncom.setImpMonth(resDate);
impIncom.setCreate_time(new Date());
impIncom.setTenant_id(userService.getTenantId());
LambdaQueryWrapper<DeptMapping> queryWrapper= Wrappers.lambdaQuery();
if(impIncom.getBillOffice()!=null&&!impIncom.getBillOffice().equals("")){
DeptMapping singleEntity = deptMappingService.getSingleEntity(queryWrapper);
if(singleEntity!=null){
if(singleEntity.getDeptType()!=null&&!singleEntity.getDeptType().equals("")){
impIncom.setField8(singleEntity.getDeptType());
}
}
}
LambdaQueryWrapper<DeptMapping> queryWrapper1= Wrappers.lambdaQuery();
if(impIncom.getExecOffice()!=null&&!impIncom.getExecOffice().equals("")){
queryWrapper1.eq(DeptMapping::getHospitalDeptName,impIncom.getExecOffice());
queryWrapper1.eq(DeptMapping::getImpMonth,resDate);
DeptMapping singleEntity1 = deptMappingService.getSingleEntity(queryWrapper1);
if(singleEntity1!=null){
if(singleEntity1.getDeptType()!=null&&!singleEntity1.getDeptType().equals("")){
impIncom.setField9(singleEntity1.getDeptType());
}
}
}
impIncomesService.createEntity(impIncom);
} catch (ParseException e) {
e.printStackTrace();
}
}
});
if (errorCountInAmount > 0) map.add("XXX不能为空");
List<DynamicColumnVo> columns = new ArrayList<>();
List<String> selectColumns = new ArrayList<>();
configList.forEach(k -> {
columns.add(new DynamicColumnVo(k.getMappingName(), k.getAliasName()));
selectColumns.add(k.getMappingName());
});
selectColumns.add(Global.PRIMARY_ID_COLUMN_NAME);
selectColumns.add(Global.CREATE_TIME_COLUMN_NAME);
selectColumns.add(Global.HOSPITAL_ID_COLUMN_NAME);
columns.add(new DynamicColumnVo(Global.CREATE_TIME_COLUMN_NAME, "导入时间"));
dataMap.put("totalCount", beanList.size());
dataMap.put("errorUrl", excelSavePath);
dataMap.put("errorCount", errorCountFeeType + errorCountInAmount);
dataMap.put("errorMsgs", map);
} catch (Exception e) {
e.printStackTrace();
}
return JsonResult.OK(dataMap);
}
2、导出
**
千万要注意,否则Debug都出不来:字段一样要一样,不能有特殊的符号
**
@Override
public void export(ImpIncomeDTO impIncomeDTO, ServletOutputStream out, HttpServletResponse response) {
int hosId = userService.getTenantId().intValue();
List<TemplateConfigInfo> configList = this.getTemplateConfigList(TemplateType.parse(impIncomeDTO.getTemplateType()));
if (configList == null || configList.size() == 0) {
throw new CommonException("未找到模板文件");
}
QueryWrapper queryWrapper = Wrappers.query();
List<ImpIncomes> entityList = impIncomesService.getEntityList(queryWrapper);
Iterator iterator = configList.iterator();
while (iterator.hasNext()) {
TemplateConfigInfo templateConfigInfo = (TemplateConfigInfo) iterator.next();
if (templateConfigInfo.getAliasName().equals("导入时间")) {
iterator.remove();
}
}
Map<String, String> titleMap = dataTitleMappingService.getTitleMap(impIncomeDTO.getTemplateType());
for (TemplateConfigInfo templateConfigInfo : configList) {
if (titleMap.containsKey(templateConfigInfo.getAliasName())) {
if (titleMap.get(templateConfigInfo.getAliasName()) != null && !titleMap.get(templateConfigInfo.getAliasName()).equals("")) {
templateConfigInfo.setAliasName(titleMap.get(templateConfigInfo.getAliasName()));
}
}
}
ExcelWriter writer = ExcelUtil.getWriter(true);
try {
for (TemplateConfigInfo templateConfigInfo : configList) {
//导出的时候记得字段不能为特殊符号比如下划线之类的,记得要转成相对应的字段
writer.addHeaderAlias(replaceUnderlineAndfirstToUpper(templateConfigInfo.getMappingName(), "_", ""), templateConfigInfo.getAliasName());
}
writer.setOnlyAlias(true);
writer.write(entityList, true);
writer.flush(out, true);
out.close();
writer.close();
} catch (Exception e) {
log.error("【{}】下载模板异常:{}", TemplateType.parse(impIncomeDTO.getTemplateType()).getDesc(), e);
throw new CommonException("下载模板文件异常");
} finally {
if (writer != null) {
writer.close();
}
IoUtil.close(out);
}
}
/**
* 替换字符串并让它的下一个字母为大写
*
* @param srcStr
* @param org
* @param ob
* @return
*/
public static String replaceUnderlineAndfirstToUpper(String srcStr, String org, String ob) {
String newString = "";
int first = 0;
while (srcStr.indexOf(org) != -1) {
first = srcStr.indexOf(org);
if (first != srcStr.length()) {
newString = newString + srcStr.substring(0, first) + ob;
srcStr = srcStr.substring(first + org.length(), srcStr.length());
srcStr = firstCharacterToUpper(srcStr);
}
}
newString = newString + srcStr;
return newString;
}
/**
* 首字母大写
*
* @param srcStr
* @return
*/
public static String firstCharacterToUpper(String srcStr) {
return srcStr.substring(0, 1).toUpperCase() + srcStr.substring(1);
}