//Controller层：
/*start---------------------------------------导出Excel接口[需要3个参数]-------------------------------------------------------------*/            /**     * 功能描述：【导出Excel接口】     * 创建者：shiyanjun     * @param columns    表头         * @param querySql    查询SQL     * @param filePath    文件目录     * @param request     * @param response     */    @RequestMapping(value = "exportExcelApi", method = RequestMethod.GET)    @ResponseBody    public void exportExcelApi(    @RequestParam (value = "http://www.mamicode.com/columns",defaultValuehttp://www.mamicode.com/= "")String columns,                                @RequestParam(value = "querySql",defaultValuehttp://www.mamicode.com/= "") String querySql,                                @RequestParam(value = "filePath",defaultValuehttp://www.mamicode.com/= "") String filePath,                                HttpServletRequest request,HttpServletResponse response) {                String fileName = "档案信息";//文件名称        columns = "ID,EHR_ID,EHR_CODE,INNER_CODE,PERSON_NAME,GENDER";//模拟表头        querySql = "SELECT ID,EHR_ID,EHR_CODE,INNER_CODE,PERSON_NAME,GENDER FROM EHR_BASE EB WHERE EB.GENDER = ‘1‘";//模拟查询SQL        String basePath = ApplicationContextUtils.getBasePath();//获取项目根路径        filePath = basePath + "exportExcelApi\\";        String dateName = "20140220\\";//日期文件夹        String tabName = "EHR\\";//表名文件夹        filePath = filePath + dateName + tabName;        try {            exportApiService.importExcelApi(querySql, columns, fileName, filePath);        } catch (Exception e) {            e.printStackTrace();        }    }    /*end---------------------------------------导出Excel接口[需要3个参数]-------------------------------------------------------------*/

/*start---------------------------------------导出Excel接口[需要3个参数]-------------------------------------------------------------*///Service层

        /**     * 功能描述：【导出Excel接口】      * @param querySql     * @param columnsStr     * @param fileName     * @param outStream     * @throws Exception     */    public void importExcelApi(String querySql,String columnsStr,String fileName,String filePath)throws Exception{        FileOutputStream outStream = null;        //根据SQL语句查询要导出的数据        List<Map<String,Object>> rowData =http://www.mamicode.com/ jdbcTemplate.queryForList(querySql);        //要导出的表头        List<ExportColumnDto> columns=new ArrayList<ExportColumnDto>();        if(columnsStr != null){            String[] arr = columnsStr.split(",");            if(arr != null){                for (String str : arr) {                    if(str != null){                        columns.add(new ExportColumnDto(str, str));                    }                }                }        }                int count = rowData.size();//数据总条数        int rows = 1500;//每个Excel文件中要显示的数据条数        int num = 1;//初始化要导出的Excel文件数                //根据count和rows来计算实际导出的文件个数        if(count >= rows){            if(count % rows == 0){                num = count/rows;            }else{                num = count/rows + 1;            }        }        List<Map<String,Object>> rowDataSub = null;//从rowData中取出指定条数(rows)的数据存到rowDataSub中                File file = new File(filePath);        //如果文件夹不存在则创建        if(!file.exists() && !file.isDirectory()){//            System.out.println("//不存在");            file.mkdirs();        }/* else {            System.out.println("//目录存在");        }*/                for (int i = 0; i < num; i++) {            try {                outStream = new FileOutputStream(filePath + (i+1) + ".xls");//创建文件输出流，指定导出的文件路径            } catch (FileNotFoundException e) {                e.printStackTrace();            }                        if(i == (num - 1)){                rowDataSub = rowData.subList(i*rows, count);//最后一个文件            }else{                rowDataSub = rowData.subList(i*rows, (i+1)*rows);            }                        try {                ExportApiService.printExcelApi(columns, rowDataSub, fileName, outStream);//导出Excel            } catch (Exception e) {                logger.error("导出Excel异常", e);            } finally {                try {                    if (outStream != null) {                        outStream.flush();                        outStream.close();                    }                } catch (Exception e1) {                }            }        }            }    /**     * 【自定义查询导出到Excel】     * @param columns List<ExportColumnDto> 列设置集合     * @param rowData 数据集合     * @param filename 自定义查询名称     * @param filePath 导出文件临时存放路径     * @return     */    public static void printExcelApi(List<ExportColumnDto> columns,List<Map<String,Object>> rowData,String fileName,FileOutputStream outStream)throws Exception{        if(rowData =http://www.mamicode.com/= null || rowData.size() < 1) throw  new RuntimeException("数据集合为空");        else if(columns == null || columns.size() < 1) throw  new RuntimeException("导出列集合集合为空");        else{            WritableWorkbook writableWorkbook=Workbook.createWorkbook(outStream); // 创建工作薄            WritableSheet writableSheet = writableWorkbook.createSheet(fileName, 0);  // 创建工作表            //设置标题格式            WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 15,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);            WritableCellFormat headerFormat = new WritableCellFormat(headerFont);//                headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN);//                headerFormat.setAlignment(Alignment.CENTRE);//水平对齐            headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐            //设置表头格式            WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);            WritableCellFormat titleFormat = new WritableCellFormat(titleFont);//                titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);//                titleFormat.setAlignment(Alignment.CENTRE);            WritableFont dateFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);            //设置显示数据格式            WritableCellFormat dataFormat = new WritableCellFormat(dateFont);//                dataFormat.setAlignment(Alignment.CENTRE);            dataFormat.setVerticalAlignment(VerticalAlignment.CENTRE);            writableSheet.mergeCells(0, 0, columns.size()-1, 0); // 合并第一行的单元格(合并了titleList.size 个单元格)            //添加标题            writableSheet.addCell(new Label(0,0,fileName,headerFormat));//第一个0 表示 列  ,第二个0 表示行, headerFormat 格式化EXCEL            //添加title            String descript="";            for(int i=0;i<columns.size();i++){                descript=columns.get(i).getDescription();                writableSheet.addCell(new Label(i,1,descript,titleFormat));            }            //填充数据            Object value=http://www.mamicode.com/null;            String showValue="";            String name="";            Method method=null;                      //初始化字段转换函数            Map<String,Method> methodCache=new HashMap<String,Method>();            Class convert=FieldConvert.class;            for(int j=0;j<columns.size();j++){                 String convertName=columns.get(j).getConvertName();                 try{                     method=convert.getMethod("convert"+convertName, String.class);                     if(null != method) methodCache.put(convertName, method);                 }catch(NoSuchMethodException ne){}            }                        for(int k=0;k<rowData.size();k++){                for(int j=0;j<columns.size();j++){                    name=columns.get(j).getName();                    value=rowData.get(k).get(name);                    showValue=value =http://www.mamicode.com/= null ? "":String.valueOf(value);                    if("".equals(showValue.trim()) || "-1".equals(showValue.trim()) ){                        showValue="不详";                    }else{                        String convertName=columns.get(j).getConvertName();                        if(methodCache.containsKey(convertName)){                            showValue=methodCache.get(convertName).invoke(convert, value).toString();                        }                    }                    writableSheet.addCell(new Label(j,k+2,showValue,dataFormat));                }            }            writableWorkbook.write();            writableWorkbook.close();        }    }/*end---------------------------------------导出Excel接口[需要3个参数]-------------------------------------------------------------*/