java通过poi导出大量excel

 

在java web系统应用中我们经常会用到大批量数据的导出，动辄就上几十万几百万的数据让我们的程序感觉压力很大，甚至都出现无法导出的情况，如内存溢出等。
在这里给大家提供一种思路：分多个文件导出，在生成文件的过程中注意回收内存，
s

 

 

package com.bip.business.ExportExcelFile;

/**
 * 主要为导出excel一些服务方法
 */
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;

public class ExportExcelFilesService {
 
 /**
  *将大的List分割成小List
  *@return List<List>
  */
 public static List splitList(List bigList){
  List<List> splitList = new ArrayList<List>();
        List small_list = null;
        int list_all = bigList.size();
        int for_count = list_all/ToolServer.excel_count;
        if(list_all%ToolServer.excel_count>0){
         for_count += 1;
        }
        for(int i=0;i<for_count;i++ ){
         small_list = new ArrayList();
         for(int j=ToolServer.excel_count*i;j<Math.min(ToolServer.excel_count*(i+1), list_all);j++){
          small_list.add(bigList.get(j));
         }
         splitList.add(small_list);
        }
  return splitList;
 }
 
// 将获得Object的对象换成list对象
 public static List objectToList(Object po,HSSFRow templateRow,int id){
  List listData = new ArrayList();
  List listSheet = getSheet(templateRow);
  Class<?> clazz = po.getClass();
  Method[] methods = clazz.getMethods();
  listData.add(id);
      for(Object sheet:listSheet){
   for (Method method : methods) {
    String mname = method.getName();
    Class<?> type = method.getReturnType();  
      if (mname.substring(0, 3).equals("get")) {
        try {
         Object returnO = method.invoke(po, new Object[] {});
         mname = mname.replaceFirst("get", "");
         if(sheet.toString().trim().equalsIgnoreCase(mname.toLowerCase())){
         listData.add(returnO);
         }
       
        }catch (Exception e) {
          e.printStackTrace();
        }
       }
     }
      }
  return listData;
  
 }
 
 /*
  * 返回excel表中第一行的隐藏字段，
  * 从第2列开始
  *
  */
 public static List  getSheet(HSSFRow templateRow) {
  List listSheet = new ArrayList();
  int rows = templateRow.getPhysicalNumberOfCells();
  for (int k = 1; k < rows; k++) {
   listSheet.add(templateRow.getCell((short)k));
  }
     return listSheet;
 
 }
}

================================================================================

 

package com.bip.business.ExportExcelFile;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.struts2.ServletActionContext;
import org.springframework.core.io.ClassPathResource;

 

 

public class ExportExcelFiles{

 
 /**
  *
  */
 private static final long serialVersionUID = 1L;
 HttpServletRequest request = ServletActionContext.getRequest();
 HttpServletResponse response = ServletActionContext.getResponse();
/**
 * 定义变量
 */
 String fileNames;
 InputStream downloadFile;
 
 
/**
 * 导出Zip文件，zip文件中包含多个excel文件，对待大数据量文件导出
 * @param dataList
 * @param templateName
 * @param excelStart
 * @return
 */
 public String outExcelZip(List dataList,String templateName,String excelStart) {

   ClassPathResource resource = new ClassPathResource("../../");
   String sysPath = "";
   try {
    sysPath = resource.getURL().getPath();
   } catch (IOException e) {
    e.printStackTrace();
   }
      String templatePath =sysPath+"templates//excel//"+templateName;
    HSSFWorkbook workbook;
    int startRow = Integer.parseInt(excelStart);
  
   
          response.reset();
          response.setContentType("application/vnd.ms-excel"); //不同类型的文件对应不同的MIME类型
      response.setHeader("Content-Disposition", "attachment; filename=" + ToolServer.getRandFileName()+"_bake.zip");
      OutputStream os =null;
          try {
   os = response.getOutputStream();
    } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
    }
    ZipOutputStream zout=new ZipOutputStream(os); //得到压缩输出流
 
    List list_sp = ExportExcelFilesService.splitList(dataList);//将大数据量List划分为多个小List
    for(int i=0;i<list_sp.size();i++){
   List smalllist = (List)list_sp.get(i);
      System.out.println("正在导出第 "+(i+1)+" 个个文件！");
     try {
     workbook = printExcel(templatePath,startRow,smalllist);
                 if(workbook != null){
                             String file_name = ToolServer.getRandFileName()+"_bake.xls";
                             this.exportExcel(workbook,file_name,zout);
                                   }
        } catch (FileNotFoundException e1) {
         // TODO Auto-generated catch block
         e1.printStackTrace();
        } catch (IOException e1) {
         // TODO Auto-generated catch block
         e1.printStackTrace();
        }
   }        
    
     try {
    zout.close();
   } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } //关闭压缩输出流
         return "success";
 }
 
 /**
  * 导出单个Excel,多个sheet，每个sheet的数据条数，在ToolServer中设置sheet_count
  * @param dataList中包含的是po
  * @param templateName
  * @param excelStart
  * @return
  */
 public String outExcel(List dataList,String templateName,String excelStart)  {
  
  ClassPathResource resource = new ClassPathResource("../../");
   String sysPath = "";
   try {
    sysPath = resource.getURL().getPath();
   } catch (IOException e) {
    e.printStackTrace();
   }
    String templatePath =sysPath+"templates//excel//"+templateName;
    HSSFWorkbook workbook;
    int startRow = Integer.parseInt(excelStart);
    
     try {
     workbook = printExcel(templatePath,startRow,dataList);
                 if(workbook != null){
                    String file_name = ToolServer.getRandFileName()+"_bake.xls";
                     this.exportExcel(workbook,file_name);
                                  }
        } catch (FileNotFoundException e1) {
         // TODO Auto-generated catch block
         e1.printStackTrace();
        } catch (IOException e1) {
         // TODO Auto-generated catch block
         e1.printStackTrace();
        }
         return "success";
 }
 
 

//获得模板样式，在模板样式的的基础上，将数据导入excel表中
 private HSSFWorkbook printExcel(String templatePath,int startRow,List dataList) throws FileNotFoundException, IOException{

  POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templatePath));
  //创建模板工作表
         HSSFWorkbook workbook = null;
        
//         System.out.println("dataList:    "+dataList.size());
        
           try{
         //创建工作簿实例
             workbook = new HSSFWorkbook(fs);
            
             int sheet_num = dataList.size()/ToolServer.sheet_count; //此excel文件分几个sheet
                if(dataList.size()%ToolServer.sheet_count>0){
                 sheet_num +=1;
                }
            
             for(int count=0;count<sheet_num;count++){
    
            System.out.println("Excel正在导出第 "+(count+1)+" 个Sheet文件！");
              
             //获得工作表实例
             HSSFSheet sheet = workbook.getSheetAt(count);
             HSSFRow templateRow = sheet.getRow(0);
       int columns = sheet.getRow((short) 0).getPhysicalNumberOfCells();
           //创建样式数组
      HSSFCellStyle styleArray[] = new HSSFCellStyle[columns];
                // 一次性创建所有列的样式放在数组里
      for (int s = 0; s < columns; s++) {
          styleArray[s] = workbook.createCellStyle();
       }
          //更改模板样式内容
     
        //循环对每一个单元格进行赋值,定位行,从第rowId开始向excel表格中添加数据   
       for (int rowId = startRow; rowId < Math.min((dataList.size()-count*ToolServer.sheet_count),ToolServer.sheet_count)+startRow; rowId++) {
        
        Object po = (Object) dataList.get(count*ToolServer.sheet_count+rowId-startRow);
        
        List valueList = ExportExcelFilesService.objectToList(po,templateRow,(rowId-startRow+1));
//        System.out.println("正在导出第 "+(count*ToolServer.sheet_count+rowId-startRow)+" 条数据，共 "+dataList.size()+" 条！");

        for (int columnId = 0; columnId < columns; columnId++) {
              String dataValue =valueList.get(columnId)==null?"": ((Object) valueList.get(columnId)).toString();
           //取出colunmId列的的style,模板每一列的样式
           HSSFCellStyle style = styleArray[columnId];
           //取模板第colunmId列的单元格对象 ,模板单元格对象
           HSSFCell templateCell = templateRow.getCell((short) columnId);
           //创建一个新的rowId行   行对象
           HSSFRow hssfRow = sheet.createRow(rowId);
           //创建新的rowId行   columnId列   单元格对象
           HSSFCell cell = hssfRow.createCell((short) columnId);
           //如果对应的模板单元格   样式为非锁定
           if (templateCell.getCellStyle().getLocked() == false) {
            //设置此列style为非锁定
            style.setLocked(false);
            //设置到新的单元格上
            cell.setCellStyle(style);
           }
           //否则样式为锁定
           else {
            //设置此列style为锁定
            style.setLocked(true);
            //设置到新单元格上
            cell.setCellStyle(style);
           }
           //设置编码
           cell.setEncoding(HSSFCell.ENCODING_UTF_16);
           //设置值   统一为String
           cell.setCellValue(dataValue);
          }
       }
             }
       
        }catch(Exception e){
               e.printStackTrace();
        }
       return workbook;
 }
 
/**
 * 写入zip文件中，导出大文件使用。
 * @param workbook
 * @param fileName
 * @param zout
 * @throws IOException
 */

 private void exportExcel(HSSFWorkbook workbook,String fileName,ZipOutputStream zout) throws IOException{
         fileNames = fileName;
         ByteArrayOutputStream baos = new ByteArrayOutputStream();
         workbook.write(baos);
         baos.flush();
         byte[] aa = baos.toByteArray();
         fileNames = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题
         fileNames = new String(fileName.getBytes("UTF-8"),"GBK"); // 处理中文文件名的问题
         downloadFile = new ByteArrayInputStream(aa, 0, aa.length);
         ZipEntry  entry=new ZipEntry(fileName); //实例化条目列表
     zout.putNextEntry(entry); //将ZIP条目列表写入输出流
     while (downloadFile.read(aa) > 0) {   //如果文件未读完
     zout.write(aa); //写入缓冲数据
     }
         baos.close();
   this.getDownloadFile().close();
 }
 
/**
 * 导出单个Excel，多个sheet
 * @param workbook
 * @param fileName
 * @throws IOException
 */
 
 private void exportExcel(HSSFWorkbook workbook,String fileName) throws IOException{
        fileNames = fileName;
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        baos.flush();
        byte[] aa = baos.toByteArray();
        downloadFile = new ByteArrayInputStream(aa, 0, aa.length);
      
       
        response.reset();
        response.setContentType("application/vnd.ms-excel"); //不同类型的文件对应不同的MIME类型
  response.setHeader("Content-Disposition", "attachment; filename=" + fileNames);
       
  OutputStream os = response.getOutputStream();
  
  while(downloadFile.read(aa)>0){
   os.write(aa);
  }
   baos.close();
  this.getDownloadFile().close();
}
 
 
 
 
 
 
// 创建Excel单元格 
 private void createCell(HSSFRow row, int column, HSSFCellStyle style,int cellType,Object value) {
        HSSFCell cell = row.createCell((short) column);
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        if (style != null) {
             cell.setCellStyle(style);
        }  
        switch(cellType){
             case HSSFCell.CELL_TYPE_BLANK: {} break;
             case HSSFCell.CELL_TYPE_STRING: {cell.setCellValue(value.toString()+"");} break;
             case HSSFCell.CELL_TYPE_NUMERIC: {
             cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                 cell.setCellValue(Double.parseDouble(value.toString()));}break;
             default: break;
       } 

 }
 
 public InputStream getDownloadFile() {
  return downloadFile;
 }
 public void setDownloadFile(InputStream downloadFile) {
  this.downloadFile = downloadFile;
 }

 public String getFileNames() {
  return fileNames;
 }
 public void setFileNames(String fileNames) {
  this.fileNames = fileNames;
 }

}

 

 

============================================================

package com.bip.business.ExportExcelFile;

/**
 * 提供静态方法以及静态变量，默认为true
 */
import java.net.*;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;

public class ToolServer {

 /**
  *定义静态变量
  */
 public static int excel_count = 5000; //每个excel文件多少条数据
 public static int sheet_count = 5000; //每个sheet多少条数据
 
 /**
  * 判断是否为linux系统，如果是返回：True
  * @return
  */
 public static boolean isLinux(){
  boolean isLinux = true;
  URL resource = ToolServer.class.getResource("OSType.class");
  String classPath = resource.getPath();  
  String className = ToolServer.class.getName().replace(".", "/") + ".class";
  String classesPath = classPath.substring(0, classPath.indexOf(className));
  if( System.getProperty("os.name").toUpperCase().indexOf("WINDOWS") != -1 &&
    classesPath.startsWith("/") )
  {
   classesPath = classesPath.substring(1);
   isLinux = false;
  }
  
  return isLinux;
 }
 
 
 /**
  * 生成一个随机的文件名，编码规则：年月日时分秒+两位随机数
  * @return
  */
 public static String getRandFileName(){
  String filename = "";
  String dataname = "";
  Date date = new Date();
  Format formatter = new SimpleDateFormat("yyyyMMddHHmmss");
  dataname = formatter.format(date);
  filename = dataname +getRandom(10,99);  
  return filename;
 }

 /**
  * 随机生成min～max范围内的一个整数
  * @param min
  * @param max
  * @return
  */
 public static int getRandom(int min,int max){
  Random random = new Random();
  int ran=Math.abs(random.nextInt());
  int returnRan=ran%(max-min+1)+min;
  return returnRan;
 }

 

 
}

 

 

还有其他文件，请下载