POI EXCEL读取性能问题

最新推荐文章于 2022-05-03 11:22:53 发布

冷小鱼

最新推荐文章于 2022-05-03 11:22:53 发布

阅读量6.8k

点赞数 2

分类专栏： Java 文章标签： EXCEL POI

Java 专栏收录该内容

69 篇文章

订阅专栏

本文对比了使用FileInputStream和File方式读取Excel的性能，发现File方式显著提升读取速度，从10分钟缩短到6秒。介绍了五种不同的Excel读取方法，包括使用WorkbookFactory、HSSFWorkbook和XSSFWorkbook，并指出直接使用File可以降低内存消耗。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

笔者用“FileInputStream”方式读取EXCEL不到2万条数据花了，10分钟。后来采用File方式6秒结束战斗，特此记录。

 
		/**
		 * When opening a workbook, either a .xls HSSFWorkbook, or a .xlsx
		 * XSSFWorkbook, the Workbook can be loaded from either a File or an
		 * InputStream. Using a File object allows for lower memory consumption,
		 * while an InputStream requires more memory as it has to buffer the
		 * whole file.
		 * new File会节省内存，new FileInputStream会占用较大内存
		 * If using WorkbookFactory, it's very easy to use one or the other:
		 */
		
		// 第一种读取方式 Use a file
		Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));
 
		//第二种读取方式  Use an InputStream, needs more memory
		Workbook wb0 = WorkbookFactory.create(new FileInputStream(
				"MyExcel.xlsx"));
				
				
 
		/**
		 * If using HSSFWorkbook or XSSFWorkbook directly, you should generally
		 * go through NPOIFSFileSystem or OPCPackage, to have full control of
		 * the lifecycle (including closing the file when done):
		 * 如果直接用HSSFWorkbook or XSSFWorkbook ，你需要显式关闭文件
		 */
		
		//第三种读取方式  HSSFWorkbook, File
		NPOIFSFileSystem fs = new NPOIFSFileSystem(new File("file.xls"));
		HSSFWorkbook wb1 = new HSSFWorkbook(fs.getRoot(), true);
 
		fs.close();
 
		//第四种读取方式  HSSFWorkbook, InputStream, needs more memory
		NPOIFSFileSystem fs1 = new NPOIFSFileSystem(new FileInputStream(
				"MyExcel.xlsx"));
		HSSFWorkbook wb2 = new HSSFWorkbook(fs.getRoot(), true);
 
		//第五种读取方式  XSSFWorkbook, File
		OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
		XSSFWorkbook wb3 = new XSSFWorkbook(pkg);
 
		pkg.close();
 
		//第六种读取方式  XSSFWorkbook, InputStream, needs more memory
		OPCPackage pkg1 = OPCPackage.open(new FileInputStream("MyExcel.xlsx"));
		XSSFWorkbook wb4 = new XSSFWorkbook(pkg);
 
		pkg1.close();