1.maven仓库地址
1
| https://mvnrepository.com/search?q=easy+excal
|
2. EasyExcel
官方网址 https://www.yuque.com/easyexcel/doc/easyexcel
导包
1
| implementation group: 'com.alibaba', name: 'easyexcel', version: '3.0.5'
|
工具类
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
| public class EasyExcelUtil {
public static final String SYS_TEM_DIR = System.getProperty("java.io.tmpdir") + File.separator;
public static <T> List<T> readExcel(InputStream excelInputStream, String fileName, Class<T> clazz) {
ExcelListener excelListener = new ExcelListener();
ExcelReader excelReader = getReader(excelInputStream, fileName,clazz, excelListener);
if (excelReader == null) {
return new ArrayList<>();
}
List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
for (ReadSheet readSheet : readSheetList) {
excelReader.read(readSheet);
}
excelReader.finish();
return Convert.toList(clazz, excelListener.getDataList());
}
public static <T> void writeExcel(HttpServletResponse response, List<T> list, String fileName, Class<T> clazz) {
OutputStream outputStream = getOutputStream(response, fileName);
ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build();
WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").registerWriteHandler(EasyExcelUtil.getStyleStrategy()).build();
excelWriter.write(list, writeSheet);
excelWriter.finish();
}
private static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
String filePath =SYS_TEM_DIR + fileName + ".xlsx";
File file = new File(filePath);
try {
if (!file.exists() || file.isDirectory()) {
file.createNewFile();
}
fileName = new String(filePath.getBytes(), "ISO-8859-1");
response.addHeader("Content-Disposition", "filename=" + fileName);
return response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
private static <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener excelListener) {
try {
if (filename == null ||
(!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
return null;
}
ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).build();
inputStream.close();
return excelReader;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static HorizontalCellStyleStrategy getStyleStrategy(){
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)12);
headWriteFont.setFontName("Frozen");
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setWrapped(false);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short)12);
contentWriteFont.setFontName("Calibri");
contentWriteCellStyle.setWriteFont(contentWriteFont);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
|
3.监听者
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
| public class ExcelListener extends AnalysisEventListener {
private List<Object> dataList = new ArrayList<>();
@Override
public void invoke(Object object, AnalysisContext context) {
dataList.add(object);
handleBusinessLogic();
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
System.out.println("导入的数据条数为: " + dataList.size());
}
private void handleBusinessLogic() {
}
public List<Object> getDataList() {
return dataList;
}
public void setDataList(List<Object> dataList) {
this.dataList = dataList;
}
}
|
4.实体类
注意导入不能加@Accessors(chain = true)注解,负责会读取不到
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
| @Data
@ExcelIgnoreUnannotated
public class CreditBillingExportVO {
/**
* 账单编号
*/
@ExcelProperty(value = "账单编号" ,index = 0)
private String billingSn;
/**
* 门店id
*/
@ExcelProperty(value = "门店id" ,index = 1)
private Long storeId;
@ExcelProperty(value = "门店名称" ,index = 2)
private String storeName;
/**
* 服务商id
*/
private Long agentId;
@ExcelProperty(value = "服务商名称" ,index = 3)
private String agentName;
/**
* 生成日期
*/
@ExcelProperty(value = "生成日期" ,index = 4)
private Date createDate;
/**
* 账单最后还款日期
*/
@ExcelProperty(value = "账单最后还款日期" ,index = 5)
private Date settlementTime;
/**
* 账单金额
*/
@ExcelProperty(value = "账单金额" ,index = 6)
private BigDecimal settleAmount;
/**
* 账单周期
*/
@ExcelProperty(value = "商品Id" ,index = 7)
private String billingDate;
/**
* 还款状态，0->待还款 1->已付款 2->已还款
*/
@ExcelProperty(value = "还款状态" ,index = 8)
private Integer status;
}
|
