EasyExcel自定义参数的读取导出

杳小鱼

已于 2024-09-09 17:43:40 修改

阅读量224

点赞数 1

文章标签： java excel

于 2024-08-30 17:53:25 首次发布

本文链接：https://blog.csdn.net/qq_51472364/article/details/141724025

版权

EasyExcel自定义参数的读取导出

1、导出

	ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder();
	// 写出额外参数的Handler
    excelWriterBuilder.registerWriteHandler(new SheetWriteHandler() {
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            Workbook workbook = writeWorkbookHolder.getWorkbook();
            // extraProp：额外参数Map
            for (Map.Entry<String, String> entry : extraProp.entrySet()) {
                if (workbook instanceof XSSFWorkbook){
                    // .xlsx设置文件属性
                    POIXMLProperties.CustomProperties customProperties = ((XSSFWorkbook) workbook).getProperties().getCustomProperties();
                    if (ExcelUtils.hasNoProperty(customProperties, entry.getKey())){
                        customProperties.addProperty(entry.getKey(), entry.getValue());
                    }
                } else if (workbook instanceof SXSSFWorkbook){
                    // .xlsx设置文件属性
                    POIXMLProperties.CustomProperties customProperties = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getProperties().getCustomProperties();
                    if (ExcelUtils.hasNoProperty(customProperties, entry.getKey())) {
                        customProperties.addProperty(entry.getKey(), entry.getValue());
                    }
                } else if (workbook instanceof HSSFWorkbook){
                    // .xls设置文件属性
                    HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
                    hssfWorkbook.createInformationProperties();
                    DocumentSummaryInformation documentSummaryInformation = hssfWorkbook.getDocumentSummaryInformation();
                    CustomProperties customProperties = documentSummaryInformation.getCustomProperties();
                    if (Objects.isNull(customProperties)){
                        customProperties = new CustomProperties();
                    }
                    if (!ExcelUtils.hasProperty(customProperties, entry.getKey())) {
                        //添加自定义属性
                        customProperties.put(entry.getKey(), entry.getValue());
                        documentSummaryInformation.setCustomProperties(customProperties);
                    }
                }
            }
        }
    });

2、读取

Map<String, Object> extraProp = new HashMap<>();
try {
    // context: com.alibaba.excel.context.AnalysisContext
    ReadWorkbookHolder readWorkbookHolder = context.readWorkbookHolder();
    if (readWorkbookHolder instanceof XlsxReadWorkbookHolder) {
        // 获取.xlsx文件额外属性
        OPCPackage opcPackage = ((XlsxReadWorkbookHolder) readWorkbookHolder).getOpcPackage();
        POIXMLProperties poiProperties = new POIXMLProperties(opcPackage);
        POIXMLProperties.CustomProperties customProperties = poiProperties.getCustomProperties();
        List<CTProperty> propertyList = customProperties.getUnderlyingProperties().getPropertyList();
        for (CTProperty property : propertyList) {
            extraProp.put(property.getName(), property.getLpwstr());
        }
    } else if (readWorkbookHolder instanceof XlsReadWorkbookHolder){
        // 获取.xls文件额外属性
        DocumentSummaryInformation documentSummaryInformation = ((XlsReadWorkbookHolder) readWorkbookHolder).getHssfWorkbook().getDocumentSummaryInformation();
        CustomProperties customProperties = documentSummaryInformation.getCustomProperties();
        List<CustomProperty> propertyList = customProperties.properties();
        for (CustomProperty property : propertyList) {
            extraProp.put(property.getName(), property.getValue());
        }
    }
	// 读取的结果存储在extraProp中
} catch (XmlException | OpenXML4JException | IOException e) {
    throw new RuntimeException("解析Excel额外属性异常");
}