Spring Boot集成Easypoi导出Excel

爱被打了一巴掌 2023-10-04 20:03 7阅读 0赞

**目录**

1.引入Maven依赖

2.实体类属性加上@Excel注解

3.导出示例

4.导出结果

5.注解@Excel说明

--------------------

## 1.引入Maven依赖 ##

<dependency>
    	<groupId>cn.afterturn</groupId>
    	<artifactId>easypoi-spring-boot-starter</artifactId>
    	<version>4.3.0</version>
    </dependency>

--------------------

## 2.实体类属性加上@Excel注解 ##

> 需要导出的属性才加上@Excel注解，不加注解的属性不会导出

package com.test.entity;
    
    import java.util.Date;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    import lombok.Data;
    
    /**
     * 测试实体
     */
    @Data
    public class ExcelVO {
    
    	/**
    	 * 主键ID
    	 */
    	private String id;
        /**
         * 用户名
         */
    	@Excel(name = "用户名", orderNum = "1", width = 10)
        private String name;
    	/**
    	 * 年龄
    	 */
    	@Excel(name = "年龄", orderNum = "2", type = 10, width = 10)
    	private Integer age;
    	/**
    	 * 性别: 1.男 2.女
    	 */
    	@Excel(name = "性别", orderNum = "3", replace = {"男_1", "女_2"}, width = 10)
    	private Integer sex;
    	/**
         * 备注
         */
    	@Excel(name = "备注", orderNum = "4", width = 20)
        private String remark;
    	/**
    	 * 创建时间
    	 */
    	@Excel(name = "创建时间", orderNum = "5", width = 20, exportFormat = "yyyy-MM-dd HH:mm:ss")
    	private Date createTime;
    
    }

--------------------

## 3.导出示例 ##

> 建议用浏览器测试，如果用postman导出execl文件名会为response.xls，是正常的
> 
> [postman导出execl文件名为response.xls问题\_涛哥是个大帅比的博客-CSDN博客][postman_execl_response.xls_-CSDN]

package com.test.controller;
    
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.List;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.test.entity.ExcelVO;
    
    import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    
    /**
     * excel导出测试
     */
    @RestController
    @RequestMapping("/excel")
    public class ExcelController {
    
        @RequestMapping(value = "/export", method = RequestMethod.GET)
        public void export(HttpServletResponse response) {
        	/*
        	 * 测试数据
        	 */
        	List<ExcelVO> list = new ArrayList<ExcelVO>();
        	for (int i = 0; i < 10; i++) {
        		ExcelVO excelVO = new ExcelVO();
        		excelVO.setName("张三-"+i);
        		excelVO.setSex(i%2==0?1:2);
        		excelVO.setAge(i+10);
        		excelVO.setRemark("备注-"+i);
        		excelVO.setCreateTime(new Date());
        		list.add(excelVO);
    		}
        	
        	try {
        		
    			//导出
    			String fileName = "用户列表";
    			/*
    			 * @param title	标题名称，表格第一行会多一行标题，不需要设置为null
    			 * @param sheetName	sheet页的名称
    			 */
    			ExportParams exportParams = new ExportParams(null, fileName);
    			/*
    		     * @param entity    表格标题属性
    		     * @param pojoClass Excel对象Class
    		     * @param dataSet   Excel对象数据List
    		     */
    			Workbook workbook = ExcelExportUtil.exportExcel(exportParams, ExcelVO.class, list);
    			
    			//当前时间作为文件名
    			Calendar calendar = Calendar.getInstance();
    			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
    			String nowDate = sdf.format(calendar.getTime());
    			fileName += nowDate;
    			
    			// 设置输出的格式
    			response.setCharacterEncoding("UTF-8");
    			response.addHeader("content-type", "application/vnd.ms-excel");
    			response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");	// 设置下载后的文件名
    			OutputStream os = response.getOutputStream();
    			workbook.write(os);
    			
    			//关闭流
    			os.flush();
    			os.close();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
        }
        
        
    }

--------------------

## 4.导出结果 ##

![42c58e61906640f48adc4b25bd892a78.png][]

## 5.注解@Excel说明 ##

<table style="width:680px;"> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>exportFormat</td> 
   <td>导出的时间格式，示例：exportFormat = "yyyy-MM-dd HH:mm:ss"</td> 
  </tr> 
  <tr> 
   <td>isWrap</td> 
   <td>是否换行 即支持\n，默认true</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>列名</td> 
  </tr> 
  <tr> 
   <td>orderNum</td> 
   <td>排序，升序方式</td> 
  </tr> 
  <tr> 
   <td>replace</td> 
   <td>值替换 &nbsp;格式{a_id,b_id}，示例：replace = {"男_1", "女_2"}</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本</td> 
  </tr> 
  <tr> 
   <td>width</td> 
   <td>每个列的宽 单位为字符，一个汉字=2个字符，限制1-255</td> 
  </tr> 
  <tr> 
   <td>isColumnHidden</td> 
   <td>是否需要隐藏该列 默认false</td> 
  </tr> 
 </tbody> 
</table>

[postman_execl_response.xls_-CSDN]: https://blog.csdn.net/u011974797/article/details/128033721
[42c58e61906640f48adc4b25bd892a78.png]: https://img-blog.csdnimg.cn/42c58e61906640f48adc4b25bd892a78.png