Java里的Excl导入的Util

 1：ExclUtil:

package com.sys.utils;

import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import static org.apache.poi.ss.usermodel.CellType.STRING;
import static org.apache.poi.ss.usermodel.CellType.BLANK;
import static org.apache.poi.ss.usermodel.CellType.NUMERIC;

/**
 * @author wzq
 * @event
 * @describe
 * @Data on 2021/10/21  09:03:58
 */
@Data
public class ExclUtil {

    //标题的行序号
    private Integer headerIndex;
    //数据的行序号
    private Integer dataIndex;
    //sheet页数
    private Integer sheetCount;

    /**
     * 导入 excl
    * */
    public List<Map<String, Object>> importExcel(MultipartFile multipartFile) throws IOException {
        List<Map<String, Object>> mapList = new ArrayList<>();
        SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd");
        if (multipartFile.isEmpty()) {
            return mapList;
        }

        InputStream inputStream = multipartFile.getInputStream();
        Workbook workbook = null;
        String path = multipartFile.getOriginalFilename();
        String name = path.substring(path.lastIndexOf("."),path.length());
        if (name.equals(".xls")){
            POIFSFileSystem fileSystem = new POIFSFileSystem(inputStream);
            workbook = new HSSFWorkbook(fileSystem);
        }else if (name.equals(".xlsx")){
            workbook = new XSSFWorkbook(inputStream);
        }
        //2 获取sheet 列数
        int sheets = workbook.getNumberOfSheets();
        if (this.sheetCount==null){
            this.sheetCount=sheets;
        }
        //3 遍历所有sheet列
        for (int i = 0; i < this.sheetCount; i++) {
            //获取sheet
            Sheet sheet = workbook.getSheetAt(i);
            //读取第一行
            Row headerRow = sheet.getRow(this.headerIndex);
            //获取sheet    所有行数
            int rows = sheet.getPhysicalNumberOfRows() - 1;
            List<String> headerColumns = new ArrayList<>();
            for (int j = 0; j < headerRow.getPhysicalNumberOfCells(); j++) {
                Cell cell = headerRow.getCell(j);
                cell.getStringCellValue();
                headerColumns.add(cell.getStringCellValue());
            }
            //获取类型
            Row secondRow = sheet.getRow(1);
            int cells = secondRow.getPhysicalNumberOfCells();

            //对第二行的数据进行操作
            for (int j = this.dataIndex; j < rows; j++) {
                Row row = sheet.getRow(j);
                if (isEmptyRow(row)){
                    continue;
                }
                Map map = new LinkedHashMap();
                //获取字段属性
                for (int k = 0; k < headerColumns.size(); k++) {
                    if (row.getCell(k) == null) {
                        map.put(headerColumns.get(k), "");
                    } else {
                        CellType cellType = row.getCell(k).getCellTypeEnum();
                        if (row.getCell(k).getCellType().equals(STRING)) {
                            map.put(headerColumns.get(k).trim(), row.getCell(k).getStringCellValue());
                        } else if (row.getCell(k).getCellType().equals(NUMERIC)) {
                            if (HSSFDateUtil.isCellDateFormatted(row.getCell(k))) {
                                String now = sim.format(row.getCell(k).getDateCellValue());
                                map.put(headerColumns.get(k).trim(), now);
                            } else {
                                map.put(headerColumns.get(k).trim(), row.getCell(k).getNumericCellValue());
                            }
                        } else if (row.getCell(k).getCellType().equals(BLANK)) {
                            map.put(headerColumns.get(k).trim(), row.getCell(k).getStringCellValue());
                        }
                    }
                }
                mapList.add(map);
            }
        }
        return mapList;
    }
    /**
     * 判断该行是否为空
     * @param row 行对象
     * @return
     */
    public  boolean isEmptyRow(Row row) {
        //行不存在
        if (row == null) {
            return true;
        }
        //第一个列位置
        int firstCellNum = row.getFirstCellNum();
        //最后一列位置
        int lastCellNum = row.getLastCellNum();
        //空列数量
        int nullCellNum = 0;
        for (int c = firstCellNum; c < lastCellNum; c++) {
            Cell cell = row.getCell(c);
            if (null == cell || BLANK == cell.getCellType()) {
                nullCellNum++;
                continue;
            }
//			cell.setCellType(CellType.STRING);
            if (cell.getCellType().equals(CellType.STRING)){
                String cellValue = cell.getStringCellValue().trim();
                if (StringUtils.isEmpty(cellValue)) {
                    nullCellNum++;
                }
            }
        }
        //所有列都为空
        if (nullCellNum == (lastCellNum - firstCellNum)) {
            return true;
        }
        return false;
    }
}

2:实际调用

  public Boolean importExclAndDB(MultipartFile file, String airId, String areaId) {
        ExclUtil exclUtil = new ExclUtil();
        //数据头
        exclUtil.setHeaderIndex(0);
        //数据栏
        exclUtil.setSheetCount(1);
        //数据源
        exclUtil.setDataIndex(3);
        List<Map<String, Object>> maps = null;
        try {
            //导入excl
            maps = exclUtil.importExcel(file);
            System.out.println(maps);
        } catch (IOException e) {
            e.printStackTrace();
        }
        TbTestExclParam tbTestExclParam = new TbTestExclParam();
        tbTestExclParam.setCreatedTime(new Date());
        //excl 的文件  是否需要传输
        Long exclId = this.add(tbTestExclParam);
        if(ObjectUtil.isNotNull(exclId) && exclId > 0){
            //1.正式导入excl数据到 数据库DB表里
            if(ObjectUtil.isNotNull(maps) && maps.size() > ForeignEnum.DEFAULT.getCode()){
                    maps.stream().forEach(x -> {
                        TbTestExclDataParam vo = new TbTestExclDataParam();
                        vo.setExclId(exclId);
                        //文档的 表头 第一行的内容
                        vo.setA(Integer.valueOf(String.valueOf(x.get("a")).split("\\.")[0]));
                      vo.setB(Integer.valueOf(String.valueOf(x.get("b")).split("\\.")[0]));
                        vo.setC(Float.valueOf(String.valueOf(x.get("c"))));
                        tbTestExclDataService.add(vo);
                    });
            }
        }
        return true;
    }

excl  文件 如下: