JAVA - 根据指定 excel 模板填充数据后导出

填充并导出的原模板如下

public void exportPublicity(HttpServletResponse response, HttpServletRequest request) throws Exception {
        // 模板地址
        String fileUrl = "J:\\评审意见填报导入模板.xlsx";
        File newFile = new File(fileUrl);
        // 文件存在则删除，保证文件的初始值
        if(newFile.exists()){
            newFile.delete();
        }

        String url = 文件的链接地址;
        // 拉取服务上的文件到本地
        FileUtil.downByFileLink(url, fileUrl, null);
        newFile = new File(fileUrl);

        InputStream is = null;
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        try {
            is = new FileInputStream(newFile);// 将excel文件转为输入流
            workbook = new XSSFWorkbook(is);// 创建个workbook，
            // 获取第一个sheet
            sheet = workbook.getSheetAt(0);
        } catch (Exception e1) {
            e1.printStackTrace();
        }

        if (sheet != null) {
            try {
                List<User>  list = 需要填充的数据;
                if (!CollectionUtils.isEmpty(list)) {
                    // 写数据
                    FileOutputStream fos = new FileOutputStream(newFile);
                    XSSFRow row = sheet.getRow(2);
                    if (row == null) {
                        row = sheet.createRow(2);
                    }
                    XSSFCell cell = row.getCell(0);
                    if (cell == null) {
                        cell = row.createCell(0);
                    }

                    // 处理身份证的格式，如不需要可删除
                    // 参考：https://blog.csdn.net/wuxin1210/article/details/121648974?spm=1001.2014.3001.5501
                    XSSFRow row2 = sheet.getRow(2); // 第二行
                    XSSFCell cell24 = row2.getCell(4); // 第四列 ，一起表示第二行的第四列的单元格
                    if (cell24 == null) {
                        cell24 = row2.createCell(4);
                    }
                    XSSFCellStyle cellStyle = cell24.getCellStyle();

                    for (int m = 0; m < list.size(); m++) {
                        User vo = list.get(m);
                        row = sheet.createRow((int) m + 2);
                        // 序号
                        cell = row.createCell(0);
                        cell.setCellValue(vo.getNo());
                        // 名称
                        cell = row.createCell(1);
                        cell.setCellValue(vo.getFullName());
                        // 地市
                        cell = row.createCell(2);
                        cell.setCellValue(vo.getCity());
                        // 区县
                        cell = row.createCell(3);
                        cell.setCellValue(vo.getArea());
                        // 身份证号
                        cell = row.createCell(4);
                        cell.setCellValue(vo.getIdCard());
                        cell.setCellStyle(cellStyle);
                    }
                    workbook.write(fos);
                    fos.flush();
                    fos.close();
                }

                // 下载
                InputStream fis = new BufferedInputStream(new FileInputStream(newFile));
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                response.reset();
                response.setContentType("text/html;charset=UTF-8");
                OutputStream toClient = new BufferedOutputStream(
                        response.getOutputStream());
                response.setContentType("application/x-msdownload");
                String newName = URLEncoder.encode("模板.xlsx", "UTF-8");
                response.addHeader("Content-Disposition", "attachment;filename=\"" + newName + "\"");
                response.addHeader("Content-Length", "" + newFile.length());
                toClient.write(buffer);
                toClient.flush();
            } catch (Exception e) {
                throw new MyFeignException("下载模板失败：" + e.getMessage());
            } finally {
                try {
                    if (null != is) {
                        is.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

FileUtil

package com.b2bwings.cqjy.common.util;

import lombok.extern.slf4j.Slf4j;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.Date;

@Slf4j
public class FileUtil {

    /**
     * 根据文件的链接来下载文件
     *
     * @param urlString https://video/static/20211020/d04ee724-3fdd-48fb-ba6c-bfe19708154a.mp4
     * @param filename  D:\wuxin\Desktop\other\test\ddd-48fb-ba6c-bfe19708154a.mp4
     * @param timeout   连接时长
     * @return
     */
    public static boolean downByFileLink(String urlString, String filename, Integer timeout) {
        log.info("下载视频开始时间 = " + DateUtil.getYYYYMMDDHHMMSS(new Date()));
        boolean ret = false;
        File file = new File(filename);
        try {
            if (file.exists()) {
                ret = true;
            } else {
                log.info("文件下载操作");
                // 构造URL
                URL url = new URL(urlString);
                // 打开连接
                HttpURLConnection con = (HttpURLConnection) url.openConnection();
                if (timeout != null) {
                    con.setConnectTimeout(timeout);
                    con.setReadTimeout(timeout);
                }
                con.connect();
                int contentLength = con.getContentLength();
                log.info("打印文件的长度" + contentLength);
                // 输入流
                InputStream is = con.getInputStream();
                // 1K的数据缓冲
                byte[] bs = new byte[1024];
                // 读取到的数据长度
                int len;
                // 输出的文件流

                File file2 = new File(file.getParent());
                file2.mkdirs();
                if (file.isDirectory()) {

                } else {
                    file.createNewFile();//创建文件
                }
                OutputStream os = new FileOutputStream(file);
                // 开始读取
                while ((len = is.read(bs)) != -1) {
                    os.write(bs, 0, len);
                }
                // 完毕，关闭所有链接
                os.close();
                is.close();
                if (contentLength != file.length()) {
                    file.delete();
                    ret = false;
                } else {
                    ret = true;
                }
            }
        } catch (IOException e) {
            file.delete();
            ret = false;
        } finally {
            log.info("下载视频结束时间 = " + DateUtil.getYYYYMMDDHHMMSS(new Date()));
            return ret;
        }
    }


}