java导出20w条数据

本文主要是介绍java导出20w条数据，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

---

前言

在上次写的java导出excel的后，在导出20w条数据时出现了请求超时的情况，而且一次查询超多的数据会很慢，所以修改了一下，
针对数据量一旦过大前端请求需要一直等待超时的情况将前端请求改为异步请求，请求完之后让后台自己跑，跑完直接下载；
在查询数据时改为分页查询；
之前的放回excel，base64字符串前端模拟点击下载，改为先生成excel，返回生成的文件名，直接访问地址下载

一、前端代码

  async doExport() {let that = this;if(that.expflag){eframe.showToast("正在导出中");return;}that.expflag = true;let rqurl = constData.baseUrl + "/export";try {const response = await axios.get(rqurl, {params: {aab004:that.form.aab301,bab001:that.form.bab001,aab998:that.form.aab998,ddz128:that.form.ddz128,ddz183:that.form.ddz183,c_date:that.form.c_date}});var fileName = response.fileName;if (fileName != null && fileName != "") {var url = constData.baseUrl + "/download/"+fileName;that.expflag = false;window.location.href = url;}} catch (error) {that.expflag = false;console.error('Error:', error);}}

二、后端代码

@ResponseBody@RequestMapping(value = "/export",)public void export(HttpServletRequest request, HttpServletResponse response) throws BusinessException, UnsupportedEncodingException {String expFile;// 获取当前日期时间Date currentDate = new Date();// 定义日期时间格式SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");// 格式化当前日期时间String formattedDateTime = dateFormat.format(currentDate);// 构建新的文件名File file = null;expFile = "";FileOutputStream out = null;expFile = expFile + "excel" + formattedDateTime + "file.xlsx";file = this.creatDownloadFile(request, expFile);String aab004 = request.getParameter("aab004");String bab001 = request.getParameter("bab001");String aab998 = request.getParameter("aab998");String ddz128 = request.getParameter("ddz128");String ddz183 = request.getParameter("ddz183");String c_date = request.getParameter("c_date");file = ecoTownEnterpriseExportForm.getFile();System.out.println("==========开始导出===========");try {// 创建工作簿Workbook book = null;Sheet ws = null;Cell labelC = null;InputStream base = null;int sheetIndex = 0;if (file.isFile()) {base = new FileInputStream(file.getPath());} else {String path = request.getSession().getServletContext().getRealPath("") + "/resources/file/template/" + "base.xlsx";//System.out.println("文件是否存在：" + new File(path).exists());base = new FileInputStream(path);}book = new XSSFWorkbook(base);((InputStream) base).close();try {ws = book.getSheetAt(sheetIndex);} catch (Exception var32) {book.createSheet("sheet" + sheetIndex);ws = book.getSheetAt(sheetIndex);}int x = 1;int i = 0;Row row = ws.createRow(0);int xx;List<String> headRow = new ArrayList<>();headRow.add("单位社保编号");headRow.add("统一社会信用代码");headRow.add("单位名称");headRow.add("法定代表人");headRow.add("电话");headRow.add("身份证号");headRow.add("联系人");headRow.add("联系电话");headRow.add("单位地址");headRow.add("经营地址");headRow.add("月份");for (xx = 0; xx < headRow.size(); xx++) {labelC = row.createCell(xx, 1);String title = (headRow.get(xx));labelC.setCellValue(title);ws.setColumnWidth(xx, 4000);}EcoDTO ecoDTO = new EcoDTO();ecoDTO rowData = null;ecoDTO.setPageSize(new BigDecimal(10000));//分页查询每页数量List<EcoDTO> data = null;label240:while (true) {ecoDTO.setPageIndex(new BigDecimal(x));//分页查询 第几页data = DZ42.getEcoDtoList(ecoDTO);System.out.println("查第" + x + "次");++x;if (data != null && !data.isEmpty()) {xx = 0;while (true) {if (xx >= data.size()) {continue label240;}rowData = data.get(xx);row = ws.createRow(i + 1);// 设置数据行内容，这里需要根据实际情况设置每一列的数据row.createCell(0).setCellValue(rowData.getDdz128());row.createCell(1).setCellValue(rowData.getDdz183());row.createCell(2).setCellValue(rowData.getBab001());row.createCell(3).setCellValue(rowData.getAab998());row.createCell(4).setCellValue(rowData.getAab004());row.createCell(5).setCellValue(rowData.getAab013());row.createCell(6).setCellValue(rowData.getAae005());row.createCell(7).setCellValue(rowData.getAac147());row.createCell(8).setCellValue(rowData.getLxr());row.createCell(9).setCellValue(rowData.getLxdh());row.createCell(10).setCellValue(rowData.getDwdz());row.createCell(11).setCellValue(rowData.getJydz());row.createCell(12).setCellValue(rowData.getC_date());++i;++xx;}}book.setActiveSheet(sheetIndex);out = new FileOutputStream(file);book.write(out);break;}} catch (Exception var36) {var36.printStackTrace();throw new BusinessException(var36);} finally {if (out != null) {try {out.close();} catch (Exception var34) {throw new BusinessException(var34);}}System.out.println("==========结束导出===========");}JSONObject responseJson = new JSONObject();response.setHeader("Service-Type", "Servlet");response.setContentType("application/json;charset=UTF-8");// 直接将responseJsonStr发送回客户端responseJson.put("fileName",expFile);try {response.getWriter().write(responseJson.toJSONString());response.getWriter().flush();} catch (IOException e) {e.printStackTrace();}}

总结

虽然成功的导出了20w条数据，写完之后我测试了一下没有问题，又用了30w条也没问题，但是当导出40w条数据时出现了

org.springframework.web.util.NestedServletException: Handler dispatch
failed; nested exception is java.lang.OutOfMemoryError: Java heap
space

Java 堆空间不足，暂时还没解决，不知道怎么整。生产环境暂时数据量还没到所有不会报错，有没有大神提点建议，感激不尽。

这篇关于java导出20w条数据的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---