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文件内存占用非常大