Excel轻松操控：掌握EasyPoi的妙用技巧（一）https://developer.aliyun.com/article/1480477
2.2.2.2 身份证实体类
/** * @ClassName: Card * @Description: 身份证 * @author: 莫提 * @date 2020/11/27 8:55 * @Version: 1.0 */ @ExcelTarget("card") @Data @AllArgsConstructor public class Card implements Serializable { /** * 身份证号 */ @Excel(name = "身份证号码",width = 20.0,orderNum = "6") private String number; /** * 地址 */ @Excel(name = "住址",width = 50.0,orderNum = "7") private String address; }
2.2.2.3 订单实体类
/** * @ClassName: Order * @Description: 订单 * @author: 莫提 * @date 2020/11/27 8:55 * @Version: 1.0 */ @ExcelTarget("orders") @Data @AllArgsConstructor public class Order implements Serializable { /** * 订单编号 */ @Excel(name = "订单编号",orderNum = "8",width = 20.0) private String num; /** * 订单名称 */ @Excel(name = "订单名称",orderNum = "9",width = 20.0) private String name; }
2.2.2.4 员工实体类
/** * @ClassName: Employee * @Description: 导入的员工实体 * @author: 莫提 * @date 2020/11/27 8:55 * @Version: 1.0 */ @ExcelTarget("employee") @Data @NoArgsConstructor @AllArgsConstructor public class Employee { /** * 用户 ID */ @Excel(name = "编号",suffix = "号") private Integer id; /** * 姓名 */ @Excel(name = "姓名") private String name; /** * 性别 */ @Excel(name = "性别") private String sex; /** * 生日 */ @Excel(name = "生日",format = "yyyy-MM-dd HH:mm:ss") private Date birthday; /** * 用户状态：【1:正常】【0:封禁】 */ @Excel(name = "用户状态",replace = {"正常_1","封禁_0"}) private Integer status; }
2.2.3 创建测试类
2.2.3.1 导出测试
public class TestExport { /** * 获取全部用户 */ public List<User> getAllUsers(){ List<User> list = new ArrayList<>(); // 创建身份证信息 Card card1 = new Card("11111","江西省南昌市"); Card card2 = new Card("22222","四川省成都市"); Card card3 = new Card("33333","河北省唐山市"); // 创建订单信息 Order order1 = new Order("1","泡面"); Order order2 = new Order("2","外套"); Order order3 = new Order("3","裤子"); Order order4 = new Order("4","AJ1"); Order order5 = new Order("5","NICK"); Order order6 = new Order("6","笔记本"); Order order7 = new Order("7","键盘"); Order order8 = new Order("8","鼠标"); list.add(User.builder() .id(1).name("张三").sex("男").status(0).password("123456").birthday(new Date()) .hobbies(Arrays.asList("唱歌", "跳舞")).card(card1).orders(Arrays.asList(order1,order2)) .photo("E:\\头像\\avatar.jpg") .build()); list.add(User.builder() .id(2).name("李四").sex("男").status(1).password("123456").birthday(new Date()) .hobbies(Arrays.asList("睡觉", "游戏")).card(card2).orders(Arrays.asList(order3,order4,order5)) .photo("E:\\头像\\1.jpg") .build()); list.add(User.builder() .id(3).name("丽丽").sex("女").status(0).password("123456").birthday(new Date()) .hobbies(Arrays.asList("技术", "代码")).card(card3).orders(Arrays.asList(order6,order7,order8)) .photo("E:\\头像\\2.jpg") .build()); return list; } /** * 导出Excel表格 */ @Test public void testExport() throws IOException { // 获取全部用户 List<User> users = getAllUsers(); // 导出到文件 FileOutputStream outputStream = new FileOutputStream("E:\\导出.xls"); // 配置Excel文件信息 ExportParams params = new ExportParams(); params.setTitle("用户信息"); params.setSheetName("用户信息1表"); // 导出Excel表格 Workbook workbook = ExcelExportUtil.exportExcel(params, User.class, users); workbook.write(outputStream); // 关闭资源 outputStream.close(); workbook.close(); } }
2.2.3.2 导入测试
public class TestImport { /** * 导入 */ @Test public void testImport() throws Exception { // 参数1：文件流 FileInputStream stream = new FileInputStream("E:\\导入.xls"); // 参数2：导入类型 ImportParams params = new ImportParams(); // 标题占用多少行 params.setTitleRows(1); // 头部属性占用多少行 params.setHeadRows(1); // 从指定的sheet的下标开始读取 // params.setStartSheetIndex(1); // 读取sheet的数量，需要和上面的配合 // params.setSheetNum(1); // 对Excle进行合法参数校验 params.setImportFields(new String[]{"编号"}); // 参数3：导出的数据结合 List<Employee> employees = ExcelImportUtil.importExcel(stream, Employee.class, params); for (Employee employee : employees) { System.out.println("employee = " + employee); } } }
2.3 SpringBoot整合EasyPOI
2.3.1 导入依赖
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.20</version> </dependency> <!--引入Mybatis的ehCache的适配--> <dependency> <groupId>org.mybatis.caches</groupId> <artifactId>mybatis-ehcache</artifactId> <version>1.0.3</version> </dependency> <!--引入分页插件的依赖--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.1.10</version> </dependency> <!--引入EasyPOI--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>4.1.0</version> </dependency> </dependencies>
2.3.2 修改配置文件
# 配置应用相关 server: servlet: context-path: / session: timeout: 60m tomcat: uri-encoding: UTF-8 # 配置MyBatis相关 mybatis: config-location: classpath:mybatis/mybatis-config.xml mapper-locations: classpath:mybatis/mapper/*.xml spring: # 禁用ThymeLeaf缓存 thymeleaf: cache: false # 配置数据源 datasource: username: root password: 983934 url: jdbc:mysql://127.0.0.1:3306/learn?serverTimezone=Hongkong&useAffectedRows=true driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true filters: stat,wall,log4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 servlet: multipart: max-file-size: 10MB max-request-size: 10MB
2.3.3 创建数据表
SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `user` -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID', `user_name` varchar(50) DEFAULT NULL COMMENT '用户名', `work_age` int(2) DEFAULT NULL COMMENT '工龄', `sex` int(1) DEFAULT NULL COMMENT '性别', `birthday` date DEFAULT NULL COMMENT '生日', `dept_id` int(11) DEFAULT NULL COMMENT '部门ID', PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=18433 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `department` -- ---------------------------- DROP TABLE IF EXISTS `department`; CREATE TABLE `department` ( `dept_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门ID', `dept_name` varchar(20) DEFAULT NULL, `address` varchar(100) DEFAULT NULL COMMENT '地址', PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
2.3.4 逆向工程生成文件
- 生成Mapper.Java
- 生成Mapper.xml
- 生成Service.java
- 生成ServiceImpl.java
- 生成Entity.java
生成基本的增删查改方法
Excel轻松操控：掌握EasyPoi的妙用技巧（三）https://developer.aliyun.com/article/1480481
