解决JAVA导出大数据的EXCEL文件

 

     在工作中遇到了要导出30多万条数据的excel文件，写了一个工具类，我用的是POI3.13版本的，至少版本也得是3.8，否则不支持导出大数据。如下：

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

import com.google.common.base.Strings;


public class ExcelUtil {
	
	private  FileOutputStream output;
	private Sheet sheet;
	private SXSSFWorkbook wb;
	private Integer countRow=0;
	private Map<String, PropertyDescriptor>objPropertyMap;
	/**
	 * 初始化
	 * @param xls_write_Address
	 * @param fieldNames
	 * @throws FileNotFoundException
	 */
	public void init_Excel( String path,String fileName,String[] fieldNames) throws FileNotFoundException{
		
		File pathfile=new File(path);
		if(!pathfile.exists()){
			pathfile.mkdirs();
		}
		
		
        output = new FileOutputStream(new File(path+fileName));  //读取的文件路径   
        wb = new SXSSFWorkbook(1000);//内存中保留 10000 条数据，以免内存溢出，其余写入 硬盘      
        sheet = wb.createSheet(String.valueOf("sheet"));  
        wb.setSheetName(0, "sheet");   
        sheet.autoSizeColumn(1);
        Row row = sheet.createRow(countRow++); 
        for(int i=0;i<fieldNames.length;i++){
            Cell cell = row.createCell(i);                     
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);//文本格式                    
            //sheet.setColumnWidth(i, fieldNames[i].length()*384); //设置单元格宽度  
            cell.setCellValue(fieldNames[i]);//写入内容  
        }
        
	}
	
	/**
	 * 
	 * @param datalist
	 * @param dataFields
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InvocationTargetException
	 * @throws IOException
	 * @throws IntrospectionException
	 */
	public void write_data_Excel(List<Object> datalist,String[] dataFields) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, IOException, IntrospectionException{
		write_data_Excel(datalist,dataFields,null,null);
	}
	
	public void write_data_Excel(List<Object> datalist,String[] dataFields,Map<String, Map<String, String>> dataDic) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, IOException, IntrospectionException{
		write_data_Excel(datalist,dataFields,dataDic,null);
	}
	
	/**
	 * 写数据
	 * @param datalist
	 * @param dataFields
	 * @throws IOException
	 * @throws IntrospectionException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InvocationTargetException
	 */
    public  void write_data_Excel(List<Object> datalist,String[] dataFields,Map<String, Map<String, String>> dataDic,Map<String,Integer> dataFormat) throws IOException, IntrospectionException, IllegalAccessException, IllegalArgumentException, InvocationTargetException  {
    	
		CellStyle cellStyle =  wb.createCellStyle();  
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));              		
		ZipSecureFile.setMinInflateRatio(0l);
		
        for(int i=0;i<datalist.size();i++){  
            Row row = sheet.createRow(countRow++);  
            Object obj=datalist.get(i);           
			BeanInfo beanInfo = Introspector.getBeanInfo(obj.getClass());  
			PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
			if(objPropertyMap==null){
				objPropertyMap=new HashMap<String, PropertyDescriptor>();
				for(PropertyDescriptor des:propertyDescriptors){
					objPropertyMap.put(des.getName(), des);
				}
			}
            for(int cols=0;cols<dataFields.length;cols++){  
            	String dataField=dataFields[cols];
            	Object value="";
                if (objPropertyMap.get(dataField)!=null) {  
                    // 得到property对应的getter方法  
                    Method gettter = objPropertyMap.get(dataField).getReadMethod();  
                    value=gettter.invoke(obj);
                    if(value instanceof Date){
                    	Date date=(Date)value;
                    	value=DateUtil.dateFormat("yyyy-MM-dd HH:mm:ss", date);
                    	value=value.toString().replaceAll(" 00:00:00", "");
                    	//数据字典的匹配
                    }else {
                    	if(dataDic!=null && dataDic.get(dataField)!=null){
                    		value=dataDic.get(dataField).get(value+"");
                    	}
                    }
                }  
                
                if(value==null){
                	value="";
                }
                Cell cell = row.createCell(cols);     
                
            	if(dataFormat!=null && dataFormat.get(dataField)!=null){
            		int format=dataFormat.get(dataField);

            		switch(format){
	            		case 10:
		            	    cell.setCellStyle(cellStyle);  
		            		cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);//数字格式
		            		if(!Strings.isNullOrEmpty(value.toString())){
		            			cell.setCellValue(Double.valueOf(value.toString()));//写入内容  
		            		}
		            		break;
	            		case XSSFCell.CELL_TYPE_NUMERIC:
		            		cell.setCellType( XSSFCell.CELL_TYPE_NUMERIC);//数字格式
		            		if(!Strings.isNullOrEmpty(value.toString())){
		            			cell.setCellValue(Double.valueOf(value.toString()));//写入内容  
		            		}
		            		break;
            		}

            	}else{
            		cell.setCellType(XSSFCell.CELL_TYPE_STRING);//文本格式    
            		cell.setCellValue(value.toString());//写入内容  
            	}
            	
               // sheet.setColumnWidth(cols, value.toString().length()*384); //设置单元格宽度  
              
            }   
        }    
          
    }  
    /**
     * 写文件
     * @throws IOException
     */
    public void write_excel_disk() throws IOException{
        wb.write(output);
        output.close(); 
    }
}

测试的调用方法为：

    	
    	ExcelUtil util=new ExcelUtil();
    	Date date=new Date();
    	String strDate=DateUtil.dateFormat("yyyy/MM/dd", date);
    	String path=ParameterConstants.UPLOAD_FILE_PATH+"excel/skcx/"+strDate+"/";
    	String fileName=date.getTime()+".xlsx";
    	
    	util.init_Excel(path,fileName, new String[]{"日期","时间","处理时间","编号","状态","金额"});
    	String[] datefiles=new String[]{"billDueDate","insertedTime","processTime","userNo","results","money"};//对象的属性名字
    	
    	/**
    	 * 定义数据字典
    	 */
    	Map<String, String> resultsMap=new HashMap<String, String>();
    	resultsMap.put("0", "未处理");
    	resultsMap.put("1", "收款成功");
    	resultsMap.put("2", "收款失败");
    	Map<String, Map<String,String>> properyMap=new HashMap<String, Map<String,String>>();
    	properyMap.put("results", resultsMap);//可以多个
    	
    	/**
    	 * 定义数据格式
    	 */
    	Map <String,Integer> dataFormat =new HashMap<String,Integer>();
    	dataFormat.put("money", 10);//带两位小数数字

    	int countPage=1;
    	command.setPageSize(2000);数据量太大，分批读取数据，一次读取2000条
    	command.setCurPageNo(countPage++);
    	BaseEntity entity=viewBillDeductResultsService.getBillDeductResultsByConditionsQuery(command);
    	//从数据库里循环读出所有数据
    	while(entity!=null && entity.getListObject()!=null){
    		util.write_data_Excel(entity.getListObject(), datefiles, properyMap,dataFormat);
    		command.setCurPageNo(countPage++);
    		entity=viewBillDeductResultsService.getBillDeductResultsByConditionsQuery(command);
    	}
    	//将文件写在硬盘上
    	util.write_excel_disk();
        return new ModelAndView("redirect:../../upload/excel/word/"+strDate+"/"+fileName);//防止直接导出造成内存溢出，所以先存放在硬盘上，再去下载