Java excel分析 java解析excel表格

转载

mob64ca140d61c6 2023-08-31 13:28:25

文章标签 Java excel分析 java 开发语言 eclipse Apache 文章分类 Java 后端开发

前言：

在Java技术生态圈中，可以进行Excel文件处理的主流技术包括：Apache POI、JXL、Alibaba EasyExcel等。
Apache POI基于DOM方式进行解析，将文件直接加载内存，所以速度较快，适合Excel文件数据量不大的应用场景。JXL只支持Excel 2003以下版本，所以不太常见。
Alibaba EasyExcel采用逐行读取的解析模式，将每一行的解析结果以观察者的模式通知处理（AnalysisEventListener），所以比较适合数据体量较大的Excel文件解析。

一、Apache POI

Apache POI是用Java编写的免费开源的跨平台的Java API，Apache POI提供给Java程序对Microsoft Office格式档案进行读写功能的API开源类库。
它分别提供对不同格式文件的解析：
●HSSF －提供读写Microsoft Excel格式档案的功能。
●XSSF －提供读写Microsoft Excel OOXML格式档案的功能。
●HWPF －提供读写Microsoft Word格式档案的功能。
●HSLF －提供读写Microsoft PowerPoint格式档案的功能。
●HDGF －提供读写Microsoft Visio格式档案的功能。

二、XSSF解析Excel文件

HSSF用于解析旧版本（*.xls）Excel文件，由于旧版本的Excel文件只能存在65535行数据，所以目前已经不常用。所以目前主要采用XSSF进行新版本（*.xlsx）Exce文件的解析。

需要的jar包：在我的资源里

WorkBook（Excel文件）

WorkBook是一个接口，所以创建WorkBook引用时需指向子类对象创建好以后所代表的是一个Excel文件

// 解析一个excel文件

		try (// 通过输入流，读取excel文件
				FileInputStream in = new FileInputStream("D:\\1694318113\\FileRecv\\1627356552686.xlsx");
				// 将输入流传入Workbook
				Workbook workbook = new XSSFWorkbook(in)) {
			// Sheet:工作簿
			// 获取工作簿
			int sheetNumber = workbook.getNumberOfSheets();
			System.out.println("工作薄数量：" + sheetNumber);

			// 按照名称获取工作簿
			Sheet sheet1 = workbook.getSheet("Sheet0");
            // 通过下标方式获取工作簿
			Sheet sheet2 = workbook.getSheetAt(1);
			System.out.println("工作薄1中的数据行" + sheet1.getLastRowNum());
			System.out.println("工作薄2中的数据行" + sheet2.getLastRowNum());
		}

我们需要使用IO流，所以推荐将输入流以及创建XSSFWorkbook对象代码块放入try（）{}catch{}块中

需要解析什么类型文件创建什么类型的对象
●HSSF －提供读写Microsoft Excel格式档案的功能。
●XSSF －提供读写Microsoft Excel OOXML格式档案的功能。
●HWPF －提供读写Microsoft Word格式档案的功能。
●HSLF －提供读写Microsoft PowerPoint格式档案的功能。
●HDGF －提供读写Microsoft Visio格式档案的功能。

Sheet （工作簿）

通过Workbook来进行Excel工作簿Sheet对象的获取或创建

工作簿的获取采用下标的方式（工作簿下标从零开始）也可以直接getSheet（工作簿的名字）来获取工作簿

Sheet sheet1 = workbook.getSheet("Sheet0");
Sheet sheet2 = workbook.getSheetAt(1);

获取工作簿的数量

int num = workbook.getNumberofSheets();

Row（数据行）

通过Sheet来进行数据行Row对象的获取或创建

Row row = sheet.createRow(0);

获取首行下标

int first = sheet.getFirstRowNum();

获取尾行下标

int last = sheet.getLastRowNum();

还可以根据下标获取指定行

Row row = sheet.getRow(0);

遍历所有行

for(Row row : sheet) {
    System.out.println(row);
}

遍历指定行，需要使用下标，所以采用for（ ; ; ）{ }循环

for (int i = 1; i <= sheet.getLastRowNum(); i++) {
    Row row = sheet.getRow(i);
    System.out.println(row);
}

Cell（单元格）

通过Row来进行单元格Cell对象的获取或创建

Cell cell0 = row.createCell(0);

设置单元格值

cell0.setCellValue(UUID.randomUUID().toString());

根据下标获取单元格

Cell cell = row.getCell(1);

遍历所有单元格

for(Cell cell : row) {
				
}

获取单元格类型

CellType type = cell.getCellType();

设置单元格样式：使用dataFormat.getFormat()方法获取一个shor类型的此类格式的编码

然后将其存入CellStyle类型的对象中（使用Workbook对象调用createCellStyle()方法来创建CellStyle类型对象），（用CellStyle对象调用setDataFormat()方法将其存入）,最后将你需要设置格式的Cell调用setCellStyle()方法将设置好的CellStyle对象导入进去

// 创建单元格样式
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);

超大Excel文件读写

1.使用POI写入

使用SXSSFWorkbook进行写入，通过设置SXXFWorkbook的构造参数，可以设置每次在内存中保持的行数，当达到这个值的时候，那么会把这些数据flush到磁盘上，这样就不会出现内存不够的情况

try (Workbook workbook = new SXSSFWorkbook(1000);
				FileOutputStream out = new FileOutputStream("D:\\测试\\10w.xlsx")) {
			Sheet sheet = workbook.createSheet();
			// 创建单元格格式对象
			DataFormat dataFormat = workbook.createDataFormat();
			// 获取单元格格式编码值
			short dateFormatCode = dataFormat.getFormat("yyyy年MM月dd日 HH:mm:ss");
			short moneyFormatCode = dataFormat.getFormat("￥#,###");
			
			// 创建日期格式对象
			CellStyle dateCellStyle = workbook.createCellStyle();
			dateCellStyle.setDataFormat(dateFormatCode);
			
			// 创建货币格式对象
			CellStyle moneyCellStyle = workbook.createCellStyle();
			moneyCellStyle.setDataFormat(moneyFormatCode);
			Row row = sheet.createRow(0);
			Cell cell0 = row.createCell(0);
			cell0.setCellValue("序号");
			
			Cell cell1 = row.createCell(1);
			cell1.setCellValue("姓名");
			
			Cell cell2 = row.createCell(2);
			cell2.setCellValue("创建时间");
			
			Cell cell3 = row.createCell(3);
			cell3.setCellValue("红包金额");
			for(int i = 0;i<100000;i++) {
				Row rowVal = sheet.createRow(i+1);
				Cell cellVal0 = rowVal.createCell(0);
				cellVal0.setCellValue(String.valueOf(i+1));
				
				Cell cellVal1 = rowVal.createCell(1);
				cellVal1.setCellValue("A"+i);
				
				Cell cellVal2 = rowVal.createCell(2);
				cellVal2.setCellStyle(dateCellStyle);
				cellVal2.setCellValue(new Date());
				
				Cell cellVal3 = rowVal.createCell(3);
				cellVal3.setCellStyle(moneyCellStyle);
				cellVal3.setCellValue(Math.random()*10000);
			}
			workbook.write(out);
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

2.使用EasyExcel

使用此方法需要导入阿里巴巴的 alibaba easyexcel相关的jar包（在我上传的资源里）

第一步：准备实体类

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 + "]";
	}
}

第二步：准备Converter转换类（兼容LocalDateTime日期时间类）此类的可以设置时间格式，否则导入的jar包不兼容，设置不成时间格式

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")));
	}

}

第三步：写入100w条Order类型数据

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;
    }
}

总结：使用easyExcel相比于SXSSFWorkbook可以大大缩短写入时间以及可以写入更大数据

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。