Apache POI与Alibaba EasyExcel 的使用

素颜马尾好姑娘i 2022-12-11 10:30 149阅读 0赞

**Apache POI 与 Alibaba EasyExcel 的使用**

*  **Apache POI**

<dependencies>
      	
      	<dependency>
      		<groupId>org.apache.poi</groupId>
      		<artifactId>poi</artifactId>
      		<version>3.9</version>
      	</dependency>
      	
      	<dependency>
      		<groupId>org.apache.poi</groupId>
      		<artifactId>poi-ooxml</artifactId>
      		<version>3.9</version>
      	</dependency>
      	
      	<dependency>
      		<groupId>joda-time</groupId>
      		<artifactId>joda-time</artifactId>
      		<version>2.10.1</version>
      	</dependency>
      	<dependency>
      		<groupId>junit</groupId>
      		<artifactId>junit</artifactId>
      		<version>4.12</version>
      	</dependency>
    </dependencies>

*  **测试写（03版）**

String Path = "D:\\data\\";
    
    @Test
    public void testWrite03() throws Exception { 
    	// 创建工作簿对象 03
    	Workbook workbook = new HSSFWorkbook();
    	// 创建一个工作表
    	Sheet sheet0 = workbook.createSheet("BLU测试表");
    	// 创建一行
    	Row row0 = sheet0.createRow(0);
    	// 创建一个单元格
    	Cell cell00 = row0.createCell(0);
    	cell00.setCellValue("编号");
    	Cell cell01 = row0.createCell(1);
    	cell01.setCellValue("姓名");
    	Row row1 = sheet0.createRow(1);
    	Cell cell10 = row1.createCell(0);
    	cell10.setCellValue(1);
    	Cell cell11 = row1.createCell(1);
    	cell11.setCellValue("BLU");
    	Cell cell12 = row1.createCell(2);
    	String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    	cell12.setCellValue(time);
    	FileOutputStream fos = new FileOutputStream(Path + "test03.xls");
    	workbook.write(fos);
    	fos.close();
    	System.out.println("文件生成完毕");
    }

![在这里插入图片描述][20200930112629473.png_pic_left]

*  **测试写（07版）**

String Path = "D:\\data\\";
    
    @Test
    public void testWrite07() throws Exception { 
    	Workbook workbook = new XSSFWorkbook();
    	Sheet sheet0 = workbook.createSheet("BLU测试表");
    	Row row0 = sheet0.createRow(0);
    	Cell cell00 = row0.createCell(0);
    	cell00.setCellValue("编号");
    	Cell cell01 = row0.createCell(1);
    	cell01.setCellValue("姓名");
    	Row row1 = sheet0.createRow(1);
    	Cell cell10 = row1.createCell(0);
    	cell10.setCellValue(1);
    	Cell cell11 = row1.createCell(1);
    	cell11.setCellValue("BLU");
    	Cell cell12 = row1.createCell(2);
    	String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    	cell12.setCellValue(time);
    	FileOutputStream fos = new FileOutputStream(Path + "test07.xlsx");
    	workbook.write(fos);
    	fos.close();
    	System.out.println("文件生成完毕");
    }

![在这里插入图片描述][20200930113000437.png_pic_left]

*  **测试大数据写入（03版）**

String Path = "D:\\data\\";
    	
    /** * 65536行数据耗时：2.815s * 最多只能创建65536行 */
    	
    @Test
    public void testWrite03BigData() throws Exception { 
    	long begin = System.currentTimeMillis();
    	HSSFWorkbook workbook = new HSSFWorkbook();
    	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 fos = new FileOutputStream(Path + "testWrite03BigData.xls");
    	workbook.write(fos);
    	fos.close();
    	long end = System.currentTimeMillis();
    	System.out.println((double) (end - begin) / 1000);
    }

*  **测试大数据写入（07版）**

String Path = "D:\\data\\";
    
    /** * 65536行数据耗时6.843s * 10万行数据耗时13.028s * 内存占用大 */
    	 
    @Test
    public void testWrite07BigData() throws Exception { 
    	long begin = System.currentTimeMillis();
    	XSSFWorkbook 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 fos = new FileOutputStream(Path + "testWrite07BigData.xlsx");
    	workbook.write(fos);
    	fos.close();
    	long end = System.currentTimeMillis();
    	System.out.println((double) (end - begin) / 1000);
    
    }

*  **使用 SXSSFWorkbook 测试大数据写入**

String Path = "D:\\data\\";
    
    /** * 10万行数据耗时1.916s */
    
    @Test
    public void testWrite07BigDataS() throws Exception { 
    	long begin = System.currentTimeMillis();
    	SXSSFWorkbook workbook = new SXSSFWorkbook();
    	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 fos = new FileOutputStream(Path + "testWrite07BigDataS.xlsx");
    	workbook.write(fos);
    	fos.close();
    	// 清除临时文件
    	workbook.dispose();
    	long end = System.currentTimeMillis();
    	System.out.println((double) (end - begin) / 1000);
    }

*  **测试读（03版）**

String Path = "D:\\data\\";
    
    @Test
    public void testRead03() throws Exception { 
    	FileInputStream fis = new FileInputStream(Path + "test03.xls");
    	Workbook workbook = new HSSFWorkbook(fis);
    	Sheet sheet = workbook.getSheetAt(0);
    	Row row = sheet.getRow(1);
    	Cell cell0 = row.getCell(0);
    	double value0 = cell0.getNumericCellValue();
    	System.out.println(value0);
    	Cell cell1 = row.getCell(1);
    	String value1 = cell1.getStringCellValue();
    	System.out.println(value1);
    	fis.close();
    }

1.0
    BLU

*  **测试读（07版）**

String Path = "D:\\data\\";
    
    @Test
    public void testRead07() throws Exception { 
    	FileInputStream fis = new FileInputStream(Path + "test07.xlsx");
    	Workbook workbook = new XSSFWorkbook(fis);
    	Sheet sheet = workbook.getSheetAt(0);
    	Row row = sheet.getRow(1);
    	Cell cell0 = row.getCell(0);
    	double value0 = cell0.getNumericCellValue();
    	System.out.println(value0);
    	Cell cell1 = row.getCell(1);
    	String value1 = cell1.getStringCellValue();
    	System.out.println(value1);
    	fis.close();
    }

1.0
    BLU

*  **根据数据类型读取的示例：**

**BLU.xls文件：**  
![在这里插入图片描述][20200930134742173.png_pic_left]

String Path = "D:\\data\\";
    
    @Test
    public void testCellType() throws Exception { 
    	FileInputStream fis = new FileInputStream(Path + "BLU.xls");
    	Workbook workbook = new HSSFWorkbook(fis);
    	Sheet sheet = workbook.getSheetAt(0);
    	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) { 
    				String cellValue = cell.getStringCellValue();
    				System.out.print(cellValue + " | ");
    			}
    		}
    	}
    	System.out.println();
    	int rowCount = sheet.getPhysicalNumberOfRows();
    	for (int rowNum = 1; rowNum < rowCount; rowNum++) { 
    		Row rowData = sheet.getRow(rowNum);
    		if (rowData != null) { 
    			int cellCount = rowData.getPhysicalNumberOfCells();
    			for (int cellNum = 0; cellNum < cellCount; cellNum++) { 
    				Cell cell = rowData.getCell(cellNum);
    				if (cell != null) { 
    					int cellType = cell.getCellType();
    					switch (cellType) { 
    					case HSSFCell.CELL_TYPE_STRING:
    						System.out.print(cell.getStringCellValue());
    						break;
    					case HSSFCell.CELL_TYPE_BOOLEAN:
    						System.out.print(String.valueOf(cell.getBooleanCellValue()));
    						break;
    					case HSSFCell.CELL_TYPE_BLANK:
    						break;
    					case HSSFCell.CELL_TYPE_NUMERIC:
    						if(HSSFDateUtil.isCellDateFormatted(cell)) { 
    							Date date = cell.getDateCellValue();
    							String s = new DateTime(date).toString("yyyy-MM-dd");
    							System.out.print(s);
    						}else { 
    							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    							System.out.print(cell.toString());
    						}
    						break;
    					case HSSFCell.CELL_TYPE_ERROR:
    						break;
    					}
    					System.out.print(" | ");
    				}
    			}
    			System.out.println();
    		}
    	}
    	fis.close();
    }

手机号 | 日期 | 订单号 | 商品编号 | 商品名称 | 价格 | 销售数量 | 销售金额 | 已发货 | 
    15651776666 | 2020-09-30 | 000001 | 1 | 蒙牛 | 65.5 | 1 | 65.5 | true | 
    15651776666 | 2020-10-01 | 000002 | 2 | 脑白金 | 100 | 10 | 1000 | false |

*  **读取计算函数的示例：**

**func.xls文件：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JMVV8xMTE_size_16_color_FFFFFF_t_70_pic_left]

String Path = "D:\\data\\";
    
    @Test
    public void testFormula() throws Exception { 
    	FileInputStream fis = new FileInputStream(Path + "func.xls");
    	Workbook workbook = new HSSFWorkbook(fis);
    	Sheet sheet = workbook.getSheetAt(0);
    	Row row = sheet.getRow(4);
    	Cell cell = row.getCell(0);
    	FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
    	int cellType = cell.getCellType();
    	switch(cellType) { 
    	case Cell.CELL_TYPE_FORMULA:
    		String formula = cell.getCellFormula();
    		System.out.println(formula);
    		CellValue evaluate = formulaEvaluator.evaluate(cell);
    		String cellValue = evaluate.formatAsString();
    		System.out.println(cellValue);
    		break;
    	}
    
    }

SUM(A2:A4)
    600.0

--------------------

*  **Alibaba EasyExcel**

<dependencies>
      	<dependency>
      		<groupId>com.alibaba</groupId>
      		<artifactId>easyexcel</artifactId>
      		<version>2.2.0-beta2</version>
      	</dependency>
      
      <dependency>
      	<groupId>joda-time</groupId>
      	<artifactId>joda-time</artifactId>
      	<version>2.10.1</version>
      </dependency>
      <dependency>
      	<groupId>org.projectlombok</groupId>
      	<artifactId>lombok</artifactId>
      	<version>1.18.12</version>
      </dependency>
      <dependency>
      	<groupId>com.alibaba</groupId>
      	<artifactId>fastjson</artifactId>
      	<version>1.2.62</version>
      </dependency>
      <dependency>
      	<groupId>junit</groupId>
      	<artifactId>junit</artifactId>
      	<version>4.12</version>
      </dependency>	
     </dependencies>

*  实体类：

package com.blu.easy;
    
    import java.util.Date;
    import com.alibaba.excel.annotation.ExcelIgnore;
    import com.alibaba.excel.annotation.ExcelProperty;
    import lombok.Data;
    
    @Data
    public class DemoData { 
        @ExcelProperty("字符串标题")
        private String string;
        @ExcelProperty("日期标题")
        private Date date;
        @ExcelProperty("数字标题")
        private Double doubleData;
        /** * 忽略这个字段 */
        @ExcelIgnore
        private String ignore;
    }

*  监听器：

package com.blu.easy;
    
    
    import java.util.ArrayList;
    import java.util.List;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.alibaba.fastjson.JSON;
    
    public class DemoDataListener extends AnalysisEventListener<DemoData> { 
    	
        private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
        private static final int BATCH_COUNT = 5;
        List<DemoData> list = new ArrayList<DemoData>();
    
        private DemoDAO demoDAO;
        
        public DemoDataListener() { 
            demoDAO = new DemoDAO();
        }
    
        public DemoDataListener(DemoDAO demoDAO) { 
            this.demoDAO = demoDAO;
        }
        
        @Override
        public void invoke(DemoData data, AnalysisContext context) { 
        	System.out.println(JSON.toJSONString(data));
            list.add(data);
            // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
            if (list.size() >= BATCH_COUNT) { 
                saveData();
                // 存储完成清理 list
                list.clear();
            }
        }
        /** * 所有数据解析完成了 都会来调用 * * @param context */
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) { 
            // 这里也要保存数据，确保最后遗留的数据也存储到数据库
            saveData();
            LOGGER.info("所有数据解析完成！");
        }
        /** * 加上存储数据库 */
        private void saveData() { 
            LOGGER.info("{}条数据，开始存储数据库！", list.size());
            demoDAO.save(list);
            LOGGER.info("存储数据库成功！");
        }
    }

*  DAO：

package com.blu.easy;
    
    import java.util.List;
    
    /** * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。 **/
    public class DemoDAO { 
        public void save(List<DemoData> list) { 
        	// 持久化操作
            // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
        }
    }

*  测试：

package com.blu.easy;
    
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    import org.junit.Test;
    
    import com.alibaba.excel.EasyExcel;
    
    public class EasyTest { 
    	
    	String Path = "D:\\data\\";
    	
    	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 + "easyTest.xlsx";
            EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    
        }
        
        /** * 最简单的读 */
        @Test
        public void simpleRead() { 
            // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        	String fileName = Path + "easyTest.xlsx";
            // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
            EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
        }
    
    }

写入结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JMVV8xMTE_size_16_color_FFFFFF_t_70_pic_left 1]  
读取结果：

{"date":1601434567000,"doubleData":0.56,"string":"字符串0"}
    {"date":1601434567000,"doubleData":0.56,"string":"字符串1"}
    {"date":1601434567000,"doubleData":0.56,"string":"字符串2"}
    {"date":1601434567000,"doubleData":0.56,"string":"字符串3"}
    {"date":1601434567000,"doubleData":0.56,"string":"字符串4"}
    {"date":1601434567000,"doubleData":0.56,"string":"字符串5"}
    {"date":1601434567000,"doubleData":0.56,"string":"字符串6"}
    {"date":1601434567000,"doubleData":0.56,"string":"字符串7"}
    {"date":1601434567000,"doubleData":0.56,"string":"字符串8"}
    {"date":1601434567000,"doubleData":0.56,"string":"字符串9"}

[20200930112629473.png_pic_left]: /images/20221123/77db7a912c764ca4a9dbd87f8932908f.png
[20200930113000437.png_pic_left]: /images/20221123/2c36b21dd12c4c87807eb9e60a6f2bf0.png
[20200930134742173.png_pic_left]: /images/20221123/899a25c0ab2948239df938c7cb782df6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JMVV8xMTE_size_16_color_FFFFFF_t_70_pic_left]: /images/20221123/dd3c92e28b284464a4f75df975c546dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JMVV8xMTE_size_16_color_FFFFFF_t_70_pic_left 1]: /images/20221123/c3f489514743445d873b9a412148ede4.png