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
package cn.wisenergy.service.util;
import com.alibaba.excel.EasyExcel;
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.LongestMatchColumnWidthStyleStrategy;
import io.undertow.util.Headers;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.http.MediaType;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
/**
* Excel工具类
*
* @author: ZHY
* @date: 2020-08-24 17:31
* @version:
**/
public class ExcelUtils {
private final static HorizontalCellStyleStrategy HORIZONTAL_CELL_STYLE_STRATEGY;
static {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 12);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 内容字体
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 11);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HORIZONTAL_CELL_STYLE_STRATEGY = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
/**
* 导出
*
* @param response
* @param data 数据
* @param fileName 文件名
* @param t 导出对象
**/
public static <T> void export(HttpServletResponse response, List<T> data, String fileName, Class<T> t) {
try {
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
response.setHeader(Headers.CONTENT_DISPOSITION_STRING, "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
EasyExcel.write(response.getOutputStream(), t)
.registerWriteHandler(HORIZONTAL_CELL_STYLE_STRATEGY)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet(fileName).doWrite(data);
} catch (Exception e) {
// 重置response
response.reset();
throw new RuntimeException("下载失败");
}
}
}