easypoi——Excel表自定义模板样式（简单丝滑）

---

问题

上一篇文章只使用简单的注解、无法改变字体大小、颜色，单元格颜色、边框问题。

实战

工具类

比上一次只多了一行代码，ExcelExportStylerUtil类只是为了自由实现我们想要的样式。

/**
     * 导出Excel加边框样式
     */
    public static void exportNewExcel(List<?> list, String title, String sheetName, Class<?> entity, String fileName, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        //添加样式
        exportParams.setStyle(ExcelExportStylerUtil.class);
        //冻结表头
        exportParams.setCreateHeadRows(true);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entity, list);

        if (workbook == null) {
            throw new RuntimeException("Excel表导出失败");
        }
        OutputStream outputStream = null;
        BufferedOutputStream buffOutputStream = null;
        try {
            // 指定下载的文件名--设置响应头
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            //response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            response.setCharacterEncoding("UTF-8");
            // 导出Excel
            outputStream = response.getOutputStream();
            buffOutputStream = new BufferedOutputStream(outputStream);
            workbook.write(buffOutputStream);
            buffOutputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
                if (buffOutputStream != null) {
                    buffOutputStream.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

ExcelExportStylerUtil类——实现自由调样式

import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;

public class ExcelExportStylerUtil extends AbstractExcelExportStyler implements IExcelExportStyler {
    public ExcelExportStylerUtil(Workbook workbook) {
        super.createStyles(workbook);
    }
    @Override
    public CellStyle getHeaderStyle(short headerColor) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);         // 水平居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);   // 上下居中
        titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
        titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
        titleStyle.setBorderTop(BorderStyle.THIN);//上边框
        titleStyle.setBorderRight(BorderStyle.THIN);//右边框
        return titleStyle;
    }

    @Override
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);         // 水平居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);   // 上下居中
        titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
        titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
        titleStyle.setBorderTop(BorderStyle.THIN);//上边框
        titleStyle.setBorderRight(BorderStyle.THIN);//右边框
        titleStyle.setWrapText(true);
        return titleStyle;
    }

    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);         // 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);   // 上下居中
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);         // 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);   // 上下居中
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

总结

在网上浏览了很多其他的博客，发现如果想要实现自己的样式，就需要重新生成一个文本簿，然后再慢慢添加样式，添加表头，但浪费了我们写的实体类注解，很是麻烦。现在，把你想要的样式写入ExcelExportStylerUtil类即可实现，目前只是提供了单元格边框、字体居中的功能。这一篇文章可以与上一篇文章完美融合，使用起来非常丝滑，解决你Excel表的大部分问题。