java实现excel的模板导出

映入pom依赖：

 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.0.1</version>
</dependency>
<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>4.0.1</version>
</dependency>

Ecel的信息实体类:

@Data
public class ExcelCellInfo {
    private String fileName;
    //表头
    private String shellTitle;
    //各个列的表头
    private List<String> heardList;
    //需要填充的数据信息,map中的是每一个title-value
    private List<Map<String, String>> data;
}

模板生成工具类：

public class ExcelCreatUtil {

    static List<ExcelCellInfo> analysisExcelCell(Map<String, Map<String, Object>> map){
        List<ExcelCellInfo> list = map.entrySet().stream().map(item ->{
            String shellTitle = item.getKey();
            Map<String, Object> info = item.getValue();
            List<String> cellTitle = info.containsKey("title") ? (List<String>)info.get("title") : Lists.newArrayList();
            List<Map<String, String>> cellData = info.containsKey("data") ? (List<Map<String, String>>)info.get("data") : Lists.newArrayList();
            ExcelCellInfo excelCellInfo = new ExcelCellInfo();
            excelCellInfo.setShellTitle(shellTitle);
            excelCellInfo.setData(cellData);
            excelCellInfo.setHeardList(cellTitle);

            return excelCellInfo;

        }).collect(Collectors.toList());

        return list;
    }


    public Object creatExcel(HttpServletResponse response, List<ExcelCellInfo> list , String fileName){
        SXSSFWorkbook wb = creatBook(list);
        //导出数据
        try {
            //设置Http响应头告诉浏览器下载这个附件
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            //名称要从新进行 ISO8859-1 编码否则会文件名称会乱码
            response.setHeader("Content-Disposition", "attachment;Filename=" + encodeFileName(fileName) + ".xlsx");
            OutputStream outputStream = response.getOutputStream();
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            wb.write(baos);
            outputStream.write(baos.toByteArray());
            baos.flush();
            baos.close();
            outputStream.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return "导出成功";
    }

    private SXSSFWorkbook creatBook(List<ExcelCellInfo> list){
        //创建工作簿
        SXSSFWorkbook wb = new SXSSFWorkbook();
        for(ExcelCellInfo excelCellInfo : list) {
            createSXSSFSheet(excelCellInfo, wb);
        }
        return wb;
    }

    private SXSSFSheet createSXSSFSheet(ExcelCellInfo excelCellInfo, SXSSFWorkbook wb){
        //创建工作簿
        SXSSFSheet sxssfSheet = wb.createSheet(excelCellInfo.getShellTitle());
        //设置默认的行宽
        sxssfSheet.setDefaultColumnWidth(20);
        //设置morning的行高（不能设置太小，可以不设置）
        sxssfSheet.setDefaultRowHeight((short)300);
        //设置morning的单元格格式
        CellStyle style = initCellStyle(wb);
        //标题单元格格式
        CellStyle titleStyle = initTitleCellStyle(wb);
        initTitle(sxssfSheet, excelCellInfo, titleStyle, style);
        initSheetData(sxssfSheet, style, excelCellInfo, 1);

        return sxssfSheet;
    }

    private void initSheetData(SXSSFSheet sxssfSheet,CellStyle style, ExcelCellInfo excelCellInfo, int dataStartLine) {
        if(CollectionUtils.isNotEmpty(excelCellInfo.getData())){
            //解析titl的值
            SXSSFRow titleRow = sxssfSheet.getRow(0);
            for(Map<String,String> item :excelCellInfo.getData()) {
                SXSSFRow row = sxssfSheet.createRow(dataStartLine);
                for (int i = 0; i < excelCellInfo.getHeardList().size(); i++) {
                    SXSSFCell titleCell = titleRow.getCell(i);
                    String titleName = titleCell.getStringCellValue();

                    //根据title的值对应的值
                    SXSSFCell cell = row.createCell(i);
                    cell.setCellStyle(style);
                    String value = item.get(titleName);
                    if (!StringUtils.isEmpty(value)) {
                        cell.setCellValue(value);
                    }
                }
                dataStartLine++;
            }
        }
    }

    private void initTitle(SXSSFSheet sxssfSheet, ExcelCellInfo excelCellInfo, CellStyle titleStyle, CellStyle dataColumnStyle) {
        //在第0行 设置标题
        SXSSFRow sxssfRow = sxssfSheet.createRow(0);
        //设置每列的标题数据
        int i = 0;
       for(String item : excelCellInfo.getHeardList()){
           //设置列的默认格式为 String
           sxssfSheet.setDefaultColumnStyle(i, dataColumnStyle);
            //设置单元格 和单元格的内容格式 - string
            SXSSFCell sxssfCell = sxssfRow.createCell(i, CellType.STRING);
            sxssfCell.setCellValue(item);
            //设置默认的行格式
            sxssfCell.setCellStyle(titleStyle);
            i++;
        }
    }

    private CellStyle initCellStyle(SXSSFWorkbook wb) {
        // 单元格样式（垂直居中）
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置为文本格式
        cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat("TEXT"));
        //文字的设置
        Font font = wb.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());   //设置字体颜色
        return cellStyle;
    }

    private CellStyle initTitleCellStyle(SXSSFWorkbook wb){
        // 单元格样式
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW1.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置为文本格式
        cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat("TEXT"));
        //边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.RED.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(IndexedColors.RED.getIndex());

        //文字的设置
        Font font = wb.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());  //设置字体颜色
        return cellStyle;
    }

    private String encodeFileName(String fileName) {
        try {
            //fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            fileName = java.net.URLDecoder.decode(fileName, "UTF-8");
            return new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        } catch (UnsupportedEncodingException e) {
            return "未命名";
        }
    }

}

测试接口：

/**
     * 导出空白模板
     * @param response 返回数据
     */
    @GetMapping("/export")
    public Object exportBlackList(HttpServletResponse response){
        Map<String, Map<String, Object>> info = new HashMap<>();
        Map<String, String> map1 = Maps.newHashMap();
        map1.put("1-title1","1-1title1-1111");
        map1.put("1-title2","1-1title2-2222");
        map1.put("1-title3","1-1title3-3333");
        map1.put("2-title1","2-2title1-1111");
        map1.put("2-title2","2-2title2-2222");
        map1.put("2-title3","2-2title3-3333");
        Map<String, String> map2 = Maps.newHashMap();
        map2.put("1-title1","1-1title1-aaaa");
        map2.put("1-title2","1-1title2-bbbb");
        map2.put("1-title3","1-1title3-cccc");
        map2.put("2-title1","2-2title1-eeee");
        map2.put("2-title2","2-2title2-dddd");
        map2.put("2-title3","2-2title3-ffff");
        List<Map<String, String>> data = new ArrayList<>();
        data.add(map1);
        data.add(map2);

        Map<String, Object> sheet1 = new HashMap<>();
        List<String> liat1 = Lists.newArrayList("1-title1", "1-title2", "1-title3");
        sheet1.put("title",liat1);
        sheet1.put("data", data);
        info.put("sheet1",sheet1);

        Map<String, Object> sheet2 = new HashMap<>();
        List<String> liat2 = Lists.newArrayList("2-title1", "2-title2", "2-title3");
        sheet2.put("title",liat2);
        sheet2.put("data", data);
        info.put("sheet2",sheet2);


        if(MapUtils.isEmpty(info)){
            return "表格信息为空，请输入表格信息";
        }

        List<ExcelCellInfo> list = ExcelCreatUtil.analysisExcelCell(info);

        return new ExcelCreatUtil().creatExcel(response, list, "测试");

    }

使用浏览器测试下载：

http://localhost:8080/demo/excel/export