List＜Map＞格式根据key取值进行的Excel下载

数据库数据进行Excel下载

Util：

public class ExcelUtil {

    /**
     * @return org.apache.poi.hssf.usermodel.HSSFWorkbook
     * @MethodName: createExcel
     * @Description: 生成Excel表格
     * @Param [sheetName, titleList, dataList]
     * @author 
     * @date 2020/10/16 16:02
     */
    public static HSSFWorkbook createExcel(String sheetName,
                                           List<Map<String,String>> dataList) throws IllegalAccessException {
        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建sheet对象
        HSSFSheet sheet = wb.createSheet(sheetName);
        //在sheet里创建第一行，这里即是表头
        HSSFRow rowTitle = sheet.createRow(0);

        //写入表头的每一个列
        Map<String,String> map = dataList.get(0);
        int i = 0;
        for (String key : map.keySet()) {

            //创建单元格(表头)
            rowTitle.createCell(i).setCellValue(key);
            System.out.println(i+ "   key == " + key);
            i++;
        }
        // 写入数据
        int j = 0;
        for (int o = 0; o < dataList.size(); o++) {
            Map<String,String> mapList = dataList.get(o);
            HSSFRow rowData = sheet.createRow(j + 1);
            int l = 0;
            for (String mp : mapList.keySet()) {
                // 返回与此项对应的值
                String value = mapList.get(mp);
                System.out.println(j +"    "+ l + "  value == " + value);
                rowData.createCell(l).setCellValue(value);
                l++;
            }
            j++;
        }
        return wb;
    }
}

用到该Util的原因在于，我们不确定数据库查询出来的字段是固定的，所以我按照数据库取值类型为List < Map > 来进行写入Excel

测试接口：

    @GetMapping(value = "/down-excel")
    @ResponseResult
    public String downExcel(HttpServletResponse response) throws IOException, IllegalAccessException {
        //文件名
        String fileName = "测试表";
        //sheet名
        String sheetName = "测试sheet";

        //测试数据，这里是手动添加，真实的环境是从数据库中得到是List<Map>接收
        Map map = new HashMap<>();
        map.put("phon","13037871682");
        map.put("name","张三");
        map.put("date","2020-10-16");
        map.put("status","是");
        Map map2= new HashMap<>();
        map2.put("phon","15224875368");
        map2.put("name","李四");
        map2.put("date","2020-10-15");
        map2.put("status","否");
        
        //将两个对象加入到集合中，作为数据参数
        List<Map<String,String>> mapList = new ArrayList<>();
        mapList .add(map);
        mapList .add(map2);

        //调取封装的方法，传入相应的参数
        HSSFWorkbook workbook = ExcelUtil.createExcel(sheetName, mapList );

        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        //中文名称要进行编码处理
        response
                .setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("GB2312"), "ISO-8859-1") + ".xls");
        response.setContentType("application/x-xls");
        workbook.write(output);
        output.close();
        return null;
    }

个人技术有限，有问题大家可以留言一起交流