作者：乔山办公网日期：
返回目录：excel表格制作
一、导入easyexcel包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta4</version>
</dependency>
二、读Excel(.xlsx)版大于1000行数据返回List<List>
三、写Excel(.xlsx)版大于1000行数据返回List<List>
1.创建读javaModel映射模型ExcelPropertyIndexModel类继承BaseRowModel
public class ExcelPropertyIndexModel extends BaseRowModel {
@ExcelProperty(value = "姓名" ,index = 0)
private String name;
@ExcelProperty(value = "年龄",index = 1)
private String age;
@ExcelProperty(value = "邮箱",index = 2)
private String email;
@ExcelProperty(value = "地址",index = 3)
private String address;
@ExcelProperty(value = "性别",index = 4)
private String sax;
@ExcelProperty(value = "高度",index = 5)
private String heigh;
@ExcelProperty(value = "备注",index = 6)
private String last;
@Override
public String toString() {
return "ExcelPropertyIndexModel{" +
"name='" + name + '\\'' +
", age='" + age + '\\'' +
", email='" + email + '\\'' +
", address='" + address + '\\'' +
", sax='" + sax + '\\'' +
", heigh='" + heigh + '\\'' +
", last='" + last + '\\'' +
'}';
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getSax() {
return sax;
}
public void setSax(String sax) {
this.sax = sax;
}
public String getHeigh() {
return heigh;
}
public void setHeigh(String heigh) {
this.heigh = heigh;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}
2.创建ExcelListener类继承 AnalysisEventListener
public class ExcelListener extends AnalysisEventListener {
Logger logger = Logger.getLogger("ExcelListener");
private List<Object> datas = new ArrayList<Object>();
@Override
public void invoke(Object object, AnalysisContext context) {
logger.info("当前行："+context.getCurrentRowNum());
logger.info(object.toString());//获取当前行的数据
datas.add(object);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
// doSomething(object);//根据自己业务做处理
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//解析结束销毁不用的资源
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
}
3.读取文件
4.创建测试类
public class ReadTest {
@Test
public void test() {
InputStream inputStream = null;
List<ExcelPropertyIndexModel> sheetContent=new ArrayList<>();
try {
inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("reader.xlsx");
// List<Object> data = EasyExcelFactory.read(inputStream, new Sheet(1, 0, ExcelPropertyIndexModel.class));
ExcelListener excelListener = new ExcelListener();
Sheet sheet= new Sheet(1, 0, ExcelPropertyIndexModel.class);
ExcelReader excelReader = EasyExcelFactory.readBySax(inputStream,sheet ,excelListener);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
5 测试读取数据
6、写操作
创建写JavaModel 映射模型MultiLineHeadExcelModel类继承BaseRowModel
public class MultiLineHeadExcelModel extends BaseRowModel {
@ExcelProperty(value = {"测试execl头","姓名"},index = 0)
private String name;
@ExcelProperty(value = {"测试execl头","年龄"},index = 1)
private String age;
@ExcelProperty(value = {"测试execl头","邮箱"},index = 2)
private String email;
@ExcelProperty(value = {"测试execl头","地址"},index = 3)
private String address;
@ExcelProperty(value = {"测试execl头","性别"},index = 4)
private String sax;
@ExcelProperty(value = {"测试execl头","高度"},index = 5)
private String heigh;
@ExcelProperty(value = {"测试execl头","备注"},index = 6)
private String last;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getSax() {
return sax;
}
public void setSax(String sax) {
this.sax = sax;
}
public String getHeigh() {
return heigh;
}
public void setHeigh(String heigh) {
this.heigh = heigh;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}
7.写测试类
public class WriteTest {
@Test
public void write() {
List<MultiLineHeadExcelModel> getModeldatas =init();
OutputStream out = null;
try {
out = new FileOutputStream("C:\\\\Users\\\\Administrator\\\\Desktop\\\\writeV20072.xlsx");
ExcelWriter writer = EasyExcelFactory.getWriter(out);
Sheet sheet1 = new Sheet(1, 0,MultiLineHeadExcelModel.class,"第一个sheet",null);
sheet1.setAutoWidth(Boolean.TRUE);
sheet1.setTableStyle(getTableStyle1());
writer.write(getModeldatas, sheet1);
writer.merge(2,3,0,0);
writer.finish();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private TableStyle getTableStyle1() {
TableStyle tableStyle =new TableStyle();
tableStyle.setTableContentBackGroundColor(IndexedColors.AQUA);
Font tableContentFont=new Font();
tableContentFont.setFontName("宋体");
tableContentFont.setFontHeightInPoints((short) 20);
tableContentFont.setBold(true);
tableStyle.setTableContentFont(tableContentFont);
tableStyle.setTableHeadFont(tableContentFont);
return tableStyle;
}
private int getRandomNumberInRange(int min, int max) {
Random r = new Random();
return r.ints(min, (max + 1)).findFirst().getAsInt();
}
public List<MultiLineHeadExcelModel> init(){
List<MultiLineHeadExcelModel> list=new ArrayList<>();
MultiLineHeadExcelModel mode=null;
for (int i=0;i<10000;i++ ) {
mode=new MultiLineHeadExcelModel();
mode.setName("test"+i);
mode.setAge(String.valueOf(getRandomNumberInRange(20,50)));
mode.setAddress("testAddress");
mode.setEmail("test"+i+"@163.com");
mode.setHeigh(String.valueOf(getRandomNumberInRange(150,186)));
mode.setSax("男");
mode.setLast(String.valueOf(i));
list.add(mode);
}
return list;
}
}
我是阳光随馨馨，如果你看完了，点个赞，加个关注，转发一下哈