poi 大数据导出测试

水深无声 2022-09-20 12:58 193阅读 0赞

可以多次写入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());
        }
    }