数据库数据准备

CREATE TABLE `people` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

填充数据

insert  into `people`(`id`,`name`,`status`,`content`) values (1,'zhangsan',1,'hello'),(2,'lisi',1,'word'),(3,'wangwu',1,'java');

简易页面

    
    



文档导出

    Excel导出

代码Demo

1、controller

@RestController
@RequestMapping("/excel")
public class ExcelController {

    @Autowired
    public ExcelService excelService;

    @RequestMapping("export")
    public String excelExport() {
        return excelService.excelExport();
    }
}

2、service

    @Override
    public String excelExport() {
        //根据id查询数据
        List peopleList = excelMapper.getPeopleById();
        if (peopleList == null || peopleList.isEmpty()) {
            return "failed";
        }
        //生成文件路径
        String newFilePath = "D:\\ExcelExport";
        //文件名称
        String fileName = "people.xlsx";
        //输出流
        OutputStream out = null;
        //Excel文件
        XSSFWorkbook workBook = new XSSFWorkbook();
        //Excel页脚
        XSSFSheet sheet = workBook.createSheet("数据导出");
        //设置列的宽度
        sheet.setDefaultColumnWidth(16);
        //创建标题行
        XSSFRow titleRow = sheet.createRow(0);
        String[] title = new String[]{"id", "name", "status", "content"};
        //设置标题字体样式
        XSSFCellStyle cellStyle = workBook.createCellStyle();
        XSSFFont font = workBook.createFont();
        font.setBold(true);//加粗
        font.setFontHeightInPoints((short) 14);//设置字体大小
        cellStyle.setFont(font);
        //设置标题列
        for (int i = 0; i < title.length; i++) {
            //创建标题的单元格
            XSSFCell titleCell = titleRow.createCell(i);
            //填充标题数值
            titleCell.setCellValue(title[i]);
            //设置样式
            titleCell.setCellStyle(cellStyle);
        }
        //填充数据
        //第一行是标题所以要从第二行开始
        for (int i = 0; i < peopleList.size(); i++) {
            People people = peopleList.get(i);
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < title.length; j++) {
                XSSFCell titleCell = row.createCell(j);
                String exportKey = title[j];
                switch (exportKey) {
                    case "id":
                        titleCell.setCellValue(people.getId());
                        break;
                    case "name":
                        titleCell.setCellValue(people.getName());
                        break;
                    case "status":
                        titleCell.setCellValue(people.getStatus());
                        break;
                    case "content":
                        titleCell.setCellValue(people.getContent());
                        break;
                }
            }
        }
        try {
            File file = new File(newFilePath + File.separator + fileName);
            out = new FileOutputStream(file);
            workBook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            log.info(e.getMessage());
        }
        return "success";
    }

3、mapper

<?xml version="1.0" encoding="UTF-8" ?>


    
        
        
        
        
    

    
        SELECT
           *
        FROM
            people t
    

4、bean对象

public class People {
    private String id;
    private String name;
    private String status;
    private String content;
。。。setterAndGetter。。。
}

结果

1、先准备一个空文件夹（例如D盘的ExcelExport）

2、点击页面“Excel导出”，提示“成功”

 3、查看目录中文件是否生成并打开查看文件内容

 补充：

参考学习博客：https://blog.csdn.net/w893932747/article/details/89354979