springboot实现从数据库导出数据到Excel表格中（完整代码）

阳光不锈@

已于 2022-04-23 16:39:05 修改

阅读量6.1k

点赞数 14

分类专栏： springboot 文章标签： excel mybatis springboot 实现数据导出到excel

于 2021-07-05 14:30:18 首次发布

本文链接：https://blog.csdn.net/qq_35207086/article/details/118489074

版权

springboot 专栏收录该内容

22 篇文章 18 订阅

订阅专栏

该博客介绍了如何在Java项目中利用MyBatis、Apache POI库和前端Axios来实现在点击按钮后将数据库中的数据导出为Excel文件。主要步骤包括在`pom.xml`中添加Apache POI依赖，创建前端下载触发器，编写后端控制器处理数据导出，以及定义服务和Mapper接口获取数据。

摘要由CSDN通过智能技术生成

1、pom.xml,除了mybatis数据库依赖之外，要加上以下的依赖。

<!--   表格导出-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.xmlunit</groupId>
            <artifactId>xmlunit-core</artifactId>
        </dependency>

2.前端页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
</head>
<body>
<table border="0" style="margin-top:4px; margin-left: 18px">
    <tr>
        <td><a href="#" class="easyui-linkbutton" onclick="downloadfile();">数据导出</a></td>

    </tr>
<script>
    function downloadfile(){
        window.location.href="/UserExcelDownloads";
    }
</script>
</table>
</body>
</html>

3.配置文件

4.建立数据库

5.实体类

package com.wei.domain;

public class Teacher {
    private Integer tno;
    private String tName;
    private String type;
    private String tPassword;
    private String phone;
    private String e_mail;

    public Teacher() {
    }

    public Teacher(Integer tno, String tName, String type, String tPassword, String phone, String e_mail) {
        this.tno = tno;
        this.tName = tName;
        this.type = type;
        this.tPassword = tPassword;
        this.phone = phone;
        this.e_mail = e_mail;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "tno=" + tno +
                ", tName='" + tName + '\'' +
                ", type='" + type + '\'' +
                ", tPassword='" + tPassword + '\'' +
                ", phone='" + phone + '\'' +
                ", e_mail='" + e_mail + '\'' +
                '}';
    }

    public Integer getTno() {
        return tno;
    }

    public void setTno(Integer tno) {
        this.tno = tno;
    }

    public String gettName() {
        return tName;
    }

    public void settName(String tName) {
        this.tName = tName;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String gettPassword() {
        return tPassword;
    }

    public void settPassword(String tPassword) {
        this.tPassword = tPassword;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getE_mail() {
        return e_mail;
    }

    public void setE_mail(String e_mail) {
        this.e_mail = e_mail;
    }
}

6.mapper接口

package com.wei.mapper;

import com.wei.domain.Teacher;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface teacherMapper {
    @Select("select * from teacher")
    public List<Teacher> teacherinfor();


}

7.service层

package com.wei.service;

import com.wei.domain.Teacher;
import com.wei.mapper.teacherMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
@Service
public class teacherService {
    @Resource
    public teacherMapper teachermapper;
    public List<Teacher> teacherinfor(){
        return teachermapper.teacherinfor();
}

}

8.controller层

package com.wei.controller;

import com.wei.domain.Teacher;
import com.wei.service.teacherService;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@Controller
@RequestMapping("/")
public class teacherController {
    @Resource
    public teacherService teacherservice;

    @RequestMapping(value = "UserExcelDownloads", method = RequestMethod.GET)
    public void downloadAllClassmate(HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();//创建HSSFWorkbook对象,  excel的文档对象
        HSSFSheet sheet = workbook.createSheet("信息表"); //excel的表单

        List<Teacher> classmateList = teacherservice.teacherinfor();

        String fileName = "userinf"  + ".xls";//设置要导出的文件的名字
        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        String[] headers = { "学号", "姓名", "身份类型", "登录密码"};
        //headers表示excel表中第一行的表头
        HSSFRow row = sheet.createRow(0);
        //在excel表中添加表头
        for(int i=0;i<headers.length;i++){
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //在表中存放查询到的数据放入对应的列
        for (Teacher teacher : classmateList) {
            HSSFRow row1 = sheet.createRow(rowNum);
            row1.createCell(0).setCellValue(teacher.getTno());
            row1.createCell(1).setCellValue(teacher.gettName());
            row1.createCell(2).setCellValue(teacher.getType());
            row1.createCell(3).setCellValue(teacher.gettPassword());
            rowNum++;
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

}

9、运行效果