spring boot 创建excel公共导出类，方便快捷

第一步：引入依赖 Apache--poi

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

第二步：定义Excel实体类，定义实体为了方便封装参数

public class EntityExcel {

    //Excel文件的文件名字
    private String fileName;

    //Excel的title
    private String title;

    //存放Map的key，方便拿值
    private String[] key;

    //Excel的列名字
    private String[] rowName;

    public String[] getKey() {
        return key;
    }

    public void setKey(String[] key) {
        this.key = key;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getFileName() {
        return fileName;
    }
    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public String[] getRowName() {
        return rowName;
    }

    public void setRowName(String[] rowName) {
        this.rowName = rowName;
    }

第三步：创建公共类--请忽略注释

public static XSSFWorkbook exportExcel(List<Map<String, Object>> list, EntityExcel entityExcel) throws UnsupportedEncodingException {
    

    String fileName = entityExcel.getFileName();
    String title = entityExcel.getTitle();
    String[] key = entityExcel.getKey();
    String[] rowName = entityExcel.getRowName();
    //判断标题
    if (key.length < 0 && rowName.length < 0) {
        System.err.println("数据格式不匹配");
    }

    //二、 数据转成excel
    //request.setCharacterEncoding("UTF-8");
    //response.setCharacterEncoding("UTF-8");
    //response.setContentType("application/x-download");

    fileName = URLEncoder.encode(fileName, "UTF-8");
    //response.addHeader("Content-Disposition", "attachment;filename=" + fileName+".xlsx");
    // 第一步：定义一个新的工作簿
    XSSFWorkbook wb = new XSSFWorkbook();
    // 第二步：创建一个Sheet页
    XSSFSheet sheet = wb.createSheet(entityExcel.getTitle());
    sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
    sheet.setColumnWidth(0, 4000);//设置列宽
    sheet.setColumnWidth(1, 5500);
    sheet.setColumnWidth(2, 5500);
    sheet.setColumnWidth(3, 5500);
    sheet.setColumnWidth(11, 3000);
    sheet.setColumnWidth(12, 3000);
    sheet.setColumnWidth(13, 3000);
    XSSFFont font = wb.createFont();
    font.setFontName("宋体");
    font.setFontHeightInPoints((short) 16);

    /**
     * 创建第一条标题title
     * 标题格式  未定义
     *    HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
     *    HSSFCellStyle style = this.getStyle(workbook);//单元格样式对象
     *
     */


    XSSFCellStyle alignStyle = wb.createCellStyle();

    //内容居中
    alignStyle.setAlignment(HorizontalAlignment.CENTER);
    alignStyle.setFillForegroundColor((short) 13);
    //合并单元格
    CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, key.length - 1);
    sheet.addMergedRegion(cellRangeAddress);
    XSSFRow row = sheet.createRow(0);
    XSSFCell cell = row.createCell(0);
    cell.setCellStyle(alignStyle);
    cell.setCellValue(title);

    //创建第二条，每一列的标题row
    XSSFRow rowTitle = sheet.createRow(1);

    for (int i = 0; i < rowName.length; i++) {
        XSSFCell cellTitle = rowTitle.createCell(i);
        cellTitle.setCellValue(rowName[i]);
    }

    XSSFRow rows;
    XSSFCell cells;
    for (int i = 0; i < list.size(); i++) {

        // 第三步：在这个sheet页里创建一行
        rows = sheet.createRow(i + 2);
        // 第四步：在该行创建一个单元格i
        for (int j = 0; j < key.length; j++) {
            cells = rows.createCell(j);
            // 第五步：在该单元格里设置值
            cells.setCellValue(list.get(i).get(key[j]).toString());
        }

    }
    return wb;
    /**File fileExcel = new File(filePath);
     if(!fileExcel.exists()){
     fileExcel.createNewFile();
     }
     FileOutputStream fileOutputStream = new FileOutputStream(fileExcel);
     wb.write(fileOutputStream);
     fileOutputStream.close();*/
}

第四步：测试类

public static void main(String[] args) throws IOException {
    String filePath = "D://test11.xlsx";
    File fileExcel = new File(filePath);
    if (!fileExcel.exists()) {
        fileExcel.createNewFile();
    }
    EntityExcel entityExcel = new EntityExcel();
    entityExcel.setTitle("这是新的文件名字");
    entityExcel.setFileName("新的文件");
    String[] rowName = {"姓名", "性别", "年龄"};
    String[] key = {"name", "sex", "age"};
    entityExcel.setRowName(rowName);
    entityExcel.setKey(key);
    List<Map<String, Object>> maps = new ArrayList<>();
    for (int i = 0; i < 4; i++) {
        Map<String, Object> map = new HashMap<>();
        map.put("name", i + "name");
        map.put("sex", i + "sex");
        map.put("age", i + "age");
        maps.add(map);
    }
    File file = new File(filePath);
    try {
       XSSFWorkbook wb =  exportExcel(maps, entityExcel);
         FileOutputStream fileOutputStream = new FileOutputStream(file);
        wb.write(fileOutputStream);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

第五部 controller层 

public Result<Object> exportExcel(@Valid PageWrapper<SalesLadingBillList> param, HttpServletResponse response) {
        IPage<ResponseLadingBillList> Page = salesladingbilllistservice.selectSalesLadingBillListList(param);
        EntityExcel entityExcel = new EntityExcel();
        response.setHeader("Content-Type", "Application/vnd.ms-excel");
        entityExcel.setTitle("主产品提货单");
        String[] rowName = new String[]{"姓名", "性别", "年龄"};
        String[] key = new String[]{"name", "sex", "age"};
        entityExcel.setRowName(rowName);
        entityExcel.setKey(key);
        List<Map<String, Object>> maps = new ArrayList();
        for(int i = 0; i < 4; ++i) {
            Map<String, Object> map = new HashMap();
            map.put("name", i + "name");
            map.put("sex", i + "sex");
            map.put("age", i + "age");
            maps.add(map);
        }
        List<ResponseLadingBillList>  list  = Page.getRecords();
        try{
            XSSFWorkbook wb = ExceUtil.exportExcel(maps, entityExcel);
            ServletOutputStream out = response.getOutputStream();
            wb.write(out);//将Excel用response返回
            out.flush();
            out.close();
        }catch (Exception e){
            e.getMessage();
        }
        return Result.success("导出成功");
    }