mian方法读取本地文件并保存至redis

          今天看项目代码，有段代码是写死的，但是有偶尔会更新的数据。想到每次手动复制黏贴的方式修改很不方便。于是写了一个mian方法读取Excel数据并放入到缓存中。

先看下之前的数据，跟改redis之后直接读取

 

话不多说上代码

首先需要建立一个bean，对应你读取的Excel列

package com.augurit.swj.zhps.overView.app.param;

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;

/**
 * @ClassName: ReadExcelBean
 * @Author: zy
 * @Date: 2021/7/22 14:40
 * @Description: 读取（排水单元内排水沟渠、排水单元内排水管线）
 */
@ApiModel("文件导入（排水单元内排水沟渠、排水单元内排水管线）")
public class ReadExcelBean {

    @ApiModelProperty("排水单元内排水沟渠--行政区")
    @ExcelProperty(index = 0)
    //index = 0 表示第一列
    private String gqLabel;


    @ApiModelProperty("排水单元内排水沟渠--长度（米）")
    @ExcelProperty(index = 1)
    private String gqLength;


    @ApiModelProperty("排水单元内排水管线--行政区")
    @ExcelProperty(index = 3)
    private String gxLabel;


    @ApiModelProperty("排水单元内排水管线--长度（米）")
    @ExcelProperty(index = 4)
    private String gxLength;

    public String getGqLabel() {
        return gqLabel;
    }

    public void setGqLabel(String gqLabel) {
        this.gqLabel = gqLabel;
    }

    public String getGqLength() {
        return gqLength;
    }

    public void setGqLength(String gqLength) {
        this.gqLength = gqLength;
    }

    public String getGxLabel() {
        return gxLabel;
    }

    public void setGxLabel(String gxLabel) {
        this.gxLabel = gxLabel;
    }

    public String getGxLength() {
        return gxLength;
    }

    public void setGxLength(String gxLength) {
        this.gxLength = gxLength;
    }
}

先给大家看下Excel中的值，根据Excel中的值，因为列名有重复我是根据列来确定属性，也可以根据表头来确定属性，并且从第三行开始读取。（代码中都有说明）

 

 之后再写个main方法读取Excel中的值

package com.augurit.swj.zhps.overView.app.service.impl;
import com.alibaba.excel.EasyExcel;
import com.augurit.swj.zhps.overView.app.param.ReadExcelBean;
import com.fasterxml.jackson.annotation.JsonAutoDetect;
import com.fasterxml.jackson.annotation.PropertyAccessor;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.data.redis.connection.jedis.JedisConnectionFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.serializer.Jackson2JsonRedisSerializer;
import org.springframework.data.redis.serializer.StringRedisSerializer;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Properties;

/**
 * @ClassName: ReadExcelToMap
 * @Author: zy
 * @Date: 2021/7/22 19:04
 * @Description: 排水单元内数据读取Excel，并保存到redis中
 */
public class ReadExcelToMap {

    private static RedisTemplate redisTemplate;

    public static void main(String[] args) throws Exception{

        //加载配置文件 测试环境
        InputStream in = ReadExcelToMap.class.getClassLoader().getResourceAsStream("application-prod.properties");
        // 正式环境
        //InputStream in = ReadExcelToMap.class.getClassLoader().getResourceAsStream("application-prod.properties");

        Properties pro = new Properties();
        try {
            pro.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //设置redis账号密码
        JedisConnectionFactory jedisConnectionFactory = new JedisConnectionFactory();
        jedisConnectionFactory.setHostName(pro.getProperty("spring.redis.host"));
        jedisConnectionFactory.setPort(Integer.parseInt(pro.get("spring.redis.port").toString()));
        jedisConnectionFactory.setPassword(pro.getProperty("spring.redis.password"));

        redisTemplate = getRedisTemplate(jedisConnectionFactory);

        importFile();

    }

    //获取redis
    public static RedisTemplate getRedisTemplate(JedisConnectionFactory connectionFactory){

        RedisTemplate<Object, Object> redisTemplate = new RedisTemplate<>();
        redisTemplate.setConnectionFactory(connectionFactory);
        // 使用Jackson2JsonRedisSerialize 替换默认序列化
        Jackson2JsonRedisSerializer jackson2JsonRedisSerializer = new Jackson2JsonRedisSerializer(Object.class);
        ObjectMapper objectMapper = new ObjectMapper();
        objectMapper.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);
        objectMapper.enableDefaultTyping(ObjectMapper.DefaultTyping.NON_FINAL);
        jackson2JsonRedisSerializer.setObjectMapper(objectMapper);
        // 设置value的序列化规则和 key的序列化规则
        redisTemplate.setValueSerializer(jackson2JsonRedisSerializer);
        redisTemplate.setKeySerializer(new StringRedisSerializer());
        redisTemplate.setHashKeySerializer(jackson2JsonRedisSerializer);
        redisTemplate.setHashValueSerializer(jackson2JsonRedisSerializer);
        redisTemplate.setDefaultSerializer(jackson2JsonRedisSerializer);
        redisTemplate.setEnableDefaultSerializer(true);
        redisTemplate.afterPropertiesSet();
        return redisTemplate;
    }

    //导入排水单元内排水 数据
    public static void importFile()throws Exception{

        //InputStream instream = new FileInputStream("D:/app管网0722备份长度统计.xlsx");

        //读取文件地址
        String fileName = "D:/app管网0722备份长度统计.xlsx";

        //headRowNumber(2)  第三行开始读取
        List<ReadExcelBean> list = EasyExcel.read(fileName).head(ReadExcelBean.class).sheet().headRowNumber(2).doReadSync();

        for (int i = 0; i < list.size(); i++) {

            //沟渠 -- 所属区
            String gqLable =list.get(i).getGqLabel();

            if("全部".equals(gqLable)){
                gqLable = "广州";
            }

            //沟渠 --长度
            String gqLength = list.get(i).getGqLength();

            if(gqLable != null){
                
                //写入redis 相当于hset
                //排水单元内排水沟渠
                redisTemplate.opsForHash().put("psgq",gqLable,gqLength);
            }

            //管线-- 所属区
            String gxLable =  list.get(i).getGxLabel();

            if("全部".equals(gxLable)){
                gxLable = "广州";
            }

            //管线--长度
            String gxLength = list.get(i).getGxLength();

            //写入redis 相当于hset
            //排水单元内排水管线
            redisTemplate.opsForHash().put("psgx",gxLable,gxLength);

        }

    }

}

成功之后读取redis的值

Map<String,BigDecimal> map = redisTemplate.opsForHash().entries("psgx");

也可以直接进redis里面 通过hgetall key 命令读取所有值

完毕