Java exportExcel分页导出excel

这里提供分页导出，以及分多个sheel，比对于一同导出来讲，速度上的确快不少，但没有做到网上其他文档提了这么快（跟查询的sql有很大的关系，单将数据导出excel来讲很快）

package eems.service;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.servlet.support.RequestContext;

import eems.domain.PersonEntryAppInfo;
import eems.mapper.personentryexit.PersonEntryAppGridQueryParam;
import eems.mapper.personentryexit.PersonEntryAppMapper;

@Service
public class personExportExcelService {

    @Autowired
    private PersonEntryAppMapper personEntryAppMapper;
    
    public  void buildExcelDocument(PersonEntryAppGridQueryParam queryParam,Long count,String fileName,
            HttpServletRequest request,HttpServletResponse response) throws Exception {
        
           SXSSFWorkbook workbook = new SXSSFWorkbook(100);
           workbook.setCompressTempFiles(true);
           
           SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
           
        try {
            response.reset();
            // 获得国际化语言
            RequestContext requestContext = new RequestContext(request);
            String CourseCompany = "";
            response.setContentType("APPLICATION/vnd.ms-excel;charset=UTF-8");
            // 注意，如果去掉下面一行代码中的attachment; 那么也会使IE自动打开文件。            
            response.setHeader("Content-disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            OutputStream os = response.getOutputStream();
            CellStyle style = workbook.createCellStyle();
            List<PersonEntryAppInfo> list = null;
            try {
               
                int page_size = 10000;// 定义每页数据数量
                Long list_count =count;
                //总数量除以每页显示条数等于页数
                int export_times = (int) (list_count % page_size > 0 ? list_count / page_size
                        + 1 : list_count / page_size);
                     //循环获取产生每页数据
                    for (int m = 0; m < export_times; m++) {
                        
                        queryParam.setRows(page_size);
                        queryParam.setPage(m+1);  
                        
                        System.out.println("================================");
                        System.out.println(page_size);
                        
                        list = personEntryAppMapper.listCard(queryParam);

                        //新建sheet
                         Sheet sheet = null;
                            sheet = workbook.createSheet(System.currentTimeMillis()
                                    + CourseCompany+m);
                            // 创建属于上面Sheet的Row，参数0可以是0～65535之间的任何一个，
                            Row header = sheet.createRow(0); // 第0行
                            // 产生标题列，每个sheet页产生一个标题
                             Cell cell;
                            String[] headerArr = new String[] {"姓名", "单位", "卡号", "清点卡号", "密码", "有效期", "领卡人", "领卡时间", "退卡人", "退卡时间", "身份证", "联系电话", "对口处室" };
                            for (int j = 0; j < headerArr.length; j++) {
                                cell = header.createCell((short) j);
                                cell.setCellStyle(style);
                                cell.setCellValue(headerArr[j]);
                            }
                            // 迭代数据
                             if (list != null && list.size() > 0) {
                                 int rowNum = 1;
                                 for (int i = 0; i < list.size(); i++) {
                                     PersonEntryAppInfo personEntryAppInfo=list.get(i);
                                         sheet.setDefaultColumnWidth((short) 17);
                                     Row row = sheet.createRow(rowNum++);
                                     row.createCell((short) 0).setCellValue(
                                             personEntryAppInfo.getName());
                                     row.createCell((short) 1).setCellValue(
                                             personEntryAppInfo.getWorkUnit());
                                     row.createCell((short) 2)
                                             .setCellValue(personEntryAppInfo.getCardNumber());
                                     row.createCell((short) 3).setCellValue(
                                             personEntryAppInfo.getNoseCardNumber());
                                     row.createCell((short) 4).setCellValue(
                                             personEntryAppInfo.getCardPassword());
                                     if(personEntryAppInfo.getValidTimeEnd()!=null) {
                                         row.createCell((short) 5).setCellValue(
                                                 simpleDateFormat.format(personEntryAppInfo.getValidTimeEnd())); 
                                     }else {
                                         row.createCell((short) 5).setCellValue("");
                                     }

                                     row.createCell((short) 6).setCellValue("");
                                     row.createCell((short) 7).setCellValue("");
                                     row.createCell((short) 8).setCellValue("");
                                     if(personEntryAppInfo.getCardReturnTime()!=null) {
                                         row.createCell((short) 9).setCellValue(
                                                 simpleDateFormat.format(personEntryAppInfo.getCardReturnTime()));
                                     }else {
                                         row.createCell((short) 9).setCellValue("");
                                     }

                                     row.createCell((short) 10).setCellValue(
                                             personEntryAppInfo.getIdentityNo());
                                     row.createCell((short) 11).setCellValue(
                                             personEntryAppInfo.getPhone());
                                     row.createCell((short) 12).setCellValue(
                                             personEntryAppInfo.getCounterpartDepartment());                                    
                                 }
                             }
                             
                             list.clear();
                         }                            
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                workbook.write(os);
                os.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
       
}

SXSSFWorkbook workbook = new SXSSFWorkbook(100);
workbook.setCompressTempFiles(true);

官方提供的进一步压榨时间，高效导出方式之一，具体有没有效果待确认

具体传的参数可以按实际来定，这个并不影响。

以下提供一个数据量小的一同导出方法，做参考

public static <T> Workbook export(HttpServletResponse response, String fileName, String[] excelHeader,
      Collection<T> dataList) throws Exception {
         // 设置请求
         
         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
         response.setHeader("Content-disposition",
                 "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
         // 创建一个Workbook，对应一个Excel文件
         Workbook wb = new XSSFWorkbook();
         // 设置标题样式
         CellStyle titleStyle = wb.createCellStyle();
    
         titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
         
         // 设置单元格边框样式
/*         titleStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框 细边线
         titleStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);// 下边框 细边线
         titleStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框 细边线
         titleStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框 细边线
         // 设置单元格对齐方式
         titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
         titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
*/         // 设置字体样式
         Font titleFont = wb.createFont();
         titleFont.setFontHeightInPoints((short) 12); // 字体高度
         titleFont.setFontName("黑体"); // 字体样式
         titleStyle.setFont(titleFont);
         // 在Workbook中添加一个sheet,对应Excel文件中的sheet
         Sheet sheet = wb.createSheet(fileName);
         // 标题数组
         String[] titleArray = new String[excelHeader.length];
         // 字段名数组
         String[] fieldArray = new String[excelHeader.length];
         for (int i = 0; i < excelHeader.length; i++) {
          String[] tempArray = excelHeader[i].split("#");// 临时数组 分割#
          titleArray[i] = tempArray[0];
          fieldArray[i] = tempArray[1];
         }
         // 在sheet中添加标题行
         Row row = sheet.createRow((int) 0);// 行数从0开始
         Cell sequenceCell = row.createCell(0);// cell列 从0开始 第一列添加序号
         sequenceCell.setCellValue("序号");
         sequenceCell.setCellStyle(titleStyle);
      //   sheet.autoSizeColumn(0);// 自动设置宽度
         sheet.setDefaultColumnWidth(17);
         // 为标题行赋值
         for (int i = 0; i < titleArray.length; i++) {
          Cell titleCell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
          titleCell.setCellValue(titleArray[i]);
          titleCell.setCellStyle(titleStyle);
       //   sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
         }
         // 数据样式 因为标题和数据样式不同 需要分开设置 不然会覆盖
         CellStyle dataStyle = wb.createCellStyle();
         // 设置数据边框
/*         dataStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
         dataStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
         dataStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
         dataStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);*/
         // 设置居中样式
         dataStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
 //        dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
         // 设置数据字体
         Font dataFont = wb.createFont();
         dataFont.setFontHeightInPoints((short) 12); // 字体高度
         dataFont.setFontName("宋体"); // 字体
         dataStyle.setFont(dataFont);
         // 遍历集合数据，产生数据行
         Iterator<T> it = dataList.iterator();
         int index = 0;
         while (it.hasNext()) {
              index++;// 0号位被占用 所以+1
              row = sheet.createRow(index);
              // 为序号赋值
              Cell sequenceCellValue = row.createCell(0);// 序号值永远是第0列
              sequenceCellValue.setCellValue(index);
              sequenceCellValue.setCellStyle(dataStyle);
           //   sheet.autoSizeColumn(0);
              T t = (T) it.next();
              // 利用反射，根据传过来的字段名数组，动态调用对应的getXxx()方法得到属性值
              for (int i = 0; i < fieldArray.length; i++) {
                   Cell dataCell = row.createCell(i + 1);
                   dataCell.setCellStyle(dataStyle);
             //      sheet.autoSizeColumn(i + 1);
             //      sheet.setColumnWidth(i+1,20*256);
                   String fieldName = fieldArray[i];
                   String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                   Class<? extends Object> tCls = t.getClass();// 泛型为Object以及所有Object的子类
                   Method getMethod = tCls.getMethod(getMethodName, new Class[] {});// 通过方法名得到对应的方法
                   Object value = getMethod.invoke(t, new Object[] {});// 动态调用方,得到属性值
                   if (value != null) {
                      
                      if (value instanceof Date) {
                          SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                          value = simpleDateFormat.format(value);
                      }
                      
                      dataCell.setCellValue(value.toString());// 为当前列赋值
  
                   }
              }
         }
                 
             OutputStream outputStream = response.getOutputStream();// 打开流
             wb.write(outputStream);// HSSFWorkbook写入流
             wb.close();// HSSFWorkbook关闭
             outputStream.flush();// 刷新流
             outputStream.close();// 关闭流
             return wb;
    }
    // XSSFCellStyle.ALIGN_CENTER 居中对齐
    // XSSFCellStyle.ALIGN_LEFT 左对齐
    // XSSFCellStyle.ALIGN_RIGHT 右对齐
    // XSSFCellStyle.VERTICAL_TOP 上对齐
    // XSSFCellStyle.VERTICAL_CENTER 中对齐
    // XSSFCellStyle.VERTICAL_BOTTOM 下对齐
    
    // CellStyle.BORDER_DOUBLE 双边线
    // CellStyle.BORDER_THIN 细边线
    // CellStyle.BORDER_MEDIUM 中等边线
    // CellStyle.BORDER_DASHED 虚线边线
    // CellStyle.BORDER_HAIR 小圆点虚线边线
    // CellStyle.BORDER_THICK 粗边线
    
    

    
}