导出接口生成excel

使用service.selectMapsPage获取到map类型的分页数据，取出map类型的集合。由于集合中的任意一个元素都是excel中任意一行的完整数据，所以对mapList.get(0).entrySet()进行for循环得到key的集合，即对应的表中字段集合。

image.png

excel标题
在对应的xxControl中定义标题集合，以及标题和字段对应关系的集合。（因为list集合是有序的！！利用有序的特点可以将数据有序地放进excel中，keyValueList 集合的顺序和页面显示的顺序是一致的。）

    @Override
    public String[] keyArray() {
        String[] headers = {"科目名称", "级别", "理论", "操作", "理、操", "人数总计", "费用总计"};
        return headers;
    }


    @Override
    public List<KeyValue> keyValueRelation() {
        List<KeyValue> keyValueList = new ArrayList<>();
        keyValueList.add(new KeyValue("subject", "科目名称"));
        keyValueList.add(new KeyValue("levelname", "级别"));
        keyValueList.add(new KeyValue("theryNum", "理论"));
        keyValueList.add(new KeyValue("operateNum", "操作"));
        keyValueList.add(new KeyValue("theryOperateNum", "理、操"));
        keyValueList.add(new KeyValue("allNum", "人数总计"));
        keyValueList.add(new KeyValue("allMoney", "费用总计"));
        return keyValueList;
    }

headers集合和keyValueList集合长度是一样的，所以利用headers进行for循环或者keyValueList进行for循环都是一样的，都能够将keyValueList集合中的元素依次取出来，并拿到字段名（比如subject），再通过字段名将mapList中的值取出来存进单元格中。

                for (short j = 0; j < headers.length; j++) {
//                for (short j = 0; j < keyValueList.size(); j++) {
                    //创建单元格
                    Cell cell = row1.createCell(j);
                    String ziduan = keyValueList.get(j).getZiduan();
                    sheet.setColumnWidth(i, 256 * 15);//设置某列的列宽，列号为“i”
                    Object o = mapList.get(i).get(ziduan);
                    System.out.println(o);
                    cell.setCellValue(mapList.get(i).get(ziduan) == null ? " " : mapList.get(i).get(ziduan).toString());
                }

对应xxControl代码

public class BaseRestController<S extends ServiceImpl<M, T>, M extends BaseMapper<T>, T, I extends Serializable> {
    /**
     * 导出接口，暂时未用
     */
    @RequestMapping(value = "export", method = RequestMethod.GET)
    public ResponseObj<Boolean> export(@ModelAttribute Page<T> page,
                                       @ModelAttribute T model,
                                       HttpServletResponse response,
                                       HttpServletRequest request) throws Exception {
        String name = model.getClass().getSimpleName();

        Page<Map<String, Object>> mapPage = service.selectMapsPage(page, queryWrapper(new EntityWrapper<T>(model)));
        List<Map<String, Object>> mapList = mapPage.getRecords();

//        Page<T> pageList = service.selectPage(page, queryWrapper(new EntityWrapper<>(model)));
//        List<T> records = pageList.getRecords();

        List<KeyValue> keyValueList = keyValueRelation();

        if (mapList != null && mapList.size() > 0) {
            List<String> keyList = new ArrayList<>();  //key的集合
            for (Map.Entry<String, Object> entry : mapList.get(0).entrySet()) {
                String key = entry.getKey();
                keyList.add(key);
            }

            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("信息表");

            String fileName = null;
            if (name.equals("CostForJnjd")) {
                fileName = "技能鉴定-费用统计" + ".xls";//设置要导出的文件的名字
            } else if (name.equals("CostForGcs")) {
                fileName = "通讯工程师-费用统计" + ".xls";//设置要导出的文件的名字
            } else if (name.equals("CostForGg")) {
                fileName = "高工评审-费用统计" + ".xls";//设置要导出的文件的名字
            }

            String[] headers = keyArray();

            //headers表示excel表中第一行的表头
            HSSFRow row = sheet.createRow(0);
            //设置列名
            for (int i = 0; i < headers.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(headers[i]);
            }

            for (int i = 1; i < mapList.size(); i++) {
                //创建行
                HSSFRow row1 = sheet.createRow(i);
                for (short j = 0; j < keyList.size(); j++) {
                    //创建单元格
                    Cell cell = row1.createCell(j);
                    String ziduan = keyValueList.get(j).getZiduan();
//                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    sheet.setColumnWidth(i, 256 * 15);//设置某列的列宽，列号为“i”
                    Object o = mapList.get(i).get(ziduan);
                    System.out.println(o);
                    cell.setCellValue(mapList.get(i).get(ziduan) == null ? " " : mapList.get(i).get(ziduan).toString());
                }
            }

            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            response.flushBuffer();
            workbook.write(response.getOutputStream());

            return new ResponseObj<>(true, RetCode.SUCCESS);
        } else {
            // 无导出数据
            request.setAttribute("noDataToExport", "没有数据可以导出!");
            return new ResponseObj<>(false, RetCode.FAIL);
        }
    }
    public void convertRow(HSSFRow row, T t) {

    }

    /**
     * 表格里表头和数据的对应关系
     * 按前端页面展示的顺序排列
     * @return
     */
    public List<KeyValue> keyValueRelation() {
        return null;
    }

    /**
     * 表头数组
     * @return
     */
    public String[] keyArray() {
        return null;
    }


}

接口调用：http://127.0.0.1:10002/api/feeAnalysis/export?current=1&size=20