easyexcel导出(官方文档,https://easyexcel.opensource.alibaba.com/docs/current/)
导出动态列基于官方文档,思路是以JSON的形式,配置不固定动态表头导出
1、依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.TypeReference;
2、JSON报文
[
{
"A": "喝水",
"B": "喝牛奶",
"C": "虫子",
"E": "吃羊肉"
},
{
"A": "喝水2",
"B": "喝牛奶2",
"C": "虫子2",
"E": "吃羊肉2"
},
{
"A": "喝水3",
"B": "喝牛奶3",
"C": "虫子3",
"E": "吃羊肉3"
},
{
"A": "喝水4",
"B": "喝牛奶4",
"C": "虫子4",
"E": "吃羊肉4"
}
]
3、导出入口control
@PostMapping(value = "/noModelWrite")
public void noModelWrite(HttpServletResponse response) throws IOException {
String str = "[\n" +
" {\n" +
" \"A\": \"喝水\",\n" +
" \"B\": \"喝牛奶\",\n" +
" \"C\": \"虫子\",\n" +
" \"E\": \"吃羊肉\"\n" +
" },\n" +
" {\n" +
" \"A\": \"喝水2\",\n" +
" \"B\": \"喝牛奶2\",\n" +
" \"C\": \"虫子2\",\n" +
" \"E\": \"吃羊肉2\"\n" +
" },\n" +
" {\n" +
" \"A\": \"喝水3\",\n" +
" \"B\": \"喝牛奶3\",\n" +
" \"C\": \"虫子3\",\n" +
" \"E\": \"吃羊肉3\"\n" +
" },\n" +
" {\n" +
" \"A\": \"喝水4\",\n" +
" \"B\": \"喝牛奶4\",\n" +
" \"C\": \"虫子4\",\n" +
" \"E\": \"吃羊肉4\"\n" +
" }\n" +
"]";
List<Map<String, String>> listMap = JSON.parseObject(str, new TypeReference<List<Map<String, String>>>() {
});
List<String> headList = listMap.stream().map(Map::keySet).flatMap(Collection::stream).distinct().collect(Collectors.toList());
List<List<String>> headlists = headList.stream().map(e -> {
List<String> list = new ArrayList<>();
list.add(e);
return list;
}).collect(Collectors.toList());
List<List<Object>> dataList = listMap.stream().map(e -> new ArrayList<Object>(e.values())).collect(Collectors.toList());
String fileName = "noModelWrite" + System.currentTimeMillis() + ".xlsx";
writerExcel(fileName,headlists,dataList,response);
}
4、导出工具
/**
* Excel导出操作
*/
public static <T> void writerExcel(String fileName,
List<List<String>> head,
List<List<Object>> dataList,
HttpServletResponse response
) throws IOException {
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream())
.head(head)
.autoCloseStream(Boolean.FALSE)
.sheet("模板")
.doWrite(dataList);
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JsonUtil.toJsonString(map));
}
}
以上代码直接复制在test中就可以运行
动态的数据转换成List<Map>,key是表头,value是值,外侧List的size代表着行数
可以将固定的数据与动态数据拼接在一起,达到动态列。