excel 导出百万级别只需一分钟

话不多说上代码

可以通过新的SXSSFWorkbook（int ROWACCESSWINDOWSIZE ）在工作簿构建时指定窗口大小 

/**
 * Excel工具类
 *
 * @author zhoaxx
 */
public class ExcelUtils {

    public static final String SHEET_NM = "sheet1";
    public static final int ROWACCESSWINDOWSIZE = 1000;

    public static Workbook getWorkBook(String[] titleNms, String[] columMethodNms, List list) {
        try {
            return getWorkBook(SHEET_NM, titleNms, columMethodNms, list, 0);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Workbook getWorkBook(String sheetNm, String[] titleNms,
                                       String[] columMethodNms, List list, int start) throws Exception {
        Workbook workbook = new SXSSFWorkbook(new XSSFWorkbook(), ROWACCESSWINDOWSIZE);
        //设置格式
        CellStyle titleStyle = titleStyle(workbook);
        CellStyle dataStyle = dataStyle(workbook);
        Sheet sheet = getSheet(sheetNm, titleNms, workbook, titleStyle);
        Class cla = null;
        if (list != null && list.size() > 0) {
            cla = list.get(0).getClass();
        }
        for (int i = 0, size = list.size(); i < size; i++) {
            Object obj = list.get(i);
            Row dataRow = sheet.createRow(i + 1 + start);// 创建行
            for (int j = 0, len = columMethodNms.length; j < len; j++) {
                setCellVal(cla.getMethod(columMethodNms[j]), obj, dataStyle, dataRow, j);
            }
        }
        return workbook;
    }

    private static void setCellVal(Method method, Object obj, CellStyle dataStyle, Row dataRow, int j) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        Cell cell = dataRow.createCell(j);// 创建单元格
        String returnType = method.getReturnType().getName().toLowerCase();
        Object value = method.invoke(obj);
        cell.setCellStyle(dataStyle);
        if (value == null || value.toString().trim().length() == 0) {
            return;
        }
        if (isNum(returnType)) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(new Double(value.toString()));
        } else if (returnType.indexOf("date") != -1) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(value.toString());
        } else {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(value.toString());
        }
    }

    private static boolean isNum(String returnType) {
        return returnType.indexOf("integer") != -1
                || returnType.indexOf("int") != -1
                || returnType.indexOf("bigdecimal") != -1
                || returnType.indexOf("double") != -1
                || returnType.indexOf("long") != -1
                || returnType.indexOf("float") != -1;
    }

    //创建sheet和设置标题
    private static Sheet getSheet(String sheetNm, String[] titleNms, Workbook workbook, CellStyle titleStyle) {
        Cell cell;
        Sheet sheet = workbook.getSheet(StringUtils.isEmpty(sheetNm) ? SHEET_NM : sheetNm);
        if (sheet == null) {
            sheet = workbook.createSheet(StringUtils.isEmpty(sheetNm) ? SHEET_NM : sheetNm);
            // 标题
            Row titleRow = sheet.createRow(0); // 创建标题行(第一行)
            titleRow.setHeight((short) 400);// 设置第一行的行高
            for (int i = 0; i < titleNms.length; i++) {
                sheet.setColumnWidth(i, 3500);// 设置单元格的宽
                cell = titleRow.createCell(i);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(titleNms[i]);
                cell.setCellStyle(titleStyle);
            }
        }
        return sheet;
    }

    public static CellStyle titleStyle(Workbook workbook) {
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); // 居中
        titleStyle.setBorderLeft((short) 1);
        titleStyle.setBorderRight((short) 1);
        titleStyle.setBorderBottom((short) 1);
        titleStyle.setBorderTop((short) 1);
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);// 设置字体大小
        titleStyle.setFont(font);// 选择需要用到的字体格式
        return titleStyle;
    }

    public static CellStyle dataStyle(Workbook workbook) {
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setBorderBottom((short) 1);
        dataStyle.setBorderLeft((short) 1);
        dataStyle.setBorderRight((short) 1);
        dataStyle.setBorderTop((short) 1);
        dataStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        return dataStyle;
    }

}
 

 

 

 

调用方法

@RequestMapping(value = "/export", method = RequestMethod.GET)
public String export(HttpServletRequest request, HttpServletResponse response, Integer status, String keyword)  throws Exception{
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
    String date = format.format(new Date());
    List<TradeRecord> dataSet = tradeRecordService.findByUserIdApp(0, null,null,null,0,600000,null);
    String[] EXCEL_TITLE_NAMES = {"提现用户名", "真实姓名", "提现金额", "提现卡号", "银行名称", "支行地址", "提现申请时间", "提现状态"};
    String[] COLUM_METHOD_NAMES = {"getId", "getUserId", "getOtherId", "getTableName", "getType", "getSource", "getTime", "getAmount"};
    try {
        Long begin = System.currentTimeMillis();
        Workbook ex = ExcelUtils.getWorkBook(EXCEL_TITLE_NAMES, COLUM_METHOD_NAMES, dataSet);
        OutputStream out = DownloadUtils.getResponseOutput(date + "提现.xlsx", response);
        ex.write(out);
        ex.close();
        DownloadUtils.closeResponseOutput(response);
        System.out.println(System.currentTimeMillis()-begin);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return null;
}