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

最新推荐文章于 2023-12-13 14:33:00 发布

u010786197

最新推荐文章于 2023-12-13 14:33:00 发布

阅读量238

点赞数

分类专栏： spring mybitis excel导出

本文链接：https://blog.csdn.net/u010786197/article/details/89948974

版权

spring 同时被 3 个专栏收录

4 篇文章 0 订阅

订阅专栏

mybitis

2 篇文章 0 订阅

订阅专栏

excel导出

1 篇文章 0 订阅

订阅专栏

话不多说上代码

可以通过新的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;
}