对上传的excel进行读取操作(SpringBoot Mybatis-Plus)

gradle

compile group: 'org.apache.poi', name: 'poi', version: '3.9';
compile group: 'org.apache.poi', name: 'poi-excelant', version: '3.9';
compile group: 'org.apache.poi', name: 'poi-scratchpad', version: '3.9';

Controller

@PostMapping(value = "/excelImport")
    @ResponseBody
    public HttpResult excelImport(@RequestParam("file") MultipartFile multipartFile, @RequestParam("deptId") Long deptId) throws IOException, InterruptedException {
        String fileName = multipartFile.getOriginalFilename();
        if (!fileName.endsWith("xlsx") && !fileName.endsWith("xls")) {
            return HttpResult.error("请上传excel文件");
        }

        String result = sysSubjectService.excelImport(multipartFile);

        if (!"succeed".equals(result)) {
            return HttpResult.error(result);
        }

        return HttpResult.ok(result);
    }

Service

@Override
    public String excelImport(MultipartFile multipartFile) throws IOException {
        String fileName = multipartFile.getOriginalFilename();

        Workbook workbook = null;
        InputStream is = multipartFile.getInputStream();
        if (fileName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(is);
        }
        if (fileName.endsWith("xls")) {
            workbook = new HSSFWorkbook(is);
        }

        if (workbook != null) {
            List<SysSubject> subjectList = new ArrayList<>();
            //读取第一个sheet
            Sheet sheet = workbook.getSheetAt(0);
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                SysSubject sysSubject = new SysSubject();

                // XSSFRow 代表一行数据
                Row row = sheet.getRow(rowIndex);
                //获取单元格信息
                Cell dateCell = row.getCell(0);
                dateCell.setCellType(Cell.CELL_TYPE_STRING);
                String code = ExcelCellToStringUtil.getStringCellValue(dateCell);
                code = code.replaceAll("\s*", "").replaceAll("[\u00A0]+", "");
                //防空异常 必加
                if (null == code || "".equals(code)) {
                    continue;
                }

                //科目代码
                sysSubject.setCode(code);

                subjectList.add(sysSubject);
            }

            sysSubjectMapper.insertBatchSubject(tableName, subjectList);

        }
        return "succeed";
    }

RedisUtils

package cn.edu.qfnu.soft.common.util;

import java.io.Serializable;
import java.util.List;
import java.util.Set;
import java.util.concurrent.TimeUnit;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.HashOperations;
import org.springframework.data.redis.core.ListOperations;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.SetOperations;
import org.springframework.data.redis.core.ValueOperations;
import org.springframework.data.redis.core.ZSetOperations;
import org.springframework.stereotype.Service;

/**
 * redis操作工具类
 * @author 吕嘉鸣
 */
@Service
public class RedisUtils {
    @Autowired
    private RedisTemplate redisTemplate;

    /**
     * 写入缓存
     *
     * @param key
     * @param value
     * @return
     */
    public boolean set(final String key, Object value) {
        boolean result = false;
        try {
            ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
            operations.set(key, value);
            result = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 写入缓存设置时效时间
     *
     * @param key
     * @param value
     * @return
     */
    public boolean set(final String key, Object value, Long expireTime, TimeUnit timeUnit) {
        boolean result = false;
        try {
            ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
            operations.set(key, value);
            redisTemplate.expire(key, expireTime, timeUnit);
            result = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 批量删除对应的value
     *
     * @param keys
     */
    public void remove(final String... keys) {
        for (String key : keys) {
            remove(key);
        }
    }

    /**
     * 批量删除key
     *
     * @param pattern
     */
    public void removePattern(final String pattern) {
        Set<Serializable> keys = redisTemplate.keys(pattern);
        if (keys.size() > 0) {
            redisTemplate.delete(keys);
        }
    }

    /**
     * 删除对应的value
     *
     * @param key
     */
    public void remove(final String key) {
        if (exists(key)) {
            redisTemplate.delete(key);
        }
    }

    /**
     * 判断缓存中是否有对应的value
     *
     * @param key
     * @return
     */
    public boolean exists(final String key) {
        return redisTemplate.hasKey(key);
    }

    /**
     * 读取缓存
     *
     * @param key
     * @return
     */
    public Object get(final String key) {
        Object result = null;
        ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
        result = operations.get(key);
        return result;
    }

    /**
     * 哈希 添加
     *
     * @param key
     * @param hashKey
     * @param value
     */
    public void hmSet(String key, Object hashKey, Object value) {
        HashOperations<String, Object, Object> hash = redisTemplate.opsForHash();
        hash.put(key, hashKey, value);
    }

    /**
     * 哈希获取数据
     *
     * @param key
     * @param hashKey
     * @return
     */
    public Object hmGet(String key, Object hashKey) {
        HashOperations<String, Object, Object> hash = redisTemplate.opsForHash();
        return hash.get(key, hashKey);
    }

    /**
     * 列表添加
     *
     * @param k
     * @param v
     */
    public void lPush(String k, Object v) {
        ListOperations<String, Object> list = redisTemplate.opsForList();
        list.rightPush(k, v);
    }

    /**
     * 列表获取
     *
     * @param k
     * @param l
     * @param l1
     * @return
     */
    public List<Object> lRange(String k, long l, long l1) {
        ListOperations<String, Object> list = redisTemplate.opsForList();
        return list.range(k, l, l1);
    }

    /**
     * 集合添加
     *
     * @param key
     * @param value
     */
    public void add(String key, Object value) {
        SetOperations<String, Object> set = redisTemplate.opsForSet();
        set.add(key, value);
    }

    /**
     * 集合获取
     *
     * @param key
     * @return
     */
    public Set<Object> setMembers(String key) {
        SetOperations<String, Object> set = redisTemplate.opsForSet();
        return set.members(key);
    }

    /**
     * 有序集合添加
     *
     * @param key
     * @param value
     * @param scoure
     */
    public void zAdd(String key, Object value, double scoure) {
        ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
        zset.add(key, value, scoure);
    }

    /**
     * 有序集合获取
     *
     * @param key
     * @param scoure
     * @param scoure1
     * @return
     */
    public Set<Object> rangeByScore(String key, double scoure, double scoure1) {
        ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
        return zset.rangeByScore(key, scoure, scoure1);
    }
}

ExcelCellToStringUtil

package cn.edu.qfnu.soft.common.util;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
/**
 * excel单元格数据工具类
 * @author ljm
 */
public class ExcelCellToStringUtil {
    /**
     * 获取excel单元格数据内容中为字符串类型的数据
     * @author ljm
     * @param cell
     * @return
     */
    public static String getStringCellValue(Cell cell)
    {
        String strCell = "";
        if(cell == null)
        {
            return "";

        }else{
            //判断数据的类型
            switch (cell.getCellType()){
                case Cell.CELL_TYPE_NUMERIC: //数字
                    DataFormatter dataFormatter = new DataFormatter();

                    strCell = dataFormatter.formatCellValue(cell);
                    break;
                case Cell.CELL_TYPE_STRING: //字符串
                    strCell = String.valueOf(cell.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN: //Boolean
                    strCell = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA: //公式
                    strCell = String.valueOf(cell.getCellFormula());
                    break;
                case Cell.CELL_TYPE_BLANK: //空值
                    strCell = "";
                    break;
                case Cell.CELL_TYPE_ERROR: //故障
                    strCell = "非法字符";
                    break;
                default:
                    strCell = "未知类型";
                    break;
            }
        }
        if("".equals(strCell)||strCell==null)
        {
            return "";
        }
        return strCell;
    }
}