XSSFWorkbook读取模板，批量填充并导出文件

1、pom文件导入

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.0</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.0</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>4.1.0</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.0</version>
</dependency>

2、读取模板并导出

@Datapublic static final class Example {private List<ExampleChild> child;private String orderNum;private String sortNum;private String barCode;}@Datapublic static final class ExampleChild {private String length;private String width;private String high;}//一个Example就是一个sheetprivate void handleData(HttpServletResponse response, List<Example> list) throws IOException {//读取模板String lastSheetName = list.get(0).getBarCode();//模板放在项目的 resource/templates/example.xlsxXSSFWorkbook workbook = new XSSFWorkbook(new ClassPathResource("templates/example.xlsx").getInputStream());//修改模板第一个sheet名称workbook.setSheetName(0, lastSheetName);//一个list就是一个sheetfor (int i = 1; i < list.size(); i++) {if(!lastSheetName.equals(list.get(i).getBarCode())){workbook.cloneSheet(0, list.get(i).getBarCode());lastSheetName = list.get(i).getBarCode();}else{workbook.cloneSheet(i-1);}}//模板转成存本地的临时文件String userHome = System.getProperties().getProperty("user.home") + "/example.xlsx";FileOutputStream fos = new FileOutputStream(userHome);workbook.write(fos);// 加载导出模板，excel模板的项目中路径ExcelTemplate excel = new ExcelTemplate(userHome);if (!excel.examine()) {return;}for (int l = 0; l < list.size(); l++) {LinkedHashMap<Integer, LinkedList<String>> rows = new LinkedHashMap<>();Map<String, String> fill = new HashMap<>();fill.put("barcode", "1");fill.put("sortNum", "2");fill.put("width", "3");fill.put("length", "4");fill.put("range", "5");fill.put("name", "6");try {// 第一个参数，需要操作的sheet的索引// 第二个参数，需要复制的区域的第一行索引// 第三个参数，需要复制的区域的最后一行索引// 第四个参数，需要插入的位置的索引// 第五个参数，填充行区域中${}的值// 第六个参数，是否需要删除原来的区域// 需要注意的是，行的索引一般要减一excel.addRowByExist(l, 3, 4, 5, rows, true);// 第一个参数，需要操作的sheet的索引// 第二个参数，替换sheet当中${<变量的值>}的值excel.fillVariable(l, fill);} catch (IOException e) {e.printStackTrace();}}String fileName = "样例文件"+new Date().getTime()+".xlsx";String fileNameURL = URLEncoder.encode(fileName, "UTF-8");response.setContentType("octets/stream");response.setHeader("Content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);response.addHeader("Pargam", "no-cache");response.addHeader("Cache-Control", "no-cache");try (OutputStream os = response.getOutputStream()) {// 删除临时文件File file = new File(userHome);file.deleteOnExit();os.write(excel.getBytes());os.flush();}}

示例：