【Java】【47】导出数据到Excel
前言:
一般后台的报表查询页面,都会有导出数据到Excel表的功能
正文:
JS:
window.open("后台接口地址");
Controller层:
@ApiOperation(value = "导出列表") @RequestMapping(value = "excel/exportExcel", method = RequestMethod.GET) public void exportExcel(HttpServletResponse response, QueryVo query) { this.theService.exportCsvPointList(response, query); }
Service层:
@Override public void exportCsvPointList(HttpServletResponse response, QueryVo query) { String title = "积分记录"; String fileName = title + ".csv"; //根据查询条件query,查询出对应的数据 List<Entity> list; LinkedHashMap<String, String> headMap = new LinkedHashMap<>(); headMap.put("phone", "手机号"); //phone为Entity中的字段名,手机号为excel表的表头名称 headMap.put("name", "姓名"); ExportCsvUtil.exportCSV(title, fileName, list, headMap, response); }
工具类:
ExportCsvUtil
package com.bf.base.utils; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.BufferedWriter; import java.io.IOException; import java.io.OutputStreamWriter; import java.net.URLEncoder; import java.util.ArrayList; import java.util.LinkedHashMap; import java.util.List; import java.util.Map.Entry; /** * 下载CSV文件 */ public class ExportCsvUtil { /** * @param response 返回体 * @param list 数据集合 * @param title 主标题 * @param map 子标题与对象属性的映射 * @param fileName 文件名 */ public static <T> void exportCSV (String title, , String fileName, List<T> list, LinkedHashMap<String,String> map, HttpServletResponse response) { JSONArray array = new JSONArray(); for (T obj : list) { array.add(obj); } // 表格头 List<String> headList = new ArrayList<>(); for (Entry<String, String> entry : map.entrySet()) { headList.add(entry.getValue()); } // 数据 BufferedWriter csvWtriter = null; try { List<List<String>> dataList = new ArrayList<List<String>>(); for (Object obj : array) { List<String> rowList = new ArrayList<>(); JSONObject jo = (JSONObject) JSONObject.toJSON(obj); for (Entry<String, String> entry : map.entrySet()) { String value = entry.getValue(); String key = entry.getKey(); String joValue = jo.get(key) == null ? "" : jo.get(key).toString(); rowList.add(joValue); } dataList.add(rowList); } // 文件下载,使用如下代码 response.setContentType("application/csv;charset=gb18030"); response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); ServletOutputStream out = response.getOutputStream(); csvWtriter = new BufferedWriter(new OutputStreamWriter(out, "gb18030"), 1024); /*int num = headList.size() / 2; StringBuffer buffer = new StringBuffer(); for (int i = 0; i < num; i++) { buffer.append(" ,"); } csvWtriter.write(buffer.toString() + title + buffer.toString()); csvWtriter.newLine();*/ // 写入文件头部 writeRow(headList, csvWtriter); // 写入文件内容 for (List<String> row : dataList) { writeRow(row, csvWtriter); } csvWtriter.flush(); csvWtriter.close(); } catch (Exception e) { e.printStackTrace(); } finally { try { csvWtriter.close(); } catch (IOException e) { e.printStackTrace(); } } } /** * 写一行数据 * @param row 数据列表 * @param csvWriter * @throws IOException */ private static void writeRow(List<String> row, BufferedWriter csvWriter) throws IOException { for (String data : row) { StringBuffer sb = new StringBuffer(); String rowStr = sb.append("\"").append(data).append("\",").toString(); csvWriter.write(rowStr); } csvWriter.newLine(); } }