根据查询sql实现excel导出功能

本文将介绍通过sql查询语句中所包含的字段名实现excel导出功能

一、添加依赖

dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>3.0.5</version>
   <exclusions>
      <exclusion>
         <groupId>cglib</groupId>
         <artifactId>cglib</artifactId>
      </exclusion>
   </exclusions>
</dependency>

<!-- POI导入导出 -->
<!-- excel处理 -->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.17</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml-schemas</artifactId>
   <version>3.17</version>
</dependency>

二、创建文件名、表头数组等实体字段

@Data
public class NoModelWriteData implements Serializable {
    private String fileName;//文件名
    private String[] headMap;//表头数组
    private String[] dataStrMap;//对应数据字段数组
    private List<Map<String, Object>> 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<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

    //创建对象的导出
    public <T> void simpleWrite(@RequestBody SimpleWriteData data, Class<T> 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<List<String>> head(String[] headMap) {
        List<List<String>> list = new ArrayList<List<String>>();

        for (String head : headMap) {
            List<String> headList = new ArrayList<String>();
            headList.add(head);
            list.add(headList);
        }
        return list;
    }

    //设置导出的数据内容
    private List<List<Object>> dataList(List<Map<String, Object>> dataList, String[] dataStrMap) {
        List<List<Object>> list = new ArrayList<List<Object>>();
        for (Map<String, Object> map : dataList) {
            List<Object> data = new ArrayList<Object>();
            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<Map<String, Object>> 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);

}