Springboot导出数据库数据为Excle文件

本文借助Poi导出数据库数据为Excle文件

导入依赖

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

准备好要导出的数据库表，实体类等

service具体业务层

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    public XSSFWorkbook show() {
        List<User> list = userMapper.list(null);//查出数据库数据
        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("users");//创建一张表
        Row titleRow = sheet.createRow(0);//创建第一行，起始为0
        titleRow.createCell(0).setCellValue("序号");//第一列
        titleRow.createCell(1).setCellValue("id");
        titleRow.createCell(2).setCellValue("姓名");
        titleRow.createCell(3).setCellValue("性别");
        int cell = 1;
        for (User users : list) {
            Row row = sheet.createRow(cell);//从第二行开始保存数据
            row.createCell(0).setCellValue(cell);
            row.createCell(1).setCellValue(users.getId());//将数据库的数据遍历出来
            row.createCell(2).setCellValue(users.getName());
            row.createCell(3).setCellValue(users.getSex());
            cell++;
        }
        return wb;
    }
}

Controller

@Controller
public class UserController {
    @Autowired
    private UserService userService;

    @RequestMapping(value = "/export/users", method = RequestMethod.GET)
    public void usersExcel(HttpServletResponse response) {
        XSSFWorkbook wb =userService.show();
        String fileName = "user.xlsx";  //或者为 .xls
        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();
        }
    }
}

运行项目，浏览器访问localhost:8080/export/users即可看到下载的excle文件。