查表将数据转成Excel下载

设置表头

    @ApiOperation("导出指标数据表")
    @GetMapping("/download")
    public Result download(HttpServletRequest request, HttpServletResponse response,
                           @RequestParam("indexId") String indexId,
                           @RequestParam("indexName") String indexName,
                           @RequestParam("startDate") String startDate,
                           @RequestParam("endDate") String endDate,
                           @RequestParam("recordId") String recordId) throws IOException {
        String downFileName = String.format(indexName + "_%s-%s.xlsx",startDate,endDate);
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        response.setCharacterEncoding("utf-8");
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + ExcelUtils.encodeFileName(request.getHeader("User-Agent"),
                downFileName));
        List<Map<String, Object>> dataList = importIndexDataService.listImportDataByRecordId(indexId, recordId);

        ImportEnum dataEnum = ImportEnum.getByValue(indexId);
        if (dataEnum==null){
            dataEnum = ImportEnum.getByValue("other");
        }
        assert dataEnum != null;
        ExcelUtils.downExcel(dataList, Arrays.asList(dataEnum.getColumns()),Arrays.asList(dataEnum.getIndexList()),"导入指标数据表","万人成诉率指标详情表"response);
        return Result.success("下载成功");
    }

下载

 /**
     * 无合并单元格的下载
     * @param dataList 数据源
     *      * @param columns 字段名
     *      * @param indexList 指标名
     *      * @param sheetName 页签名字
     *      * @param tableName 表头名字
     *      * @param response 响应流
     * @throws IOException
     */
    public static void downExcel(List<Map<String, Object>> dataList,List<String> columns,List<String> indexList,String sheetName,String tableName,HttpServletResponse response) throws IOException {
        //数据处理,动态生成excel
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        //
        workbook.setSheetName(0, sheetName);
        //第一行作为表头行的时候的设置
        HSSFCellStyle styleTitle = ExcelUtils.getTitleStyle(workbook);//标题样式
        HSSFCellStyle styleContent = ExcelUtils.getContentStyle(workbook);//正文样式
        //表头数据
        //第一行
        Row titleRowOne = sheet.createRow(0);
        sheet.setDefaultColumnWidth(15);
        titleRowOne.setHeightInPoints(30f);
        for (int i = 0; i < columns.size(); i++) {
            Cell typeCell = titleRowOne.createCell(i);
            typeCell.setCellValue(tableName);
            typeCell.setCellStyle(styleTitle);
        }
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, columns.size()-1);
        sheet.addMergedRegion(cellRangeAddress);
        ExcelUtils.setBorderForMergeCell(BorderStyle.THIN, cellRangeAddress, sheet);

        //第二行
        Row titleRow = sheet.createRow(1);
        sheet.setDefaultColumnWidth(15);
        titleRow.setHeightInPoints(30f);
        for (int i = 0; i < columns.size(); i++) {
            String column = columns.get(i);
            Cell typeCell = titleRow.createCell(i);
            typeCell.setCellValue(column);
            typeCell.setCellStyle(styleTitle);
        }
        int row = 2;

        //获取表格输入的字段--必须按表格每列实际顺序存入数值
        for (Map<String, Object> map : dataList) {
            Row rowItem = sheet.createRow(row);
            for (int s = 0; s < indexList.size(); s++) {
                Cell cell = rowItem.createCell(s);
                cell.setCellValue(""+(map.get(indexList.get(s))==null?"":map.get(indexList.get(s))));
                cell.setCellStyle(styleContent);
            }
            row++;
        }
        workbook.write(response.getOutputStream());
    }