POI&EasyExcel读写操作

POI & EasyExcel

POI

Excel写操作

导入依赖

<!-- xls(03) --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <!-- xlsx(07) --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <!-- joda-time --> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.6</version> </dependency> <!-- junit --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13</version> </dependency>

写入测试

public class ExcelWriteTest { String PATH = "/Users/work/idea_work/poi_easy-excel/"; @Test public void testWrite03() throws Exception { // 创建一个工作簿, 03版本的为new HSSFWorkbook(), 07版本的为new XSSFWorkbook() Workbook workbook = new XSSFWorkbook(); // 创建一个sheet页 Sheet sheet = workbook.createSheet("学生信息表1"); // 创建一行 Row row1 = sheet.createRow(0); // 创建一个单元格(1,1) Cell cell11 = row1.createCell(0); cell11.setCellValue("学生姓名"); // (1,2) Cell cell12 = row1.createCell(1); cell12.setCellValue("年龄"); // (1,3) Cell cell31 = row1.createCell(2); cell31.setCellValue("创建时间"); // 第二行 Row row2 = sheet.createRow(1); // (2,1) Cell cell21 = row2.createCell(0); cell21.setCellValue("李志鹏"); // (2,2) Cell cell22 = row2.createCell(1); cell22.setCellValue("23"); // (2,3) Cell cell32 = row2.createCell(2); String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell32.setCellValue(time); // 创建一个文件输出流，03版本的后缀为.xls, 07版本的后缀为.xlsx FileOutputStream fileOutputStream = new FileOutputStream(PATH + "学生信息表.xlsx"); workbook.write(fileOutputStream); // 关流 fileOutputStream.close(); System.out.println("学生表创建成功~"); } }

大数据的写入

HSSF (03)

优点:

在内存中操作，最后一次写入磁盘，速度快

缺点：

最大只能一次插入65536行数据，否则会报错

java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535) @Test public void testWriteBigData() throws IOException { long start = System.currentTimeMillis(); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(); for (int rowNum = 0; rowNum < 65537; rowNum++) { // 最大行数为65536行 Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream outputStream = new FileOutputStream(PATH + "大数据写入.xls"); workbook.write(outputStream); outputStream.close(); long end = System.currentTimeMillis(); System.out.println((double) (end - start)/1000); }

XSSF (07)

优点：

可以写入更大的数据量

缺点：

写数据速度慢，耗内存

@Test public void testWriteBigData2() throws IOException { long start = System.currentTimeMillis(); Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); for (int rowNum = 0; rowNum < 100000; rowNum++) { Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream outputStream = new FileOutputStream(PATH + "大数据写入2.xlsx"); workbook.write(outputStream); outputStream.close(); long end = System.currentTimeMillis(); System.out.println((double) (end - start)/1000); }

SXSSF 大数据操作

优点：

可以写入非常大的数据量，速度更快，占用内存少

注意：

过程中会产生临时文件，需要清理临时文件

@Test public void testWriteBigData3() throws IOException { long start = System.currentTimeMillis(); Workbook workbook = new SXSSFWorkbook(); Sheet sheet = workbook.createSheet(); for (int rowNum = 0; rowNum < 65536; rowNum++) { Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream outputStream = new FileOutputStream(PATH + "大数据写入3.xlsx"); workbook.write(outputStream); outputStream.close(); ((SXSSFWorkbook) workbook).dispose(); long end = System.currentTimeMillis(); System.out.println((double) (end - start)/1000); }

Excel读取操作

HSSF (3)

@Test public void ExcelRead03() throws IOException { FileInputStream inputStream = new FileInputStream(PATH + "学生信息表.xls"); // 根据输入流创建工作簿 Workbook workbook = new HSSFWorkbook(inputStream); // 获取表 Sheet sheet = workbook.getSheet("学生信息表1"); // 获取行 Row row = sheet.getRow(0); // 获取列 Cell cell = row.getCell(0); System.out.println(cell.getStringCellValue()); // 关闭输入流 inputStream.close(); }

XSSF

@Test public void ExcelRead07() throws IOException { FileInputStream inputStream = new FileInputStream(PATH + "学生信息表.xlsx"); // 根据输入流创建工作簿 Workbook workbook = new XSSFWorkbook(inputStream); // 获取表 Sheet sheet = workbook.getSheet("学生信息表1"); // 获取行 Row row = sheet.getRow(1); // 获取列 Cell cell = row.getCell(1); System.out.println(cell.getStringCellValue()); // 关闭输入流 inputStream.close(); }

不同数据类型数据读取

@Test public void testCellType() throws IOException { FileInputStream inputStream = new FileInputStream(PATH + "学生信息表.xls"); Workbook workbook = new HSSFWorkbook(inputStream); Sheet sheet = workbook.getSheet("学生信息表1"); // 获取标题行 Row rowTitle = sheet.getRow(0); if (rowTitle != null) { int cellCount = rowTitle.getPhysicalNumberOfCells(); // 获取该行总共列数 for (int cellNum = 0; cellNum < cellCount; cellNum++) { Cell cell = rowTitle.getCell(cellNum); if (cell != null) { int cellType = cell.getCellType(); String cellValue = cell.getStringCellValue(); System.out.print(cellValue + " | "); } } System.out.println(); } // 获取表内容 int rowCount = sheet.getPhysicalNumberOfRows(); for (int rowNum = 0; rowNum < rowCount; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { int cellCount = row.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { Cell cell = row.getCell(cellNum); if (cell != null) { int cellType = cell.getCellType(); String cellValue = ""; switch (cellType) { case HSSFCell.CELL_TYPE_BLANK: // 空数据 break; case HSSFCell.CELL_TYPE_STRING: // 字符串数据 cellValue = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔类型数据 cellValue = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: // 数字类型，包含日期类型 if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期类型 Date date = cell.getDateCellValue(); cellValue = new DateTime(date).toString("yyyy-MM-dd"); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 数字类型转字符串类型，防止数字过长 cellValue = cell.toString(); } break; case HSSFCell.CELL_TYPE_ERROR: // 错误数据类型 break; } System.out.print(cellValue + " | "); } } } System.out.println(); } inputStream.close(); }

公式类型数据读取

@Test public void testFormula() throws IOException { FileInputStream inputStream = new FileInputStream(PATH + "公式.xlsx"); Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(3); Cell cell = row.getCell(0); // 获取计算公式 FormulaEvaluator evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); // 输出单元格内容 int cellType = cell.getCellType(); switch (cellType) { case Cell.CELL_TYPE_FORMULA: // 公式数据 String formula = cell.getCellFormula(); System.out.println(formula); // 计算公式 CellValue evaluate = evaluator.evaluate(cell); String cellValue = evaluate.formatAsString(); // 公式转字符串 System.out.println(cellValue); break; } inputStream.close(); }

EasyExcel

导入依赖：

<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.8</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13</version> </dependency>

实体类：

@Data public class DemoData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; @ExcelProperty("数字标题") private Double doubleData; /** * 忽略这个字段 */ @ExcelIgnore private String ignore; }

写入操作

简单写入

public class EasyExcelTest { String PATH = "/Users/work/idea_work/poi_easy-excel/"; // 通用数据 private List<DemoData> data() { List<DemoData> list = new ArrayList<DemoData>(); for (int i = 0; i < 10; i++) { DemoData data = new DemoData(); data.setString("字符串" + i); data.setDate(new Date()); data.setDoubleData(0.56); list.add(data); } return list; } // 写入操作 @Test public void simpleWrite() { String fileName = PATH + "EasyExcelTest.xls"; // 文件写入位置和文件名 // write(文件名称，对应实体类); sheet(表名); doWrite(写入的数据); 文件流会自动关闭 EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data()); } }

读取操作

简单读取

监听器:

public class DemoDataListener extends AnalysisEventListener<DemoData> { private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class); // 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 private static final int BATCH_COUNT = 5; List<DemoData> list = new ArrayList<DemoData>(); // 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 private DemoDAO demoDAO; public DemoDataListener() { // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 demoDAO = new DemoDAO(); } // 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 public DemoDataListener(DemoDAO demoDAO) { this.demoDAO = demoDAO; } // 这个每一条数据解析都会来调用 @Override public void invoke(DemoData data, AnalysisContext context) { //LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data)); System.out.println("解析到一条数据：" + JSON.toJSONString(data)); list.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list list.clear(); } } // 所有数据解析完成了 都会来调用 @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 saveData(); // LOGGER.info("所有数据解析完成！"); System.out.println("所有数据解析完成！"); } // 加上存储数据库 private void saveData() { // LOGGER.info("{}条数据，开始存储数据库！", list.size()); System.out.println(list.size() + "条数据，开始存储数据库！"); demoDAO.save(list); // LOGGER.info("存储数据库成功！"); System.out.println("存储数据库成功！"); } }

简单读取：

String PATH = "/Users/work/idea_work/poi_easy-excel/"; @Test public void simpleRead() { String fileName = PATH + "EasyExcelTest.xls"; // 文件位置 // read(文件名称，对应实体类，监听器).sheet().deRead(); 读取第一个sheet，文件流会自动关闭 EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead(); }

其他API

指定参数不写入

// 指定参数不写入 @Test public void excludeOrIncludeWrite() { String fileName = PATH + "EasyExcelTest.xls"; Set<String> excludeColumnFiledNames = new HashSet<String>(); excludeColumnFiledNames.add("date"); // date不写入excel EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板").doWrite(data()); }

指定写入的列

@Data public class IndexData { @ExcelProperty(value = "字符串标题", index = 0) private String string; @ExcelProperty(value = "日期标题", index = 1) private Date date; @ExcelProperty(value = "数字标题", index = 3) // 这里设置3 会导致第二列空的 private Double doubleData; } // 指定写入的列 @Test public void indexWrite() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.write(fileName, IndexData.class).sheet("模板").doWrite(data()); }

复杂头写入

@Data public class ComplexHeadData { @ExcelProperty({"主标题", "字符串标题"}) private String string; @ExcelProperty({"主标题", "日期标题"}) private Date date; @ExcelProperty({"主标题", "数字标题"}) private Double doubleData; } // 复杂头写入 @Test public void complexHeadWrite() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.write(fileName, ComplexHeadData.class).sheet("模板").doWrite(data()); }

重复多次写入

单个对象写入一个sheet

// 重复多次写入（单个对象写入一个sheet） @Test public void repeatedWrite() { String fileName = PATH + "EasyExcelTest.xls"; ExcelWriter excelWriter = null; try { excelWriter = EasyExcel.write(fileName, DemoData.class).build(); // 如果写入同一个sheet只需要创建一次 WriteSheet sheet = EasyExcel.writerSheet("模板").build(); for (int i = 0; i < 2; i++) { List<DemoData> data = data(); excelWriter.write(data, sheet); } } finally { // 关闭流 if (excelWriter != null) { excelWriter.finish(); } } }

同一对象写入不同sheet

// 同一对象写入不同sheet @Test public void repeatedWrite2() { String fileName = PATH + "EasyExcelTest.xls"; ExcelWriter excelWriter = null; try { excelWriter = EasyExcel.write(fileName, DemoData.class).build(); for (int i = 0; i < 5; i++) { WriteSheet sheet = EasyExcel.writerSheet("模板" + i).build(); List<DemoData> data = data(); excelWriter.write(data, sheet); } } finally { if (excelWriter != null) { excelWriter.finish(); } } }

不同对象写入不同sheet

// 不同对象写入不同sheet @Test public void repeatedWrite3() { String fileName = PATH + "EasyExcelTest.xls"; ExcelWriter excelWriter = null; try { excelWriter = EasyExcel.write(fileName).build(); for (int i = 1; i <= 3; i++) { switch (i) { case 1: WriteSheet sheet1 = EasyExcel.writerSheet(i, "模板1").head(DemoData.class).build(); List<DemoData> data = data(); excelWriter.write(data, sheet1); break; case 2: WriteSheet sheet2 = EasyExcel.writerSheet(i, "模板2").head(IndexData.class).build(); List<IndexData> indexData = indexData(); excelWriter.write(indexData, sheet2); break; case 3: WriteSheet sheet3 = EasyExcel.writerSheet(i, "模板3").head(ComplexHeadData.class).build(); List<ComplexHeadData> complexHeadData = complexHeadData(); excelWriter.write(complexHeadData, sheet3); break; } } } finally { if (excelWriter != null) { excelWriter.finish(); } } }

模板1：

模板2：

模板3：

日期、数字自定义格式转换

@Data public class ConverterData { /** * 我想所有的 字符串起前面加上"自定义："三个字 */ @ExcelProperty(value = "字符串标题", converter = CustomStringStringConverter.class) private String string; /** * 我想写到excel 用年月日的格式 */ @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒") @ExcelProperty("日期标题") private Date date; /** * 我想写到excel 用百分比表示 */ @NumberFormat("#.##%") @ExcelProperty(value = "数字标题") private Double doubleData; } public class CustomStringStringConverter implements Converter<String> { @Override public Class supportJavaTypeKey() { return String.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } /** * 这里是读的时候会调用 不用管 * * @param cellData NotNull * @param contentProperty Nullable * @param globalConfiguration NotNull * @return */ @Override public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { return cellData.getStringValue(); } /** * 这里是写的时候会调用 不用管 * * @param value NotNull * @param contentProperty Nullable * @param globalConfiguration NotNull * @return */ @Override public CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { return new CellData("自定义：" + value); } } // 日期、数字自定义格式转换 @Test public void converterWrite() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.write(fileName, ConverterData.class).sheet("模板").doWrite(data()); }

图片写入

/** * 图片导出类 */ @Data @ContentRowHeight(100) @ColumnWidth(100/8) public class ImageData { private File file; private InputStream inputStream; @ExcelProperty(converter = StringImageConverter.class) private String string; private byte[] byteArray; // 根据url导出 private URL url; } public class TestFileUtil { public static InputStream getResourcesFileInputStream(String fileName) { return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName); } public static String getPath() { return TestFileUtil.class.getResource("/").getPath(); } public static File createNewFile(String pathName) { File file = new File(getPath() + pathName); if (file.exists()) { file.delete(); } else { if (!file.getParentFile().exists()) { file.getParentFile().mkdirs(); } } return file; } public static File readFile(String pathName) { return new File(getPath() + pathName); } public static File readUserHomeFile(String pathName) { return new File(System.getProperty("user.home") + File.separator + pathName); } } // 图片写入 @Test public void imageWrite() throws Exception { String fileName = PATH + "EasyExcelTest.xls"; FileInputStream inputStream = null; try { ArrayList<ImageData> list = new ArrayList<ImageData>(); ImageData imageData = new ImageData(); list.add(imageData); String imagePath = TestFileUtil.getPath() + "图片1.png"; // 五种方式写入图片 imageData.setFile(new File(imagePath)); imageData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath))); imageData.setString(imagePath); inputStream = FileUtils.openInputStream(new File(imagePath)); imageData.setInputStream(inputStream); imageData.setUrl(new URL("https://raw.githubusercontent.com/alibaba/easyexcel/master/src/test/resources/converter/img.jpg")); EasyExcel.write(fileName, ImageData.class).sheet().doWrite(list); } finally { if (inputStream != null) { inputStream.close(); } } }

根据模板写入

// 根据模板写入 @Test public void templateWrite() { String templateFileName = PATH + "demo.xls"; String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.write(fileName, DemoData.class).withTemplate(templateFileName).sheet().doWrite(data()); }

写入时设置列宽行高

@Data @ContentRowHeight(10) @HeadRowHeight(20) @ColumnWidth(25) public class WidthAndHeightData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; /** * 宽度为50 */ @ColumnWidth(50) @ExcelProperty("数字标题") private Double doubleData; } // 写入时设置列宽和行高 @Test public void widthAndHeightWrite() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.write(fileName, WidthAndHeightData.class).sheet().doWrite(data()); }

写入时配置自定义样式（注解形式）

/** * 样式的数据类 * * @author Jiaju Zhuang **/ @Data // 头背景设置成红色 IndexedColors.RED.getIndex() @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10) // 头字体设置成20 @HeadFontStyle(fontHeightInPoints = 20) // 内容的背景设置成绿色 IndexedColors.GREEN.getIndex() @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17) // 内容字体设置成20 @ContentFontStyle(fontHeightInPoints = 20) public class DemoStyleData { // 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex() @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14) // 字符串的头字体设置成20 @HeadFontStyle(fontHeightInPoints = 30) // 字符串的内容的背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex() @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40) // 字符串的内容字体设置成20 @ContentFontStyle(fontHeightInPoints = 30) @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; @ExcelProperty("数字标题") private Double doubleData; } // 写入时配置自定义样式 @Test public void annotationStyleWrite() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.write(fileName, DemoStyleData.class).sheet().doWrite(data()); }

写入时自定义样式

// 写入时自定义样式 @Test public void styleWrite() { String fileName = PATH + "EasyExcelTest.xls"; // 头的策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 背景设置为红色 headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short)20); headWriteCellStyle.setWriteFont(headWriteFont); // 内容的策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定 contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND); // 背景绿色 contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex()); WriteFont contentWriteFont = new WriteFont(); // 字体大小 contentWriteFont.setFontHeightInPoints((short)20); contentWriteCellStyle.setWriteFont(contentWriteFont); // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭 EasyExcel.write(fileName, DemoData.class).registerWriteHandler(horizontalCellStyleStrategy).sheet("模板") .doWrite(data()); }

合并单元格

// 合并单元格(注解方式) @Test public void mergeWrite() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.write(fileName, DemoMergeData.class).sheet().doWrite(data()); } // 合并单元格 @Test public void mergeWrite2() { String fileName = PATH + "EasyExcelTest.xls"; LoopMergeStrategy strategy = new LoopMergeStrategy(2, 0); // 每2个合并，第0列元素 EasyExcel.write(fileName, DemoData.class).registerWriteHandler(strategy).sheet().doWrite(data()); }

使用table去写入

// 使用table去写入 @Test public void tableWrite() { String fileName = PATH + "EasyExcelTest.xls"; // 这里直接写多个table的案例了，如果只有一个 也可以直一行代码搞定，参照其他案例 // 这里 需要指定写用哪个class去写 ExcelWriter excelWriter = null; try { excelWriter = EasyExcel.write(fileName, DemoData.class).build(); // 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了 WriteSheet writeSheet = EasyExcel.writerSheet("模板").needHead(Boolean.FALSE).build(); // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要 WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build(); WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build(); // 第一次写入会创建头 excelWriter.write(data(), writeSheet, writeTable0); // 第二次写如也会创建头，然后在第一次的后面写入数据 excelWriter.write(data(), writeSheet, writeTable1); } finally { // 千万别忘记finish 会帮忙关闭流 if (excelWriter != null) { excelWriter.finish(); } } }

动态头

private List<List<String>> head() { List<List<String>> list = new ArrayList<List<String>>(); List<String> head0 = new ArrayList<String>(); head0.add("字符串" + System.currentTimeMillis()); List<String> head1 = new ArrayList<String>(); head1.add("数字" + System.currentTimeMillis()); List<String> head2 = new ArrayList<String>(); head2.add("日期" + System.currentTimeMillis()); list.add(head0); list.add(head1); list.add(head2); return list; } // 动态头，实时生成头写入 @Test public void dynamicHeadWrite() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.write(fileName) // 这里放入动态头 .head(head()).sheet("模板") // 当然这里数据也可以用 List<List<String>> 去传入 .doWrite(data()); }

自动列宽（不太精确）

@Data public class LongestMatchColumnWidthData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题很长日期标题很长日期标题很长很长") private Date date; @ExcelProperty("数字") private Double doubleData; } // 自动列宽 @Test public void longestMatchColumnWidthWrite() { String fileName = PATH + "EasyExcelTest.xls"; // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭 EasyExcel.write(fileName, LongestMatchColumnWidthData.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("模板").doWrite(dataLong()); } private List<LongestMatchColumnWidthData> dataLong() { List<LongestMatchColumnWidthData> list = new ArrayList<LongestMatchColumnWidthData>(); for (int i = 0; i < 10; i++) { LongestMatchColumnWidthData data = new LongestMatchColumnWidthData(); data.setString("测试很长的字符串测试很长的字符串测试很长的字符串" + i); data.setDate(new Date()); data.setDoubleData(1000000000000.0); list.add(data); } return list; }

自定义拦截器

对第一行第一列的头超链接到:https://github.com/alibaba/easyexcel

/** * 自定义拦截器。对第一行第一列的头超链接到:https://github.com/alibaba/easyexcel * * @author Jiaju Zhuang */ public class CustomCellWriteHandler implements CellWriteHandler { private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class); @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 这里可以对cell进行任何操作 LOGGER.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex()); if (isHead && cell.getColumnIndex() == 0) { CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper(); Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL); hyperlink.setAddress("https://github.com/alibaba/easyexcel"); cell.setHyperlink(hyperlink); } } }

对第一列第一行和第二行的数据新增下拉框，显示 测试1 测试2

/** * 自定义拦截器.对第一列第一行和第二行的数据新增下拉框，显示 测试1 测试2 * * @author Jiaju Zhuang */ public class CustomSheetWriteHandler implements SheetWriteHandler { private static final Logger LOGGER = LoggerFactory.getLogger(CustomSheetWriteHandler.class); @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { LOGGER.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo()); // 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行 CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0); DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper(); DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"测试1", "测试2"}); DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList); writeSheetHolder.getSheet().addValidationData(dataValidation); } } // 自定义拦截器 @Test public void customHandlerWrite() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.write(fileName, DemoData.class).registerWriteHandler(new CustomCellWriteHandler()) .registerWriteHandler(new CustomSheetWriteHandler()).sheet().doWrite(data()); }

插入批注

/** * 自定义拦截器.新增注释,第一行头加批注 * * @author Jiaju Zhuang */ public class CommentWriteHandler extends AbstractRowWriteHandler { @Override public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) { if (isHead) { Sheet sheet = writeSheetHolder.getSheet(); Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch(); // 在第一行 第二列创建一个批注 Comment comment = drawingPatriarch.createCellComment( new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 0, (short) 2, 1)); // 输入批注信息 comment.setString(new HSSFRichTextString("创建批注!")); // 将批注添加到单元格对象中 sheet.getRow(0).getCell(1).setCellComment(comment); } } } // 插入批注 @Test public void commentWrite() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.write(fileName, DemoData.class).inMemory(Boolean.TRUE).registerWriteHandler(new CommentWriteHandler()).sheet().doWrite(data()); }

读取指定列名或下标

@Data public class IndexOrNameData { /** * 强制读取第三个 这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配 */ @ExcelProperty(index = 2) private Double doubleData; /** * 用名字去匹配，这里需要注意，如果名字重复，会导致只有一个字段读取到数据 */ @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; } /** * 模板的读取类 * * @author Jiaju Zhuang */ public class IndexOrNameDataListener extends AnalysisEventListener<IndexOrNameData> { private static final Logger LOGGER = LoggerFactory.getLogger(IndexOrNameDataListener.class); /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 */ private static final int BATCH_COUNT = 5; List<IndexOrNameData> list = new ArrayList<IndexOrNameData>(); @Override public void invoke(IndexOrNameData data, AnalysisContext context) { LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data)); System.out.println("解析到一条数据:" + JSON.toJSONString(data)); list.add(data); if (list.size() >= BATCH_COUNT) { saveData(); list.clear(); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { saveData(); LOGGER.info("所有数据解析完成！"); } /** * 加上存储数据库 */ private void saveData() { LOGGER.info("{}条数据，开始存储数据库！", list.size()); LOGGER.info("存储数据库成功！"); } } // 读取指定列名或下标 @Test public void indexOrNameRead() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.read(fileName, IndexOrNameData.class, new IndexOrNameDataListener()).sheet().doRead(); }

读取多个sheet

读取全部sheet

// 读取全部sheet @Test public void repeatedRead() { String fileName = PATH + "EasyExcelTest.xls"; EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll(); }

读取部分sheet

// 读取部分sheet @Test public void repeatedRead2() { String fileName = PATH + "EasyExcelTest.xls"; ExcelReader excelReader = null; try { excelReader = EasyExcel.read(fileName).build(); // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener ReadSheet readSheet1 = EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build(); ReadSheet readSheet2 = EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build(); // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能 excelReader.read(readSheet1, readSheet2); } finally { if (excelReader != null) { // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的 excelReader.finish(); } } }