【工作技能】Excel处理

关于Java如何操作Excel的读写

1 Excel处理

1.1 相关技术

• Apache POI
• easyExcel

---

2 POI

2.1 介绍

• 官网：https://poi.apache.org/

• POI提供API给Java程序对Micorsoft Office格式档案读和写的功能

2.2 基本功能

• HSSF – 提供读写 Microsoft Excel 格式方案的功能（03版excel）
• XSSF – 提供读写Microsoft Excel OOXML格式档案的功能（07版excel）
• HWPF – 提供读写Microsoft Word 格式档案的功能
• HSLF – 提供读写Microsoft PowerPoint 格式档案的功能
• HDGF – 提供读写Microsoft Visio 格式档案的功能

2.3 Excel内容划分

2.4 Excel写入

• （1）依赖导入

  <!-- apache-poi：处理office文件（03版excel-xls） -->
  <dependency>
  	<groupId>org.apache.poi</groupId>
  	<artifactId>poi</artifactId>
  	<version>5.0.0</version>
  </dependency>
  
  <!-- apache-poi (07版excel-xlsx) -->
  <dependency>
  	<groupId>org.apache.poi</groupId>
  	<artifactId>poi-ooxml</artifactId>
  	<version>5.0.0</version>
  </dependency>

• （2）写入代码

  @Test
  public void wirte03Test() throws Exception{
  	//1、创建03版工作簿
  	Workbook workbook = new HSSFWorkbook();
  
  	//2、创建工作表
  	Sheet sheet = workbook.createSheet("测试表");
  
  	//3、创建单元格
  	Row row0 = sheet.createRow(0); //行
  	Cell cell = row0.createCell(0);//列
  	//第一行第一列，对应excel的(1， A)单元格
  
  	//4、写入数据
  	cell.setCellValue("Up主名单");
  
  	//5、将工作簿存储到磁盘上
  	FileOutputStream fos = new FileOutputStream("./src/main/resources/excel/03Excel.xls"); //创建流
  	workbook.write(fos); //写入磁盘
  	fos.close(); //关闭流
  }
  
  //03与07基本一致，创建对象该为XSSFWorkBook，存储文件后缀名改为xlsx

2.5 大数据写入

• （03版）HSSF
• 优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
• 缺点：最多处理65536行，否则会抛出异常

  @Test
  public void writeBig03Test() throws Exception {
  	//1、创建工作簿
  	Workbook workbook = new HSSFWorkbook();
  
  	//2、创建工作表
  	Sheet sheet = workbook.createSheet("测试表");
  
  	//3、循环写入数据
  	for(int rowNum = 0; rowNum < 65536 ; rowNum ++) {
  		Row row = sheet.createRow(rowNum);
  		for (int cellNum = 0 ; cellNum < 5 ; cellNum++) {
  			Cell cell = row.createCell(cellNum);
  			cell.setCellValue("( " + rowNum + ", " + cellNum + " )");
  		}
  	}
  
  	//4、写入磁盘
  	FileOutputStream fos = new FileOutputStream("./src/main/resources/excel/writeBig03.xls");
  	workbook.write(fos);
  	fos.close();
  }

• （07版）XSSF
• 优点：处理的数据更多
• 缺点：写数据非常慢，非常消耗内存，会发生内存溢出

• （07版升级）SXSSF
• 优点：可以写非常大的数据量，写数据的速度更快，占用更少内存
• 缺点：过程会产生临时文件，需要清理临时文件（默认100条记录保存在内存，超过会将前面数据写入临时文件，可以通过带参创建对象时，带定义内存保存数据个数）
• 通过调用其dispose()方法来处理临时文件，子类特有方法

2.6 Excel读取

@Test
public void readExcel03Test() throws Exception {
	//1、创建输入流
	FileInputStream fis = new FileInputStream("./src/main/resources/excel/03Excel.xls");

	//2、根据流获取工作簿
	HSSFWorkbook sheets = new HSSFWorkbook(fis);

	//3、获取表1
	HSSFSheet sheet = sheets.getSheetAt(0);

	//4、获取单元格
	HSSFRow row = sheet.getRow(0); //行
	HSSFCell cell = row.getCell(0); // 列

	//5、获取值（注意值的类型）
	String value = cell.getStringCellValue();
	System.out.println(value);

	//6、关闭流
	fis.close();
}

// 07版读取与03版基本一致，注意文件后缀名以及创建的对象即可

2.7 判断读取数据类型

@Test
public void readExcelType() throws Exception {
	//1、创建输入流
	FileInputStream fis = new FileInputStream("./src/main/resources/excel/03Excel.xls");

	//2、根据流获取工作簿
	HSSFWorkbook workbook = new HSSFWorkbook(fis);

	//3、获取工作表
	HSSFSheet sheet = workbook.getSheetAt(0);

	//4、获取单元格
	HSSFRow row = sheet.getRow(1);
	HSSFCell cell = row.getCell(1);

	//5、根据数据类型获取数据
	CellType cellType = cell.getCellType(); // 获取数据类型（枚举类）
	/*
		_NONE(-1),  //没有数据
		NUMERIC(0), //数字，判断是否普通数字还是日期
		STRING(1),  //字符串
		FORMULA(2), //公式
		BLANK(3),   //空
		BOOLEAN(4), //布尔型
		ERROR(5);   //错误
	 */
	switch (cellType) { //switch对枚举类进行分支选择读取
		case _NONE:
			break;
		case NUMERIC:
			if(DateUtil.isCellDateFormatted(cell)) { //判断是否日期
				Date date = cell.getDateCellValue();
				System.out.println(date);
			}else {
				double num = cell.getNumericCellValue();
				System.out.println(num);
			}
			break;
		case STRING:
			String value = cell.getStringCellValue();
			System.out.println(value);
			break;
		case FORMULA:
			try {
				value = String.valueOf(cell.getNumericCellValue());
			} catch (IllegalStateException e) {
				value = String.valueOf(cell.getRichStringCellValue());
			}
			System.out.println(value);
			break;
		case BLANK:
			break;
		case BOOLEAN:
			boolean bool = cell.getBooleanCellValue();
			System.out.println(bool);
		case ERROR:
			break;
	}


	//6、关闭流
	fis.close();
}

---

3 EasyExcel

3.1 介绍

• EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称
• EasyExcel能大大减少占用内存的主要原因在解析Excel时没有讲文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析

• 官网：https://github.com/alibaba/easyexcel
• 官方文档：https://www.yuque.com/easyexcel/doc/easyexcel

3.2 简单写入

• （1）依赖引入

  <!-- alibaba-easyExcel：excel处理 -->
  <dependency>
  	<groupId>com.alibaba</groupId>
  	<artifactId>easyexcel</artifactId>
  	<version>2.2.8</version>
  </dependency>

• （2）创建实体类

  @Data
  public class Person {
      @ExcelProperty("姓名") //表标题
      private String name;
  
      @ExcelProperty("性别")
      private String sex;
  
      @ExcelIgnore //忽略字段
      private Integer age;
  }

• （3）写入数据

  @Test
  public void simpleWrite() {
  	//1、创建数据（List集合）
  	List<Person> data = new ArrayList<>();
  	Person person1 = new Person();
  	person1.setName("C酱");
  	person1.setSex("女");
  	data.add(person1);
  
  	//2、写入数据
  	String fileName = "./src/main/resources/excel/person.xls";
  	EasyExcel.write(fileName, Person.class) //文件路径，写入对象
  			.sheet(0, "表1") //工作表号，表名
  			.doWrite(data); //写入数据
  }

2.3 高级写入

2.4 简单读取

• （1）创建监听器

  public class PersonListener extends AnalysisEventListener<Person> {
      //easyExcel会一条一条数据进行读取
  
      //读取一条数据调用方法
      @Override
      public void invoke(Person person, AnalysisContext analysisContext) {
          System.out.println(person); //打印读取到的数据
      }
  
      //读取完所有数据调用方法
      @Override
      public void doAfterAllAnalysed(AnalysisContext analysisContext) {
          System.out.println("已全部读取完毕！");
      }
  }

• （2）读取数据

  @Test
  public void simpleRead() {
  	//1、读取数据
  	String fileName = "./src/main/resources/excel/person.xls";
  	EasyExcel.read(fileName, Person.class, new PersonListener()) //文件路径 对象 监听器
  			.sheet(0) //工作表
  			.doRead(); //读取
  }

2.5 高级读取

• （1）读取多表数据

public void repeatedRead() {
    //读取数据
    String fileName = "./src/main/resources/excel/person.xls";
    EasyExcel.read(fileName, Person.class, new PersonListener()).doReadAll();
}

2.4 小结

• EasyExcel并没有比较详细的讲解，因为EasyExcel是中国人所写的，有中文文档，读官方文档压力不大，也更加详细，推荐读取官方文档