本文将介绍通过sql查询语句中所包含的字段名实现excel导出功能
一、添加依赖
dependency>
com.alibaba
easyexcel
3.0.5
cglib
cglib
org.apache.poi
poi
3.17
org.apache.poi
poi-ooxml
3.17
org.apache.poi
poi-ooxml-schemas
3.17
二、创建文件名、表头数组等实体字段
@Data
public class NoModelWriteData implements Serializable {
private String fileName;//文件名
private String[] headMap;//表头数组
private String[] dataStrMap;//对应数据字段数组
private List dataList;//数据集合
}
@Data
public class SimpleWriteData implements Serializable {
private String fileName;//文件名
private List dataList;//数据列表
}
三、导出工具类
public class ExcelUtil {
//不创建对象的导出
public void noModleWrite(@RequestBody NoModelWriteData data, HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
try {
// response.setContentType("application/vnd.ms-excel");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(data.getFileName(), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream()).head(head(data.getHeadMap())).sheet(data.getFileName()).doWrite(dataList(data.getDataList(), data.getDataStrMap()));
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map map = new HashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
//创建对象的导出
public void simpleWrite(@RequestBody SimpleWriteData data, Class clazz, HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
// response.setContentType("application/vnd.ms-excel");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(data.getFileName(), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz).sheet(data.getFileName()).doWrite(data.getDataList());
}
//设置表头
private List head(String[] headMap) {
List list = new ArrayList();
for (String head : headMap) {
List headList = new ArrayList();
headList.add(head);
list.add(headList);
}
return list;
}
//设置导出的数据内容
private List dataList(List dataList, String[] dataStrMap) {
List list = new ArrayList();
for (Map map : dataList) {
List data = new ArrayList();
for (int i = 0; i < dataStrMap.length; i++) {
data.add(map.get(dataStrMap[i]));
}
list.add(data);
}
return list;
}
}
四、controller层进行调用
@GetMapping("export")
public void export(HttpServletResponse response, @RequestParam String sql) throws IOException {
//sql查询所有导出的数据
List query = jdbcTemplate.queryForList(sql);
//得到excel表头数组
Object[] array = query.get(0).keySet().toArray();
String[] strs1 = Arrays.asList(array).toArray(new String[]{});
NoModelWriteData d = new NoModelWriteData();
d.setFileName("全量导出"); //文件名
d.setHeadMap(strs1); //表头数组
d.setDataStrMap(strs1); //对应数据字段数组
d.setDataList(query); //数据集合
ExcelUtil easyExcelUtils = new ExcelUtil();
easyExcelUtils.noModleWrite(d, response);
}
|