excel模板下载,替换指定内容

1、引入poi依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>2、

2、自定义相关实体

import io.swagger.annotations.ApiModel;
import lombok.Data;

@Data
@ApiModel(value = " Excel替换内容存储对象")
public class ExcelReplaceDataVO {

    private int row;// Excel单元格行

    private int column;// Excel单元格列

    private String key;// 替换的关键字

    private String value;// 替换的文本
}

3、编写工具类

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.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import tongtu.cn.excel.vo.ExcelReplaceDataVO;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;

public class ExcelReplaceUtils {
    /**
     * 替换Excel模板文件内容
     *
     * @param date           文档数据
     * @param sourceFilePath Excel模板文件路径
     */
    public static HSSFWorkbook replaceModel(List<ExcelReplaceDataVO> date, String sourceFilePath) throws IOException {
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(sourceFilePath));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        try {
            HSSFSheet sheet = wb.getSheetAt(0);
            for (ExcelReplaceDataVO data : date) {
                //获取单元格内容
                HSSFRow row = sheet.getRow(data.getRow());
                HSSFCell cell = row.getCell((short) data.getColumn());
                String str = cell.getStringCellValue();
                //替换单元格内容
                str = str.replace(data.getKey(), data.getValue());
                //写入单元格内容
                cell.setCellType(CellType.STRING);
                cell.setCellValue(str);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return wb;
    }
}

4、接口调用

@PostMapping(path = "/exportExcelTemplate")
@ApiOperation(value = "excel模板下载,替换指定内容", notes = "excel模板下载,替换指定内容")
public void exportExcelTemplate(HttpServletResponse response, @RequestParam String name) throws IOException {
    List<ExcelReplaceDataVO> date = new ArrayList<>();
    ExcelReplaceDataVO title = new ExcelReplaceDataVO();
    title.setRow(0);
    title.setColumn(0);
    title.setKey("title");
    title.setValue(name + "养护工程质量评分");
    ExcelReplaceDataVO xmMc = new ExcelReplaceDataVO();
    xmMc.setRow(2);
    xmMc.setColumn(0);
    xmMc.setKey("name");
    xmMc.setValue(name);
    date.add(title);
    date.add(xmMc);
    String path = this.getClass().getClassLoader().getResource("excel/替换.xls").getPath();
    path = URLDecoder.decode(path, "UTF-8");
    HSSFWorkbook workbook = ExcelReplaceUtils.replaceModel(date, path);
    String filename = DateUtil.format(new Date(), "yyyy-MM-dd") + name + "excel模板下载,替换指定内容.xls";
    response.addHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
    response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
    workbook.write(response.getOutputStream());
}
参考：https://www.iteye.com/blog/yaoh6688-1152273