java-Excel的导入导出

java-Excel的导入导出 java-Excel的导入导出 更新历史 名称内容版本1.0创建时间2021-10-14更新时间2021-10-19 功能清单

• 基于maven从零集成Excel功能
• maven集成
• Excel导入
• Excel导出
• vue-Excel导入对话框模板
• axios-文件下载(使用blob下转换)

maven集成

    4.4.0



    
    
        cn.afterturn
        easypoi-spring-boot-starter
        ${easy.poi.version}
    
    
        cn.afterturn
        easypoi-base
        ${easy.poi.version}
    
    
        cn.afterturn
        easypoi-web
        ${easy.poi.version}
    
    
        cn.afterturn
        easypoi-annotation
        ${easy.poi.version}
    
    

开发Excel流程

1. 下载Excel导入模板
2. Excel导入
3. Excel导出

概述

• 本文只用Excel模板的方式导出Excel，因为用注解或者代码配置Excel样式实在是太丑了

下载Excel导入模板 resouce文件转file

为了部署方便，一般会将这些模板文件直接放在jar包中，但是jar只能读取InputStream，所以就需要一个工具类来转换一下

java-jar包resource转文件

新建Excel处理Biz

我一般会吧excel的 *** 作类新建一个batch文件夹，然后在按照biz的方式新建，这样代码可以比较简洁

定义Biz接口

public interface DataDictValueBatchBiz {
    void downloadimportTemplate(HttpServletResponse response, Long dataDictId);

    ExcelimportResultVO batchimport(MultipartFile file, Long dataDictId);
}

实现下载Excel-Biz方法

@Override
public void downloadimportTemplate(HttpServletResponse response, Long dataDictId) {
    DataDict dataDict = dataDictBiz.getById(dataDictId);
    if (null == dataDict) {
        throw new DirtyException("xx信息不存在");
    }
    setFileDownloadHeader(response, String.format("%s-导入模板.xlsx", dataDict.getInfo()));
    TemplateExportParams params = new TemplateExportParams(JarResourceTool.fileByResource("excel-template/xxx.xlsx").getAbsolutePath());

    DataDictExcelTemplateExportDTO data = new DataDictExcelTemplateExportDTO();
    data.setDate(DateUtil.formatDateTime(new Date()));
    data.setVersion("1.0");
    data.setName(dataDict.getName());
    // 开启列遍历
    params.setColForEach(true);
    List fields = new ArrayList<>();
    // 动态列
    data.setColList(fields);
    if (null != dataDict.getFieldConfig()) {
        fields.addAll(dataDict.getFieldConfig().getList().stream().filter(DataDictFiledConfig.FiledConfig::isHasExcel).collect(Collectors.toList()));
    }

    Workbook workbook = ExcelExportUtil.exportExcel(params, BeanUtil.beanToMap(data));
    try {
        workbook.write(response.getOutputStream());
    } catch (IOException e) {
        log.error("teacher import template error", e);
    }
}


public static void setFileDownloadHeader(HttpServletResponse response, String filename) {
    String headerValue = "attachment;";
    headerValue += " filename="" + encodeURIComponent(filename) + "";";
    headerValue += " filename*=utf-8''" + encodeURIComponent(filename);
    response.setHeader("Content-Disposition", headerValue);
    //expose header
    response.setHeader("Access-Control-Expose-Headers", "Content-Disposition,Content-Type");
    response.setHeader("Content-Type", "application/octet-stream");

}


public static String encodeURIComponent(String value) {
    try {
        return URLEncoder.encode(value, "UTF-8").replaceAll("\+", "%20");
    } catch (UnsupportedEncodingException e) {
        e.printStackTrace();
    }
    return null;
}

Controller层

@PostMapping(value = "/downloadimportTemplate")
@ApiOperation("下载导入模板")
public void downloadimportTemplate(HttpServletResponse response, @RequestParam("dataDictId") Long dataDictId) {
    dataDictValueBatchBiz.downloadimportTemplate(response, dataDictId);
}

Excel导入 定义Excel导入结果类

@Getter
@Setter
public class ExcelimportResultVO implements Serializable {
    private Integer successCount;
    private Integer errorCount;
    private Integer total;
    private String remark;
}

实现batchimport方法

@Override
public ExcelimportResultVO batchimport(MultipartFile file, Long dataDictId) {
    DataDict dataDict = dataDictBiz.getById(dataDictId);
    if (null == dataDict) {
        throw new DirtyException("信息不存在");
    }
    if (null == dataDict.getFieldConfig() || null == dataDict.getFieldConfig().getList()) {
        throw new DirtyException("字段信息不存在");
    }
    List importsField = dataDict.getFieldConfig().getList()
            .stream()
            .filter(DataDictFiledConfig.FiledConfig::isHasExcel)
            .map(DataDictFiledConfig.FiledConfig::getName)
            .collect(Collectors.toList());
    if (CollectionUtils.isEmpty(importsField)) {
        throw new DirtyException("字典导出字段信息不存在");
    }
    String[] importsFieldArr = importsField.toArray(new String[0]);
    checkFileSize(file, FILE_MAX);
    checkFileFormat(file, EXCEL_ALLOW_FORMAT);
    importParams params = new importParams();
    params.setTitleRows(1);
    params.setHeadRows(1);
    params.setReadRows(100000);
    params.setimportFields(importsFieldArr);
    List> excelList;
    try {
        excelList = ExcelimportUtil.importExcel(file.getInputStream(), Map.class, params);
    } catch (Exception e) {
        throw new DirtyException("excel解析错误");
    }
    // TODO 校验excel模板版本

    ExcelimportResultVO result = new ExcelimportResultVO();

    // 数据整理
    List importList = excelList.stream().map(map -> {
        DataDictValue value = new DataDictValue();
        Map json = new HashMap<>();
        value.setDataDictId(dataDictId);

        // 第一个有效字段必须有值
        if (!map.containsKey(importsFieldArr[0])
                || null == map.get(importsFieldArr[0])
                || StringUtils.isBlank(String.valueOf(map.get(importsFieldArr[0])))) {
            // 没有必填数据
            return null;
        }
        boolean hasValidate = true;
        for (DataDictFiledConfig.FiledConfig filedConfig : dataDict.getFieldConfig().getList()) {
            if (!filedConfig.isHasExcel()) {
                // 未开启Excel导入导出
                continue;
            }
            if (map.containsKey(filedConfig.getName())) {

                Object item = map.get(filedConfig.getName());
                if (null == item) {
                    map.remove(filedConfig.getName());
                } else {
                    String str = StrUtil.trim(String.valueOf(item));
                    if (str.length() == 0) {
                        map.remove(filedConfig.getName());
                    }
                    item = str;
                    map.put(filedConfig.getName(), String.valueOf(item));
                }

            }

            if (filedConfig.isHasRequire()) {
                if (!map.containsKey(filedConfig.getName())) {
                    // 没有必填数据
                    return null;
                }
            }

            json.put(filedConfig.getId(), map.get(filedConfig.getName()));
        }

        value.setDataValueObj(JSON.toJSONString(json));
        return value;
    }).filter(Objects::nonNull).collect(Collectors.toList());
    if (importList.size() > 0) {
        dataDictValueBiz.saveBatch(importList);
    }
    result.setTotal(importList.size());
    result.setSuccessCount(importList.size());
    result.setErrorCount(0);
    return result;
}
private void checkFileFormat(MultipartFile file, String formats) {
    String[] listFormat = formats.split(FILE_FORMAT_SPLIT);
    for (String allow : listFormat) {
        if (Objects.equals(FileNameUtil.extName(file.getOriginalFilename()), allow)) {
            return;
        }
    }
    throw new DirtyException(String.format("文件类型只支持:%s", formats));
}

private void checkFileSize(MultipartFile file, int size) {
    if (file.getSize() > size) {
        throw new DirtyException(String.format("文件大小不能超过:%sMB", size / 1024 / 1024));
    }
}

Controller层

@PostMapping(value = "/excelimport")
public RestObjectResponse excelimport(@RequestParam("file") MultipartFile file, @RequestParam("dataDictId") Long dataDictId) {
    ExcelimportResultVO result = dataDictValueBatchBiz.batchimport(file, dataDictId);
    return RestObjectResponse.ok(result);
}