java实现赋值Excel模板并写入后导出

引入pom
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.16</version>
</dependency>


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;

@RestController
public class testController {
    @GetMapping("excel")
    public void excel(HttpServletResponse response) throws Exception {
        exportExcel(response);
    }

    //目前实现的替换为sheet1的B5-L5数据替换为1000以内的随机数
    public void exportExcel(HttpServletResponse response) {

        //获取模板路径
        String filePath = new File("").getAbsolutePath();
        String path = filePath + "\\upload\\原始模板.xls";

        //获取新模板的新路径
        String fileName = new File(path).getName();
        fileName = fileName.substring(0, fileName.lastIndexOf(".") - 2);
        String newFilePath = filePath + "\\upload\\" + fileName + System.currentTimeMillis() + ".xlsx";

        //复制文件
        File newFile = copyFile(path, newFilePath);

        //替换数据
        replaceDate(newFile);

        // 下载文件
        downLoadFile(response, newFile);
    }

    public void replaceDate(File newFile) {
        InputStream is = null;
        try {
            is = new FileInputStream(newFile);// 将excel文件转为输入流
            HSSFWorkbook workbook = new HSSFWorkbook(is);// 获取workbook，
            HSSFSheet sheet = workbook.getSheetAt(0);// 获取第一个sheet

            //替换数据 替换第5行第二列到第十四列的元素
            if (sheet == null)
                return;
            FileOutputStream fos = new FileOutputStream(newFile);
            HSSFRow row = sheet.getRow(4);
            if (row == null)
                return;
            for (int i = 1; i < 13; i++) {
                HSSFCell cell = row.getCell(i);
                int value = (int) (Math.random() * 1000);
                cell.setCellValue("" + value);
            }
            workbook.write(fos);
            fos.flush();
            fos.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (newFile.exists())
                    newFile.delete();
                if (null != is) {
                    is.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public void downLoadFile(HttpServletResponse response, File file) {
        try {
            InputStream fis = new BufferedInputStream(new FileInputStream(
                    file));
            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(
                    file.getName(),
                    "UTF-8");
            response.addHeader("Content-Disposition",
                    "attachment;filename=\"" + newName + "\"");
            response.addHeader("Content-Length", "" + file.length());
            toClient.write(buffer);
            toClient.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 复制文件
     *
     * @param s 源文件
     * @param t 复制到的新文件
     */
    public void fileChannelCopy(File s, File t) {
        try {
            InputStream in = null;
            OutputStream out = null;
            try {
                in = new BufferedInputStream(new FileInputStream(s), 1024);
                out = new BufferedOutputStream(new FileOutputStream(t), 1024);
                byte[] buffer = new byte[1024];
                int len;
                while ((len = in.read(buffer)) != -1) {
                    out.write(buffer, 0, len);
                }
            } finally {
                if (null != in) {
                    in.close();
                }
                if (null != out) {
                    out.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取excel模板，并复制到新文件中供写入和下载
     *
     * @return
     */
    public File copyFile(String path, String newFilePath) {
        // 读取模板，并赋值到新文件************************************************************
        // 文件模板路径

        File file = new File(path);
        File newFile = new File(newFilePath);
        try {
            newFile.createNewFile();
            fileChannelCopy(file, newFile);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return newFile;
    }

}