使用idea,SpringBoot数据导出成Excel

注意:本文参照https://www.cnblogs.com/lin02/p/11648899.html

1.表设计在这里插入图片描述

2.pom.xml文件添加依赖

<!--        poi实现excel导入导出-->
        <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>

3.写Controller层

@RestController
@RequestMapping("/export")
public class UserExportController {

    @Autowired
    private UserExportService userExportService;

    @RequestMapping(value = "/user",method = RequestMethod.GET)
    public void userExcel(HttpServletResponse response) throws IOException {
        XSSFWorkbook wb = userExportService.show();
        String fileName = "User报表.xlsx";
        OutputStream outputStream = null;
        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();
    }
}

4.写Service层

@Service
public class UserExportService {

    @Autowired
    private UserExportMapper userExportMapper;

    public XSSFWorkbook show() {
        List<User> list = userExportMapper.selectByExample(null);//查出数据库数据
        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Goods");//创建一张表
        Row titleRow = sheet.createRow(0);//创建第一行,起始为0
        titleRow.createCell(0).setCellValue("uid");//第一列
        titleRow.createCell(1).setCellValue("用户名");
        titleRow.createCell(2).setCellValue("密码");
        titleRow.createCell(3).setCellValue("姓名");
        titleRow.createCell(4).setCellValue("年龄");
        int cell = 1;
        for (User users : list) {
            Row row = sheet.createRow(cell);//从第二行开始保存数据
            row.createCell(0).setCellValue(cell);
            row.createCell(1).setCellValue(users.getUsername());//将数据库的数据遍历出来
            row.createCell(2).setCellValue(users.getPawd());
            row.createCell(3).setCellValue(users.getUname());
            row.createCell(4).setCellValue(users.getAge());
            cell++;
        }
        return wb;
    }
}

5.写Mapper层

public interface UserExportMapper {
    List<User> selectByExample(Object o);
}

6.写Mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo1.userMapper.UserExportMapper">
    <select id="selectByExample" resultType="com.example.demo1.userDomain.User">
        select * from user
    </select>
</mapper>

7.进行测试
浏览器网址栏:http://localhost:8081/export/user
注意:有些人端口默认为8080,如果有和我一样的新手,访问时出错,请将8081改成8080

8.导出截图
在这里插入图片描述

9.核心思想
将数据库中的数据查出,然后使用io流输出到excel表格中

10.本文参照https://www.cnblogs.com/lin02/p/11648899.html

  • 1
    点赞
  • 0
    评论
  • 4
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 程序猿惹谁了 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值