Excel的动态解析+MybatisPlus+Springboot

Excel的动态解析+MybatisPlus+Springboot

（省去了手动录入数据）主要用于前端动态渲染

---

将Excel分为结构数据、业务数据解析为 JSON，分别存储在数据库中

1. 结构数据就是指excel表的表头部分，
2. 业务数据就是正文部分的数据

• 表头部分的数据，我们会用父子及关系来存储

1、pom依赖

<!-- POI -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
    <exclusions>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<!-- lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>
<!--fastjson-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.47</version>
</dependency>
<!--hutool-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.6.5</version>
</dependency>

2、实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CellNode {

    private String id;
    private String pId;
    
    // 行
    private Integer row;
    // 列
    private Integer column;
    // 单元格地址
    CellAddress address;
    // 单元格横向合并个数
    private Integer mergeCellCol;
    // 单元格纵向合并个数
    private Integer mergeCellRow;
    private String value;
    // 子节点
    private List<CellNode> children;

}


@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("excel")
@JsonInclude(JsonInclude.Include.NON_EMPTY)
public class Excel {
    
	// 表Id
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private Long id;
	// 目录 or 类型
    @NotEmpty(message = "catalog为必填")
    private String catalog;
	// json-> 表头
    private String header;
	// json-> 业务数据
    private String data;
	// excel的名称
    private String excelName;
	// 表头数据部分结束行
    @JsonIgnore
    private Integer endHeaderRow;
	// 业务数据部分结束行
    @JsonIgnore
    private Integer endDataRow;
	// 创建时间
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss") //出参
    private Date createDate = new Date();
}

3、工具类

import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.extra.pinyin.PinyinUtil;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.piesat.kdlsnatdis.entity.vo.CellNode;
import com.piesat.kdlsnatdis.enums.Constants;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.Comparator;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Collectors;

public class ReadExcelUtils {
	
    /**
     * 测试
     */
    public static void main(String[] args) {
        String path = "E:\\QMDownload\\AppData\\Firefox" + File.separator + "调查XXX况表.xls";
        try {
            List<CellNode> header = getHeader(5, path);
            header.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 获取Excel 的表结点
     *
     * @param rows      excel表，头结点的结束行
     * @param excelPath excel文件路径
     */
    public static List<CellNode> getHeader(Integer rows, String excelPath) throws Exception {
        // 获取源文件的输入流
        FileInputStream inputStream = new FileInputStream(excelPath);
        // 根据流，来创建对应的 工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        // 获取工作表
        HSSFSheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
        // 表头结果集
        List<CellNode> nodeList = new CopyOnWriteArrayList<>();
        // 表示当前行
        int row = 1;
        // 斗节点的总宽度
        int allColumn = 0;
        // 指针
        int mergerNode = 0;
        Boolean flag = false;
        try {
            // 遍历行
            for (Row next : sheet) {

                // 遍历每一行的单元格
                for (Cell cell : next) {
                    String value = getCellValue(cell); // 值
                    CellAddress address = cell.getAddress();
                    int lie = address.getColumn(); // 列
                    int hang = address.getRow(); // 行
                    int mergerNumCol = getMergerCellRegionCol(sheet, hang, lie); // 获取单元格-横向合并的数量
                    int mergerNumRow = getMergerCellRegionRow(sheet, hang, lie); // 获取单元格-纵向合并的数量
                    if (mergerNumCol == 0 && mergerNumRow == 0) {
                        // 这一步非常重要，但是又坑！！坑死了
                        mergerNumCol = 1;
                    }

                    // `mergerNode==0` 表示没有单元格合并
                    if (mergerNode == 0 && mergerNumCol > 1) {
                        mergerNode = mergerNumCol - 1;
                    }
                    if (row != (hang + 1)) {
                        // 当前行
                        row++;
                    }
                    // 对第一行操作
                    if ((mergerNode != 0) && (hang + 1) == row && StringUtils.isBlank(value)) {
                        // 过滤掉合并单元格的空值单元格
                        mergerNode--;
                        continue;
                    }
                    if (row == 1) {
                        nodeList.add(new CellNode(IdUtil.simpleUUID(), "0", hang + 1, lie + 1, address, mergerNumCol, mergerNumRow, value, null));
                        // 记录第一行，所有字段的总宽度
                        allColumn += mergerNumCol;

                    } else {
                        for (CellNode i : nodeList) {
                            // 起始列
                            Integer startCol = i.getColumn();
                            // 父节点的横向长度
                            int lengthCol = i.getColumn() + i.getMergeCellCol() - 1;
                            if (StringUtils.isNotBlank(value) && startCol <= (lie + 1) && lengthCol >= (lie + 1)) {
                                List<CellNode> resList = nodeList.stream()
                                        .filter(t -> ((t.getColumn() + t.getMergeCellCol() - 1) >= (lie + 1) && (t.getColumn() <= (lie + 1))))
                                        .sorted(Comparator.comparing(CellNode::getRow).reversed())
                                        .collect(Collectors.toList());
                                if (resList.size() > 0) {
                                    nodeList.add(new CellNode(IdUtil.simpleUUID(), resList.get(0).getId(), hang + 1, lie + 1, address, mergerNumCol, mergerNumRow, value, null));
                                    break;
                                }
                            }
                        }
                    }
                }
                if (row == (rows - 1)) {
                    inputStream.close();
                    return nodeList;
                }
            }
        } catch (Throwable throwable) {
            throwable.printStackTrace();
        } finally {
            inputStream.close();
        }
        inputStream.close();
        return null;
    }

    public static List<List<String>> getData(Integer startRow, Integer endRow, String excelPath) throws Exception {
        // 获取源文件的输入流
        FileInputStream inputStream = new FileInputStream(excelPath);
        // 根据流，来创建对应的 工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        // 获取工作表
        HSSFSheet sheet = workbook.getSheetAt(0); // 获取第一个工作表

        List<List<String>> list = new CopyOnWriteArrayList<>();
        List<CellNode> nodeList = new CopyOnWriteArrayList<>();
        int row = startRow;
        // 遍历行
        for (Row next : sheet) {
            List<String> data = new CopyOnWriteArrayList<>();
            int rowNum = next.getRowNum();
            if (rowNum < startRow - 1) {
                continue;
            }
            if (rowNum > endRow - 1) {
                inputStream.close();
                return list;
            }
            // 遍历列
            next.forEach(i -> {
                try {
                    String value = getCellValue(i); // 值
                    CellAddress address = i.getAddress();
                    int lie = address.getColumn(); // 列
                    int hang = address.getRow(); // 行
                    int mergerNumRow = getMergerCellRegionRow(sheet, hang, lie); // 获取单元格-纵向合并的数量
                    // 如果为"", 则说明单元格纵向合并了。
                    if ("".equals(value)) {
                        for (CellNode t : nodeList) {
                            if (t.getRow() == hang && t.getColumn() == (lie + 1)) {
                                value = t.getValue();
                                nodeList.add(new CellNode("", "", hang + 1, lie + 1, address, 1, mergerNumRow, value, null));
                            }
                        }
                    }
                    nodeList.add(new CellNode("", "", hang + 1, lie + 1, address, 1, mergerNumRow, value, null));
                    data.add(value);
                } catch (Throwable throwable) {
                    throwable.printStackTrace();
                }
            });
            list.add(data);
        }
        inputStream.close();
        return list;
    }

    /**
     * 判断单元格的值，并且返回
     */
    private static String getCellValue(Cell cell) {
        String value;
        switch (cell.getCellType().name()) {
            case "NUMERIC": // 数字
                // 如果为时间格式的内容
                if (DateUtil.isCellDateFormatted(cell)) {
                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                    value = sdf.format(DateUtil.getJavaDate(cell.
                            getNumericCellValue()));
                    break;
                } else {
                    double cellNumericCellValue = cell.getNumericCellValue();
                    // 数字转字符串，自动并去除尾小数点儿后多余的0
                    value = NumberUtil.toStr(cellNumericCellValue);
                }
                break;
            case "STRING": // 字符串
                String cellValue = cell.getStringCellValue();
                // 对字符做过滤
                value = cellValue.replace(Constants.YMD_HMS, "")
                        .replace(Constants.YMD, "")
                        .replace(Constants.FBT, "");
                break;
            case "BOOLEAN": // Boolean
                value = cell.getBooleanCellValue() + "";
                break;
            case "FORMULA": // 公式
                value = cell.getCellFormula() + "";
                break;
            case "BLANK": // 空值
                value = "";
                break;
            case "ERROR": // 故障
                value = "非法字符";
                break;
            default:
                value = "未知类型";
                break;
        }
        return value;
    }

    /**
     * 得到单元格横向的合并情况
     */
    private static int getMergerCellRegionCol(HSSFSheet sheet, int cellRow, int cellCol) throws Throwable {
        int retVal = 0;
        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cra = sheet.getMergedRegion(i);
            int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
            int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
            int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
            int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
            if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
                if (cellCol >= firstCol && cellCol <= lastCol) {
                    retVal = lastCol - firstCol + 1; // 得到合并的列数
                    break;
                }
            }
        }
        return retVal;
    }

    /**
     * 得到单元格纵向的合并情况
     */
    private static int getMergerCellRegionRow(HSSFSheet sheet, int cellRow, int cellCol) throws Throwable {
        int retVal = 0;
        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cra = sheet.getMergedRegion(i);
            int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
            int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
            int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
            int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
            if (cellCol >= firstCol && cellCol <= lastCol) {
                if (cellRow >= firstRow && cellRow <= lastRow) { 
                    retVal = lastRow - firstRow + 1; 
                    break;
                }
            }
        }
        return retVal;
    }
}

4、Dao层

@Mapper
public interface ExcelMapper extends BaseMapper<Excel> {
}

5、Service层

public interface ExcelService extends IService<Excel> {

    Boolean readExcel(String path, Integer rows, Integer endRows, String catalog);

}

@Service
public class ExcelServiceImpl extends ServiceImpl<ExcelMapper, Excel> implements ExcelService {
    @Resource
    private ExcelMapper excelMapper;

    @Override
    public Boolean readExcel(String path, Integer rows, Integer endRows, String catalog) {
        // 封装实体类
        Excel excel = new Excel();
        try {
            // 获取表头数据
            List<CellNode> header = ReadExcelUtils.getHeader(rows, path);
            if (header != null) {
                // 转JSON
                List<CellNode> tree = buildTree(header);
                String headerStr = JSON.toJSONString(tree);
                excel.setHeader(headerStr);
            } else {
                return false;
            }
            // 获取表的业务数据
            List<List<String>> data = ReadExcelUtils.getData(rows, endRows, path);
            String dataStr = JSON.toJSONString(data);
            excel.setData(dataStr);
            // 封装简称
            excel.setCatalog(catalog);
            // 封装表头结束行和业务数据结束行
            excel.setEndHeaderRow(rows);
            excel.setEndDataRow(endRows);
            // 封装文件名
            File file = new File(path);
            String fileName = file.getName();
            excel.setExcelName(fileName);
            return excelMapper.insert(excel) == 1;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public List<CellNode> buildTree(List<CellNode> pidList) {
        // 转为 IP-PID结构
        Map<String, List<CellNode>> pidListMap = pidList.stream().collect(Collectors.groupingBy(CellNode::getPId));
        pidList.forEach(i -> {
            i.setChildren(pidListMap.get(i.getId()));
        });
        //返回结果也改为返回顶层节点的list
        return pidListMap.get("0");
    }
}

6、Controller

@RestController
@RequestMapping("/excel")
public class ExcelController {

    private final Logger log = LoggerFactory.getLogger(NdZrzhServiceImpl.class);

    @Autowired
    private ExcelService excelService;

    /**
     * 解析Excel文件成 json，并且存入数据库
     */
    @PostMapping("/read")
    public Result<?> addJsonToSql(@RequestBody Map<String, Object> map) {
        // 参数校验
        int rows;
        int endRows;
        if (Objects.isNull(map.get("path")) || StringUtils.isBlank(map.get("path").toString())) {
            return new Result<>(StatusCode.ERROR, "'path'必须必填");
        }
        if (Objects.isNull(map.get("catalog")) || StringUtils.isBlank(map.get("catalog").toString())) {
            return new Result<>(StatusCode.ERROR, "'catalog'必须必填");
        }
        if (map.get("rows") != null) {
            rows = Integer.parseInt(map.get("rows").toString());
            if (rows < 0) {
                return new Result<>(StatusCode.ERROR, "rows必须>0");
            }
        } else {
            return new Result<>(StatusCode.ERROR, "rows必填");
        }
        if (map.get("endRows") != null) {
            endRows = Integer.parseInt(map.get("endRows").toString());
            if (endRows < 0) {
                return new Result<>(StatusCode.ERROR, "endRows必须>0");
            }
        } else {
            return new Result<>(StatusCode.ERROR, "endRows必填");
        }
        String path = map.get("path").toString();
        String catalog = map.get("catalog").toString();
        // 解析
        if (excelService.readExcel(path, rows, endRows, catalog)) {
            return new Result<>(StatusCode.SUCCESS, "excel解析成功");
        } else {
            return new Result<>(StatusCode.ERROR, "excel解析失败");
        }
    }


}

7、Postman 测试

• 参数：catalog，为文件名的简写。
• 参数：rows 和 endrows 的取值说明