excel导入

最新推荐文章于 2024-08-01 18:45:00 发布

史密斯太太和她的驴

最新推荐文章于 2024-08-01 18:45:00 发布

阅读量714

点赞数

文章标签： excel java 开发语言

本文链接：https://blog.csdn.net/weixin_43766651/article/details/131376305

版权

@ApiOperation(value = “进场验收导入”)
@PostMapping(“/importEquipments”)
public ResponseBean EquipmentRecImport(@RequestParam(“file”) MultipartFile file) {
try {
List list = ExcelUtils.importExcel(file, 1, 1, true, ExcelEquipVo.class);
if (list == null) {
return ResponseBean.fail(“数据为空”);
}
if (list.size() == 0) {
return ResponseBean.fail(“数据为空”);
}
GenerateImportDto model = iEquipmentService.EquipmentRecImport(list);
if (model != null) {
return ResponseBean.success(model);
} else {
return ResponseBean.fail();
}
} catch (Exception e) {
//logger.error(“导入设备类型出错”, e);
return ResponseBean.fail(“导入设备类型出错”);
}
}
导入

@Override
public Map getWaitEquipmentListByPage(GetEquipImportReq req) {
String projectNo = req.getProjectNo();
Integer currentPage = req.getCurrPage();
Integer pageSize = req.getPageSize();

    String redisKey = OrderUtils.getRedisProjectNoKey(projectNo);
    String redisCountKey = OrderUtils.getCRedisCountKey(projectNo);
    Long start = Long.valueOf((currentPage - 1) * pageSize);
    Long end = Long.valueOf(currentPage * pageSize - 1);
    Set<Object> ids = redisUtils.rangeZset(redisCountKey, 1, 10);
    List<Object> list = redisUtils.multiGetHash(redisKey, ids);

    List<ExcelEquipVo> rtnList = new ArrayList<>();
    for (Object obj : list
    ) {
        JSONObject json = (JSONObject) JSONObject.toJSON(obj);
        String str = json.toString();//将json对象转换为字符串
        ExcelEquipVo dto = JSON.parseObject(str, ExcelEquipVo.class);
        rtnList.add(dto);
    }
    PageInfo<ExcelEquipVo> pageinfo = new PageInfo<>();
    pageinfo.setPageSize(req.getPageSize());
    pageinfo.setPageNum(req.getCurrPage());
    pageinfo.setList(rtnList);
    Map<Object, Object> maplist = redisUtils.getHashMap(redisKey);
    Integer totalSize = maplist.size();
    pageinfo.setPages(totalSize / req.getPageSize());
    pageinfo.setSize(totalSize);
    String redisStaticKey = OrderUtils.getRedisStaticKey(projectNo);
    GenerateImportDto staticDto = (GenerateImportDto) redisUtils.get(redisStaticKey);
    if (staticDto == null) {
        return null;
    }
    Map map = new HashMap();
    map.put("generateRechargeDto", staticDto);
    map.put("pageInfo", pageinfo);
    return map;
}

package appapi.utils;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;
import java.util.concurrent.TimeUnit;

/**

Redis工具类
@author luocheng
@version 1.0
@date 2023/06/08 14:36
/
@Component
public class RedisUtils {
/*
- 注入redisTemplate bean
  */
  @Autowired
  private RedisTemplate<String,Object> redisTemplate;
/**
- 指定缓存失效时间
- @param key 键
- @param time 时间(秒)
- @return
  */
  public boolean expire(String key, long time) {
  try {
  if (time > 0) {
  redisTemplate.expire(key, time, TimeUnit.SECONDS);
  }
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 根据key获取过期时间
- @param key 键不能为null
- @return 时间(秒) 返回0代表为永久有效
  */
  public long getExpire(String key) {
  return redisTemplate.getExpire(key, TimeUnit.SECONDS);
  }
/**
- 判断key是否存在
- @param key 键
- @return true 存在 false不存在
  */
  public boolean hasKey(String key) {
  try {
  return redisTemplate.hasKey(key);
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 删除缓存
- @param key 可以传一个值或多个
  */
  @SuppressWarnings(“unchecked”)
  public void del(String… key) {
  if (key != null && key.length > 0) {
  if (key.length == 1) {
  redisTemplate.delete(key[0]);
  } else {
  redisTemplate.delete((List) CollectionUtils.arrayToList(key));
  }
  }
  }
// String(字符串)=

/**
- 普通缓存获取
- @param key 键
- @return 值
  */
  public Object get(String key) {
  return key == null ? null : redisTemplate.opsForValue().get(key);
  }
/**
- 普通缓存放入
- @param key 键
- @param value 值
- @return true成功 false失败
  */
  public boolean set(String key, Object value) {
  try {
  redisTemplate.opsForValue().set(key, value);
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 普通缓存放入并设置时间
- @param key 键
- @param value 值
- @param time 时间(秒) time要大于0 如果time小于等于0 将设置无限期
- @return true成功 false 失败
  */
  public boolean set(String key, Object value, long time) {
  try {
  if (time > 0) {
  redisTemplate.opsForValue().set(key, value, time, TimeUnit.SECONDS);
  } else {
  set(key, value);
  }
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 普通缓存放入并设置过期时间
- @param key 键
- @param value 值
- @return true成功 false 失败
  */
  public boolean setDayValid(String key, Object value) {
  try {
  Long totalSecond = getTomorrowSeconds();
  redisTemplate.opsForValue().set(key, value, totalSecond / 1000, TimeUnit.SECONDS);
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 获取当前时间到第二天凌晨秒数
- @return
  */
  public Long getTomorrowSeconds() {
  LocalDateTime localDateTime = LocalDateTime.now();
  long l1 = localDateTime.atZone(ZoneId.of(“Asia/Shanghai”)).toInstant().toEpochMilli();
  LocalDate localDate = LocalDate.now();
  LocalDate localDate1 = localDate.plusDays(1);
  LocalDateTime localDateTime1 = localDate1.atStartOfDay();
  return localDateTime1.atZone(ZoneId.of(“Asia/Shanghai”)).toInstant().toEpochMilli();
  }
/**
- 递增
- @param key 键
- @param delta 要增加几(大于0)
- @return
  */
  public long incr(String key, long delta) {
  if (delta <= 0) {
  throw new RuntimeException(“递增因子必须大于0”);
  }
  return redisTemplate.opsForValue().increment(key, delta);
  }
public long incr(String key, long delta, int time) {
if (delta <= 0) {
throw new RuntimeException(“递增因子必须大于0”);
}
long res = redisTemplate.opsForValue().increment(key, delta);
redisTemplate.expire(key, time, TimeUnit.MINUTES);
return res;
}

/**
- 递减
- @param key 键
- @param delta 要减少几(小于0)
- @return
  */
  public long decr(String key, long delta) {
  if (delta < 0) {
  throw new RuntimeException(“递减因子必须大于0”);
  }
  return redisTemplate.opsForValue().increment(key, -delta);
  }
// Hash(哈希)=

/**
- HashGet
- @param key 键不能为null
- @param item 项不能为null
- @return 值
  */
  public Object getHash(String key, String item) {
  return redisTemplate.opsForHash().get(key, item);
  }
/**
- multiGetHash
- @param key
- @param items
- @return
  */
  public List multiGetHash(String key, Collection items) {
  return redisTemplate.opsForHash().multiGet(key, items);
  }
/**
- 向一张hash表中放入数据,如果不存在将创建
- @param key 键
- @param item 项
- @param value 值
- @return true 成功 false失败
  */
  public boolean setHash(String key, String item, Object value) {
  try {
  redisTemplate.opsForHash().put(key, item, value);
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 向一张hash表中放入数据,如果不存在将创建
- @param key 键
- @param item 项
- @param value 值
- @param time 时间(秒) 注意:如果已存在的hash表有时间,这里将会替换原有的时间
- @return true 成功 false失败
  */
  public boolean setHash(String key, String item, Object value, long time) {
  try {
  redisTemplate.opsForHash().put(key, item, value);
  if (time > 0) {
  expire(key, time);
  }
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 删除hash表中的值
- @param key 键不能为null
- @param item 项可以使多个不能为null
  */
  public Long delHash(String key, Object… item) {
  return redisTemplate.opsForHash().delete(key, item);
  }
/**
- 判断hash表中是否有该项的值
- @param key 键不能为null
- @param item 项不能为null
- @return true 存在 false不存在
  */
  public boolean hasKeyHash(String key, String item) {
  return redisTemplate.opsForHash().hasKey(key, item);
  }
/**
- hash递增如果不存在,就会创建一个并把新增后的值返回
- @param key 键
- @param item 项
- @param by 要增加几(大于0)
- @return
  */
  public double incrHash(String key, String item, double by) {
  return redisTemplate.opsForHash().increment(key, item, by);
  }
/**
- hash递减
- @param key 键
- @param item 项
- @param by 要减少记(小于0)
- @return
  */
  public double decrHash(String key, String item, double by) {
  return redisTemplate.opsForHash().increment(key, item, -by);
  }
/**
- 获取hashKey对应的所有键值
- @param key 键
- @return 对应的多个键值
  */
  public Map<Object, Object> getHashMap(String key) {
  return redisTemplate.opsForHash().entries(key);
  }
/**
- HashSet
- @param key 键
- @param map 对应多个键值
- @return true 成功 false 失败
  */
  public boolean setHashMap(String key, Map map) {
  try {
  redisTemplate.opsForHash().putAll(key, map);
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- HashSet 并设置时间
- @param key 键
- @param map 对应多个键值
- @param time 时间(秒)
- @return true成功 false失败
  */
  public boolean setHashMap(String key, Map<String, Object> map, long time) {
  try {
  redisTemplate.opsForHash().putAll(key, map);
  if (time > 0) {
  expire(key, time);
  }
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
// Set(集合)=

/**
- 根据key获取Set中的所有值
- @param key 键
- @return
  */
  public Set getSet(String key) {
  try {
  return redisTemplate.opsForSet().members(key);
  } catch (Exception e) {
  e.printStackTrace();
  return null;
  }
  }
/**
- 根据value从一个set中查询,是否存在
- @param key 键
- @param value 值
- @return true 存在 false不存在
  */
  public boolean hasKeySet(String key, Object value) {
  try {
  return redisTemplate.opsForSet().isMember(key, value);
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 将数据放入set缓存
- @param key 键
- @param values 值可以是多个
- @return 成功个数
  */
  public long setSet(String key, Object… values) {
  try {
  return redisTemplate.opsForSet().add(key, values);
  } catch (Exception e) {
  e.printStackTrace();
  return 0;
  }
  }
/**
- 将set数据放入缓存
- @param key 键
- @param time 时间(秒)
- @param values 值可以是多个
- @return 成功个数
  */
  public long setSet(String key, long time, Object… values) {
  try {
  Long count = setSet(key, values);
  if (time > 0) {
  expire(key, time);
  }
  return count;
  } catch (Exception e) {
  e.printStackTrace();
  return 0;
  }
  }
/**
- 获取set缓存的长度
- @param key 键
- @return
  */
  public long getSizeSet(String key) {
  try {
  return redisTemplate.opsForSet().size(key);
  } catch (Exception e) {
  e.printStackTrace();
  return 0;
  }
  }
/**
- 移除值为value的
- @param key 键
- @param values 值可以是多个
- @return 移除的个数
  */
  public long setRemove(String key, Object… values) {
  try {
  return redisTemplate.opsForSet().remove(key, values);
  } catch (Exception e) {
  e.printStackTrace();
  return 0;
  }
  }
  //=zset(集合)===
/**
- 增加有序集合
- @param key
- @param value
- @param seqNo
- @return
  */
  public Boolean addZset(String key, Object value, double seqNo) {
  try {
  return redisTemplate.opsForZSet().add(key, value, seqNo);
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
public Boolean addZset(String key, Object value, double seqNo, int time) {
try {
Boolean res = redisTemplate.opsForZSet().add(key, value, seqNo);
redisTemplate.expire(key, time, TimeUnit.MINUTES);
return res;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}

/**
- 获取zset指定范围内的集合
- @param key
- @param start
- @param end
- @return
  */
  public Set rangeZset(String key, long start, long end) {
  try {
  return redisTemplate.opsForZSet().range(key, start, end);
  } catch (Exception e) {
  e.printStackTrace();
  return null;
  }
  }
/**
- 根据key和value移除指定元素
- @param key
- @param value
- @return
  */
  public Long removeZset(String key, Object value) {
  try {
  return redisTemplate.opsForZSet().remove(key, value);
  } catch (Exception e) {
  e.printStackTrace();
  return 0L;
  }
}

public Long removeZset(String key, Object[] values) {
try {
return redisTemplate.opsForZSet().remove(key, values);
} catch (Exception e) {
e.printStackTrace();
return 0L;
}

}

//=zset(集合)结束===
// =List(列表)===

/**
- 获取list缓存的内容
- @param key 键
- @param start 开始
- @param end 结束 0 到 -1代表所有值
- @return
  */
  public List getList(String key, long start, long end) {
  try {
  return redisTemplate.opsForList().range(key, start, end);
  } catch (Exception e) {
  e.printStackTrace();
  return null;
  }
  }
/**
- 获取list缓存的长度
- @param key 键
- @return
  */
  public long getSizeList(String key) {
  try {
  return redisTemplate.opsForList().size(key);
  } catch (Exception e) {
  e.printStackTrace();
  return 0;
  }
  }
/**
- 通过索引获取list中的值
- @param key 键
- @param index 索引 index>=0时， 0 表头，1 第二个元素，依次类推；index<0时，-1，表尾，-2倒数第二个元素，依次类推
- @return
  */
  public Object getIndexList(String key, long index) {
  try {
  return redisTemplate.opsForList().index(key, index);
  } catch (Exception e) {
  e.printStackTrace();
  return null;
  }
  }
/**
- 将list放入缓存
- @param key 键
- @param value 值
- @return
  */
  public boolean setList(String key, Object value) {
  try {
  redisTemplate.opsForList().rightPush(key, value);
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 将list放入缓存
- @param key 键
- @param value 值
- @param time 时间(秒)
- @return
  */
  public boolean setList(String key, Object value, long time) {
  try {
  redisTemplate.opsForList().rightPush(key, value);
  if (time > 0) {
  expire(key, time);
  }
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 将list放入缓存
- @param key 键
- @param value 值
- @return
  */
  public boolean setList(String key, List value) {
  try {
  redisTemplate.opsForList().rightPushAll(key, value);
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 将list放入缓存
- @param key 键
- @param value 值
- @param time 时间(秒)
- @return
  */
  public boolean setList(String key, List value, long time) {
  try {
  redisTemplate.opsForList().rightPushAll(key, value);
  if (time > 0) {
  expire(key, time);
  }
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 根据索引修改list中的某条数据
- @param key 键
- @param index 索引
- @param value 值
- @return
  */
  public boolean updateIndexList(String key, Long index, Object value) {
  try {
  redisTemplate.opsForList().set(key, index, value);
  return true;
  } catch (Exception e) {
  e.printStackTrace();
  return false;
  }
  }
/**
- 移除N个值为value
- @param key 键
- @param count 移除多少个
- @param value 值
- @return 移除的个数
  */
  public long removeList(String key, long count, Object value) {
  try {
  Long remove = redisTemplate.opsForList().remove(key, count, value);
  return remove;
  } catch (Exception e) {
  e.printStackTrace();
  return 0;
  }
  }
/**
- 根据正则表达式获取key列表
- @param patternKey 正则表达式
- @return 匹配key列表
  */
  public Set keys(String patternKey) {
  try {
  Set keys = redisTemplate.keys(patternKey);
  return keys;
  } catch (Exception e) {
  e.printStackTrace();
  return new HashSet<>();
  }
  }
/**
- 判断key是否存在
- @param key
- @return
  */
  public boolean exists(String key) {
  return redisTemplate.hasKey(key);
  }
/**
- 判断key是否过期
- @param key
- @return
  */
  public boolean isExpire(String key) {
  return expire(key) > 1 ? false : true;
  }
/**
- 从redis中获取key对应的过期时间;
- 如果该值有过期时间，就返回相应的过期时间;
- 如果该值没有设置过期时间，就返回-1;
- 如果没有该值，就返回-2;
- @param key
- @return
  */
  public long expire(String key) {
  return redisTemplate.opsForValue().getOperations().getExpire(key);
  }
public void putOne(String k, Object mk, Object mv) {
redisTemplate.opsForHash().put(k, mk, mv);
}

}

@Override
public Map getWaitEquipmentListByPage(GetEquipImportReq req) {
String projectNo = req.getProjectNo();
Integer currentPage = req.getCurrPage();
Integer pageSize = req.getPageSize();

    String redisKey = OrderUtils.getRedisProjectNoKey(projectNo);
    String redisCountKey = OrderUtils.getCRedisCountKey(projectNo);
    Long start = Long.valueOf((currentPage - 1) * pageSize);
    Long end = Long.valueOf(currentPage * pageSize - 1);
    Set<Object> ids = redisUtils.rangeZset(redisCountKey, 1, 10);
    List<Object> list = redisUtils.multiGetHash(redisKey, ids);

    List<ExcelEquipVo> rtnList = new ArrayList<>();
    for (Object obj : list
    ) {
        JSONObject json = (JSONObject) JSONObject.toJSON(obj);
        String str = json.toString();//将json对象转换为字符串
        ExcelEquipVo dto = JSON.parseObject(str, ExcelEquipVo.class);
        rtnList.add(dto);
    }
    PageInfo<ExcelEquipVo> pageinfo = new PageInfo<>();
    pageinfo.setPageSize(req.getPageSize());
    pageinfo.setPageNum(req.getCurrPage());
    pageinfo.setList(rtnList);
    Map<Object, Object> maplist = redisUtils.getHashMap(redisKey);
    Integer totalSize = maplist.size();
    pageinfo.setPages(totalSize / req.getPageSize());
    pageinfo.setSize(totalSize);
    String redisStaticKey = OrderUtils.getRedisStaticKey(projectNo);
    GenerateImportDto staticDto = (GenerateImportDto) redisUtils.get(redisStaticKey);
    if (staticDto == null) {
        return null;
    }
    Map map = new HashMap();
    map.put("generateRechargeDto", staticDto);
    map.put("pageInfo", pageinfo);
    return map;
} 
导入查询

@Override
public GenerateImportDto deliEquipmentRedis(DelEquipImportReq req) {
String projectNo = req.getProjectNo();
String redisKey = OrderUtils.getRedisProjectNoKey(projectNo);
String redisCountKey = OrderUtils.getRedisRecordCountKey(projectNo);
String redisStaticKey = OrderUtils.getRedisStaticKey(projectNo);
Map<Object, Object> maplist = redisUtils.getHashMap(redisKey);
Integer total = maplist.size();
//取记录
ExcelEquipVo delDto = (ExcelEquipVo) redisUtils.getHash(redisKey, req.getId().toString());
//删除记录
redisUtils.delHash(redisKey, req.getId().toString());
redisUtils.removeZset(redisCountKey, req.getId().toString());
//更新余额
GenerateImportDto staticDto = (GenerateImportDto) redisUtils.get(redisStaticKey);
if (staticDto == null) {
return null;
}
staticDto.setTotalCount(total - 1);
redisUtils.set(redisStaticKey, staticDto);
return staticDto;
}
导入删除

@Override
public GenerateImportDto updateImportEquip(UpdateImportEquipVo vo) {
String redisKey = OrderUtils.getRedisProjectNoKey(vo.getProjectNo());
String redisCountKey = OrderUtils.getRedisRecordCountKey(vo.getProjectNo());
String redisStaticKey = OrderUtils.getRedisStaticKey(vo.getProjectNo());
Map<Object, Object> maplist = redisUtils.getHashMap(redisKey);
Integer total = maplist.size();
//取记录
ExcelEquipVo editDto = (ExcelEquipVo) redisUtils.getHash(redisKey, vo.getId().toString());
if (editDto == null) {
return null;
}
//编辑记录
editDto.setEquipNo(vo.getEquipNo());
editDto.setEquipName(vo.getEquipName());
editDto.setEquipTypeName(vo.getEquipTypeName());
editDto.setProjName(vo.getProjName());
editDto.setEquipSource(vo.getEquipSource());
Map<String, Object> map = new HashMap<>();
map.put(vo.getId().toString(), editDto);
redisUtils.setHashMap(redisKey, map);
//TODO::验证记录
if (editDto.getEquipNo() == null || editDto.getEquipName() == null || editDto.getEquipTypeName() == null || editDto.getProjName() == null || editDto.getEquipSource() == null) {
return null;
}
GenerateImportDto staticDto = (GenerateImportDto) redisUtils.get(redisStaticKey);
if (staticDto == null) {
return null;
}
staticDto.setTotalCount(total);
//更新项目信息
redisUtils.set(redisStaticKey, staticDto, OrderUtils.ORDER_EXPIRE);
return staticDto;
}导入修改