java使用Apache POI导出excel模板

效果图

第一个sheet页

第一行合并单元格

第二个sheet页，为字典数据

前端代码，具体方法根据所用框架封装

 <div @click="exportTemplate">导入模板下载</div>

//发起请求，具体方法根据所用框架封装
    exportTemplate(){
      this.Base.downloadFile({
        url: 'controller/exportIndexTemplate',
        options: {
        },
        fileName: "导出模板",
        type: 'application/vnd.ms-excel'
      }).then(res => {
        this.$message.success('导出成功')
      })
    },

后端引入依赖

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

后端代码，controller层

    /**
     * 导出数据模板
     *
     * @param paramJson 模板id
     * @param response  响应
     */
    @PostMapping("exportIndexTemplate")
    public void exportIndexTemplate(String paramJson, HttpServletResponse response) {
        mmsDoctorAuthorityReadService.exportIndexTemplate(paramJson, response);
    }

service层

    @Override
    public void exportIndexTemplate(String paramJson, HttpServletResponse response) {
//        Map<String, Object> param = JsonFactory.json2bean(paramJson, HashMap.class);
        LinkedHashMap<String, String> title = new LinkedHashMap<>();
        title.put("工号","工号");
        title.put("类型(多个用,分割)","类型(多个用,分割)");
        title.put("姓名","姓名");
        title.put("性别","性别");
        title.put("住址","住址");
        title.put("电话","电话");
        title.put("年龄","年龄");
        title.put("爱好(多个用,分割)","爱好(多个用,分割)");

//第二个sheet页需要到处基础数据
        List<LinkedHashMap<String, Object>> dictList= readMapper.getDictList();

        ExcelUtils.exportTemplate(title, dictList, response);
    }

工具类

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

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

import java.text.NumberFormat;

import java.util.*;


/**
 * Excel导入导出工具类
 */

@Slf4j
public class ExcelUtils {

    private static final String XLSX = ".xlsx";
    private static final String XLS = ".xls";
    public static final String ROW_MERGE = "row_merge";
    public static final String COLUMN_MERGE = "column_merge";
    private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    private static final String ROW_NUM = "rowNum";
    private static final String ROW_DATA = "rowData";
    private static final String ROW_TIPS = "rowTips";
    private static final int CELL_OTHER = 0;
    private static final int CELL_ROW_MERGE = 1;
    private static final int CELL_COLUMN_MERGE = 2;
    private static final int IMG_HEIGHT = 30;
    private static final int IMG_WIDTH = 30;
    private static final char LEAN_LINE = '/';
    private static final int BYTES_DEFAULT_LENGTH = 10240;
    private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();

    public static void exportTemplate(LinkedHashMap<String, String> title, List<LinkedHashMap<String, Object>> dataList, HttpServletResponse response) {
        try {
            // 创建一个HSSFWorkbook，对应一个Excel文件
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = workbook.createSheet("导入数据");
            // 在sheet中添加表头第0行
            // 声明列对象
            HSSFRow row;
            row = sheet.createRow(1);
            HSSFRow row0 = sheet.createRow(0);
            //合并首行单元格 ：起始行号,终止行号,起始列号,终止列号
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8);
            sheet.addMergedRegion(region);

            HSSFCell rowCell0 = row0.createCell(0);
            rowCell0.setCellValue("导入模板(不要删模板内容),导入数据继续在后面添加");

            // 创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            DataFormat dataFormat = workbook.createDataFormat();
            cellStyle.setDataFormat(dataFormat.getFormat("@"));

            HSSFCell headCell;
            // 创建标题
            int rowKey = 0;
            for (Map.Entry entry : title.entrySet()) {
                headCell = row.createCell(rowKey);
                headCell.setCellValue(entry.getValue().toString());
                headCell.setCellStyle(cellStyle);
                rowKey++;
            }
            //插入一条模板数据 
            // 创建一个字体
            HSSFFont font = workbook.createFont();
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            font.setColor(IndexedColors.RED.getIndex()); // 设置字体颜色为红色
            row = sheet.createRow(2);
            HSSFCell row_12 =  row.createCell(0);
            row_12.setCellValue("test(勿删)");
            // 将字体应用到样式
            cellStyle1.setFont(font);
            row_12.setCellStyle(cellStyle1);
            HSSFCell row_13 =  row.createCell(1);
            row_13.setCellValue("1,3");
            row_13.setCellStyle(cellStyle1);
            HSSFCell row_14 =  row.createCell(2);
            row_14.setCellValue("1");
            row_14.setCellStyle(cellStyle1);
            HSSFCell row_15 =  row.createCell(3);
            row_15.setCellValue("1");
            row_15.setCellStyle(cellStyle1);
            HSSFCell row_16 =  row.createCell(4);
            row_16.setCellValue("1");
            row_16.setCellStyle(cellStyle1);
            HSSFCell row_17 =  row.createCell(5);
            row_17.setCellValue("1");
            row_17.setCellStyle(cellStyle1);
            HSSFCell row_18 =  row.createCell(6);
            row_18.setCellValue("1");
            row_18.setCellStyle(cellStyle1);
            HSSFCell row_19 =  row.createCell(7);
            row_19.setCellValue("1,2");
            row_19.setCellStyle(cellStyle1);



            //创建内容

            HSSFSheet sheet2 = workbook.createSheet("字典数据");
            HSSFRow row_20 = sheet2.createRow(0);
            HSSFCell rowCell_20 = row_20.createCell(0);
            rowCell_20.setCellValue("字典名称");
            HSSFCell rowCell_21 = row_20.createCell(1);
            rowCell_21.setCellValue("码值");
            HSSFCell rowCell_22 = row_20.createCell(2);
            rowCell_22.setCellValue("码值描述");
            // 创建内容
            HSSFCell rowCell;
            if (!CollectionUtils.isEmpty(dataList)) {
                for (int i = 0; i < dataList.size(); i++) {
                    row = sheet2.createRow(i + 1);
                    LinkedHashMap<String, Object> data = dataList.get(i);
                    int j = 0;
                    for (Map.Entry entry : data.entrySet()) {
                        rowCell = row.createCell(j);
                        rowCell.setCellType(CellType.STRING);
                        rowCell.setCellValue(entry.getValue().toString());
                        rowCell.setCellStyle(cellStyle);
                        j++;
                    }
                }
            }
            response.setContentType("application/octet-stream; charset=utf-8");//以流的形式对文件进行下载
            response.setHeader("Content-Disposition", "attachment;");
            workbook.write(response.getOutputStream());
            response.getOutputStream().close();

        } catch (IOException e) {
            log.error("error", e);
        }
    }

}