依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
编写实体类,映射表头
记得 继承 BaseRowModel 类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import java.math.BigDecimal;
/**
* @author Yuan.
* @description StudentVo
* @since 2019/3/9 12:59
*/
@Data
@EqualsAndHashCode(callSuper = true) //作用是 继承父类的属性,false 不继承,默认是false
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER) //设置数据水平居中
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 22) //设置标题颜色
public class StudentVo extends BaseRowModel {
/**
* 如果直接使用实体类去继承BaseRowModel,那么当持久层去数据库查询实体类时会带上cell_style_map字段,而这个字段在数据表中时没有的,所以会出现异常,
* 应该另外定义一个传输对象继承BaseRowModel,用于专门实现报表的导出
*/
@ExcelProperty(value = {"姓名"}, index = 1)
private String name; //姓名
@ExcelProperty(value = {"性别"}, index = 2)
private String sex; //性别
@ExcelProperty(value = {"年龄"}, index = 3)
private int age; //年龄
@ExcelProperty(value = {"身高"}, index = 4)
private String stature; //身高
@ExcelProperty(value = {"体重"}, index = 5)
private BigDecimal weight; //体重
}
***** 如果直接使用实体类去继承BaseRowModel,那么当持久层去数据库查询实体类时会带上cell_style_map字段,而
这个字段在数据表中时没有的,所以会出现异常,应该另外定义一个传输对象继承BaseRowModel,用于专门实现报表的导出
controller 控制层
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.yuan.service.StudentService;
import com.yuan.vo.StudentVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
/**
* @author Yuan.
* @description StudentController
* @since 2019/3/9 14:54
*/
@RestController
public class StudentController {
@Autowired
private StudentService studentService;
@GetMapping("/exportExcel")
public void export(HttpServletResponse response) throws IOException {
List<StudentVo> list = studentService.getStudentList();
ServletOutputStream out = response.getOutputStream();
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
String fileName = "学生信息表";
Sheet sheet = new Sheet(1, 0,StudentVo.class);
//设置自适应宽度
sheet.setAutoWidth(Boolean.TRUE);
// 第一个 sheet 名称
sheet.setSheetName("学生信息");
writer.write(list, sheet);
//通知浏览器以附件的形式下载处理,设置返回头要注意文件名有中文
response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xlsx");
writer.finish();
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
out.flush();
}
}
测试结果
搞定收工!