easyexcel 读写demo

 1 <dependencies>
 2         <dependency>
 3             <groupId>com.alibaba</groupId>
 4             <artifactId>easyexcel</artifactId>
 5             <version>1.1.1</version>
 6         </dependency>
 7 
 8         <dependency>
 9             <groupId>junit</groupId>
10             <artifactId>junit</artifactId>
11             <version>4.9</version>
12         </dependency>
13 
14         <!-- jdbc -->
15         <dependency>
16             <groupId>commons-dbutils</groupId>
17             <artifactId>commons-dbutils</artifactId>
18             <version>1.7</version>
19         </dependency>
20 
21         <dependency>
22             <groupId>com.mchange</groupId>
23             <artifactId>c3p0</artifactId>
24             <version>0.9.5.2</version>
25         </dependency>
26 
27         <dependency>
28             <groupId>mysql</groupId>
29             <artifactId>mysql-connector-java</artifactId>
30             <version>5.1.40</version>
31         </dependency>
32 
33 </dependencies>

工具类

 1 package cn.tele.demo;
 2 
 3 import java.sql.Connection;
 4 import java.sql.ResultSet;
 5 import java.sql.SQLException;
 6 import java.sql.Statement;
 7 import javax.sql.DataSource;
 8 import com.mchange.v2.c3p0.ComboPooledDataSource;
 9 
10 /** 
11  *
12  *@author Tele
13  *
14  */
15 
16 public class JdbcUtils {
17     private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
18     
19     
20     public static Connection getConnection() throws SQLException {
21         return dataSource.getConnection();
22     }
23     
24     public static DataSource getDS() {
25         return dataSource;
26     }
27     
28     // 关闭数据库连接
29     public static void close(ResultSet rs, Statement stat, Connection conn) {
30             try {
31                 if (rs != null)
32                     rs.close();
33                 if (stat != null)
34                     stat.close();
35                 if (conn != null)
36                     conn.close();
37             } catch (SQLException e) {
38                 e.printStackTrace();
39             }
40             
41      }
42     
43 }

View Code

实体类(模型)

 1 package cn.tele.demo;
 2 
 3 import com.alibaba.excel.annotation.ExcelProperty;
 4 import com.alibaba.excel.metadata.BaseRowModel;
 5 
 6 /** 
 7  *
 8  *@author Tele
 9  *
10  */
11 
12 public class Student extends BaseRowModel{
13     @ExcelProperty(value="学号",index=0)
14     private Integer id;
15     
16     @ExcelProperty(value= {"姓名","name","xxx","xxx"},index=1)
17     private String name;
18     
19     @ExcelProperty(value="指导老师",index=2)
20     private String teacher;
21 
22     public Integer getId() {
23         return id;
24     }
25 
26     public void setId(Integer id) {
27         this.id = id;
28     }
29 
30     public String getName() {
31         return name;
32     }
33 
34     public void setName(String name) {
35         this.name = name;
36     }
37 
38     public String getTeacher() {
39         return teacher;
40     }
41 
42     public void setTeacher(String teacher) {
43         this.teacher = teacher;
44     }
45 
46     @Override
47     public String toString() {
48         return "Student [id=" + id + ", name=" + name + ", teacher=" + teacher + "]";
49     }
50 }

View Code

监听器

 1 package cn.tele.demo;
 2 
 3 import java.sql.SQLException;
 4 import java.util.ArrayList;
 5 import java.util.List;
 6 
 7 import org.apache.commons.dbutils.QueryRunner;
 8 
 9 import com.alibaba.excel.context.AnalysisContext;
10 import com.alibaba.excel.event.AnalysisEventListener;
11 
12 /** 
13  *
14  *@author Tele
15  *
16  */
17 
18 public class ExcelListener extends AnalysisEventListener<Student>{
19 
20     //自定义用于暂时存储data。
21     //可以通过实例获取该值
22     private List<Student> datas = new ArrayList<Student>();
23     
24     //每解析一行调用一次invoke方法
25     public void invoke(Student object, AnalysisContext context) {
26         System.out.println("当前行："+context.getCurrentRowNum());
27         System.out.println(object);
28         datas.add(object);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
29         doSomething(datas);//根据自己业务做处理
30     }
31     
32     private void doSomething(List<Student> list) {
33       
34     }
35     
36     //解析结束后自动调用
37     public void doAfterAllAnalysed(AnalysisContext context) {
38        // datas.clear();//解析结束销毁不用的资源
39           //1、入库调用接口
40         QueryRunner runner = new  QueryRunner(JdbcUtils.getDS());
41         String sql = "insert into student values(?,?,?)";
42         for(Student student:datas) {
43             try {
44                 runner.update(sql, new Object[] {student.getId(),student.getName(),student.getTeacher()});
45             } catch (SQLException e) {
46                 e.printStackTrace();
47             }
48         }
49         
50         System.out.println("数据更新完成");
51         try {
52             JdbcUtils.close(null,null,JdbcUtils.getConnection());
53         } catch (SQLException e) {
54             e.printStackTrace();
55         }
56         
57     }
58     public List<Student> getDatas() {
59         return datas;
60     }
61     public void setDatas(List<Student> datas) {
62         this.datas = datas;
63     }
64 
65 }

View Code

Demo

  1 package cn.tele.demo;
  2 
  3 import java.io.File;
  4 import java.io.FileInputStream;
  5 import java.io.FileNotFoundException;
  6 import java.io.FileOutputStream;
  7 import java.io.IOException;
  8 import java.io.InputStream;
  9 import java.io.OutputStream;
 10 import java.sql.SQLException;
 11 import java.util.List;
 12 
 13 import org.apache.commons.dbutils.QueryRunner;
 14 import org.apache.commons.dbutils.handlers.BeanHandler;
 15 import org.apache.commons.dbutils.handlers.BeanListHandler;
 16 import org.junit.Test;
 17 
 18 import com.alibaba.excel.ExcelReader;
 19 import com.alibaba.excel.ExcelWriter;
 20 import com.alibaba.excel.metadata.Sheet;
 21 import com.alibaba.excel.support.ExcelTypeEnum;
 22 
 23 /**
 24  * 读取Excel
 25  * 
 26  * @author Tele
 27  *
 28  */
 29 
 30 public class Demo {
 31 
 32     /**
 33      * 读取excel并插入mysql中 sheet第一个参数为sheetNo代表sheet的编号,第二个参数表示从第几行开始读,最小是1, new
 34      * Sheet(1,0,Student.class)发现无输出结果
 35      * 
 36      * @throws FileNotFoundException
 37      */
 38     @SuppressWarnings("deprecation")
 39     @Test
 40     public void readExcel() throws FileNotFoundException {
 41         InputStream inputStream = new FileInputStream("d:/excelTest/test.xlsx");
 42         try {
 43             // 解析每行结果在listener中处理
 44             ExcelListener listener = new ExcelListener();
 45 
 46             ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null, listener);
 47             excelReader.read(new Sheet(1, 1, Student.class));
 48         } catch (Exception e) {
 49 
 50         } finally {
 51             try {
 52                 inputStream.close();
 53             } catch (IOException e) {
 54                 e.printStackTrace();
 55             }
 56         }
 57     }
 58 
 59     /**
 60      * 查询测试
 61      */
 62     @Test
 63     public void queryByGroup() {
 64         QueryRunner runner = new QueryRunner(JdbcUtils.getDS());
 65         String sql = "SELECT * from student group by teacher,id";
 66         try {
 67             List<Student> query = runner.query(sql, new BeanListHandler<Student>(Student.class));
 68             for (Student student : query) {
 69                 System.out.println(student.getName() + "----" + student.getTeacher());
 70             }
 71 
 72         } catch (SQLException e) {
 73             e.printStackTrace();
 74         }
 75     }
 76 
 77     /**
 78      * 将查询结果写出到excel中 在写excel的过程中,含有模型的写入,对于sheet的第二个参数是忽略的,全部从左侧第一行进行读写
 79      */
 80     @Test
 81     public void writeExcel() {
 82         try {
 83             OutputStream os = new FileOutputStream(new File("d:/excelTest/new.xlsx"));
 84             ExcelWriter writer = new ExcelWriter(os, ExcelTypeEnum.XLSX, true);
 85 
 86             // 2代表sheetNo,不可以重复,如果两个sheet的sheetNo相同则输出时只会有一个sheet
 87             Sheet sheet1 = new Sheet(1, 5, Student.class);
 88             sheet1.setSheetName("第一个sheet");
 89 
 90             Sheet sheet2 = new Sheet(2, 1, Student.class);
 91             sheet2.setSheetName("第二个sheet");
 92 
 93             QueryRunner runner = new QueryRunner(JdbcUtils.getDS());
 94             String sql = "SELECT * from student group by teacher,id";
 95 
 96             List<Student> result = runner.query(sql, new BeanListHandler<Student>(Student.class));
 97             writer.write(result, sheet1);
 98             // writer.write(result,sheet2);
 99 
100             writer.finish();
101             System.out.println("数据已写出");
102         } catch (FileNotFoundException e) {
103             e.printStackTrace();
104         } catch (SQLException e) {
105             e.printStackTrace();
106         }
107 
108     }
109 
110 }