Java操作excel工具&easyExcel
EasyExcel写入Excel代码:
方法一(没成功):
/** * 根据路径写入excel * @param path */ public static void writeExcelSample(String path) { try { OutputStream out = new FileOutputStream(path); try { ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX,false); //写第一个sheet, sheet1 数据全是List<String> 无模型映射关系 Sheet sheet1 = new Sheet(1, 0); sheet1.setSheetName("第一个sheet"); writer.write(getListString(), sheet1); writer.finish(); } catch (Exception e) { e.printStackTrace(); } finally { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } catch (FileNotFoundException e) { e.printStackTrace(); } }
方法二:
表头,也可以作为写入数据读取数据的载体
@Data public static class ExcelTitle extends BaseRowModel { @ExcelProperty(value = "姓名", index = 0) private String name; @ExcelProperty(value = "学校", index = 1) private String school; @ExcelProperty(value = "教室", index = 2) private String classRoom; @ExcelProperty(value = "性别", index = 3) private String sex; @ExcelProperty(value = "年龄", index = 4) private Integer age; @ExcelProperty(value = "地址", index = 5) private String address; }
创建50个工作簿,每个工作部写入6万条数据,共3百万条数据
/** * 模拟查询数据 * @return */ private static List<ExcelTitle> getList() { List<ExcelTitle> list = new ArrayList<>(); ExcelTitle data = new ExcelTitle(); data.setName("***"); data.setAddress("**省**市**县**镇**村***路*号"); data.setAge(24); data.setSchool("*****学校"); data.setSex("男"); data.setClassRoom("******班"); for(int i=0; i<60000; i++) { list.add(data); } return list; } /** * 根据文件路径写入数据 * @param path */ public static void writeExel(String path) { ExcelWriter excelWriter = EasyExcel.write(path, ExcelTitle.class).build(); for(int i=0; i<50; i++) { WriteSheet sheet = EasyExcel.writerSheet(i, i+"工作簿").build(); excelWriter.write(getList(), sheet); } excelWriter.finish(); }
EasyExcel读取数据:
/** * 根据path 同步读取excel数据 * @param path */ public static void readExcel(String path) { Sheet sheet = new Sheet(1, 1, ExcelTitle.class); List<Object> readList = null; List<ExcelTitle> list = new ArrayList<>(); try { readList = EasyExcelFactory.read(new FileInputStream(path), sheet); } catch (FileNotFoundException e) { e.printStackTrace(); } for (Object object : readList) { list.add((ExcelTitle) object); System.out.println(object.toString()); } }
public static class ExcelModelListener extends AnalysisEventListener<ExcelTitle> { List<ExcelTitle> list = new ArrayList<>(); private static int count = 0; @Override public void invoke(ExcelTitle excelTitle, AnalysisContext analysisContext) { System.out.println("解析到一条数据:" + excelTitle.toString()); list.add(excelTitle); count++; } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { System.out.println("所有数据解析完成,解析道" + count + "条"); } } /** * 根据path异步读取excel * @param path */ public static void readExcelOfAsyn(String path) { try { Sheet sheet = new Sheet(1, 1, ExcelTitle.class); EasyExcelFactory.readBySax(new FileInputStream(path), sheet, new ExcelModelListener()); } catch (FileNotFoundException e) { e.printStackTrace(); } }
EasyExcel依赖:
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.6</version> </dependency>
推荐阅读: https://blog.csdn.net/jiangjiandecsd/article/details/81115622
https://blog.csdn.net/weixin_37775583/article/details/89642093
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步