easypoi(生成Excel表格)
1.基本注解
@ExcelTarget("user") //设置表头 @Excel(name = "姓名",orderNum = "2") //设置列名 @Excel(name = "编号",orderNum = "1") //orderNum 排序,数值越小越靠前,默认为0 @Excel(name = "性别",orderNum = "3",replace = {"男_0","女_1"}) //replace 替换 0替换男 @Excel(name = "生日",orderNum = "4",width = 100) //width 设置宽度 @Excel(name = "日期",orderNum = "5",format = "yyyy年MM月dd日") //format 日期格式化 @Excel(name = "百分比",orderNum = "4",suffix = "%") //suffix 添加尾缀 @ExcelIgnore //忽略
@ExcelEntity //表示该属性是一个实体类 private Card card; user,setCard(new Card("001","武汉市")) @ExcelCollection(name = "订单信息") //表示该属性是一个集合 private List<Order> orders; user.setOrder(Arrays.asList(new Order("01","小米"),new Order("02","华为")))
2.导出
1.导入起步依赖
<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-boot-starter</artifactId> <version>4.0.0</version> </dependency>
允许定义相同的bean对象 去覆盖原有的
spring: main: allow-bean-definition-overriding: true
2.编写实体类 序列化 并 配置注解
User
@Data @NoArgsConstructor @AllArgsConstructor @ExcelTarget("用户列表") public class User implements Serializable { @Excel(name = "编号",orderNum = "1") private Integer id; @Excel(name = "姓名",orderNum = "2") private String name; @Excel(name = "年龄",orderNum = "4",suffix = "岁") private Integer age; @Excel(name="性别",orderNum = "3") private String gender; @Excel(name="生日",orderNum = "5",format = "yyyy年MM月dd日",width = 40) private Date birthday; @ExcelEntity private Details details; @ExcelCollection(name = "订单信息",orderNum = "10") private List<Order> orderList; }
Details
@Data @NoArgsConstructor @AllArgsConstructor @ExcelTarget("用户细节") public class Details implements Serializable { @Excel(name="用户名",orderNum = "6") private String username; @Excel(name="密码",orderNum = "7") private String password; }
Order
@Data @NoArgsConstructor @AllArgsConstructor @ExcelTarget("订单") public class Order implements Serializable { @Excel(name="订单编号",orderNum = "8") private String id; @Excel(name="商品名称",orderNum = "9") private String name; }
3.编写配置类
package com.fy.demo; import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import org.apache.poi.ss.usermodel.Workbook; import java.io.File; import java.io.FileOutputStream; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.List; public class ExportDemo { public static void main(String[] args) throws Exception{ //1.导出设置 ExportParams exportParams = new ExportParams(); exportParams.setTitle("用户列表"); //设置表格头 exportParams.setSheetName("用户列表sheet"); //设置表名 //2.数据准备 List<User> users = new ArrayList<User>(){{ add(new User(1,"张三",89,"男",new Date(),new Details("三三","123"),Arrays.asList(new Order("0100","小米"),new Order("0120","小米10")))); add(new User(2,"李四",78,"男",new Date(),new Details("ss","123"),Arrays.asList(new Order("0200","华为"),new Order("0220","华为60")))); add(new User(3,"王五",67,"男",new Date(),new Details("ww","123"),Arrays.asList(new Order("0300","vivo"),new Order("0330","vivox21")))); }}; Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, users); //3.文件输出 FileOutputStream fileOutputStream = new FileOutputStream( new File("C:\\Users\\方子琰\\OneDrive\\桌面\\sprint-vue-kaoshixitong\\user.xls") ); workbook.write(fileOutputStream); fileOutputStream.close(); workbook.close(); } }
3.导入
1.编写映射 的实体类
...
2.编写配置类
package com.fy.demo; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ImportParams; import java.io.File; import java.io.FileInputStream; import java.util.List; public class ImportDemo { public static void main(String[] args) throws Exception{ //1.文件输入流 FileInputStream fileInputStream = new FileInputStream( new File("C:\\Users\\方子琰\\OneDrive\\桌面\\sprint-vue-kaoshixitong\\user.xls") ); ImportParams importParams = new ImportParams(); importParams.setTitleRows(1); //指定标题占用行数 importParams.setHeadRows(2); //指定表头占用行数 importParams.setSheetNum(1); List<User> userList = ExcelImportUtil.importExcel(fileInputStream, User.class, importParams); userList.forEach(System.out::println); } }
4.试题导出功能
//导出
@GetMapping("export")
public void export(HttpServletResponse response)throws Exception{
//获取数据
List<SysUser> users = userMapper.findAll();
//导出
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息", "user_list")
, SysUser.class, users);
//设置响应
response.setHeader("content-disposition","attachment;fileName="+ URLEncoder.encode("用户列表.xls","UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=utf-8");
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.close();
workbook.close();
}