Excel文件读写(创建与解析)

深碍√TFBOYSˉ_ 2024-05-07 17:12 61阅读 0赞

可以进行Excel文件处理的主流技术包括：

> ① Apache POI ( 基于DOM方式进行解析，将文件直接加载内存，速度较快，适合Excel文件数据量不大的应用场景)
> 
> ② JXL (适合Excel文件数据量不大的应用场景)
> 
> ③ Alibaba EasyExcel (采用逐行读取的解析模式，将每一行的解析结果以观察者的模式通知处理(AnalysisEventListener),所以比较适合数据量较大的Excel文件解析)  
> (其中常用的是Apache POI，Alibaba EasyExcel。)

### 一.Apache POI ###

> POI提供了对不同格式文件的解析：  
> HSSF用于解析旧版本的Excel文件(*.xls)，由于旧版本的Excel文件只能存在65535行数据，所以已经不常用，目前常用XSSF解析Excel文件(*.xlsx)。

首先添加jjar包依赖：![在这里插入图片描述][37db306b85bd496e95ffa14c12bec6fd.png]

**1. Workbook**  
Workbook接口代表一个Excel文件，通常创建它的实现类对象来创建(/加载)Excel文件，常用实现类是**XSSFWorkbook**。

(1).创建Excel文件

try (Workbook workbook = new XSSFWorkbook();
    				FileOutputStream out =
    						new FileOutputStream("E:\\猿\\demo.xlsx")) {
    			
    			// 将 workbook 对象中包含的数据，通过输出流，写入至Excel文件
    			workbook.write(out);
    			} catch (IOException e) {
    			e.printStackTrace();
    		}

(2).加载(解析)Excel文件

// 通过输入流，读取excel文件
    		FileInputStream in = new FileInputStream("E:\\成绩.xlsx");
    		// 将输入流传入Workbook
    		Workbook workbook = new XSSFWorkbook(in);

注意还要关闭输入流，可以调用 in.close()，我们通常使用 try catch块来自动关闭：

try (Workbook workbook = new XSSFWorkbook();
    				FileOutputStream out =
    						new FileOutputStream("E:\\demo.xlsx")) {
    			// 将 workbook 对象中包含的数据，通过输出流，写入至Excel文件
    			workbook.write(out);
    		} catch (IOException e) {
    			e.printStackTrace();
    		}

（3）sheet (工作簿)  
通过Workbook来进行工作簿sheet的创建和获取  
①.sheet的创建

// 按照默认名称创建工作簿
    Sheet sheet1 = workbook.createSheet();
    
    // 按照自定义名称创建工作簿
    Sheet sheet2 = workbook.createSheet("自定义工作簿");

②.sheet的获取

// 按照工作簿下标获取Sheet
    Sheet sheet01 = workbook.getSheetAt(0);
    
    // 按照工作簿名称获取Sheet
    Sheet sheet02 = workbook.getSheet("Sheet0");

③.工作簿的数量

int sheetNum = workbook.getNumberOfSheets();
    System.out.println("工作簿数量：" + sheetNum);

④.工作簿的数据行

System.out.println("工作簿0的数据行：" + sheet0.getLastRowNum());

(3).Row(数据行)  
①.创建数据行

Row row0 = sheet0.createSheet(0);

②.获取首/尾行下标

int first = sheet.getFirstRowNum();
    int last = sheet.getLastRowNum();

③. 根据下标获取指定行

Row row = sheet.getRow(0); //第一行

④.遍历指定区域行

// 遍历所有行
    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        System.out.println(row);
    }

(4).Cell(单元格)  
①.单元格的创建与获取

Cell cell0 = Row.createCell(0); // cell的创建
    Cell cell = Row.getCell(0); // cell的获取

②.设置列头(表格第一行)的内容

cellHead.setCellValue("序号");
    cellHead1.setCellValue("姓名");
    cellHead2.setCellValue("性别");
    cellHead3.setCellValue("创建日期");

②.获取单元格内容

System.out.println("序号：" + cell0.getNumericCellValue());
    System.out.println("姓名：" + cell1.getStringCellValue());

③.设置单元格内容

cell0.setCellValue(Math.random()*1000);

④.获取单元格类型

CellType type = cell.getCellType();

⑤.设置单元格格式

// 创建单元格格式
         // 获取格式编码
          DataFormat dataFormat = workbook.createDataFormat();
          short formatCode = dataFormat.getFormat("yyyy-MM-dd HH:mm:ss");
    	  System.out.println("单元格格式编码：" + formatCode);
    	
    	 // 创建cellStyle单元格格式对象
    	  CellStyle cellStyle = workbook.createCellStyle();
    	  cellStyle.setDataFormat(formatCode); // 设置单元格格式编码
          ...
         // 通过设置cellStyle单元格格式对象，来正常显示日期
          Cell cell0 = Row.createCell();
    	  cell0.setCellStyle(cellStyle); // 设置单元格格式
    	  cell0.setCellValue(new Date());	 // 保存日期至本单元格

### 二. 超大Excel文件的读写 ###

**1.使用POI写入 ===> SXXSFWorkbook**

> 它可以写入非常大的数据量，如100w甚至更多，写数据速度快，占用内存更少。  
> 它在写入过程中会产生临时文件，默认为100条数据被保存至内存中，如果超过这个默认数据量，前面的数据就会被写入临时文件，可通过设置SXSSFWorkbook的构造参数来设置每次在内存中保持的行数，当达到这个值时，会把这些数据flush到磁盘上，这样就不会出现内存不够的情况。

SXSSFWorkbook写入10w行数据(由于时间问题就用用10w行数据来测试)  
实现代码如下：

//  Workbook的实现类 SXSSFWorkbook:
    		try (Workbook workbook = new SXSSFWorkbook();
    				FileOutputStream out = new FileOutputStream("E:\\10w.xlsx")) {
    			long begin = System.currentTimeMillis(); 
    			Sheet sheet = workbook.createSheet();
    			
    			// 创建列头
    			Row headRow = sheet.createRow(0);
    		    // 设置列头单元格内容
    			Cell cellVal0 = headRow.createCell(0);
    			cellVal0.setCellValue("序号");
    			
    			// 创建数据行
    			for(int i = 0;i < 100000;i++) {
    				String name = "A"  + i;
    				// 创建行
    				Row row = sheet.createRow(i+1);
    				
    				// 创建单元格
    				Cell cell0 = row.createCell(0); // 序号
    				cell0.setCellValue(String.valueOf(i+1));
    			}	
    		workbook.write(out);
    		long after = System.currentTimeMillis();  
    System.out.println("SXSSFWorkbook写入10w行数据所耗时：" + (after-begin) + "ms");
    		
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}

运行结果如下：  
![在这里插入图片描述][54d1b412ad49469da193d568e8a86e64.png]

CPU使用如下  
![在这里插入图片描述][86398b176c7d45339ddb0c9d9b3f08de.png]

**2.使用Alibaba EasyExcel写入**  
首先下载Alibaba EasyExcel相关jar包  
![在这里插入图片描述][913bef3d1a924055b6ccf77a0266e70c.png]  
使用EasyExcel写入10w订单数据

// easyExcel:对超大文件读取快，内存占用小
    // 写入10w订单数据
    		long begin = System.currentTimeMillis();
    		// 10w
    		EasyExcel.write("E:\\easy10w.xlsx",Order.class).sheet("订单列表").doWrite(data());
    	
    		long after = System.currentTimeMillis();
    		System.out.println("共耗时" + (after-begin) + "ms");
    	
    	}
    	// 创建100w条订单数据
    	private static List<Order> data(){
    		List<Order> list = new ArrayList<Order>();
    		
    		for(int i = 0;i < 100000;i++) {
    			list.add(new Order());
    			}
    		return list;
    		}

运行结果如下：  
![在这里插入图片描述][d95eaee6d3ba42b9b99a25e51374b273.png]  
CPU使用率如下：  
![在这里插入图片描述][6115297839fe4139b3b60bea3ee7432a.png]

> 由此可见，10w的数据，相比之下，SXSSFWorkbook运行速度最快，CPU占用率最高；EasyExcel运行速度较低，CPU占用率最低。由于poi的内存消耗很大，EasyExcel可以大量减少内存的占用(原因是EasyExcel采用逐行读取的解析模式，并没有将文件数据一次性全部加载到内存，是在磁盘上一行一行读取数据)，故推荐使用EasyExcel。

[37db306b85bd496e95ffa14c12bec6fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/07/34edbdc97b12440abcb9ea67eb08f496.png
[54d1b412ad49469da193d568e8a86e64.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/07/c6ace4b75b8443f6bf3dc97c26e67413.png
[86398b176c7d45339ddb0c9d9b3f08de.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/07/f827ff4f2fda4786b87737150ec67292.png
[913bef3d1a924055b6ccf77a0266e70c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/07/8876be7ebef14c91989167db7ebc3a45.png
[d95eaee6d3ba42b9b99a25e51374b273.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/07/b92fff5942044843b517d9be45c991f9.png
[6115297839fe4139b3b60bea3ee7432a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/07/d9179fe2b05f490ca3ffa302699c079f.png