将MySQL数据获取成Excel表格样式，统计数据

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>

    @GetMapping("/download")
    public Object download(HttpServletResponse response) {
        //创建查询构造器
        LambdaQueryWrapper<Police> queryWrapper = new QueryWrapper<Police>().lambda();
        //设置查询条件
        queryWrapper.orderByAsc(Police::getMath);
        List<Police> userList = policeMapper.selectList(queryWrapper);
        ExcelUtil.writeExcel(userList, response);
        return "ok";
    }

格式化下载

public class ExcelUtil {
    /**
     * 使用poi 将查询出数据写入excel
     */
    public static void writeExcel(List<Police> list, HttpServletResponse response) {
        Workbook workbook = null;
        try {
            //创建工作簿
            workbook = exportExcel(list);
            //将excel写入到输出流中
            //workbook.write(Files.newOutputStream(Paths.get("D:\\test.xlsx")));

            //使用流下载excel 浏览器自动下载
            response.setContentType("application/vnd.ms-excel");
            //设置响应头信息 以附件形式下载 文件名为学生成绩.xlsx 进行UTF-8编码
            String encode = URLEncoder.encode("学生成绩.xlsx", String.valueOf(StandardCharsets.UTF_8));
            response.setHeader("Content-disposition", "attachment;filename=" + encode);
            //将excel写入到输出流中
            workbook.write(response.getOutputStream());


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 创建单元格
     *
     * @param list 数据
     * @return Workbook
     */
    public static Workbook exportExcel(List<Police> list) {
        //创建工作簿
        Workbook workbook = new XSSFWorkbook();
        //单元格 背景色黄色
        CellStyle cellStyle = workbook.createCellStyle();
        // 设置水平和垂直对齐方式为居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置填充模式为实心前景色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置填充前景色为黄色
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        //设置其他格式
        CellStyle style = workbook.createCellStyle();
        // 设置水平和垂直对齐方式为居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建工作表
        Sheet sheet = workbook.createSheet();
        //创建行
        Row row = sheet.createRow(0);
        //创建单元格
        Cell cell = row.createCell(0);
        //设置单元格内容
        cell.setCellValue("姓名");
        //创建单元格
        Cell cell1 = row.createCell(1);
        //设置单元格内容
        cell1.setCellValue("成绩");
        row.getCell(0).setCellStyle(cellStyle);
        row.getCell(1).setCellStyle(cellStyle);
        for (Police user : list) {
            //创建行
            Row row1 = sheet.createRow(sheet.getLastRowNum() + 1);
            //创建单元格
            Cell cell2 = row1.createCell(0);
            //设置单元格内容
            cell2.setCellValue(user.getName());
            cell2.setCellStyle(style);
            //创建单元格
            Cell cell3 = row1.createCell(1);
            //设置单元格内容
            cell3.setCellValue(user.getMath());
            cell3.setCellStyle(style);
        }
        //创建空行
        for (int i = 0; i < 2; i++) {
            Row row1 = sheet.createRow(sheet.getLastRowNum() + 1);
            //创建单元格
            Cell cell2 = row1.createCell(0);
            //设置单元格内容
            cell2.setCellValue("");
        }


        String[] str = {"50以下", "50-59", "60-69", "70-79", "80-89", "90-100"};

        //成绩档次统计
        int[] count = new int[6];
        for (Police user : list) {
            int i = user.getMath();
            if (i < 50) {
                count[0]++;
            } else if (i < 60) {
                count[1]++;
            } else if (i < 70) {
                count[2]++;
            } else if (i < 80) {
                count[3]++;
            } else if (i < 90) {
                count[4]++;
            } else {
                count[5]++;
            }
        }
        //将成绩档次写入excel
        for (int i = 0; i < str.length; i++) {
            Row row1 = sheet.createRow(sheet.getLastRowNum() + 1);
            //创建单元格
            Cell cell2 = row1.createCell(0);
            cell2.setCellStyle(cellStyle);
            //设置单元格内容
            cell2.setCellValue(str[i]);
            //创建单元格
            Cell cell3 = row1.createCell(1);
            cell3.setCellStyle(cellStyle);
            //设置单元格内容
            cell3.setCellValue(count[i]);
        }


        return workbook;
    }
}