java 读取excel
java 操作读取excel中的内容
-
首先创建一个maven工程,引入一下依赖：
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.4</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.72</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> -
创建一个xlsx(这里只是截图)
-
创建一个普通对象，和xlsx字段名称对应起来
package com.example.demo; import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.metadata.BaseRowModel; import lombok.Data; import javax.sql.rowset.BaseRowSet; /** * @Author: heiye * @Date: 2021/08/23/20:11 * @Description: */ @Data public class UserInfo extends BaseRowModel { @ExcelProperty(index=0) //这个注解代表的是xlsx中的字段名称 ,index代表的是第几个，默认从0开始 private String id; @ExcelProperty(index =1) private String date; } -
创建一个DemoDataListener
package com.example.demo; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSON; import lombok.val; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /** * @Author: heiye * @Date: 2021/08/23/20:22 * @Description: */ // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去 public class DemoDataListener extends AnalysisEventListener<UserInfo> { /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 */ private static final int BATCH_COUNT = 6; List<UserInfo> list = new ArrayList<UserInfo>(); /** * 这个每一条数据解析都会来调用 * * @param data * @param context */ @Override public void invoke(UserInfo data, AnalysisContext context) { System.out.println(data.getId()+'\t'+data.getDate()); try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection connection = null; try { connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/chenhao?serverTimezone=UTC","root",""); } catch (SQLException throwables) { throwables.printStackTrace(); } System.out.println(connection.toString()); String sql = "update test set toilet_date=? where serial=?"; PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); } catch (SQLException throwables) { throwables.printStackTrace(); } try { preparedStatement.setObject(1, data.getDate()); preparedStatement.setObject(2,data.getId()); } catch (SQLException throwables) { throwables.printStackTrace(); } try { preparedStatement.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); } try { preparedStatement.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } System.out.println("解析到一条数据:" + JSON.toJSONString(data)); val str=JSON.toJSONString(data); list.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (list.size() >= BATCH_COUNT) { // 存储完成清理 list list.clear(); } } /** * 所有数据解析完成了 都会来调用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { System.out.println("最后一次解析了" + list.size() + "条数据！"); System.out.println("所有数据解析完成！"); } public List<UserInfo> returnList() { return list; } } -
创建EasyExcelRead.java
package com.example.demo; import com.alibaba.excel.EasyExcel; import org.testng.annotations.Test; import java.io.File; /** * @Author: heiye * @Date: 2021/08/23/20:22 * @Description: */ public class EasyExcelRead { /** * 读取的excel所在的路径 */ private static final String PATH = "D:\\"; /** * 最简单的读 * 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去 * 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 */ @Test public void simpleRead() { // 路径的文件名 - 即文件所在位置，通过它能找到这个文件 String fileName = PATH + File.separator + "22.xlsx"; /* * fileName - 文件名(包含路径) * DemoData.class - 实体类的反射对象 * new DemoDataListener() - 监听器 * sheetName - 工作表的名称 * data() - 数据部分（实体类集合） */ EasyExcel.read(fileName, UserInfo.class, new DemoDataListener()).sheet().doRead(); } } -
主类
package com.example.demo; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelReader; import com.alibaba.excel.metadata.Sheet; import com.alibaba.excel.read.metadata.ReadSheet; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.InputStream; @SpringBootApplication public class DemoApplication { static EasyExcelRead easyExcelRead=new EasyExcelRead(); public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); easyExcelRead.simpleRead(); } }
