EasyPoi的使用-第五天🚲
导入Excel之基本数据导入
1、准备一个将要导入的excel文档(importExcel.xlsx),数据打开如下：
2、在实体类包（entity）下新建实体类，命名（Emp）
Emp.java👀
package com.demo1poi.day01.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;
import java.util.Date;
@Data
@ExcelTarget("emps")
public class Emp {
@Excel(name = "编号")
private String id;
@Excel(name = "姓名")
private String name;
@Excel(name = "年龄")
private Integer age;
@Excel(name = "生日",format = "yyyy-MM-dd HH:mm:ss")
private Date bir;
@Excel(name = "状态",replace = {"激活_1","未激活_0"})
private String status;
}
3、修改TestPoiDay01.java
package com.demo1poi.day01.test;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.demo1poi.day01.entity.Card;
import com.demo1poi.day01.entity.Emp;
import com.demo1poi.day01.entity.Order;
import com.demo1poi.day01.entity.User;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
/**
* @author 安静的茶叶
*/
public class TestPoiDay01 {
/**
* 查询所有用户
*/
public List<User> getUsers() {
List<User> users = new ArrayList<>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setId(String.valueOf(i));
user.setName("茶叶_" + i);
user.setAge(10 + i);
user.setBir(new Date());
//身份信息
Card card=new Card();
card.setNo("6326384674844333");
card.setAddress("广东省广州市广州塔");
user.setCard(card);
//订单信息
List<Order> orders=new ArrayList<>();
orders.add(new Order("12","休闲裤"));
orders.add(new Order("13","跑鞋"));
orders.add(new Order("14","笔记本电脑"));
user.setOrders(orders);
user.setPhoto("C:/Users/Administrator/Desktop/ceshi.jpg");
if (i%2==0){
user.setStatus("1");
user.setHabbys(Arrays.asList("化妆","美甲","购物"));
}else{
user.setStatus("0");
user.setHabbys(Arrays.asList("看书","跳舞","品茶"));
}
users.add(user);
}
return users;
}
/**
* 导出excel，普通数据导出
*/
@Test
public void TestPoiDay01() throws IOException {//FileNotFoundException->IOException
//获取数据
List<User> users = getUsers();
//导出excel
//参数 1:exportParams，导出配置对象 参数 2:导出的类型 参数 3:导出数据集合
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
//写入指定的位置
FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/expoerExcel.xls");
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
workbook.close();
}
@Test
public void testBigExport() throws IOException {//FileNotFoundException->IOException
//获取数据
List<User> users = getUsers();
//导出excel
//参数 1:exportParams，导出配置对象 参数 2:导出的类型 参数 3:导出数据集合
Workbook workbook = ExcelExportUtil.exportBigExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
//写入指定的位置
FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/expoerBigExcel.xls");
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
workbook.close();
}
@Test
public void testImport() throws Exception {//FileNotFoundException->Exception
ImportParams importParams=new ImportParams();
importParams.setTitleRows(1);//标题占几行
importParams.setHeadRows(2);//Header占几行
List<Emp> emps=ExcelImportUtil.importExcel(new FileInputStream("C:/Users/Administrator/Desktop/importExcel.xlsx"), Emp.class, importParams);
emps.forEach(System.out::println);
}
}
4、运行单元测试测试该类，可以看到运行成功，并且成功看到我们导入的结果
importParams之属性小测试
setImportFields属性
(1).修改TestPoiDay01.java，excel中有编号这个列
package com.demo1poi.day01.test;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.demo1poi.day01.entity.Card;
import com.demo1poi.day01.entity.Emp;
import com.demo1poi.day01.entity.Order;
import com.demo1poi.day01.entity.User;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
/**
* @author 安静的茶叶
*/
public class TestPoiDay01 {
/**
* 查询所有用户
*/
public List<User> getUsers() {
List<User> users = new ArrayList<>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setId(String.valueOf(i));
user.setName("茶叶_" + i);
user.setAge(10 + i);
user.setBir(new Date());
//身份信息
Card card=new Card();
card.setNo("6326384674844333");
card.setAddress("广东省广州市广州塔");
user.setCard(card);
//订单信息
List<Order> orders=new ArrayList<>();
orders.add(new Order("12","休闲裤"));
orders.add(new Order("13","跑鞋"));
orders.add(new Order("14","笔记本电脑"));
user.setOrders(orders);
user.setPhoto("C:/Users/Administrator/Desktop/ceshi.jpg");
if (i%2==0){
user.setStatus("1");
user.setHabbys(Arrays.asList("化妆","美甲","购物"));
}else{
user.setStatus("0");
user.setHabbys(Arrays.asList("看书","跳舞","品茶"));
}
users.add(user);
}
return users;
}
/**
* 导出excel，普通数据导出
*/
@Test
public void TestPoiDay01() throws IOException {//FileNotFoundException->IOException
//获取数据
List<User> users = getUsers();
//导出excel
//参数 1:exportParams，导出配置对象 参数 2:导出的类型 参数 3:导出数据集合
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
//写入指定的位置
FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/expoerExcel.xls");
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
workbook.close();
}
@Test
public void testBigExport() throws IOException {//FileNotFoundException->IOException
//获取数据
List<User> users = getUsers();
//导出excel
//参数 1:exportParams，导出配置对象 参数 2:导出的类型 参数 3:导出数据集合
Workbook workbook = ExcelExportUtil.exportBigExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
//写入指定的位置
FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/expoerBigExcel.xls");
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
workbook.close();
}
@Test
public void testImport() throws Exception {//FileNotFoundException->Exception
ImportParams importParams=new ImportParams();
importParams.setTitleRows(1);//标题占几行
importParams.setHeadRows(2);//Header占几行
importParams.setImportFields(new String[]{"编号"});
List<Emp> emps=ExcelImportUtil.importExcel(new FileInputStream("C:/Users/Administrator/Desktop/importExcel.xlsx"), Emp.class, importParams);
emps.forEach(System.out::println);
}
}
(2).运行单元测试测试该类，可以看到运行成功，并且成功看到我们导入的结果
(3).再次修改TestPoiDay01.java，excel中有编号这个列，但是没有头像这个列
package com.demo1poi.day01.test;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.demo1poi.day01.entity.Card;
import com.demo1poi.day01.entity.Emp;
import com.demo1poi.day01.entity.Order;
import com.demo1poi.day01.entity.User;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
/**
* @author 安静的茶叶
*/
public class TestPoiDay01 {
/**
* 查询所有用户
*/
public List<User> getUsers() {
List<User> users = new ArrayList<>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setId(String.valueOf(i));
user.setName("茶叶_" + i);
user.setAge(10 + i);
user.setBir(new Date());
//身份信息
Card card=new Card();
card.setNo("6326384674844333");
card.setAddress("广东省广州市广州塔");
user.setCard(card);
//订单信息
List<Order> orders=new ArrayList<>();
orders.add(new Order("12","休闲裤"));
orders.add(new Order("13","跑鞋"));
orders.add(new Order("14","笔记本电脑"));
user.setOrders(orders);
user.setPhoto("C:/Users/Administrator/Desktop/ceshi.jpg");
if (i%2==0){
user.setStatus("1");
user.setHabbys(Arrays.asList("化妆","美甲","购物"));
}else{
user.setStatus("0");
user.setHabbys(Arrays.asList("看书","跳舞","品茶"));
}
users.add(user);
}
return users;
}
/**
* 导出excel，普通数据导出
*/
@Test
public void TestPoiDay01() throws IOException {//FileNotFoundException->IOException
//获取数据
List<User> users = getUsers();
//导出excel
//参数 1:exportParams，导出配置对象 参数 2:导出的类型 参数 3:导出数据集合
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
//写入指定的位置
FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/expoerExcel.xls");
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
workbook.close();
}
@Test
public void testBigExport() throws IOException {//FileNotFoundException->IOException
//获取数据
List<User> users = getUsers();
//导出excel
//参数 1:exportParams，导出配置对象 参数 2:导出的类型 参数 3:导出数据集合
Workbook workbook = ExcelExportUtil.exportBigExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
//写入指定的位置
FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/expoerBigExcel.xls");
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
workbook.close();
}
@Test
public void testImport() throws Exception {//FileNotFoundException->Exception
ImportParams importParams=new ImportParams();
importParams.setTitleRows(1);//标题占几行
importParams.setHeadRows(2);//Header占几行
importParams.setImportFields(new String[]{"编号","头像"});
List<Emp> emps=ExcelImportUtil.importExcel(new FileInputStream("C:/Users/Administrator/Desktop/importExcel.xlsx"), Emp.class, importParams);
emps.forEach(System.out::println);
}
}
(4).再次运行单元测试测试该类，可以看到运行失败，报错信息：cn.afterturn.easypoi.exception.excel.ExcelImportException: 不是合法的Excel模板，原因是我们要导入的excel表中不含头像这一列
1244
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
