EasyExcel--基础--4.3--写Excel--设置样式_easyexcel设置样式

EasyExcel–基础–4.3–写Excel–设置样式

代码位置

https://gitee.com/DanShenGuiZu/learnDemo/tree/master/easyExcel_learn
1

1、代码

package fei.zhou.easyexcel_learn.business.demo8;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import org.apache.poi.common.usermodel.fonts.FontCharset;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.assertj.core.util.Lists;

import java.io.File;
import java.util.List;


public class Test2 {

    public static void main(String[] args) throws Exception {

        //文件路径
        File file = new File("src\\main\\java\\fei\\zhou\\easyexcel_learn\\business\\demo8\\test2.xlsx");

        String filePath = file.getAbsolutePath();
        //设置列名
        List<List<String>> heads = Lists.newArrayList();
        heads.add(Lists.newArrayList("表头1"));
        heads.add(Lists.newArrayList("表头2"));
        heads.add(Lists.newArrayList("表头3"));
        heads.add(Lists.newArrayList("表头4"));
        heads.add(Lists.newArrayList("表头5"));

        //设置 行列数据
        List<List<String>> contents = Lists.newArrayList();
        for (int i = 0; i <= 10; i++) {
            List<String> content = Lists.newArrayList();
            for (int j = 0; j < 5; j++) {
                content.add("第" + i + "行，第" + j + "例内容");
            }
            contents.add(content);
        }


        // 表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置数据格式
        headWriteCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("m/d/yy h:mm"));
        // 是否换行
        headWriteCellStyle.setWrapped(false);
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 前景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        // 背景色
        headWriteCellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
        // 设置为1时，单元格将被前景色填充
        headWriteCellStyle.setFillPatternType(FillPatternType.NO_FILL);
        // 控制单元格是否应自动调整大小以适应文本过长时的大小
        headWriteCellStyle.setShrinkToFit(false);
        // 单元格边框类型
        headWriteCellStyle.setBorderBottom(BorderStyle.NONE);
        headWriteCellStyle.setBorderLeft(BorderStyle.NONE);
        headWriteCellStyle.setBorderRight(BorderStyle.NONE);
        headWriteCellStyle.setBorderTop(BorderStyle.NONE);
        // 单元格边框颜色
        headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        // 字体策略
        WriteFont writeFont = new WriteFont();
        // 是否加粗/黑体
        writeFont.setBold(false);
        // 字体颜色
        writeFont.setColor(Font.COLOR_RED);
        // 字体名称
        writeFont.setFontName("宋体");
        // 字体大小
        writeFont.setFontHeightInPoints((short) 11);
        // 是否使用斜体
        writeFont.setItalic(false);
        // 是否在文本中使用横线删除
        writeFont.setStrikeout(false);
        // 设置要使用的文本下划线的类型
        writeFont.setUnderline(Font.U_NONE);
        // 设置要使用的字符集
        writeFont.setCharset(FontCharset.DEFAULT.getNativeId());
        headWriteCellStyle.setWriteFont(writeFont);

        // 内容样式策略策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.GENERAL);
        contentWriteCellStyle.setBorderBottom(BorderStyle.NONE);
        contentWriteCellStyle.setBorderLeft(BorderStyle.NONE);
        contentWriteCellStyle.setBorderRight(BorderStyle.NONE);
        contentWriteCellStyle.setBorderTop(BorderStyle.NONE);
        contentWriteCellStyle.setFillPatternType(FillPatternType.NO_FILL);
        contentWriteCellStyle.setWrapped(false);


        ExcelWriterBuilder write = EasyExcel.write(filePath);
        // 构建列表
        write.head(heads);

        //设置样式
        write.registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle));
        write.registerWriteHandler(new SimpleColumnWidthStyleStrategy(16));// 列宽

        // 构建sheet
        ExcelWriterSheetBuilder sheet = write.sheet("销售订单");
        // 写数据
        sheet.doWrite(contents);


    }

}

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

2、结果