Springboot结合axios导出mySql中数据

Springboot导出mySql数据为Excel

html部分

引入elemenUi相关文件

    <link rel="stylesheet" th:href="@{/element-ui/css/element.css}" type="text/css"/>
    <script th:src="@{/js/vue.min.js}"></script>
    <script th:src="@{/js/eui.js}"></script>
    <script th:src="@{/js/axios.min.js}"></script>

设置导出按钮

<!--                导出-->
<div style="float:left;margin-left: 10px">
       <el-button @click="exportData" icon="el-icon-upload2" plain type="primary">导出</el-button>
</div>

js部分 采用axios

	// 导出数据
	exportData() {
	axios({
	 method: "GET",
	 url: "/student/export",
	 params: {},
	 responseType: "blob"
	})
	 .then(res => {
	     let blob = new Blob([res.data], {type: "application/vnd.ms-excel"}); // 将服务端返回的文件流（二进制）excel文件转化为blob
	     let fileName = "学生列表.xls";
	
	     if (window.navigator && window.navigator.msSaveOrOpenBlob) {
	         // IE
	         window.navigator.msSaveOrOpenBlob(blob, fileName);
	     } else {
	         let objectUrl = (window.URL || window.webkitURL).createObjectURL(
	             blob
	         );
	         let downFile = document.createElement("a");
	         downFile.style.display = "none";
	         downFile.href = objectUrl;
	         downFile.download = fileName; // 下载后文件名
	         document.body.appendChild(downFile);
	         downFile.click();
	         document.body.removeChild(downFile); // 下载完成移除元素
	         // window.location.href = objectUrl
	         window.URL.revokeObjectURL(objectUrl); // 只要映射存在，Blob就不能进行垃圾回收，因此一旦不再需要引用，就必须小心撤销URL，释放掉blob对象。
	     }
	 })
	 .catch(err => {
	     console.log(err);
	 });
	},

引入相关依赖

    <!--mysql数据库驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <!--添加mybatis依赖-->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.1.1</version>
    </dependency>
    <!--添加lombok依赖-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
   </dependency>
    <!--引入poi-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.0.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.0.0</version>
    </dependency>

yaml配置或properties配置

//yaml
spring:
#  数据库配置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/graduate?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root

//properties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/graduate?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=root

编写导出数据实体类

@Data		//引入该注解能够省略get和set方法的书写
@TableName("stuinfo")
public class Student {
    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;
    private String sid;   //学号
    private String xm;   //姓名
    private String xb;   //性别
	......
    private String syszd;   //审核生源所在地
}

编写Mapper接口

@Mapper
public interface StuMapper extends BaseMapper<Student> {
}

编写Service接口

public interface StuService extends IService<Student> {

}

编写ServiceImpl实现类

@Service
public class StuServiceImpl extends ServiceImpl<StuMapper, Student> implements StuService {
}

编写Controller控制类

@Controller
public class ExcelController {
	//注意是否引入该注解
    @Autowired
    StuService stuService;
    /**
     *  数据导出为 excel
     * @param response
     * @throws Exception
     */
    @GetMapping("/student/export")
    public void export(HttpServletResponse response)  throws Exception{
        // 创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建sheet
        HSSFSheet sheet = workbook.createSheet("sheet1");
        String fileName = "学生列表.xls"; // 设置要导出的文件的名字
        // 获取数据集合  采用mybatis查询方法，null为无条件查询
        List<Student> list = stuService.list(null);
        // 生成标题行
        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("序号");
        row.createCell(1).setCellValue("学号");
        row.createCell(2).setCellValue("姓名");
        row.createCell(3).setCellValue("性别");
        row.createCell(4).setCellValue("所在院系");
        row.createCell(5).setCellValue("专业");
        row.createCell(6).setCellValue("班级");
        row.createCell(7).setCellValue("学历");
        row.createCell(8).setCellValue("身份证号");
        row.createCell(9).setCellValue("民族");
        row.createCell(10).setCellValue("政治面貌");
        row.createCell(11).setCellValue("培养方式");
        row.createCell(12).setCellValue("生源所在地");


        Student entity=null;
        for (int i = 0; i < list.size(); i++) {
            entity = list.get(i);
            row = sheet.createRow(i+1); // 从第2行开始填充数据
            // 序号  导出想要导出的字段
            row.createCell(0).setCellValue(String.valueOf(i+1));
            row.createCell(1, CellType.STRING).setCellValue(entity.getSid());
            row.createCell(2,CellType.STRING).setCellValue(entity.getXm());
            row.createCell(3).setCellValue(entity.getXb());
            row.createCell(4).setCellValue(entity.getYx());
            row.createCell(5).setCellValue(entity.getZy());
            row.createCell(6).setCellValue(entity.getBj());
            row.createCell(7,CellType.STRING).setCellValue(entity.getXl());
            row.createCell(8,CellType.STRING).setCellValue(entity.getSfzh());
            row.createCell(9,CellType.STRING).setCellValue(entity.getMz());
            row.createCell(10,CellType.STRING).setCellValue(entity.getZzmm());
            row.createCell(11,CellType.STRING).setCellValue(entity.getPyfs());
            row.createCell(12,CellType.STRING).setCellValue(entity.getSyszd());
//            row.createCell(8,CellType.STRING).setCellValue(DateUtil.format(entity.getEnrollDate(),"yyyy-MM-dd"));
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition",
                "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }
 }

测试结果

导出效果