springboot实现数据库数据导出生成Excel报表

一.导入poi依赖

<!--        poi实现excel导入导出-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

主要用到该依赖下的两个类：XSSFWorkbook和HSSFWorkbook类。

XSSFWorkbook是Excel中的xlsx版本,HSSFWorkbook是xls版本,我使用的是xlsx版本的。

二.详细代码

 业务层：

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;
    
    @Override
    public XSSFWorkbook downloadAssociatedUser() {
        List<User> list = userMapper.selectByExample(null);//查出数据库数据
        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("UserList");//创建一张表
        Row titleRow = sheet.createRow(0);//创建第一行，起始为0
        titleRow.createCell(0).setCellValue("账号");//第一列
        titleRow.createCell(1).setCellValue("名称");
        titleRow.createCell(2).setCellValue("权限来源");
        titleRow.createCell(3).setCellValue("账号状态");
        int cell = 1;
        for (User user : list) {
            Row row = sheet.createRow(cell);//从第二行开始保存数据
            row.createCell(0).setCellValue(user.getUserAccount());
            row.createCell(1).setCellValue(user.getUserName());//将数据库的数据遍历出来
            row.createCell(2).setCellValue(user.getFromAuth());
            row.createCell(3).setCellValue(user.getStatus());
            cell++;
        }
        return wb;
    }

 控制层:

@Controller
public class UserController {

        @Autowired
        private UserService userService;
        
        @RequestMapping(value = "/user/download",method = RequestMethod.GET)
        public void userExcel(HttpServletResponse response) {
            XSSFWorkbook wb = userService.downloadAssociatedUser();
            String fileName = "用户清单" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + ".xlsx";
            OutputStream outputStream = null;
            try {
                fileName = URLEncoder.encode(fileName,"UTF-8");
                //设置ContentType请求信息格式
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName);
                outputStream = response.getOutputStream();
                wb.write(outputStream);
                outputStream.flush();
                outputStream.close();
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
}

参考文章：springboot实现数据库数据导出生成Excel报表 - changlinlo - 博客园