Java基于jxl与模板导出excel并弹出下载
- 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();
- }
- }
然后同理,连接数据库操作类似,就是数据的获取与处理略为不同
- /**
- * 根据模型名称动态导出对应数据库表数据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;
- }
- 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模板导出相关包