解析含有合并单元格的excel

367 阅读4分钟

一、说明

例如解析如下excel。

image.png

可以看到,这个excel中包含了单元格的合并,解析起来稍微有点难度。

二、思路

1.提供一个包含解析该excel列标题映射具体属性的map。主要作用是将excel中2,3行的标题内容拼接起来作为唯一的标题。

 /**
     * 要解析的列和属性的映射关系
     */
    private static final Map<String, String> COLUMN_PRIORITY_MAP = new HashMap<String, String>() {
        {
            put("序号", "orderNum");
            put("主管部门(区县、开发区)", "mainDept");
            put("事业单位名称", "institutionName");
            put("单位性质/经费形式", "unitNature");
            put("岗位类别", "positionCategory");
            put("岗位简称", "positionName");
            put("岗位代码", "positionCode");
            put("招聘人数", "recruitersNum");
            put("招聘岗位所需资格条件专业名称", "needMajorName");
            put("招聘岗位所需资格条件学历", "needRecord");
            put("招聘岗位所需资格条件学位", "needDegree");
            put("招聘岗位所需资格条件其他条件", "needOtherCondition");
            put("笔试类别", "writtenType");
            put("备注", "mark");
        }
    };

2.调用poi提供的方法,收集所有的合并单元格信息。针对合并单元格而言,将收集到的信息组装成一个map;

map的key是:当前行号+“-”+当前列号;

map的value是:包含起始行号,起始列号的数组。Integer[起始行号,起始列号]

之所以构建这样的map是因为针对合并单元格而言,只有起始行,起始列对应的那个单元格才能获取到值,而其他单元格只能获取到空值。因此我们构造这样一个map,让其他单元格也映射到起始行,起始列对应的那个单元格上,到时就可以获取到真正的值了。

3.遍历excel。在这个例子中我们知道列标题是2,3行,所以在解析的过程中就针对2,3行构造一个列号对应标题的map。如果说你并不知道列标题是2,3行,也可以通过比较判断出来。

4.接下来我们以(4,A)这个单元格举个简单的例子,A作为起始列起始就是0。

image.png

思路图如下:

image.png

三、完整代码

package excel.parse;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelCellValueParse {

    private static final String PATH = "D:" + File.separator + "合并单元格测试.xlsx";

    /**
     * 指明是几行的标题合并
     */
    private static final Integer TITLE_MERGE_NUM = 2;

    /**
     * 开始解析行
     */
    private static final Integer START_PARSE_NUM = 1;

    /**
     * 要解析的列和属性的映射关系
     */
    private static final Map<String, String> COLUMN_PRIORITY_MAP = new HashMap<String, String>() {
        {
            put("序号", "orderNum");
            put("主管部门(区县、开发区)", "mainDept");
            put("事业单位名称", "institutionName");
            put("单位性质/经费形式", "unitNature");
            put("岗位类别", "positionCategory");
            put("岗位简称", "positionName");
            put("岗位代码", "positionCode");
            put("招聘人数", "recruitersNum");
            put("招聘岗位所需资格条件专业名称", "needMajorName");
            put("招聘岗位所需资格条件学历", "needRecord");
            put("招聘岗位所需资格条件学位", "needDegree");
            put("招聘岗位所需资格条件其他条件", "needOtherCondition");
            put("笔试类别", "writtenType");
            put("备注", "mark");
        }
    };


    public static void main(String[] args) throws Exception {
        // 获取要解析的excel文件
        InputStream inputStream = new FileInputStream(PATH);
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        // 获取第一个sheet页
        XSSFSheet sheet = workbook.getSheetAt(0);
        /**
         * 注意:所有的合并单元格只有第一个单元格能获取到值,其他的单元格都是空白格。
         *
         * 通过获取所有合并信息,构造一个合并信息中的当前行,当前列为key,合并信息的起始行,起始列为value的map获取所有合并单元格信息
         */
        Map<String, Integer[]> mergeCoordinateMap = mergeCoordinate(sheet);

        // 获取sheet页中最后一行的行号
        int lastRowNum = sheet.getLastRowNum();
        Map<String, String> titleMap = new HashMap<>();
        Map<String, String> numPriorityMap = new HashMap<>();
        List<Map<String, String>> entityList = new ArrayList<>();
        /**
         * 遍历sheet页;
         * 第0行是大标题不解析,所以从第一行开始;
         */
        for (int rowNum = START_PARSE_NUM; rowNum <= lastRowNum; rowNum++) {
            XSSFRow row = sheet.getRow(rowNum);
            // 获取本行最后一列的列号
            int rowLastCellNum = row.getLastCellNum();
            // 每一行的属性及值都封在此map中
            Map<String, String> entityMap = new HashMap<>();
            // 遍历本行的每一列
            for (int columnNum = 0; columnNum < rowLastCellNum; columnNum++) {
                // 获取当前行,某列的单元格
                XSSFCell cell = row.getCell(columnNum);
                /**
                 * 判断是否合并单元格
                 * 是:从mergeCoordinateMap中取值
                 * 否:直接获取单元格的值
                 */
                Integer[] integers = mergeCoordinateMap.get(rowNum + "-" + columnNum);
                /**
                 * 获取当前单元格的值
                 */
                String cellValue = getValue(sheet, cell, integers);

                /**
                 * 构造一个列的下标为key,前两行标题合并后为value的map;
                 */
                if (rowNum <= TITLE_MERGE_NUM) {
                    buildTitleMap(titleMap, columnNum, cellValue);
                    continue;
                }
                /**
                 * 构造一个列下标为key,属性为value的map;
                 */
                buildNumPriorityMap(titleMap, numPriorityMap);
                /**
                 * 构造属性为key,单元格的值为value的map;
                 */
                buildEntityMap(sheet, mergeCoordinateMap, numPriorityMap, rowNum, entityMap, columnNum, cell);
            }
            // 将组装好的map,放入list
            if (entityMap == null || entityMap.keySet().size() == 0) {
                continue;
            }
            entityList.add(entityMap);
        }
        System.out.println(entityList);
    }

    private static void buildEntityMap(XSSFSheet sheet, Map<String, Integer[]> mergeCoordinateMap, Map<String, String> numPriorityMap, int rowNum, Map<String, String> entityMap, int columnNum, XSSFCell cell) {
        for (Map.Entry<String, String> entry : numPriorityMap.entrySet()) {
            if (StringUtils.equalsIgnoreCase(entry.getKey(), String.valueOf(columnNum))) {
                String entityKey = numPriorityMap.get(String.valueOf(columnNum));
                String priorityVal = getValue(sheet, cell, mergeCoordinateMap.get(rowNum + "-" + columnNum));
                entityMap.put(entityKey, priorityVal);
                break;
            }
        }
    }

    private static void buildNumPriorityMap(Map<String, String> titleMap, Map<String, String> numPriorityMap) {
        for (Map.Entry<String, String> titleEntry : titleMap.entrySet()) {
            String key = titleEntry.getKey();
            String value = titleEntry.getValue();
            String priorityKey = COLUMN_PRIORITY_MAP.get(value);
            numPriorityMap.put(key, priorityKey);
        }
    }

    private static void buildTitleMap(Map<String, String> titleMap, int columnNum, String cellValue) {
        String titleTemp = titleMap.get(String.valueOf(columnNum));
        if (StringUtils.equalsIgnoreCase(titleTemp, cellValue)) {
            return;
        }
        titleMap.put(String.valueOf(columnNum), StringUtils.join(titleTemp, cellValue));
        return;
    }

    private static String getValue(XSSFSheet sheet, XSSFCell cell, Integer[] integers) {
        String cellValue;
        if (integers != null) {
            cellValue = getCellValue(sheet.getRow(integers[0]).getCell(integers[1]));
        } else {
            cellValue = getCellValue(cell);
        }
        return cellValue;
    }


    private static Map<String, Integer[]> mergeCoordinate(XSSFSheet sheet) {
        Map<String, Integer[]> mergeValueMap = new HashMap<>();
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (CellRangeAddress mergedRegion : mergedRegions) {
            int firstRow = mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            Integer[] coordinateMap = new Integer[]{firstRow, firstColumn};
            for (int currentRow = firstRow; currentRow <= lastRow; currentRow++) {
                for (int currentColumn = firstColumn; currentColumn <= lastColumn; currentColumn++) {
                    mergeValueMap.put(currentRow + "-" + currentColumn, coordinateMap);
                }
            }
        }
        return mergeValueMap;
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return StringUtils.EMPTY;
        }
        CellType cellTypeEnum = cell.getCellTypeEnum();
        switch (cellTypeEnum) {
            case STRING:// 字符串
                return cell.getStringCellValue();
            case NUMERIC:// 数值类型(整数、小数、日期)
                if (DateUtil.isCellDateFormatted(cell)) {
                    return DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd");
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:// 布尔
                return String.valueOf(cell.getBooleanCellValue());
            case BLANK:// 空白格,有样式
                return "";
            case _NONE:// 未知类型
                return "未知类型";
            case ERROR:// 错误单元格
                return "错误单元格";
            default:
                return StringUtils.EMPTY;
        }
    }
}