poi 大数据导出测试

可以多次写入excel，避免内存溢出

 

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;

/**
 * 大数据测试
 * @author liubenlong
 *
 */
public class BigDataExcelOut {   
       
    /**
     * 测试导出--根据模板
     * @throws Exception
     */
	@Test
    void testExportByTemplete() throws Exception {
    	String fileName = "f:\\世界五百强企业名次表.xls";   
    	FileInputStream inputStream = new FileInputStream(new File(fileName));
    	Workbook wb = new HSSFWorkbook(inputStream);   
    	Sheet sheet = wb.getSheetAt(0);
    	String[] a  = {"11","北京","xxx","y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y"};
    	
    	/**
    	 * 可以多次写入，避免内存溢出问题
    	 */
    	for (int h = 6 ; h < 8 ; h ++) {
    		a[0] = h + "";
    		for (int j = h*5000; j < 5000*(h+1); j++) {
    			Row row = sheet.createRow(j);   
    			for (int i  = 0 ; i <a.length ; i ++) {
    				Cell cell = row.createCell(i); 
    	            cell.setCellValue(a[i]); 
    			}
        	}
        	
        	 FileOutputStream out=new FileOutputStream(fileName); 
    		 out.flush(); 
    		 wb.write(out);
    		 out.close(); 
		}
		 
    	System.out.println(sheet.getLastRowNum());
    }
    
    
    /**
     * 测试导入
     * @throws Exception
     */
	@Test
    void testImport() throws Exception {
    	String fileName = "f:\\世界五百强企业名次表.xls";   
    	FileInputStream inputStream = new FileInputStream(new File(fileName));
    	Workbook wb = new HSSFWorkbook(inputStream);   
    	Sheet sheet = wb.getSheetAt(0);
    	
    	Iterator<Row> iterator = sheet.iterator();
    	for (Row row : sheet) {
    		short firstCellNum = row.getFirstCellNum();
    		short lastCellNum = row.getLastCellNum();
    		for (Cell cell : row) {
				System.out.print(cell.getStringCellValue());
			}
    		System.out.println();
		}
    	
    	inputStream.close();
    	
    	System.out.println(sheet.getLastRowNum());
    }
    
    /**
     * 测试导出
     * @throws Exception
     */
	@Test
    void testExport() throws Exception {
    	String fileName = "f:\\世界五百强企业名次表.xls";   
    	Workbook wb = new HSSFWorkbook();   
    	Sheet sheet = wb.createSheet("2222");
    	String[] a  = {"11","北京","xxx","y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y"};
    	
    	/**
    	 * 可以多次写入，避免内存溢出问题
    	 */
    	for (int h = 1 ; h < 5 ; h ++) {
    		a[0] = h + "";
    		for (int j = h*5000; j < 5000*(h+1); j++) {
    			Row row = sheet.createRow(j);   
    			for (int i  = 0 ; i <a.length ; i ++) {
    				Cell cell = row.createCell(i); 
    	            cell.setCellValue(a[i]); 
    			}
        	}
        	
        	 FileOutputStream out=new FileOutputStream(fileName); 
    		 out.flush(); 
    		 wb.write(out);
    		 out.close(); 
		}
		 
    	System.out.println(sheet.getLastRowNum());
    }
}