Java使用poi生成excel文件

poi生成excel,Java使用poi生成excel文件

生成excel表格导出是经常遇到的操作,我们使用poi来生成excel文件。

maven依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

示例代码

HSSFWorkbook生成的是xls文件,XSSFWorkbook生成的是xlsx文件(Excel2007版本)

package com.bytrees;

import java.io.File;
import java.io.FileOutputStream;
import java.lang.management.ManagementFactory;
import java.lang.management.MemoryUsage;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Hello world!
 *
 */
public class App  {
    public static void main( String[] args ) {
    	String[] data = {"73982", "1", "NH", "1", "2018122510", "2", "0", "0", "0", "12233", "0", "楼层组件优化", "0"};
    	
    	Date startTime = new Date();
    	MemoryUsage startMemory = ManagementFactory.getMemoryMXBean().getHeapMemoryUsage();

    	// 创建工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet sheet = workbook.createSheet("sheet1");
        
        //设置数据
        for (int row = 0; row < 50000; row++) {
        	XSSFRow sheetRow = sheet.createRow(row);
        	for (int column = 0; column < data.length; column++) {
        		sheetRow.createCell(column).setCellValue(data[column]);
        	}
        }

        //写入文件
        try {
        	workbook.write(new FileOutputStream(new File("excel.xlsx")));
        	workbook.close();
        } catch (Exception ex) {
        	System.out.println(ex.getMessage());
        }
        Date endTime = new Date();
        MemoryUsage endMemory = ManagementFactory.getMemoryMXBean().getHeapMemoryUsage();
        System.out.println("Cost time(ms): " + (endTime.getTime() - startTime.getTime()));
        System.out.println("Cost memory(): " + (endMemory.getUsed() - startMemory.getUsed()));
        
        System.out.println( "Hello World!" );
    }
}

性能分析

Java我使用HSSFWorkbook(xls)和XSSFWorkbook(Excel2007.xlsx)进行测试

1)xls文件生成比xlsx文件要快,但是这样不如用cvs文件代替更快

2)Excel2007文件内存占用非常大

 HSSFWorkbook执行时间HSSFWorkbook占用内存XSSFWorkbook执行时间XSSFWorkbook占用内存
1w行数据0.8s23MB7.3s201MB
2w行数据

0.9s

85MB11.7s379MB
5w行数据2.3s161MB24.2s907MB

 

Java使用poi生成excel文件的相关内容如下:

java经典编程300例| poi生成excel| 一键生成excel文件| poi读取excel文件的方式| poi excel| labview生成excel文件| poi导入excel| excel生成文件目录|