浅谈Excel文件解析

日常生活中，我们经常会遇到一些Excel文件，一般我们只会去用，不会去想他到底是怎样进行数据导入和导出的，今天，我们看看在java应用开发过程中，Excel文件的解析过程。

1. 使用-XSSF解析Excel文件

HSSF通常用于解析旧版本(*.xls) Excel文件，由于旧版本只能存储65535行数据，现在已经基本不用。目前主要采用XSSF进行新版本(*.xlsx)的Excel文件解析。

（注意：对Excel文件的解析需要导入第三方的jar包 ）

1.1 创建Excel文件

Workbook接口代表一个Excel文件，常常用于解析创建Excel文件。常用的实现类是XSSFWorkbook。

//创建Excel文件对象
try(Workbook workbook = new XSSFWorkbook();
//通过输出流进行写入
FileOutputStream out = new FileOutputStream("D:\\JavaTest\\20220717.xlsx")){
    workbook.write(out);
}catch (IO exception e){
    e.printStackTrace();
}

1.2 解析Excel文件

//输入流读取本地文件
FileInputStream fis = new FileInputStream("D:\\JavaTest\\20220717.xlsx");
//Excel文件对象
Workbook workbook = new XSSFWorkbook(fis);

1.3 创建工作簿(Sheet)

通过Workbook来创建Sheet(工作簿)对象。

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

1.4 获取工作簿

// 按照下标获取Sheet
Sheet sheet01 = workbook.getSheetAt(0);

// 按照名称获取Sheet
Sheet sheet02 = workbook.getSheet("Sheet0");

//获取工作簿数量
int n = workbook.getNumberOfSheets();

1.5 创建Row(数据行)

通过Sheet来获取(Row)数据行

Row row = sheet.createRow(0);

1.6 获取

//获取首行下标
int first = sheet.getFirstRowNum();
//获取尾行下标
int last = sheet.getLastRowNum();
//根据下标获取指定行
Row row = sheet.getRow(0);

//遍历所有行
for(Row row : sheet) {
    System.out.println(row);
}

遍历指定区域行
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
    Row row = sheet.getRow(i);
    System.out.println(row);
}

1.7 创建Cell(单元格)

通过Row来进行Cell（单元格）创建

Cell cell0 = row.createCell(0);

1.8 设置单元格

//设置单元格的值
cell0.setCellValue(UUID.randomUUID().toString());


//设置单元格样式
DataFormat dataFormat = workbook.createDataFormat();
Short formatCode = dataFormat.getFormat("yyyy-MM-dd HH:mm:ss");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(formatCode);

// 为当前行创建单元格
Cell cell1 = row.createCell(1);
cell1.setCellStyle(cellStyle); // 设置单元格样式
cell1.setCellValue(new Date()); // 保存当前日期时间至本单元格


//设置单元格对齐
CellStyle cellStyle = workbook.createCellStyle();

//设置单元格的水平对齐类型。 此时水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);

// 设置单元格的垂直对齐类型。 此时垂直靠底边
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);

1.9 获取

//根据下标获取单元格
Cell cell = row.getCell(1);

//遍历所有单元格
for(Cell cell : row) {
				
}

//获取单元格类型
CellType type = cell.getCellType();

2. -超大Excel文件的读写

对于一些超大Excel文件的读写，一般使用SXSSFWorkbook进行写入，通过设置构造参数可以设置每次在内存中保存的行数，当达到这个数值时，会把这些数据一次性flush到磁盘上，这样就不会出现内存不够的情况。

try (Workbook workbook = new SXSSFWorkbook(100);
				FileOutputStream fos = new FileOutputStream("c:\\test\\temp.xlsx")) {
    Sheet sheet1 = workbook.createSheet();

    for (int i = 0; i <= 1000000; i++) {
        Row row = sheet1.createRow(i);
        Cell cell0 = row.createCell(0);
        cell0.setCellValue(UUID.randomUUID().toString());

        Cell cell1 = row.createCell(1);
        cell1.setCellValue(new Date());
    }

    workbook.write(fos);
} catch (IOException e) {
    e.printStackTrace();
}

(注：下载alibaba-easyexcel的相关jar包)

例：写入100w条数据和读取100w条数据

1. 准备一个实体类(订单类)

public class Order {
    @ExcelProperty("订单编号")
	private String orderId; // 订单编号
	
    @ExcelProperty("支付金额")
	@NumberFormat("￥#,###")
	private Double payment; // 支付金额
	
    @ExcelProperty(value = "创建日期",converter = LocalDateTimeConverter.class)
	private LocalDateTime creationTime; // 创建时间

	public Order() {
		this.orderId = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddhhmmss"))
				+ UUID.randomUUID().toString().substring(0, 5);
		this.payment = Math.random() * 10000;
		this.creationTime = LocalDateTime.now();
	}

	public String getOrderId() {
		return orderId;
	}

	public void setOrderId(String orderId) {
		this.orderId = orderId;
	}

	public Double getPayment() {
		return payment;
	}

	public void setPayment(Double payment) {
		this.payment = payment;
	}

	public LocalDateTime getCreationTime() {
		return creationTime;
	}

	public void setCreationTime(LocalDateTime creationTime) {
		this.creationTime = creationTime;
	}



	@Override
	public String toString() {
		return "Order [orderId=" + orderId + ", payment=" + payment + ", creationTime=" + creationTime + "]";
	}
}

2. 准备日期时间转换类

public class LocalDateTimeConverter implements Converter<LocalDateTime> {

	@Override
	public Class<LocalDateTime> supportJavaTypeKey() {
		return LocalDateTime.class;
	}

	@Override
	public CellDataTypeEnum supportExcelTypeKey() {
		return CellDataTypeEnum.STRING;
	}

	@Override
	public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
			GlobalConfiguration globalConfiguration) {
		return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
	}

	@Override
	public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
			GlobalConfiguration globalConfiguration) {
		return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
	}

}

3. 写入100w条数据

public class Demo {
	public static void main(String[] args) {
        // 写入100w
        EasyExcel.write("c:\\test\\run\\easy.xlsx", Order.class)
                 .sheet("订单列表")
                 .doWrite(data());
    }
    
    // 创建100w条订单数据
    private static List<Order> data() {
        List<Order> list = new ArrayList<Order>();
        for (int i = 0; i < 1000000; i++) {
            list.add(new Order());
        }
        return list;
    }
}

4. 读取100w条数据

EasyExcel.read("c:\\test\\run\\easy.xlsx", Order.class,new AnalysisEventListener<Order>() {
    @Override
    public void invoke(Order order, AnalysisContext arg1) {
        // 读取每条数据
        orderList.add(order);
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        // 读取到列头
        System.out.println(headMap);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext arg0) {
        // 读取完毕
        System.out.println("END");
    }
}).sheet().doRead();