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]-----------------------------------------------------------*/