Controller获取数据导出Excel，详细教程

1：导入对应依赖

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.3</version>
        </dependency>

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.3</version>
        </dependency>

2：创建实体类 User

public class User {
	//name代表 表头行 orderNum 代表 排序顺序
    @Excel(name="姓名",orderNum = "0")
    private String name;
    @Excel(name="性别",orderNum = "1")
    private String sex;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

创建工具类 FileUtil工具类，固定写法，拿去直接用就可以

package hr.manage.util;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

public class FileUtil {

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    //设置导出文件内的内容
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    //设置导出文件名，编码
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            //让服务器告诉浏览器它发送的数据属于什么文件类型
            response.setHeader("content-Type", "application/vnd.ms-excel");
            //当Content-Type 的类型为要下载的类型时 , 这个信息头会告诉浏览器这个文件的名字和类型。
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            // throw new NormalException(e.getMessage());
        }
    }

}

创建UserController

package hr.manage.controller.book;
import hr.manage.entity.User;
import hr.manage.util.FileUtil;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
//导出数据为Excel
@RequestMapping("/user")
@Controller
public class UserController {

    @RequestMapping("/excel")
    public void exceladd(HttpServletResponse response){
        List<User>list = new ArrayList<>();
        list.add(new User("张三","男"));
        list.add(new User("李四","女"));
        // 参数
        // 1：传入数据集合，2：标题，3：工作表名称 4：实体类的Class 5：保存的Excel名字，传入HttpServletResponse
        FileUtil.exportExcel(list,"花名册","员工",User.class,"花名册.xls",response);
    }

开启tomcat 访问你自己的路径

打开Excel 这时数据已经导到里面了

前面步骤都完成的，现在我们可以改造一下代码，读取数据库中的数据
创建和数据表对应的实体类

package hr.manage.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;

import java.util.Date;

import javax.xml.soap.Detail;

public class CwgBookManager {
	//主键id
	private Integer id;
	//编号
	@Excel(name = "编号",orderNum = "0")
	private String number;
	//书名
	@Excel(name = "书名",orderNum = "1")
	private String name;
	//图书号
	@Excel(name = "图书编号",orderNum = "2")
	private String serialNumber;
	//出版社
	@Excel(name = "出版社",orderNum = "3")
	private String publisher;
	//作者
	@Excel(name = "作者",orderNum = "4")
	private String author;
	//创建人
	@Excel(name = "创建人",orderNum = "5")
	private int createBy;
	//创建时间
	@Excel(name = "创建时间",exportFormat ="yyyy-MM-dd", orderNum = "6")
	private Date creationDate;
	//册数，数量
	@Excel(name = "册数",orderNum = "7")
	private int total;
	//价格
	@Excel(name = "价格",orderNum = "8")
	private double price;
	//备注，详情
	@Excel(name = "备注",orderNum = "9")
	private String detail;
// 状态0：无备案，1，有备案
	@Excel(name = "备案状态",orderNum = "10",type = 10)
	private int status;
	//备用字段1
	private String reserve;
	//备用字段2
	private String spare;
	//备用字段3
	private String standby;

	public Integer getId() {
		return id;
	}
	
	//getter,setter方法略
}

查询数据库表数据

@Repository
public interface BookDao {
	/** * 查询book表数据 * @return */
	@Select("SELECT * FROM cwg_bookmanager")
	public List<CwgBookManager> findBookList();

编写Controller

package hr.manage.controller.book;
import hr.manage.dao.book.BookDao;
import hr.manage.entity.CwgBookManager;
import hr.manage.util.FileUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
/** * 导出Execl */
@Controller
@RequestMapping("/hrm/book")
public class ExcelBookController {

    @Resource
    private BookDao book;

    @RequestMapping("/demo")
    public void execl(HttpServletResponse response) throws IOException {
    	//对应你们查询数据库方法
        List<CwgBookManager> list = book.findBookList();
        System.out.println("Excel");
        FileUtil.exportExcel(list,"图书信息","张三",CwgBookManager.class,"图书.xls",response);
    }
}

访问该Controller路径即可
打开Excel文件 这是可以看到数据库内的数据都导入到Excel中了

前台页面可以编写一个按钮，绑定一个单击事件

jq代码

function excel(){
	$.ajax({
		url:'/HRManagement/hrm/book/demo',
		type:'get',
		success:function (data) {
			if (data != null){
				alert("导出成功");
			} 	
		}
	});
}