Excel文件解析

最新推荐文章于 2023-06-15 11:23:58 发布

沉浸在java的世界里

最新推荐文章于 2023-06-15 11:23:58 发布

阅读量442

点赞数 19

文章标签： java servlet 服务器

本文链接：https://blog.csdn.net/m0_56763026/article/details/125770189

版权

在应用程序的开发过程中，经常需要使用Excel文件来进行数据的导入、导出。所以，在通过java类实现此类需求的时候，往往会面临着Excel文件的解析（导入）和生成（导出）。

不管是对Excel做出何种操作；我们都必须遵从它的顺序以及规律；workbook（接口；代表一个Excel文件）->sheet（工作蒲）->row（行）->cell（单元格）;

无论是读还是写都应该遵从这样一个顺序和定律。

例如：

Excel文件（内容未知）的解析如下：

import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xddf.usermodel.text.CapsType;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test1 {
	public static void main(String[] args) {
		try (Workbook workbook = new XSSFWorkbook(new FileInputStream("D:\\pic\\test.xlsx"))) {
			Sheet sheet = workbook.getSheetAt(0);
			//列头
			Row headrow = sheet.getRow(0);
			//遍历列头单元格
			for(Cell headcell : headrow) {
				System.out.printf(headcell.getStringCellValue() + "\t");
			}
			System.out.println();
			
			//获取数据行
			for(int i = 1 ; i <= sheet.getLastRowNum(); i++) {
				//获取当前行
				Row row = sheet.getRow(i);
				//遍历当前行中所有单元格
				for(Cell cell : row) {
					//获取当前单元格的类型
					CellType cellType = cell.getCellType();
					
					//根据当前单元格类型获取单元格的值
					switch(cellType) {
					case STRING: 
						String strval = cell.getStringCellValue();
						System.out.printf(strval + "\t");
						break;
					case NUMERIC:
						double numval = cell.getNumericCellValue();
						System.out.printf(numval + "\t");
						break;
					}
				}
				System.out.println();
				
			}
			
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}

生成（自己创建一个简单的Excel文件）：

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test2 {
	public static void main(String[] args) {
		List<String>  list = Arrays.asList("妲己","吕布","孙尚香","刘备","关羽","张飞","赵云"); 
		try (Workbook workbook = new XSSFWorkbook(new FileInputStream("D:\\pic\\test.xlsx"));
				FileOutputStream out = new FileOutputStream("D:\\pic\\test.xlsx")) {
			//在已存在的文件中创建新的Sheet
			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);//设置格式编码值
			for(int i = 0 ; i < list.size();i++) {
				String name = list.get(i);
				
				//创建行
				Row row = sheet.createRow(i+1);
				
				//创建单元格
				Cell cell0 = row.createCell(0);//序号
				cell0.setCellValue(String.valueOf(i + 1));
				Cell cell1 = row.createCell(1);//姓名
				cell1.setCellValue(name);
				Cell cell2 = row.createCell(2);//日期
				cell2.setCellStyle(dateCellStyle);//日期格式对象
				cell2.setCellValue(new Date());
				Cell cell3 = row.createCell(3);//红包金额
				cell3.setCellStyle(moneyCellStyle);
				cell3.setCellValue((int)(Math.random() * 100000));
				
			}
			//写入文件
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}

}

输出结果：

超大Excele文件的读写（100w）：

使用POI写入：（注：可能会出现以内存溢出异常（电脑运存较小）附图；）

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test3 {
	public static void main(String[] args) {
		try (Workbook workbook = new SXSSFWorkbook(100);
			FileOutputStream out = new FileOutputStream("D:\\pic\\test.xlsx")){
			//在已存在的文件中创建新的Sheet
			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);//设置格式编码值
			for(int i = 0 ; i < 10000000;i++) {
				String name ="A" + i;
				
				//创建行
				Row row = sheet.createRow(i+1);
				
				//创建单元格
				Cell cell0 = row.createCell(0);//序号
				cell0.setCellValue(String.valueOf(i + 1));
				Cell cell1 = row.createCell(1);//姓名
				cell1.setCellValue(name);
				Cell cell2 = row.createCell(2);//日期
				cell2.setCellStyle(dateCellStyle);//日期格式对象
				cell2.setCellValue(new Date());
				Cell cell3 = row.createCell(3);//红包金额
				cell3.setCellStyle(moneyCellStyle);
				cell3.setCellValue((int)(Math.random() * 100000));
				
			}
			//写入文件
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}

}

内存溢出：

使用EasyExcel:

写入：

import java.util.List;

import com.alibaba.excel.EasyExcel;

public class Test1 {
	public static void main(String[] args) {
		long begin = System.currentTimeMillis();
		 // 写入100w
        EasyExcel.write("D:\\pic\\easy.xlsx", Order.class)
                 .sheet("订单列表")
                 .doWrite(data());
        long end = System.currentTimeMillis();
        System.out.println("共耗时" + (end-begin) + "毫秒");
    }
    
    // 创建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;
    }
	

}

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.NumberFormat;

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

}

package Day1;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

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

}

读取：

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

public class Test2 {
	public static void main(String[] args) {
		List<Order> orderList = new ArrayList<Order>();
		
		EasyExcel.read("D:\\pic\\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);
		        super.invokeHeadMap(headMap, context);
		    }

		    @Override
		    public void doAfterAllAnalysed(AnalysisContext arg0) {
		        // 读取完毕
		        System.out.println("Game over");
		    }
		}).sheet().doRead();
		
	  //遍历
	  for(Order order : orderList) {
		System.out.println(order);
	  }
}
}

想比较POI写入来说，EasyExcel更快且一般不会出现内存溢出的情况，对电脑的运存要求不高，所以在面对大数据的读或者写操作时，推荐使用EasyExcel。