关于POI解析Excel中[Check Box 221]组件的状态值的一次经历

描述

当前在做一个关于Excel导入的功能，遇到一个需要解析出复选框状态值，如图所示： 特别需要说的是，博主卡在了如何解析复选框的状态，经过一系列的查询获取到了复选框的文本，但始终无法获取其状态（是否勾选）。 现在在做一次复盘，在这之前，博主对于POI解析Excel接触不深，所以也算是一次的探索的过程。首先是当接到这个需求的时候，有种畏难的情绪（需解析的Excel非常的复杂）；其次，当解析到复选框时，始终得不到想要的结果，就有点想放弃；最后就是陷入死循环中。

解决方案

private final static Pattern checkBoxReg = Pattern.compile("\\[sid=0x000A.+?\\[0(\\d),");

/**  
* 解析文本框的选择项  
* @param sheet sheet页  
* @param targetRow 目标行  
* @param targetCol 目标列  
* @return  
* @throws Exception  
*/  
public static List<String> parseTextBoxSelections(HSSFSheet sheet, int targetRow, int targetCol) throws Exception {  
    List<String> selections = new ArrayList<>();  
    HSSFPatriarch patriarch = sheet.getDrawingPatriarch();  
    if (patriarch != null) {  
        processShapes(patriarch.getChildren(), targetRow, targetCol, selections, null);  
    }  
    return selections;  
}

private static void processShapes(List<HSSFShape> shapes, int targetRow, int targetCol,  
List<String> selections, HSSFClientAnchor parentClientAnchor) throws Exception {  
    for (HSSFShape shape : shapes) {  
        if (shape instanceof HSSFShapeGroup) {  
            // 处理组合形状：递归遍历子形状，并传递父容器的 ClientAnchor  
            HSSFShapeGroup group = (HSSFShapeGroup) shape;  
            HSSFAnchor groupAnchor = group.getAnchor();  
            HSSFClientAnchor currentParentAnchor = parentClientAnchor;  

            // 如果组合形状自身有 ClientAnchor，则更新父锚点  
            if (groupAnchor instanceof HSSFClientAnchor) {  
                currentParentAnchor = (HSSFClientAnchor) groupAnchor;  
            }  

            // 递归处理子形状，传递当前父锚点  
            processShapes(group.getChildren(), targetRow, targetCol, selections, currentParentAnchor);  
        } else if (shape instanceof HSSFSimpleShape) {  
            HSSFSimpleShape simpleShape = (HSSFSimpleShape) shape;  

            if (simpleShape.getShapeType() == HSSFSimpleShape.OBJECT_TYPE_COMBO_BOX) {  
                HSSFAnchor anchor = simpleShape.getAnchor();  
                boolean isMatch = false;  

                if (anchor instanceof HSSFClientAnchor) {  
                    // 直接检查 ClientAnchor 的坐标  
                    HSSFClientAnchor clientAnchor = (HSSFClientAnchor) anchor;  
                    isMatch = (clientAnchor.getRow1() == targetRow && clientAnchor.getCol1() == targetCol);  
                } else if (anchor instanceof HSSFChildAnchor && parentClientAnchor != null) {  
                    // 使用父容器的 ClientAnchor 坐标  
                    isMatch = (parentClientAnchor.getRow1() == targetRow && parentClientAnchor.getCol1() == targetCol);  
            }  

                if (isMatch) {  
                    String text = simpleShape.getString().toString();  
                    String simpleName = simpleShape.getShapeName();  
                    // 记录选中的选项  
                    isCheckedText(selections, simpleShape, text);  
                }  
            }  
        }  
    }  
}  
  
private static void isCheckedText(List<String> selections, HSSFSimpleShape simpleShape, String text) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {  
    Method getTextObjectRecord = simpleShape.getClass().getSuperclass().getDeclaredMethod("getObjRecord", null);  
    getTextObjectRecord.setAccessible(true);  
    Object textObjectRecord = getTextObjectRecord.invoke(simpleShape, null);  
    Matcher m = checkBoxReg.matcher(textObjectRecord.toString());  
    if (m.find()) {  
        String checkBit = m.group(1);  
        if (checkBit.length() == 1) {  
            boolean checked = "1".equals(checkBit);  
            if (checked) {  
                selections.add(text);  
            }  
        }  
    }  
}

总结

关键代码：

Method getTextObjectRecord = simpleShape.getClass().getSuperclass().getDeclaredMethod("getObjRecord", null);

// 获取HSSFShape类中定义的getObjRecord方法，该方法用于获取ObjRecord对象  
Method getObjRecord = HSSFShape.class.getDeclaredMethod("getObjRecord");

博主在做的时候始终关注点在simpleShape类本身的方法中获取复选框的状态，忽略了其父类的属性以及方法，从而一直没有解决。也是一次经验和教训吧，多阅读源码！