java通过poi导出大数据的excel

java中导出excel
第一步  创建一个个workbook对象
创建一个工作空间
设置表格样式
第二部 设置标题  设置表列sheet.createRow(rownum++); 创建一行
第三部  设置表列sheet.createRow(rownum++);创建一行
第四部 创建行对象
创建一个单元格设置 添加的行列  添加的列 还有值

 第五步，将excel文件存到指定位置

====================工具类

/**
 * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.thinkgem.jeesite.common.utils.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.Encodes;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;

/**
 * 导出Excel文件（导出“XLSX”格式，支持大数据量导出   @see org.apache.poi.ss.SpreadsheetVersion）
 * @author ThinkGem
 * @version 2013-04-21
 */
public class ExportExcel {
   
   private static Logger log = LoggerFactory.getLogger(ExportExcel.class);
         
   /**
    * 工作薄对象
    */
   private SXSSFWorkbook wb;
   
   /**
    * 工作表对象
    */
   private Sheet sheet;
   
   /**
    * 样式列表
    */
   private Map<String, CellStyle> styles;
   
   /**
    * 当前行号
    */
   private int rownum;
   
   /**
    * 注解列表（Object[]{ ExcelField, Field/Method }）
    */
   List<Object[]> annotationList = Lists.newArrayList();
   
   /**
    * 构造函数
    * @param title 表格标题，传“空值”，表示无标题
    * @param cls 实体对象，通过annotation.ExportField获取标题
    */
   public ExportExcel(String title, Class<?> cls){
      this(title, cls, 1);
   }
   
   /**
    * 构造函数
    * @param title 表格标题，传“空值”，表示无标题
    * @param cls 实体对象，通过annotation.ExportField获取标题
    * @param type 导出类型（1:导出数据；2：导出模板）
    * @param groups 导入分组
    */
   public ExportExcel(String title, Class<?> cls, int type, int... groups){
      // Get annotation field 
      Field[] fs = cls.getDeclaredFields();
      for (Field f : fs){
         ExcelField ef = f.getAnnotation(ExcelField.class);
         if (ef != null && (ef.type()==0 || ef.type()==type)){
            if (groups!=null && groups.length>0){
               boolean inGroup = false;
               for (int g : groups){
                  if (inGroup){
                     break;
                  }
                  for (int efg : ef.groups()){
                     if (g == efg){
                        inGroup = true;
                        annotationList.add(new Object[]{ef, f});
                        break;
                     }
                  }
               }
            }else{
               annotationList.add(new Object[]{ef, f});
            }
         }
      }
      // Get annotation method
      Method[] ms = cls.getDeclaredMethods();
      for (Method m : ms){
         ExcelField ef = m.getAnnotation(ExcelField.class);
         if (ef != null && (ef.type()==0 || ef.type()==type)){
            if (groups!=null && groups.length>0){
               boolean inGroup = false;
               for (int g : groups){
                  if (inGroup){
                     break;
                  }
                  for (int efg : ef.groups()){
                     if (g == efg){
                        inGroup = true;
                        annotationList.add(new Object[]{ef, m});
                        break;
                     }
                  }
               }
            }else{
               annotationList.add(new Object[]{ef, m});
            }
         }
      }
      // Field sorting
      Collections.sort(annotationList, new Comparator<Object[]>() {
         public int compare(Object[] o1, Object[] o2) {
            return new Integer(((ExcelField)o1[0]).sort()).compareTo(
                  new Integer(((ExcelField)o2[0]).sort()));
         };
      });
      // Initialize
      List<String> headerList = Lists.newArrayList();
      for (Object[] os : annotationList){
         String t = ((ExcelField)os[0]).title();
         // 如果是导出，则去掉注释
         if (type==1){
            String[] ss = StringUtils.split(t, "**", 2);
            if (ss.length==2){
               t = ss[0];
            }
         }
         headerList.add(t);
      }
      initialize(title, headerList);
   }
   
   /**
    * 构造函数
    * @param title 表格标题，传“空值”，表示无标题
    * @param headers 表头数组
    */
   public ExportExcel(String title, String[] headers) {
      initialize(title, Lists.newArrayList(headers));
   }
   
   /**
    * 构造函数
    * @param title 表格标题，传“空值”，表示无标题
    * @param headerList 表头列表
    */
   public ExportExcel(String title, List<String> headerList) {
      initialize(title, headerList);
   }
   
   /**
    * 初始化函数
    * @param title 表格标题，传“空值”，表示无标题
    * @param headerList 表头列表
    */
   private void initialize(String title, List<String> headerList) {
      this.wb = new SXSSFWorkbook(500);
      this.sheet = wb.createSheet("Export");
      this.styles = createStyles(wb);
      // Create title
      if (StringUtils.isNotBlank(title)){
         Row titleRow = sheet.createRow(rownum++);
         titleRow.setHeightInPoints(30);
         Cell titleCell = titleRow.createCell(0);
         titleCell.setCellStyle(styles.get("title"));
         titleCell.setCellValue(title);
         sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
               titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
      }
      // Create header
      if (headerList == null){
         throw new RuntimeException("headerList not null!");
      }
      Row headerRow = sheet.createRow(rownum++);
      headerRow.setHeightInPoints(16);
      for (int i = 0; i < headerList.size(); i++) {
         Cell cell = headerRow.createCell(i);
         cell.setCellStyle(styles.get("header"));
         String[] ss = StringUtils.split(headerList.get(i), "**", 2);
         if (ss.length==2){
            cell.setCellValue(ss[0]);
            Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
                  new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
         }else{
            cell.setCellValue(headerList.get(i));
         }
         sheet.autoSizeColumn(i);
      }
      for (int i = 0; i < headerList.size(); i++) {  
         int colWidth = sheet.getColumnWidth(i)*2;
           sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);  
      }
      log.debug("Initialize success.");
   }
   
   /**
    * 创建表格样式
    * @param wb 工作薄对象
    * @return 样式列表
    */
   private Map<String, CellStyle> createStyles(Workbook wb) {
      Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
      
      CellStyle style = wb.createCellStyle();
      style.setAlignment(CellStyle.ALIGN_CENTER);
      style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      Font titleFont = wb.createFont();
      titleFont.setFontName("Arial");
      titleFont.setFontHeightInPoints((short) 16);
      titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
      style.setFont(titleFont);
      styles.put("title", style);

      style = wb.createCellStyle();
      style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      style.setBorderRight(CellStyle.BORDER_THIN);
      style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
      style.setBorderLeft(CellStyle.BORDER_THIN);
      style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
      style.setBorderTop(CellStyle.BORDER_THIN);
      style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
      style.setBorderBottom(CellStyle.BORDER_THIN);
      style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
      Font dataFont = wb.createFont();
      dataFont.setFontName("Arial");
      dataFont.setFontHeightInPoints((short) 10);
      style.setFont(dataFont);
      styles.put("data", style);
      
      style = wb.createCellStyle();
      style.cloneStyleFrom(styles.get("data"));
      style.setAlignment(CellStyle.ALIGN_LEFT);
      styles.put("data1", style);

      style = wb.createCellStyle();
      style.cloneStyleFrom(styles.get("data"));
      style.setAlignment(CellStyle.ALIGN_CENTER);
      styles.put("data2", style);

      style = wb.createCellStyle();
      style.cloneStyleFrom(styles.get("data"));
      style.setAlignment(CellStyle.ALIGN_RIGHT);
      styles.put("data3", style);
      
      style = wb.createCellStyle();
      style.cloneStyleFrom(styles.get("data"));
//    style.setWrapText(true);
      style.setAlignment(CellStyle.ALIGN_CENTER);
      style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
      style.setFillPattern(CellStyle.SOLID_FOREGROUND);
      Font headerFont = wb.createFont();
      headerFont.setFontName("Arial");
      headerFont.setFontHeightInPoints((short) 10);
      headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
      headerFont.setColor(IndexedColors.WHITE.getIndex());
      style.setFont(headerFont);
      styles.put("header", style);
      
      return styles;
   }

   /**
    * 添加一行
    * @return 行对象
    */
   public Row addRow(){
      return sheet.createRow(rownum++);
   }
   

   /**
    * 添加一个单元格
    * @param row 添加的行
    * @param column 添加列号
    * @param val 添加值
    * @return 单元格对象
    */
   public Cell addCell(Row row, int column, Object val){
      return this.addCell(row, column, val, 0, Class.class);
   }
   
   /**
    * 添加一个单元格
    * @param row 添加的行
    * @param column 添加列号
    * @param val 添加值
    * @param align 对齐方式（1：靠左；2：居中；3：靠右）
    * @return 单元格对象
    */
   public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
      Cell cell = row.createCell(column);
      String cellFormatString = "@";
      try {
         if(val == null){
            cell.setCellValue("");
         }else if(fieldType != Class.class){
            cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
         }else{
            if(val instanceof String) {
               cell.setCellValue((String) val);
            }else if(val instanceof Integer) {
               cell.setCellValue((Integer) val);
               cellFormatString = "0";
            }else if(val instanceof Long) {
               cell.setCellValue((Long) val);
               cellFormatString = "0";
            }else if(val instanceof Double) {
               cell.setCellValue((Double) val);
               cellFormatString = "0.00";
            }else if(val instanceof Float) {
               cell.setCellValue((Float) val);
               cellFormatString = "0.00";
            }else if(val instanceof Date) {
               cell.setCellValue((Date) val);
               cellFormatString = "yyyy-MM-dd HH:mm";
            }else {
               cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), 
                  "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
            }
         }
         if (val != null){
            CellStyle style = styles.get("data_column_"+column);
            if (style == null){
               style = wb.createCellStyle();
               style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:"")));
                 style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
               styles.put("data_column_" + column, style);
            }
            cell.setCellStyle(style);
         }
      } catch (Exception ex) {
         log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
         cell.setCellValue(val.toString());
      }
      return cell;
   }

   /**
    * 添加数据（通过annotation.ExportField添加数据）
    * @return list 数据列表
    */
   public <E> ExportExcel setDataList(List<E> list){
      for (E e : list){
         int colunm = 0;
         Row row = this.addRow();
         StringBuilder sb = new StringBuilder();
         for (Object[] os : annotationList){
            ExcelField ef = (ExcelField)os[0];
            Object val = null;
            // Get entity value
            try{
               if (StringUtils.isNotBlank(ef.value())){
                  val = Reflections.invokeGetter(e, ef.value());
               }else{
                  if (os[1] instanceof Field){
                     val = Reflections.invokeGetter(e, ((Field)os[1]).getName());
                  }else if (os[1] instanceof Method){
                     val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
                  }
               }
               // If is dict, get dict label
               if (StringUtils.isNotBlank(ef.dictType())){
                  val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
               }
            }catch(Exception ex) {
               // Failure to ignore
               log.info(ex.toString());
               val = "";
            }
            this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
            sb.append(val + ", ");
         }
         log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
      }
      return this;
   }
   
   /**
    * 输出数据流
    * @param os 输出数据流
    */
   public ExportExcel write(OutputStream os) throws IOException{
      wb.write(os);
      return this;
   }
   
   /**
    * 输出到客户端
    * @param fileName 输出文件名
    */
   public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{
      response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
      write(response.getOutputStream());
      return this;
   }
   
   /**
    * 输出到文件
    * @param fileName 输出文件名
    */
   public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{
      FileOutputStream os = new FileOutputStream(name);
      this.write(os);
      return this;
   }
   
   /**
    * 清理临时文件
    */
   public ExportExcel dispose(){
      wb.dispose();
      return this;
   }
   
// /**
//  * 导出测试
//  */
// public static void main(String[] args) throws Throwable {
//    
//    List<String> headerList = Lists.newArrayList();
//    for (int i = 1; i <= 10; i++) {
//       headerList.add("表头"+i);
//    }
//    
//    List<String> dataRowList = Lists.newArrayList();
//    for (int i = 1; i <= headerList.size(); i++) {
//       dataRowList.add("数据"+i);
//    }
//    
//    List<List<String>> dataList = Lists.newArrayList();
//    for (int i = 1; i <=1000000; i++) {
//       dataList.add(dataRowList);
//    }
//
//    ExportExcel ee = new ExportExcel("表格标题", headerList);
//    
//    for (int i = 0; i < dataList.size(); i++) {
//       Row row = ee.addRow();
//       for (int j = 0; j < dataList.get(i).size(); j++) {
//          ee.addCell(row, j, dataList.get(i).get(j));
//       }
//    }
//    
//    ee.writeFile("target/export.xlsx");
//
//    ee.dispose();
//    
//    log.debug("Export success.");
//    
// }

}

事例

String fileName = null;
List<WorkerCashDeposit> list = super.findList(workerCashDeposit);
if (!CollectionUtils.isEmpty(list)) {
    List<String> excelTitle = Arrays.asList("姓名", "手机号", "缴纳金额", "缴纳时间", "缴纳方式", "签约方式");
    ExportExcel exportExcel = new ExportExcel("师傅保证金管理", excelTitle);
    Row row = null;
    for (int i = 0; i < list.size(); i++) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if (list.get(i) != null) {
            WorkerCashDeposit ci = list.get(i);
            row = exportExcel.addRow();
            exportExcel.addCell(row, 0, ci.getByWorker().getWorkerName());
            exportExcel.addCell(row, 1, ci.getByWorker().getTelephone());
            exportExcel.addCell(row, 2, ci.getAmount());
            if (ci.getCreateTime() != null) {
                exportExcel.addCell(row, 3, simpleDateFormat.format(ci.getCreateTime()));
            }
            String str = "";
            if ("1".equals(ci.getThirdPayType())) {
                str = "支付宝";
            }
            if ("2".equals(ci.getThirdPayType())) {
                str = "微信";
            }
            exportExcel.addCell(row, 4, str);
            String str1 = "";
            if ("0".equals(ci.getSignStatus())) {
                str1 = "未签约";
            }
            if ("1".equals(ci.getSignStatus())) {
                str1 = "签约成功";
            }
            exportExcel.addCell(row, 5, str1);


        }
    }
    fileName = "downList" + new Date().getTime() + ".xlsx";
    HttpServletRequest request = Servlets.getRequest();
    String absolutePath = request.getSession().getServletContext().getRealPath(Global.USERFILES_BASE_URL);
    File file = new File(absolutePath);
    if (!file.exists()) {
        file.mkdir();
    }
    exportExcel.writeFile(absolutePath + "/" + fileName);
}