java-Excel的导入导出

java-Excel的导入导出

更新历史

名称	内容
版本	1.0
创建时间	2021-10-14
更新时间	2021-10-19

功能清单

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

maven集成

<properties>
    <easy.poi.version>4.4.0</easy.poi.version>
</properties>

<dependencies>
    <!--easy-poi excel start-->
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-spring-boot-starter</artifactId>
        <version>${easy.poi.version}</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>${easy.poi.version}</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>${easy.poi.version}</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>${easy.poi.version}</version>
    </dependency>
    <!--easy-poi excel end-->
</dependencies>

开发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<DataDictFiledConfig.FiledConfig> 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);
    }
}

/**
 * <pre>
 * 浏览器下载文件时需要在服务端给出下载的文件名，当文件名是ASCII字符时没有问题
 * 当文件名有非ASCII字符时就有可能出现乱码
 *
 * 这里的实现方式参考这篇文章
 * http://blog.robotshell.org/2012/deal-with-http-header-encoding-for-file-download/
 *
 * 最终设置的response header是这样:
 *
 * Content-Disposition: attachment;
 *                      filename="encoded_text";
 *                      filename*=utf-8''encoded_text
 *
 * 其中encoded_text是经过RFC 3986的“百分号URL编码”规则处理过的文件名
 * </pre>
 *
 * @param response
 * @param filename
 * @return
 */
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");

}

/**
 * <pre>
 * 符合 RFC 3986 标准的“百分号URL编码”
 * 在这个方法里，空格会被编码成%20，而不是+
 * 和浏览器的encodeURIComponent行为一致
 * </pre>
 *
 * @param value
 * @return
 */
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<String> 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<Map<String, Object>> excelList;
    try {
        excelList = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, params);
    } catch (Exception e) {
        throw new DirtyException("excel解析错误");
    }
    // TODO 校验excel模板版本

    ExcelImportResultVO result = new ExcelImportResultVO();

    // 数据整理
    List<DataDictValue> importList = excelList.stream().map(map -> {
        DataDictValue value = new DataDictValue();
        Map<String, Object> 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<ExcelImportResultVO> excelImport(@RequestParam("file") MultipartFile file, @RequestParam("dataDictId") Long dataDictId) {
    ExcelImportResultVO result = dataDictValueBatchBiz.batchImport(file, dataDictId);
    return RestObjectResponse.ok(result);
}