导入导出（excel格式/压缩包格式导出至response、excel导入）

---

导出

一、excel格式导出至response

1、List<对象>导出

Map<String, String> headerAlias = new LinkedHashMap<String, String>();
headerAlias.put("数据列一对象英文名称", "显示的中文名称");
... // 

BigExcelWriter excelWriter = new BigExcelWriter();
excelWriter.setHeaderAlias(headerAlias);
excelWriter.write(list); //  List<对象> list 是数据
if (null != response) {
    // 下边两行任取一行都可以
    response.addHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");
    // response.addHeader("Content-Type", "application/octet-stream");
    response.addHeader("Content-Disposition", excelWriter.getDisposition("excel表名", StandardCharsets.UTF_8));
}
excelWriter.flush(response.getOutputStream(), true);
excelWriter.close();

2、json对象不固定列导出

// swagger无法测试
HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse(); // 获取response对象

List<List<String>> resultListList = new ArrayList<>(); // 用于模拟excel
List<String> resultHeadList = new ArrayList<>(); // excel表头
resultHeadList.add("NAME");
... // 这里是固定的确定的列

for (int j = 0; j < jsonObj.size(); j++) {
    JSONArray jsonArray = jsonObj.getJSONArray("num" + j);
    List<String> resultList = new ArrayList<>(); // 模拟excel一行
    if (j == 0) { // 第一行
        resultList.add("固定列的相同取值");
        ... // 固定列处理
    } else { // 其余行
        resultList.add("");
        ... // 固定列处理
    }
    for (int i = 0; i < jsonArray.size(); i++) {
        JSONObject obj = jsonArray.getJSONObject(i);
        String name = StrUtil.trimToEmpty(obj.getStr("name"));
        String value = StrUtil.trimToEmpty(obj.getStr("value"));
        if (j == 0) {
            resultHeadList.add(name); // 表头增加相应名称
        }
        resultList.add(value);
    }
    resultListList.add(resultList);
}
resultListList.add(0, resultHeadList);
BigExcelWriter excelWriter = new BigExcelWriter();
excelWriter.write(resultListList);
if (null != response) {
    // 下边两行任取一行都可以
    response.addHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");
    // response.addHeader("Content-Type", "application/octet-stream");
    response.addHeader("Content-Disposition", excelWriter.getDisposition("excel表名", StandardCharsets.UTF_8));
}
excelWriter.flush(response.getOutputStream(), true);
excelWriter.close();

二、压缩包格式导出至response

// 多个数据逗号分隔 String ->String[] 正则表达式：[,;，；：\\s]+
String[] fileUrlArray = Arrays.stream(StrUtil.trimToEmpty(request.getParameter("fileUrl")).replaceAll([,;，；：\\s]+, ",").replaceAll("^,|,$", "").split(","))
    .filter(StrUtil::isNotBlank).distinct().toArray(String[]::new);
// String[] 转换成 List 也仅仅用于 fileUrlSuffixMap 中快捷获得某一元素的下标 fileUrlList.indexOf(m)
List<String> fileUrlList = new ArrayList<>(fileUrlArray.length);
Collections.addAll(fileUrlList, fileUrlArray);

List<String> suffixList = Arrays.stream(fileUrlArray).map(m -> m.substring(m.lastIndexOf("."))).collect(Collectors.toList()); // 图片后缀名
Map<String, String> fileUrlSuffixMap = Arrays.stream(fileUrlArray).collect(Collectors.toMap(m -> m, m -> suffixList.get(fileUrlList.indexOf(m)))); // 图片url和后缀名map

String sku = StrUtil.trimToEmpty(request.getParameter("sku")); // 仅仅用于压缩包命名
String rootPath = request.getRealPath("/");
File file = new File(rootPath + "temp_download");
if (!file.exists()) {
    file.mkdir();
}
Arrays.stream(fileUrlArray).forEach(m -> HttpUtil.downloadFile(m,FileUtil.file(new File(rootPath + "temp_download" + 
                                                                                        File.separator + m.substring(m.lastIndexOf("/") + 1, m.lastIndexOf(".")) + fileUrlSuffixMap.get(m)))));

try {
    File zipFile = null;
    zipFile = new File(rootPath + "temp_download" + "/" + sku + ".zip");
    FileOutputStream zipFos = new FileOutputStream(zipFile);
    ArchiveOutputStream archOut = new ArchiveStreamFactory().createArchiveOutputStream(ArchiveStreamFactory.ZIP, zipFos);
    ZipArchiveOutputStream zos = (ZipArchiveOutputStream) archOut;
    for (int i = 0; i < fileUrlArray.length; i++) {
        String ele = fileUrlArray[i];
        File imageFile = new File(rootPath + "temp_download" + "/" + ele.substring(ele.lastIndexOf("/") + 1, ele.lastIndexOf(".")) + suffixList.get(i));
        ZipArchiveEntry zipEntry = new ZipArchiveEntry(imageFile, imageFile.getName());
        zos.putArchiveEntry(zipEntry);
        zos.write(FileUtils.readFileToByteArray(imageFile));
        if (imageFile.exists()) {
            imageFile.delete();
        }
    }
    zos.closeArchiveEntry();
    zos.flush();
    zos.close();
    OutputStream out = null;
    out = response.getOutputStream();
    response.reset();
    response.setHeader("Content-Disposition", "attachment;filename=" + new String((sku + ".zip").getBytes("GB2312"), "ISO-8859-1"));
    response.setContentType("application/octet-stream; charset=utf-8");
    response.setCharacterEncoding("UTF-8");
    out.write(FileUtils.readFileToByteArray(zipFile));
    out.flush();
    out.close();
    if (zipFile.exists()) {
        zipFile.delete();
    }
    if (file.exists()) {
        file.delete();
    }
} catch (Exception e) {
    e.printStackTrace();
}

使用Hutool工具简化代码

// 输出到内存里面
String[] fileArray = fileStr.split(",");
try {
    AtomicInteger atomicAdd = new AtomicInteger(); // 文件大小累加器
    List<String> fileName = CollUtil.list(false); // 文件名
    InputStream[] ins = Arrays.stream(fileArray).map(url -> {
        byte[] bytes = HttpUtil.downloadBytes(url); // 上传到内存中
        fileName.add(url.substring(url.lastIndexOf("/") + 1)); // 文件名
        atomicAdd.addAndGet(bytes.length);
        return new ByteArrayInputStream(bytes);
    }).toArray(InputStream[]::new);

    ByteArrayOutputStream byteStream = new ByteArrayOutputStream(atomicAdd.intValue());
    ZipUtil.zip(byteStream, fileName.toArray(new String[0]), ins); // 参数：目标流、流数据在压缩文件中的路径或文件名、要压缩的
    response.setHeader("Content-Disposition", "attachment;filename=" + sku + ".zip");
    response.setContentType("application/octet-stream; charset=utf-8");

    OutputStream out = response.getOutputStream();
    out.write(byteStream.toByteArray());
    out.flush();
    out.close();
} catch (Exception e) {
    e.printStackTrace();
}

导入

一、对象导入

excelList = excelReader.readAll(....class);

二、不固定列导入

if (file.isEmpty()) {
    return failure("文件为空");
}
InputStream inputStream = null;
Boolean result = false; // 修改成功标志
List<Map<String,Object>> excelList = new ArrayList<>();
Obj obj = new Obj(); // excel对应的对象，其中一个字段存json数据
try {
    inputStream = file.getInputStream();
    ExcelReader excelReader = ExcelUtil.getReader(inputStream);
    // 检验固定列是否正确
    if (!"NAME".equals(excelReader.readRow(0).get(0))) {
        return ...
    } 
    // else if ... 其余固定列的检验
    
    // 读取为Map列表，默认第一行为标题行，Map中的key为标题，value为标题对应的单元格值。
    excelList = excelReader.readAll();
    // 两种方式
    // excelReader.readRow(0).get(0)、excelList.get(0).get("NAME")
    obj.setName(excelList.get(0).get("NAME").toString());
    JSONObject jsonObj = new JSONObject();
    for (int i = 0; i < excelList.size(); i++) {
        JSONArray jsonArray = new JSONArray();
        Map<String, Object> oneObjMap = excelList.get(i);
        for (Map.Entry<String, Object> entry : oneObjMap.entrySet()) {
            JSONObject jsonObject = new JSONObject();
            String key = entry.getKey();
            Object value = entry.getValue();
            if ("NAME".equals(key) || ...) { // 遇到固定列则跳过
                continue;
            }
            jsonObject.set("name", key);
            jsonObject.set("value", value);
            jsonArray.add(jsonObject);
        }
        jsonObj.set("num" + i, jsonArray);
    }
    obj.setJsonObj(jsonObj.toString());
} catch (Exception e) {
    e.printStackTrace();
}