alibaba easyexcel导出excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import com.alibaba.excel.util.FileUtils;
try {
String resourceFileName = "Autel_桩_导入_模板.xlsx";
String resourceFilePath = "xls/" + resourceFileName;
InputStream inputStream = ChargeCardExcelEntity.class.getClassLoader().getResourceAsStream(resourceFilePath);
File tempFile = File.createTempFile("test", "xlsx");
FileUtils.writeToFile(tempFile, inputStream);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
downLoadFileName = URLEncoder.encode(downLoadFileName, "UTF-8").replaceAll("\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + downLoadFileName + ".xlsx");
byte[] fileToByteArray = FileUtils.readFileToByteArray(tempFile);
response.getOutputStream().write(fileToByteArray);
} catch (IOException e) {
e.printStackTrace();
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ExcelWriter excelWriter = null;
String fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
try {
// 这里 需要指定写用哪个class去写
excelWriter = EasyExcel.write(fileName, DemoData.class).build();
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来
for (int i = 0; i < 5; i++) {
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
List<DemoData> data = data();
excelWriter.write(data, writeSheet);
}
} finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ExcelWriter excelWriter = null;
fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
try {
// 这里 指定文件
excelWriter = EasyExcel.write(fileName, DemoData.class).build();
// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
for (int i = 0; i < 5; i++) {
// 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
List<DemoData> data = data();
excelWriter.write(data, writeSheet);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ExcelWriter excelWriter = null;
fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
try {
// 这里 指定文件
excelWriter = EasyExcel.write(fileName).build();
// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
for (int i = 0; i < 5; i++) {
// 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class 实际上可以一直变
WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(DemoData.class).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
List<DemoData> data = data();
excelWriter.write(data, writeSheet);
}

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
1.添加LocalDateTimeConverter 文件
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
@Override
public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
}
2.ExcelProperty注解里添加convert属性
@ExcelProperty(value = "反馈时间", converter = LocalDateTimeConverter.class)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private LocalDateTime userFeedBackTime;

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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
public class EasyExcelUtil {
/**
* 读取实体对象集合
*
* @param inputStream 输入流
* @param tClass
返回对象类型
* @param <T>
发挥对象泛型
* @return 实体对象集合
*/
public static <T> List<T> readData(InputStream inputStream, Class<T> tClass) {
EasyExcelDataListener<T> easyExcelDataListener = new EasyExcelDataListener<>();
EasyExcel.read(inputStream, tClass, easyExcelDataListener).sheet().doRead();
return new ArrayList<>(easyExcelDataListener.dataLists);
}
/**
* 下载excel
*
* @param response http响应
* @param tClass
数据类型
* @param fileName 文件名
* @param dataList 数据集合
* @param <T>
数据类型泛型
*/
public static <T> void downloadData(HttpServletResponse response, String fileName, Class<T> tClass, List<T> dataList) {
try {
setDownloadParam(response, fileName);
EasyExcel.write(response.getOutputStream(), tClass).sheet("数据").doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 下载excel
*
* @param response http响应
* @param tClass
数据类型
* @param fileName 文件名
* @param dataList 数据集合
* @param <T>
数据类型泛型
*/
public static <T> void downloadIncludeData(HttpServletResponse response, String fileName, Set<String> includeColumnFiledNames, Class<T> tClass, List<T> dataList) {
try {
setDownloadParam(response, fileName);
EasyExcel.write(response.getOutputStream(), tClass).includeColumnFiledNames(includeColumnFiledNames).sheet("数据").doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 下载excel
*
* @param response http响应
* @param tClass
数据类型
* @param fileName 文件名
* @param dataList 数据集合
* @param <T>
数据类型泛型
*/
public static <T> void downloadExcludeData(HttpServletResponse response, String fileName, Set<String> excludeColumnFiledNames, Class<T> tClass, List<T> dataList) {
try {
setDownloadParam(response, fileName);
EasyExcel.write(response.getOutputStream(), tClass).excludeColumnFiledNames(excludeColumnFiledNames).sheet("数据").doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 下载excel
*
* @param response http响应
* @param tClass
数据类型
* @param fileName 文件名
* @param dataList 数据集合
* @param <T>
数据类型泛型
*/
public static <T> void downloadMergeColData(HttpServletResponse response, String fileName, int mergeBeginRowIndex, int[] mergeColIndexs, Class<T> tClass, List<T> dataList) {
try {
setDownloadParam(response, fileName);
EasyExcel.write(response.getOutputStream(), tClass).sheet("数据").registerWriteHandler(new ExcelFillCellMergeColStrategy(mergeBeginRowIndex, mergeColIndexs)).doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 下载excel
*
* @param response http响应
* @param tClass
数据类型
* @param fileName 文件名
* @param dataList 数据集合
* @param <T>
数据类型泛型
*/
public static <T> void downloadMergeRowData(HttpServletResponse response, String fileName, int mergeBeginRowIndex, List<MergeCol> mergeCols, Class<T> tClass, List<T> dataList) {
try {
setDownloadParam(response, fileName);
EasyExcel.write(response.getOutputStream(), tClass).sheet("数据").registerWriteHandler(new ExcelFillCellMergeRowStrategy(mergeBeginRowIndex, mergeCols))
.doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 下载excel
*
* @param response
http响应
* @param fileName
文件名
* @param titles
标题
* @param tupleList 查询结果集
* @param <T>
数据类型泛型
*/
public static <T> void downloadData(HttpServletResponse response, String fileName, List<String> titles, List<Tuple> tupleList) {
try {
setDownloadParam(response, fileName);
//构建标题头
List<List<String>> heads = new ArrayList<>();
for (String title : titles) {
List<String> list = new ArrayList<>();
list.add(title);
heads.add(list);
}
//构建数据
List<List<String>> dataList = new ArrayList<>();
for (int i = 0; i < tupleList.size(); i++) {
List<String> cellList = new ArrayList<>();
Tuple tuple = tupleList.get(i);
Object[] objs = tuple.toArray();
for (int j = 0; j < tuple.size(); j++) {
cellList.add(formatAttributeValue(objs[j]));
}
dataList.add(cellList);
}
EasyExcel.write(response.getOutputStream()).head(heads).sheet("数据").doWrite(dataList);
} catch (Exception e) {
log.error("excel下载失败！", e);
}
}
/**
* 设置下载参数
*
* @param response 响应对象
* @param fileName 文件名
* @throws UnsupportedEncodingException 编码异常
*/
private static void setDownloadParam(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
}
/**
* 转换属性值为字符串
*
* @param value 属性值
* @return 属性值字符串
*/
private static String formatAttributeValue(Object value) {
if (value == null) {
return "";
}
String attributeValue;
Class type = value.getClass();
if (type == String.class) {
attributeValue = (String) value;
} else if (type == Integer.class || type == Integer.TYPE) {
attributeValue = ((Integer) value).toString();
} else if (type == LocalDateTime.class) {
attributeValue = ((LocalDateTime) value).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
} else if (type == LocalDate.class) {
attributeValue = ((LocalDate) value).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
} else if (type == LocalTime.class) {
attributeValue = ((LocalTime) value).format(DateTimeFormatter.ofPattern("HH:mm:ss"));
} else if (type == Long.class || type == Long.TYPE) {
attributeValue = ((Long) value).toString();
} else if (type == Double.class || type == Double.TYPE) {
attributeValue = value.toString();
} else if (type == Float.class || type == Float.TYPE) {
attributeValue = value.toString();
} else if (type == Boolean.class || type == Boolean.TYPE) {
attributeValue = ((Boolean) value).toString();
} else {
if (type != BigDecimal.class) {
log.error("类型转换未定义！");
throw new RuntimeException("类型转换未定义！");
}
attributeValue = value.toString();
}
return attributeValue;
}
}