Controller数据导出Excel 详细教程——easypoi-base，easypoi-web，easypoi-annotation

- 日理万妓 2023-10-01 10:05 43阅读 0赞

### 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 访问你自己的路径**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pnYzU1OTg3_size_16_color_FFFFFF_t_70]  
**打开Excel 这时数据已经导到里面了**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pnYzU1OTg3_size_16_color_FFFFFF_t_70 1]  
**前面步骤都完成的，现在我们可以改造一下代码，读取数据库中的数据**  
**创建和数据表对应的实体类**

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中了**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pnYzU1OTg3_size_16_color_FFFFFF_t_70 2]  
**前台页面可以编写一个按钮，绑定一个单击事件**

**jq代码**

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pnYzU1OTg3_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190811180454171.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pnYzU1OTg3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pnYzU1OTg3_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190811180933100.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pnYzU1OTg3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pnYzU1OTg3_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190811182520582.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pnYzU1OTg3,size_16,color_FFFFFF,t_70