Java基于jxl与模板导出excel并弹出下载

来源：互联网发布：苹果手机短信备份软件编辑：程序博客网时间：2024/06/03 23:00

操作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","等等。。");//除了单个字段  还可以存入一个listList<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模板导出相关包

1 1