easyexcel导入导出多个sheet数据，支持国际化表头

当年明月思古今

于 2024-09-19 11:25:36 发布

阅读量357

点赞数 2

本文链接：https://blog.csdn.net/2301_78101515/article/details/142355432

版权

	@PostMapping("/import")
	public R importModelConfig(@RequestPart("file") MultipartFile file) {
		return R.ok(importModelConfig(file));
	}
    private Boolean importModelConfig(MultipartFile file) {

        if (file.isEmpty()) {
            log.warn("Uploaded file is empty");
            return false;
        }

        try {
            convertTableNames(WebUtils.getLanguage());

            List<ModelConfigInfoVo> infoList = readSheet(file, 0, ModelConfigInfoVo.class);
            List<ModelConfigParamVo> paramList = readSheet(file, 1, ModelConfigParamVo.class);
            List<ModelConfigDataVo> dataList = readSheet(file, 2, ModelConfigDataVo.class);
            List<ModelConfigStatusVo> statusList = readSheet(file, 3, ModelConfigStatusVo.class);
            List<ModelConfigCommandVo> commandList = readSheet(file, 4, ModelConfigCommandVo.class);

            // TODO: Uncomment and implement saveData method
            // saveData(infoList, paramList, dataList, statusList, commandList);

            return true;
        } catch (IOException e) {
            log.error("Error occurred during import", e);
            return false;
        }
    }
    private <T> List<T> readSheet(MultipartFile file, int sheetNo, Class<T> clazz) throws IOException {
        List<T> resultList = new ArrayList<>();
        EasyExcel.read(file.getInputStream(), clazz, new SimpleReadListener<>(resultList))
                .sheet(sheetNo)
                .doRead();
        return resultList;
    }
    private void convertTableNames(String language) {
        List<Class<?>> classesToConvert = Arrays.asList(
                ModelConfigInfoVo.class,
                ModelConfigInfoModelVo.class,
                ModelConfigParamVo.class,
                ModelConfigDataVo.class,
                ModelConfigStatusVo.class,
                ModelConfigCommandVo.class
        );

        classesToConvert.forEach(clazz -> ExcelUtil.convertTableName(createInstance(clazz), language));
    }

	@GetMapping("/export}")
	public void exportModelConfig(HttpServletResponse response) {
		exportModelConfig(response, modelConfigId);
	}
    public void exportModelConfig(HttpServletResponse response, Long modelConfigId) {
        String language = WebUtils.getLanguage();
        ModelConfig modelConfig = this.getInfoById(modelConfigId, language);

        LinkedHashMap<String, SimpleEntry<List<?>, Class<?>>> detailLists = createDetailLists(modelConfig);

        convertTableNames(language);

        exportToExcel(response, detailLists);
    }
    private LinkedHashMap<String, SimpleEntry<List<?>, Class<?>>> createDetailLists(ModelConfig modelConfig) {
        LinkedHashMap<String, SimpleEntry<List<?>, Class<?>>> detailLists = new LinkedHashMap<>();

        // Add base info
        Class<?> baseInfoClass = modelConfig.getIsTemplate() ? ModelConfigInfoVo.class : ModelConfigInfoModelVo.class;
        detailLists.put("Base info", new SimpleEntry<>(List.of(convertToVo(modelConfig, baseInfoClass)), baseInfoClass));

        // Add other details
        addDetail(detailLists, "Base parameter", modelConfig.getTechnicalDetailList(), ModelConfigParamVo.class);
        addDetail(detailLists, "Data", modelConfig.getDataDetailList(), ModelConfigDataVo.class);
        addDetail(detailLists, "Status", modelConfig.getStatusDetailList(), ModelConfigStatusVo.class);
        addDetail(detailLists, "Instruct", modelConfig.getCommandDetailList(), ModelConfigCommandVo.class);

        return detailLists;
    }

    private void exportToExcel(HttpServletResponse response, Map<String, SimpleEntry<List<?>, Class<?>>> detailLists) {
        try {
            setResponseHeaders(response);
            try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {
                // Write sheets
                int sheetIndex = 0;
                for (Map.Entry<String, SimpleEntry<List<?>, Class<?>>> entry : detailLists.entrySet()) {
                    List<?> data = entry.getValue().getKey();
                    Class<?> clazz = entry.getValue().getValue();
                    WriteSheet sheet = EasyExcel.writerSheet(sheetIndex++, entry.getKey()).head(clazz).build();
                    excelWriter.write(data, sheet);
                }
            }
        } catch (IOException e) {
            throw new AuroraException(MsgUtils.getMessage(ErrorCodes.EXPORT_FAILURE));
        }
    }

public class ExcelUtil {


	/**
	 * 转换表头
	 *
	 * @param t
	 * @param language
	 */
	public synchronized static void convertTableName(Object t, String language) {
		Field[] fields = t.getClass().getDeclaredFields();
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelProperty.class) && field.isAnnotationPresent(ExcelTableName.class)) {
				try {
					field.setAccessible(true);

					ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
					ExcelTableName excelTableName = field.getAnnotation(ExcelTableName.class);

					InvocationHandler tableNameH = Proxy.getInvocationHandler(excelTableName);
					InvocationHandler excelH = Proxy.getInvocationHandler(excelProperty);

					Field tableNameF = tableNameH.getClass().getDeclaredField("memberValues");
					Field excelF = excelH.getClass().getDeclaredField("memberValues");

					tableNameF.setAccessible(true);
					excelF.setAccessible(true);

					Map tableNameValues = (Map) tableNameF.get(tableNameH);
					Map excelValues = (Map) excelF.get(excelH);

					String[] value = LanguageUtils.handleLanguage(language, tableNameValues, excelValues);
					excelValues.put("value", value);
				} catch (NoSuchFieldException | IllegalAccessException e) {
					throw new RuntimeException(e);
				}
			}
		}
	}
}

@Data
@ColumnWidth(30)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)
public class ModelConfigInfoVo {
    @ExcelProperty("设备类型")
    @ExcelTableName(name_en = "Device category", name_cn = "设备类型", name_sv = "Enhetskategori")
    private String deviceType;

    @ExcelProperty("设备性质")
    @ExcelTableName(name_en = "Device nature", name_cn = "设备性质", name_sv = "Enhetens beskaffenhet")
    private String deviceNature;

    @ExcelProperty("是否可控")
    @ExcelTableName(name_en = "Is controllable", name_cn = "是否可控", name_sv = "Är kontrollerbar")
    private Boolean isController;
}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelTableName {

	String name_cn() default "";
	String name_en() default "";
	String name_sv() default "";
}

备注：jvm需要加 --add-opens java.base/sun.reflect.annotation=ALL-UNNAMED