easyexcel 读取指定行数据_easyexcel 读写demo

1 packagecn.tele.demo;2

3 importjava.io.File;4 importjava.io.FileInputStream;5 importjava.io.FileNotFoundException;6 importjava.io.FileOutputStream;7 importjava.io.IOException;8 importjava.io.InputStream;9 importjava.io.OutputStream;10 importjava.sql.SQLException;11 importjava.util.List;12

13 importorg.apache.commons.dbutils.QueryRunner;14 importorg.apache.commons.dbutils.handlers.BeanHandler;15 importorg.apache.commons.dbutils.handlers.BeanListHandler;16 importorg.junit.Test;17

18 importcom.alibaba.excel.ExcelReader;19 importcom.alibaba.excel.ExcelWriter;20 importcom.alibaba.excel.metadata.Sheet;21 importcom.alibaba.excel.support.ExcelTypeEnum;22

23 /**

24 * 读取Excel25 *26 *@authorTele27 *28 */

29

30 public classDemo {31

32 /**

33 * 读取excel并插入mysql中 sheet第一个参数为sheetNo代表sheet的编号,第二个参数表示从第几行开始读,最小是1, new34 * Sheet(1,0,Student.class)发现无输出结果35 *36 *@throwsFileNotFoundException37 */

38 @SuppressWarnings("deprecation")39 @Test40 public void readExcel() throwsFileNotFoundException {41 InputStream inputStream = new FileInputStream("d:/excelTest/test.xlsx");42 try{43 //解析每行结果在listener中处理

44 ExcelListener listener = newExcelListener();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 @Test63 public voidqueryByGroup() {64 QueryRunner runner = newQueryRunner(JdbcUtils.getDS());65 String sql = "SELECT * from student group by teacher,id";66 try{67 List query = runner.query(sql, new BeanListHandler(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 @Test81 public voidwriteExcel() {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 = newQueryRunner(JdbcUtils.getDS());94 String sql = "SELECT * from student group by teacher,id";95

96 List result = runner.query(sql, new BeanListHandler(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 }