[Bug] 读取无法处理混合表头，即有合并表头又有多行表头

[zxuanhong]

Search before asking

• I searched in the issues and found nothing similar.

Fesod version

1.3.0

JDK version

21

Operating system

No response

Steps To Reproduce

读取 excel 时无法处理复杂表头，例如如下表头，无法有效读取数据

Current Behavior

1. 无法正确读取数据

Expected Behavior

1. 希望能正确读取数据

Anything else?

No response

Are you willing to submit a PR?

• I'm willing to submit a PR!

[delei]

Could you please provide a simple runnable sample code that contains the Excel sample file? This will help to better understand this issue.

[bengbengbalabalabeng]

可以通过 @ExcelProperty 注解中指定 index 属性来临时规避问题。下面是一个示例代码，供参考：

@EqualsAndHashCode(callSuper = false)
@Data
public class MultiHeadIndexBindingData implements Serializable {

    @ExcelProperty(index = 0)
    private Integer no;

    @ExcelProperty(index = 1)
    private String name;

    @ExcelProperty(index = 2, converter = StringBooleanConverter.class)
    private Boolean isCurrVillage;

    @ExcelProperty(index = 3)
    private String aframework;

    @ExcelProperty(index = 4)
    private String abrickConcrete;

    @ExcelProperty(index = 5)
    private String abrickWood;

    @ExcelProperty(index = 6)
    private String awood;

    @ExcelProperty(index = 7)
    private String aother;

    @ExcelProperty(index = 8)
    private String bframework;

    @ExcelProperty(index = 9)
    private String bbrickConcrete;

    @ExcelProperty(index = 10)
    private String bbrickWood;

    @ExcelProperty(index = 11)
    private String bwood;

    @ExcelProperty(index = 12)
    private String bother;

}

@Slf4j
public class MultiHeadBindingTest {

    @Test
    void testMultiHeadReadWithIndexBinding() {
        String path = TestFileUtil.getPath() + "demo" + File.separator + "multi-head-binding-test.xlsx";

        List<MultiHeadIndexBindingData> rows = FastExcel.read()
                .file(path)
                .head(MultiHeadIndexBindingData.class)
                .headRowNumber(3)
                .sheet()
                .doReadSync();

        log.info("\nrows\n: {}", JSON.toPrettyJSONString(rows));
    }

}

示例 Excel (AI Mock):

输出结果:

[{
	"no": 1,
	"name": "客厅装修",
	"isCurrVillage": true,
	"aframework": "12",
	"abrickConcrete": "0",
	"abrickWood": "0",
	"awood": "1",
	"aother": "5",
	"bframework": "2",
	"bbrickConcrete": "0",
	"bbrickWood": "0",
	"bwood": "0",
	"bother": "0"
}, ......]

---

问题代码复现:

@EqualsAndHashCode(callSuper = false)
@Data
public class MultiHeadValueBindingData implements Serializable {
    
    // 或者 @ExcelProperty(value = {"序号"})
    @ExcelProperty(value = {"序号", "序号", "序号"})
    private Integer no;

    // 或者 @ExcelProperty(value = {"项目名称"})
    @ExcelProperty(value = {"项目名称", "项目名称", "项目名称"})
    private String name;

    // 或者 @ExcelProperty(value = {"是否本村"}, converter = StringBooleanConverter.class)
    @ExcelProperty(value = {"是否本村", "是否本村", "是否本村"}, converter = StringBooleanConverter.class)
    private Boolean isCurrVillage;

    @ExcelProperty(value = {"xxxx", "结构", "框架"})
    private String aframework;

    @ExcelProperty(value = {"xxxx", "结构", "砖混"})
    private String abrickConcrete;

    @ExcelProperty(value = {"xxxx", "结构", "砖木"})
    private String abrickWood;

    @ExcelProperty(value = {"xxxx", "结构", "木"})
    private String awood;

    @ExcelProperty(value = {"xxxx", "结构", "其它"})
    private String aother;

    @ExcelProperty(value = {"xxxx", "实际用于经营", "框架"})
    private String bframework;

    @ExcelProperty(value = {"xxxx", "实际用于经营", "砖混"})
    private String bbrickConcrete;

    @ExcelProperty(value = {"xxxx", "实际用于经营", "砖木"})
    private String bbrickWood;

    @ExcelProperty(value = {"xxxx", "实际用于经营", "木"})
    private String bwood;

    @ExcelProperty(value = {"xxxx", "实际用于经营", "其它"})
    private String bother;
}

@Slf4j
public class MultiHeadBindingTest {

    @Test
    void testMultiHeadReadWithValueBinding() {
        String path = TestFileUtil.getPath() + "demo" + File.separator + "multi-head-binding-test.xlsx";

        List<MultiHeadValueBindingData> rows = FastExcel.read()
                .file(path)
                .head(MultiHeadValueBindingData.class)
                .headRowNumber(3)
                .sheet()
                .doReadSync();

        log.info("\nrows\n: {}", JSON.toPrettyJSONString(rows));
    }

}

输出结果:

[{
	"no": null,
	"name": null,
	"isCurrVillage": null,
	"aframework": null,
	"abrickConcrete": null,
	"abrickWood": null,
	"awood": null,
	"aother": null,
	"bframework": "12",
	"bbrickConcrete": "0",
	"bbrickWood": "0",
	"bwood": "1",
	"bother": "5"
}, ......]

初步分析:

headNameList.get(headNameList.size() - 1) 获取 headName 存在缺陷

1. 可能获取不到合并单元格的 headName 值（IS NULL）
2. headName 存在重复时，tmpHeadMap.put(...) 的逻辑会覆盖已存在的值

Head 绑定异常定位: cn.idev.excel.read.processor.DefaultAnalysisEventProcessor#buildHead

private void buildHead(AnalysisContext analysisContext, Map<Integer, ReadCellData<?>> cellDataMap) {
        // 省略......

        Map<Integer, String> dataMap = ConverterUtils.convertToStringMap(cellDataMap, analysisContext);
        ExcelReadHeadProperty excelHeadPropertyData =
                analysisContext.readSheetHolder().excelReadHeadProperty();
        Map<Integer, Head> headMapData = excelHeadPropertyData.getHeadMap();
        Map<Integer, Head> tmpHeadMap = new HashMap<Integer, Head>(headMapData.size() * 4 / 3 + 1);
        for (Map.Entry<Integer, Head> entry : headMapData.entrySet()) {
            Head headData = entry.getValue();

            // 存在 index 定义时直接 put 正常 continue
            if (headData.getForceIndex() || !headData.getForceName()) {
                tmpHeadMap.put(entry.getKey(), headData);
                continue;
            }
            List<String> headNameList = headData.getHeadNameList();
            // 1. 问题: headName IS NULL
            String headName = headNameList.get(headNameList.size() - 1);
            for (Map.Entry<Integer, String> stringEntry : dataMap.entrySet()) {
                if (stringEntry == null) {
                    continue;
                }
                String headString = stringEntry.getValue();
                Integer stringKey = stringEntry.getKey();
                if (StringUtils.isEmpty(headString)) {
                    continue;
                }
                if (analysisContext.currentReadHolder().globalConfiguration().getAutoTrim()) {
                    headString = headString.trim();
                }
                if (headName.equals(headString)) {
                    headData.setColumnIndex(stringKey);
                    // 2. 问题: 重复赋值
                    tmpHeadMap.put(stringKey, headData);
                    break;
                }
            }
        }
        excelHeadPropertyData.setHeadMap(tmpHeadMap);
    }

[github-actions]

This issue has been automatically marked as stale because it has not had recent activity for 30 days. It will be closed in next 7 days if no further activity occurs.

[github-actions]

This issue has been closed because it has not received response for too long time. You could reopen it if you encountered similar problems in the future.

[RoeGross]

+1 不想用index 如何解决

[bengbengbalabalabeng]

+1 不想用 index 如何解决

Hi, @RoeGross

如果你不希望依赖 index，结合你提供 #886 表头模式也可以通过 ReadListener 自行合并多行表头。不过这种方式我个人不太推荐：

• 灵活性有限
• 后期维护成本对比 index 更高

下面是一个简单示例：

Model

@EqualsAndHashCode
@Data
public static class ExcelModel {
    @ExcelProperty(value = "表头")
    private String field1;

    @ExcelProperty(value = "表头2")
    private String field2;
    
    @ExcelProperty(value = "表头3")
    private String field3;
    
    @ExcelProperty(value = "表头4")
    private String field4;
    
    @ExcelProperty(value = "表头5")
    private String field5;
    
    @ExcelProperty(value = "表头6")
    private String field6;
    
    @ExcelProperty(value = "表头7")
    private String field7;
}

Listener

public static class ExcelHeaderMerger implements ReadListener<Object> {

    private Map<Integer, ReadCellData<?>> tmp;

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        Integer rowIndex = context.readRowHolder().getRowIndex();
        if (rowIndex == 0) {
            tmp = new HashMap<>(headMap);
            tmp.remove(3);
        } else if (rowIndex == 1) {
            headMap.putAll(tmp);
            tmp = null;
        }
    }

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        // do nothing
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // do nothing
    }
}

Demo

List<ExcelModel> rows = FesodSheet.read(pathname)
                .head(ExcelModel.class)
                .registerReadListener(new ExcelHeaderMerger())
                .headRowNumber(2)
                .sheet()
                .doReadSync();