Java jxl 基于模板导出excel

转载自:http://blog.csdn.net/zsj65776529/article/details/51476255

    操作Excel文件的开源工具有很多,用得比较多的就是POI与JXL，一般的导出操作需要涉及很多的单元格操作，比较繁琐。而通过模板的方式先定好布局，就免去不少麻烦，我们只需要遍历数据即可。

---

首先，先看看不连接数据库的例子

public static void test(){  
            ReportEnginer enginer = new ReportEnginer();  
            String templateFile = "E:/template.xls";  
            Map<String, Object> context = new HashMap<String, Object>();  
            context.put("title", "测试模板标题");  
            context.put("name","测试字段");  
            context.put("more","等等。。");  

            //除了单个字段  还可以存入一个list  
            List<Map> testList = new ArrayList<Map>();  
            for(int i=0;i<5;i++){  
                Map innerMap = new HashMap();  
                innerMap.put("name", "姓名"+i);  
                innerMap.put("sex", "男");  
                innerMap.put("age", i);  
                innerMap.put("birthday","2016-05-20");  
                testList.add(innerMap);  
            }  
            context.put("testList", testList);  

            //看需求存入多个list  
            //context.put("list1", list1);  
            //context.put("list2", list2);  
            //。。。  

            String destFile = "E:/target.xls";;  
            try {  
                enginer.excute(templateFile, context, destFile);  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  

        }

其中的测试模板文件 template.xls 大致如下

然后同理，连接数据库操作类似，就是数据的获取与处理略为不同

 /** 
     * 根据模型名称动态导出对应数据库表数据excel 
     * @param modelName 模型名称/表名 
     * @param cols 列名 
     * @param sql 查询条件 
     */  
    public static String export(String modelName,String cols,String sql){  
        ReportEnginer enginer = new ReportEnginer();  
        String templateFile = PathKit.getWebRootPath()+"/project/cars3/excelTemplate/"+modelName+".xls";  
        String[] colList=cols.split(",");  
        Map<String, Object> context = new HashMap<String, Object>();  

        List<Record> list = Db.find(sql);  
        List<Map> testList = new ArrayList<Map>();  
        for(Record rec:list){  
            Map innerMap = new HashMap();  
            for(int j=0;j<colList.length;j++){  
                //特殊处理某些需要额外转换的字段  
                if("Status".equals(colList[j])){  
                    rec.set("Status","Y".equals(rec.get("Status"))?"启用":"停用");  
                }  
                if("storeName".equals(colList[j])){  
                    String storeName=Db.queryStr("select storeName from zdmember where storeId=?",rec.get("storeId"));  
                    rec.set("storeName", storeName);  
                }  
                if("Content".equals(colList[j])){  
                    String content=rec.get(colList[j]).toString().trim();  
                    if(content.contains("\r")){         
    //ReportEnginer类中对以"</r_"  ">"作为单元格开始结束标记 执行substring， 存在回车换行符会报异常  ArrayIndexOutOfBoundsException  
                        //System.out.println("replace前："+content);  
                        content=content.replaceAll("[\\t\\n\\r]", "");  
                        //System.out.println("replace后："+content);  
                    }  
                    rec.set(colList[j],content);  
                }  
                    innerMap.put(colList[j],rec.get(colList[j]));  
            }  
            testList.add(innerMap);  
        }  
        context.put("models", testList);  
        String fileName=DateKit.getCurrentDateTime("yyyyMMddhhmmss")+".xls";  
        String destFile = PathKit.getWebRootPath()+"/upload/excel/"+modelName+"/"+fileName;  
        try {  
            enginer.excute(templateFile, context, destFile);  
        } catch (Exception e) {  
            e.printStackTrace();  
            return "error";  
        }  
        return fileName;  
    } 

然后是service层处理

 public void exportExcel(){  
        String modelName="a7usedgarage";  
        String cols="Title,storeName,Price,Contact,Tel,Saled,AddTime";  
        String sql="select * from a7usedgarage order by AddTime desc";  
        //long start=System.currentTimeMillis();  
        String fileName=ExcelKit.export(modelName, cols,sql);     //将返回的文件名再返回到前端   用于下载  
        //long end=System.currentTimeMillis();  
        //System.out.println("花费时间："+(end-start));  
        //renderFile(new File(PathKit.getWebRootPath()+"/upload/excel/a7usedgarage/"+fileName));  
        renderEasyUISuccess(fileName);  
    }  

前端JS处理 弹出下载 并提示导出进度

 <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-control-double-270" plain="true" onclick="clickDownload(this,'${CONTEXT_PATH}/project/admin/vehicleManagement/exportExcel')">导出</a>  

    function clickDownload(a,url){   
         $.ajax({  
                 url:url,  
                 type:"post",  
                 beforeSend: function(){  
                     $.messager.progress({  
                            title: 'Please waiting',  
                            msg: '导出中...',  
                            interval:0 ,  
                    });  
                     progress();  
                 },  
                 complete: function(){  
                         $.messager.progress('close');  
                 },  
                 success: function (resp) {  
                     var iframe = document.createElement("iframe");  
                     iframe.src = "${CONTEXT_PATH}/upload/excel/a7usedgarage/"+resp.message;  
                     iframe.style.display = "none";  
                     document.body.appendChild(iframe);  
                 },  
         });  
    }   

---

ps：用到的几个包

jxl-2.6.10.jar

freemarker-2.3.18.jar

freemarker-util-0.0.1-SNAPSHOT.jar freemarker工具相关包

jxl-report-1.0.jar jxl模板导出相关包