Controller数据导出Excel 详细教程

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

IDEA 2019-03版本 spring 3.x版本 tomcat 7.5 老项目 所以easypoi用的最老版本，根据朋友们自己使用的版本选择相应的版本
1：导入jar包 ps：Maven项目导入对应依赖即可

**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;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSerialNumber() {
		return serialNumber;
	}
	public void setSerialNumber(String serialNumber) {
		this.serialNumber = serialNumber;
	}
	public String getPublisher() {
		return publisher;
	}
	public void setPublisher(String publisher) {
		this.publisher = publisher;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public int getCreateBy() {
		return createBy;
	}
	public void setCreateBy(int createBy) {
		this.createBy = createBy;
	}
	public Date getCreationDate() {
		return creationDate;
	}
	public void setCreationDate(Date creationDate) {
		this.creationDate = creationDate;
	}
	public int getTotal() {
		return total;
	}
	public void setTotal(int total) {
		this.total = total;
	}
	public double getPrice() {
		return price;
	}
	public void setPrice(double price) {
		this.price = price;
	}
	public String getDetail() {
		return detail;
	}
	public void setDetail(String detail) {
		this.detail = detail;
	}
	public int getStatus() {
		return status;
	}
	public void setStatus(int status) {
		this.status = status;
	}
	public String getReserve() {
		return reserve;
	}
	public void setReserve(String reserve) {
		this.reserve = reserve;
	}
	public String getSpare() {
		return spare;
	}
	public void setSpare(String spare) {
		this.spare = spare;
	}
	public String getStandby() {
		return standby;
	}
	public void setStandby(String standby) {
		this.standby = standby;
	}

}

查询数据库表数据

@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("导出成功");
			} 	
		}
	});
}