首页 > 代码库 > 分页并分文件导出Excel接口Controller和Service
分页并分文件导出Excel接口Controller和Service
1 /*start---------------------------------------[导出Excel接口Controller--shiyanjun]-------------------------------------------------------------*/ 2 3 /** 4 * 功能描述：【导出Excel接口】 5 * 创建者：shiyanjun 6 * 创建时间：2014年11月25日10:00:18 周二 7 * @param columns 表头 8 * @param querySql 查询SQL 9 * @param filePath 文件目录10 * @param request11 * @param response12 */13 @RequestMapping(value = "http://www.mamicode.com/exportExcelApi", method = RequestMethod.GET)14 @ResponseBody15 public void exportExcelApi( @RequestParam (value = "http://www.mamicode.com/columns",defaultValuehttp://www.mamicode.com/= "")String columns,16 @RequestParam(value = "http://www.mamicode.com/querySql",defaultValuehttp://www.mamicode.com/= "") String querySql,17 @RequestParam(value = "http://www.mamicode.com/filePath",defaultValuehttp://www.mamicode.com/= "") String filePath,18 HttpServletRequest request,HttpServletResponse response) {19 20 //文件名称21 String fileName = "档案信息";22 //表头23 columns = "ID,EHR_ID,EHR_CODE,INNER_CODE,PERSON_NAME,GENDER";24 //查询SQL25 querySql = "SELECT * FROM EHR_BASE";26 // querySql = "SELECT ID,EHR_ID,EHR_CODE,INNER_CODE,PERSON_NAME,GENDER FROM EHR_BASE EB WHERE EB.GENDER = ‘1‘";27 // querySql = "SELECT ID,EHR_ID,EHR_CODE,INNER_CODE,PERSON_NAME,GENDER FROM EHR_BASE EB WHERE EB.BIRTH_DATE > TO_DATE(‘2007-11-17‘,‘YYYY-MM-DD‘) AND EB.BIRTH_DATE <= SYSDATE AND EB.CANCELLED = ‘1‘ AND EB.DEATH = ‘1‘ AND EB.MOVED_OUT = ‘1‘ AND EB.MNG_ORG_CODE LIKE ‘0203%‘ AND EB.SIGN_TEAM_CODE = ‘a843979a-50d0-4f35-84ca-c5e886291995‘";28 29 //文件导出目录[D:\apache-tomcat-6\webapps\phis\exportExcelApi\20141121\EMH\]30 String basePath = ApplicationContextUtils.getBasePath();31 filePath = basePath + "exportExcelApi\\";32 String dateName = "20140221\\";33 String tabName = "MS\\";34 filePath = filePath + dateName + tabName;35 36 try {37 exportApiService.importExcelApi(querySql, columns, fileName, filePath);38 } catch (Exception e) {39 e.printStackTrace();40 }41 42 }43 44 /*end---------------------------------------[导出Excel接口--shiyanjun]-------------------------------------------------------------*/
1 /*start---------------------------------------[导出Excel接口Service--shiyanjun]-------------------------------------------------------------*/ 2 /** 3 * 功能描述： [导出Excel接口] 4 * 创建者：shiyanjun 5 * 创建时间：2014年11月25日11:14:18 周二 6 * @param querySql 查询sql 7 * @param columnsStr 导出的列 8 * @param fileName 文件名称 9 * @param filePath 文件存放路径 10 */ 11 public void importExcelApi(String querySql,String columnsStr,String fileName,String filePath){ 12 13 StringBuilder selectSql = new StringBuilder(); 14 StringBuilder countSql = new StringBuilder();//统计数据总数的sql 15 16 selectSql.append("SELECT COUNT(1) FROM(\n"); 17 countSql.append(selectSql).append(querySql).append(")"); 18 19 // System.out.println(countSql); 20 21 int dataCount = jdbcTemplate.queryForInt(countSql.toString());//数据总数 22 String dir = "DESC"; //默认升序 23 String sort = "ID"; //默认排序字段 24 FileOutputStream outStream = null; 25 //要导出的表头 26 List<ExportColumnDto> columns=new ArrayList<ExportColumnDto>(); 27 if(columnsStr != null){ 28 String[] arr = columnsStr.split(","); 29 if(arr != null){ 30 for (String str : arr) { 31 if(str != null){ 32 columns.add(new ExportColumnDto(str, str)); 33 } 34 } 35 } 36 } 37 38 //根据filePath创建目录 39 File file = new File(filePath); 40 if(!file.exists() && !file.isDirectory()){ 41 file.mkdirs(); 42 } 43 44 int exportNumber = 888;//每个文件导出数 45 int qryNumber = 450;//每页查询数 46 int qryCount = getQryCount(dataCount,qryNumber);//计算出查询次数 47 int end = 1;//用于判断是否取到最后 48 int fileNum = 0;//文件名称编号 49 List<Map<String,Object>> tempList = new ArrayList<Map<String, Object>>();//定义一个临时集合 50 51 // long startTime = System.currentTimeMillis(); 52 53 for (int i = 0; i < qryCount; i++) {//按查询次数循环 54 List<Map<String,Object>> pageList = new ArrayList<Map<String, Object>>(); 55 if(i != (qryCount - 1)){//如果不是最后一次查询 56 pageList = queryForPage(querySql, i*qryNumber, (i+1)*qryNumber, dir, sort);//查询出一页数据 57 }else{ 58 pageList = queryForPage(querySql, i*qryNumber, dataCount, dir, sort); 59 } 60 for (int j = 0; j < pageList.size(); j++) {//遍历此页数据 61 tempList.add(pageList.get(j)); 62 if(tempList.size() == exportNumber || (end ==qryCount && j == pageList.size() - 1 )){ 63 try { 64 outStream = new FileOutputStream(filePath + (fileNum+1) + ".xls");//创建文件 65 } catch (FileNotFoundException e) { 66 e.printStackTrace(); 67 } 68 try { 69 ExportApiService.printExcelApi(columns, tempList, fileName, outStream);//导出Excel 70 // System.out.println("导出文件"+(fileNum+1)+".xls,包含"+tempList.size()+"条数据"); 71 } catch (Exception e) { 72 logger.error("导出Excel异常", e); 73 } finally { 74 try { 75 if (outStream != null) { 76 outStream.flush(); 77 outStream.close(); 78 } 79 } catch (Exception e1) { 80 } 81 } 82 tempList.clear();//清空临时集合 83 fileNum++;//文件名序号递增 84 } 85 } 86 end++; 87 } 88 //计算耗时 89 // long endTime = System.currentTimeMillis(); 90 // long chaTime = endTime - startTime; 91 // long time = chaTime/1000; 92 // System.out.println("共耗时"+time+"秒"); 93 94 } 95 96 //计算查询次数 97 public int getQryCount(int count, int rows) { 98 int num = 1; 99 if(count >= rows){100 if(count % rows == 0){101 num = count/rows;102 }else{103 num = count/rows + 1;104 }105 }106 return num;107 }108 109 /**110 * 【自定义查询导出到Excel】111 * @param columns List<ExportColumnDto> 列设置集合112 * @param rowData 数据集合113 * @param filename 自定义查询名称114 * @param filePath 导出文件临时存放路径115 * @return116 */117 public static void printExcelApi(List<ExportColumnDto> columns,List<Map<String,Object>> rowData,String fileName,FileOutputStream outStream)throws Exception{118 if(rowData =http://www.mamicode.com/= null || rowData.size() < 1) throw new RuntimeException("数据集合为空");119 else if(columns == null || columns.size() < 1) throw new RuntimeException("导出列集合集合为空");120 else{121 WritableWorkbook writableWorkbook=Workbook.createWorkbook(outStream); // 创建工作薄122 WritableSheet writableSheet = writableWorkbook.createSheet(fileName, 0); // 创建工作表123 //设置标题格式124 WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 15,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);125 WritableCellFormat headerFormat = new WritableCellFormat(headerFont);126 // headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN);127 // headerFormat.setAlignment(Alignment.CENTRE);//水平对齐128 headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐129 //设置表头格式130 WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);131 WritableCellFormat titleFormat = new WritableCellFormat(titleFont);132 // titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);133 // titleFormat.setAlignment(Alignment.CENTRE);134 WritableFont dateFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);135 //设置显示数据格式136 WritableCellFormat dataFormat = new WritableCellFormat(dateFont);137 // dataFormat.setAlignment(Alignment.CENTRE);138 dataFormat.setVerticalAlignment(VerticalAlignment.CENTRE);139 writableSheet.mergeCells(0, 0, columns.size()-1, 0); // 合并第一行的单元格(合并了titleList.size 个单元格)140 //添加标题141 writableSheet.addCell(new Label(0,0,fileName,headerFormat));//第一个0 表示 列 ,第二个0 表示行, headerFormat 格式化EXCEL142 //添加title143 String descript="";144 for(int i=0;i<columns.size();i++){145 descript=columns.get(i).getDescription();146 writableSheet.addCell(new Label(i,1,descript,titleFormat));147 }148 //填充数据149 Object value=http://www.mamicode.com/null;150 String showValue="";151 String name="";152 Method method=null;153 154 //初始化字段转换函数155 Map<String,Method> methodCache=new HashMap<String,Method>();156 Class convert=FieldConvert.class;157 for(int j=0;j<columns.size();j++){158 String convertName=columns.get(j).getConvertName();159 try{160 method=convert.getMethod("convert"+convertName, String.class);161 if(null != method) methodCache.put(convertName, method);162 }catch(NoSuchMethodException ne){}163 }164 165 for(int k=0;k<rowData.size();k++){166 for(int j=0;j<columns.size();j++){167 name=columns.get(j).getName();168 value=http://www.mamicode.com/rowData.get(k).get(name);169 showValue=http://www.mamicode.com/value == null ? "":String.valueOf(value);170 if("".equals(showValue.trim()) || "-1".equals(showValue.trim()) ){171 showValue="http://www.mamicode.com/不详";172 }else{173 String convertName=columns.get(j).getConvertName();174 if(methodCache.containsKey(convertName)){175 showValue=http://www.mamicode.com/methodCache.get(convertName).invoke(convert, value).toString();176 }177 }178 writableSheet.addCell(new Label(j,k+2,showValue,dataFormat));179 }180 }181 182 writableWorkbook.write();183 writableWorkbook.close();184 }185 186 }187 /**188 * 功能描述： [分页查询数据]189 * 创建者：shiyanjun190 * 创建时间：2014年11月21日 13:01:24191 * @param querySql 查询SQL192 * @param startRowIndex 起始页193 * @param pageSize 每页数据条数194 * @param dir 排序方式195 * @param sort 排序字段196 * @return197 */198 private List<Map<String,Object>> queryForPage(String querySql, int startRowIndex, int pageSize, String dir, String sort) {199 if (pageSize < 1) {200 pageSize = 15;201 logger.warn("pageSize<1，将自动调整为15。");202 }203 if(StringUtils.equals(sort, "personName")){//姓名排序转换为按拼音排序204 sort = "nameIndex";205 }206 String orderSql = "ORDER BY " + sort + " " + dir;207 String rownumSql = "";208 if (Config.isOracle() || Config.isKingbase()) {209 rownumSql = "ROWNUM AS NUM";210 } else if (Config.isDb2()) {211 rownumSql = "ROWNUMBER() OVER(" + orderSql + ") AS NUM";212 }213 StringBuilder pageSql = new StringBuilder(" SELECT * FROM ( \n");214 pageSql.append(" SELECT TEMP.*, ").append(rownumSql).append(" FROM ( \n");215 pageSql.append(querySql);216 pageSql.append(")\n");217 pageSql.append("TEMP\n");218 pageSql.append(" ) WHERE NUM <= ").append(pageSize).append(" AND NUM > ").append(startRowIndex).append(" \n");219 if (Config.isOracle() || Config.isKingbase()) {220 pageSql.append(orderSql);221 }222 //根据pageSql查询分页数据223 List<Map<String,Object>> pageList = jdbcTemplate.queryForList(pageSql.toString());224 225 // System.out.println("查询出条"+pageList.size()+"数据[第"+startRowIndex+"条到第"+pageSize+"条]："+pageList);226 // System.out.println("本次查询sql："+pageSql.toString());227 228 229 return pageList;230 } 231 232 /*end---------------------------------------[导出Excel接口--shiyanjun]-----------------------------------------------------------*/
分页并分文件导出Excel接口Controller和Service
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。