/** * 订单的excel导出 * * @param request * @param iccid * @param fullName * 商户名称 * @param orderStatus * 订单状态 * @return * @throws Exception */ @RequestMapping(value = "/orderExcel") public void orderExcel(HttpServletRequest request, HttpServletResponse response, @RequestParam(value = "accessNumber", required = false) String accessNumber, @RequestParam(value = "fullName", required = false) String fullName, @RequestParam(value = "orderStatus", required = false) Integer orderStatus, @RequestParam(value = "access_token", required = true) String accessToken) throws Exception { AdminUserDTO usrDto = (AdminUserDTO) request.getAttribute(Constants.SESSION_NAME_USER); Map<String,Object> params = new HashMap<String,Object>(); params.put("agentId", usrDto.getUserInfo().getAgentId()); params.put("accessNumber", accessNumber); params.put("fullName", fullName); params.put("orderStatus", orderStatus); rechargeOrderService.orderExcel(params, response); }
@Override public void orderExcel(Map<String, Object> params, HttpServletResponse response) {
try { String[] export = {"订单号","接入号","订购套餐","充值","分润","所属商户","支付状态","订购时间"};//标题元素 String fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()); StringBuffer sbsql = new StringBuffer("select fpo.order_id as orderId,ic.access_number as accessNumber,fp.name,round(fpo.order_money/100,2) as orderMoney,\r\n" + " round(po.profit_money/100,2) as profitMoney,cu.full_name as fullName,fpo.order_status as orderStatus,date_format(fpo.modify_date,'%Y-%m-%d') as modifyDate\r\n" + " from agency_flow_package_order fpo\r\n" + " LEFT JOIN agency_customer cu ON cu.customer_id = fpo.customer_id \r\n" + " LEFT JOIN agency_flow_package_order_detail fpod ON fpod.order_id = fpo.order_id\r\n" + " LEFT JOIN agency_flow_package fp ON fp.id = fpod.package_id\r\n" + " LEFT JOIN agency_iot_cards ic ON ic.iccid = fpod.iccid\r\n" + " LEFT JOIN agency_profit_order po ON po.order_id = fpod.order_id\r\n" + " where fpo.is_del != 1\r\n" + " and cu.status != 1\r\n" + " and fp.is_del != 1" + " and cu.agent_id in(select agent_id from agency_info where agent_id = " + params.get("agentId").toString() + " or parent_agent_id = " + params.get("agentId").toString() + ")\r\n"); List<String> parList = new ArrayList<String>(); if(null != params.get("accessNumber")&&StringUtils.isNotBlank(params.get("accessNumber").toString())) { parList.add(params.get("accessNumber").toString()); sbsql.append(" and ic.access_number=?"); } if(null != params.get("fullName")&&StringUtils.isNotBlank(params.get("fullName").toString())) { parList.add("%"+params.get("fullName").toString()+"%"); sbsql.append(" and cu.full_name like?"); } if(null != params.get("orderStatus")&&StringUtils.isNotBlank(params.get("orderStatus").toString())) { parList.add(params.get("orderStatus").toString()); sbsql.append(" and fpo.order_status = ?"); } sbsql.append(" ORDER BY fpo.modify_date DESC"); ExcelUtils2.excelExport(response, fileName, export,sbsql.toString(),parList,"yyyy-MM-dd");// 调用封装好的导出方法，具体方法在下面 } catch (Exception e) { e.printStackTrace(); } }
/** * 百万级数据导出 * * @param response * @param fileName,生成的文件名称 * @param excelHeader，存放"标题#字段"这样格式的数组 * @param sql执行的sql，如："SELECT * * FROM users WHERE username=? AND PASSWORD = ?" * @param params，sql里面的参数 */ public static void excelExport(HttpServletResponse response, String fileName, String[] excelHeader, String sql, List params,String rule) throws InstantiationException, IllegalAccessException, ClassNotFoundException, IOException, InterruptedException { // 设置请求 response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8")); // 内存中只创建100个对象，写临时文件，当超过100条，就将内存中不用的对象释放。 @SuppressWarnings("resource") SXSSFWorkbook wb = new SXSSFWorkbook(1000); // 关键语句 // 设置标题样式 CellStyle titleStyle = wb.createCellStyle(); // 设置单元格边框样式 titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 细边线 titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框 细边线 titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 细边线 titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 细边线 // 设置单元格对齐方式 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中 titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中 // 设置字体样式 Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 15); // 字体高度 titleFont.setFontName("黑体"); // 字体样式 titleStyle.setFont(titleFont); Sheet sheet = null; // 工作表对象 Row nRow = null; // 行对象 Cell nCell = null; // 列对象 long startTime = 0;// 开始操作数据时间 // 使用jdbc链接数据库 Class.forName(PropertiesUtil.getStringValue("driverClassName_admin")).newInstance(); String url = PropertiesUtil.getStringValue("url_admin"); String user = PropertiesUtil.getStringValue("username_admin"); String passward = PropertiesUtil.getStringValue("password_admin"); try { Class.forName(PropertiesUtil.getStringValue("driverClassName_admin")); } catch (ClassNotFoundException e1) { e1.printStackTrace(); } Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { // 创建数据库连接 con = DriverManager.getConnection(url, user, passward); // ResultSet的TYPE_FORWARD_ONLY属性只允许结果集的游标向下移动,ResultSet.CONCUR_READ_ONLY属性类似只读 // 属性，不可仪更改，不能用结果集更新数据 ps = (PreparedStatement) con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); System.out.println("excel sql: "+sql); ps.setFetchSize(Integer.MIN_VALUE);// 每次抓取数据的条数
ps.setFetchDirection(ResultSet.FETCH_REVERSE); if (params == null) { params = new ArrayList<>(); } for (int i = 0; i < params.size(); i++) { if (params.get(i) == null) { continue; } if (params.get(i) instanceof Integer) { ps.setInt(i + 1, (int) params.get(i)); } else if (params.get(i) instanceof Double) { ps.setDouble(i + 1, (double) params.get(i)); } else if (params.get(i) instanceof Float) { ps.setFloat(i + 1, (float) params.get(i)); } else if (params.get(i) instanceof Long) { ps.setLong(i + 1, (long) params.get(i)); } else if (params.get(i) instanceof Date) { ps.setDate(i + 1, (java.sql.Date) params.get(i)); } else if (params.get(i) instanceof BigDecimal) { ps.setBigDecimal(i + 1, (BigDecimal) params.get(i)); } else if (params.get(i) instanceof Boolean) { ps.setBoolean(i + 1, (boolean) params.get(i)); } else if (params.get(i) instanceof Timestamp) { ps.setTimestamp(i + 1, (Timestamp) params.get(i)); } else { ps.setString(i + 1, params.get(i).toString()); } } rs = ps.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); startTime = System.currentTimeMillis(); // 开始时间 System.out.println("strat execute time: " + startTime);
int rowNo = 0; // 总行号 int pageRowNo = 0; // 页行号 boolean haveData = false;//默认执行sql没有返回数据 while (rs.next()) { haveData = true; // 打印300000条后切换到下个工作表 if (rowNo %300000 == 0) {//300000 System.out.println("Current Sheet:" + rowNo / 300000); sheet = wb.createSheet("第" + (rowNo / 300000 + 1) + "个工作簿");// 建立新的sheet对象 sheet = wb.getSheetAt(rowNo / 300000); // 动态指定当前的工作表 pageRowNo = 1; // 每当新建了工作表就将当前工作表的行号重置为0 // 为标题行赋值 Row row = sheet.createRow(0);// 行数从0开始 for (int i = 0; i < excelHeader.length; i++) { Cell titleCell = row.createCell(i); titleCell.setCellValue(excelHeader[i]); titleCell.setCellStyle(titleStyle); } } rowNo++; nRow = sheet.createRow(pageRowNo++); // 新建行对象 for (int j = 0; j < rsmd.getColumnCount(); j++) { nCell = nRow.createCell(j); if(rs.getObject(j+1) instanceof Date) { Date date = (Date) rs.getObject(j + 1); String dateStr = new SimpleDateFormat(rule).format(date); nCell.setCellValue(dateStr); }else { if(j == 6) { if(rs.getString(j+1)!=null) { nCell.setCellValue(Integer.parseInt(rs.getString(j+1)) == 1?"已支付":"未支付"); }else { nCell.setCellValue("-"); } continue; } nCell.setCellValue(rs.getString(j+1)); } }
if (rowNo % 10000 == 0) { System.out.println("row no: " + rowNo); } Thread.sleep(1); // 休息一下，防止对CPU占用 } if(!haveData) {//没有数据的时候 sheet = wb.createSheet("第1个工作簿");// 建立新的sheet对象 sheet = wb.getSheetAt(rowNo / 300000); // 动态指定当前的工作表 // 为标题行赋值 Row row = sheet.createRow(0);// 行数从0开始 for (int i = 0; i < excelHeader.length; i++) { Cell titleCell = row.createCell(i); titleCell.setCellValue(excelHeader[i]); titleCell.setCellStyle(titleStyle); } } long finishedTime = System.currentTimeMillis(); // 处理完成时间 System.out.println("finished execute time: " + (finishedTime - startTime) / 1000 + "m"); OutputStream outputStream = response.getOutputStream();// 打开流 wb.write(outputStream); outputStream.flush(); // 刷新缓冲区 outputStream.close(); // 销毁临时文件 wb.dispose();
long stopTime = System.currentTimeMillis(); // 写文件时间 System.out.println("write xlsx file time: " + (stopTime - startTime) / 1000 + "m"); } catch (Exception e) { e.printStackTrace(); throw new BizException("E1000005", MessageUtil.getMessage("E1000005")); } finally { try { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } if (con != null) { con.close(); } } catch (SQLException e) { System.out.println(e.getMessage()); } } }
注意：一定要用原生的jdbc，原生的jdbc查出来的是游标不会出现内存溢出
