大内容的导出,excel一个sheet页100W条(excel一个sheet最大104W),测试了导出10个sheet页。这么也不知道合不合适
controller层:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | @GetMapping(value = "exportExcel") public void exportExcel(HttpServletResponse response, @RequestParam(required = false) String startTime, @RequestParam(required = false) String stopTime) { try { long start = System.currentTimeMillis(); logger.info("导出操作日志开始计时"); String fileName = "操作日志"; Map<String, Object> map = new HashMap<>(); map.put("startTime", startTime); map.put("stopTime", stopTime); List<OperationLogEntity> objs = operationLogDao.exportExcelBigStock(map); ExcelUtilBigData excelUtilBigData = new ExcelUtilBigData(); excelUtilBigData.exportExcel(response, fileName, objs, PARAM.ETST_COLUMS.getParams(), PARAM.TEST_FIELDS.getParams()); long end = System.currentTimeMillis(); System.out.println("导出操作日志用时:" + (end - start)); } catch (Exception e) { logger.error("导出文件失败:" + e.getMessage()); } } |
util:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 | package ; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; /** * Created by HHYang on 2018/4/16. */ public class ExcelUtilBigData { public <T extends Object> void exportExcel(HttpServletResponse response, String fileName, List<T> objs, String[] colums, String[] fields) throws Exception { String thizTime = new SimpleDateFormat("yyyy-MM-dd").format(new Date()); response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + "-" + thizTime + ".xlsx", "UTF-8")); response.setContentType("application/vnd.ms-excel;charset=utf-8"); SXSSFWorkbook wb = new SXSSFWorkbook(100); System.out.println("总行数:" + objs.size()); int page_size = 10000; int sheet_count = 1000000; List<T> objsCut; List<T> objsSheet; Sheet sh; Row row; for (int sheetNo = 1; sheetNo <= (objs.size() / sheet_count + 1); sheetNo++) { objsSheet = this.pagerBYXX(objs, sheetNo, sheet_count); sh = wb.createSheet("sheet" + sheetNo); // 建立新的sheet对象 sh.createFreezePane(0, 1);//冻结标题行 row = sh.createRow(0); // 创建第一行对象 for (int i = 0; i < colums.length; i++) { Cell cel0 = row.createCell(i);//定义表头 cel0.setCellValue(colums[i]); } // 求该sheet页待导出数据的行数 int list_count = objsSheet.size(); // 根据行数求数据提取次数 int export_times = list_count % page_size > 0 ? list_count / page_size + 1 : list_count / page_size; // 按次数将数据写入文件 for (int j = 0; j < export_times; j++) { objsCut = this.pagerBYXX(objsSheet, j + 1, page_size); int len = objsCut.size() < page_size ? objsCut.size() : page_size; for (int i = 0; i < len; i++) { Row row_value = sh.createRow(j * page_size + i + 1); for (int field = 0; field < fields.length; field++) { Cell cel0_value = row_value.createCell(field); String callValue = this.getClassValue(objsCut.get(i), fields[field]); cel0_value.setCellValue(callValue); } } objsCut = null; // 每次存储len行,用完了将内容清空,以便内存可重复利用 } objsSheet = null; } OutputStream os = response.getOutputStream(); wb.write(os); os.close(); wb.dispose(); } /*手动分页*/ public static <T extends Object> List<T> pagerBYXX(List<T> lists, int page, int pageSize) { List<T> listRes; if (null == lists || lists.size() == 0) { return null; } page = page <= 0 ? 1 : page; pageSize = pageSize <= 0 ? 10 : pageSize; int fromIndex = (page - 1) * pageSize; int toIndex = (page - 1) * pageSize + pageSize; if (page == 1 && lists.size() <= page * pageSize) { return lists; } else if (page * pageSize > lists.size()) { int remain = lists.size() % pageSize; listRes = lists.subList(lists.size() - remain, lists.size()); return listRes; } else { listRes = lists.subList(fromIndex, toIndex); return listRes; } } /*获取单元格内容*/ public String getClassValue(Object obj, String filed) throws InvocationTargetException, IllegalAccessException { SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Class<?> temClass = obj.getClass(); Field[] fields = temClass.getDeclaredFields(); Object invoke = null; for (Field field : fields) { String userFieldName = field.getName(); Object value = field.getType(); if (userFieldName.equals(filed)) { //首字母大写 char[] cs = userFieldName.toCharArray(); cs[0] -= 32; //System.out.println(String.valueOf(cs)); //首字母大写 Method method = null; try { //转大小写 method = temClass.getMethod("get" + String.valueOf(cs)); //不转大小写 //method = temClass.getMethod("get" + String.valueOf(userFieldName)); if (String.valueOf(value).equals("class java.util.Date")) { Object getDate = method.invoke(obj); if (null == getDate) { return ""; } invoke = simpleDateFormat.format(getDate); } else { invoke = method.invoke(obj); if (null == invoke) { return ""; } } } catch (NoSuchMethodException e) { e.printStackTrace(); continue; } return String.valueOf(invoke); } } return String.valueOf(invoke); } /** * 设置标题样式 - 失败 * */ /*private void titleFont() { Font titleFont = wb.createFont(); titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); titleFont.setFontName("宋体"); titleFont.setFontHeightInPoints((short) 13); titleStyle = wb.createCellStyle(); titleStyle.setFont(titleFont); titleStyle.setBorderBottom((short)1); titleStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); }*/ } |
enum:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | package ; /** * Created by liyue on 2017/11/4. * <p> * <p> * excel表格需要的参数 */ public enum PARAM { ETST_COLUMS(new String[]{"操作人id", "操作人名称", "操作url", "操作事件", "操作时间", "描述", "操作IP"}), TEST_FIELDS(new String[]{"userId", "userName", "operationUrl", "operationEventC", "operationTime", "description", "operationIp"}),; private String[] params; PARAM(String[] params) { this.params = params; } public String[] getParams() { return this.params; } } |