平台表格导出指定数据库连接的数据

当平台表格数据绑定指定连接数据库的sqlMap的时候，使用平台自带的gridtoExcel构件无法导出数据，会连接到默认的数据库。
如图，绑定了一个指定的数据库zentao。

图片：1.png

解决办法：
重写平台导出servlet,创建指定数据库的session，代码如下：
（修改的代码只有这一句： DBSession session = DataBaseUtil.getHibernateSession("zentao");）


import com.sunsheen.jfids.commons.io.IOUtils;
import com.sunsheen.jfids.gson.Gson;
import com.sunsheen.jfids.gson.reflect.TypeToken;
import com.sunsheen.jfids.system.base.composite.data.query.QueryParameterImpl;
import com.sunsheen.jfids.system.database.DBSession;
import com.sunsheen.jfids.system.persistence.ISession;
import com.sunsheen.jfids.system.servlet.Servlet;
import com.sunsheen.jfids.util.DataBaseUtil;
import com.sunsheen.jfids.util.IdsDate;
import com.sunsheen.jfids.util.StringUtil;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;

@SuppressWarnings("serial")
@Servlet(value="/data/gridWeekToExcel.svt",anonymous=true)
public class WeekTaskServlet  extends HttpServlet
{
 private static final long serialVersionUID = 1L;
 private ArrayList<Map> jsoncolums;
 private int columsnum;
 private String title;
 private Query query;
 private HSSFWorkbook book;
 private HSSFSheet sheet;
 private HSSFRow row;
 private HSSFCell cell;
 private HSSFCellStyle style;
 private HSSFFont font;
 
 public String getStringFromInputStream(InputStream is)
   throws IOException
 {
   BufferedReader br = new BufferedReader(new InputStreamReader(
     new BufferedInputStream(new BufferedInputStream(is))));
   StringBuffer sb = new StringBuffer();
   try {
     String line = "";
     while ((line = br.readLine()) != null) {
       sb.append(line).append("\n");
     }
   } catch (IOException e) {
     e.printStackTrace();
   }
   return sb.toString();
 }
 
 protected void doGet(HttpServletRequest req, HttpServletResponse resp)
   throws ServletException
 {
   InputStream is = null;
   try {
     this.title = URLDecoder.decode(req.getParameter("title"), "UTF-8");
     this.title = ((this.title == null) || ("".equals(this.title)) ? new SimpleDateFormat(
       "yyyy-MM-dd").format(new Date()) : this.title);
     resp.setContentType("application/vnd.ms-excel");
     resp.setCharacterEncoding("UTF-8");
     resp.setHeader("Content-disposition", "attachment; filename=" +
       URLEncoder.encode(this.title, "UTF-8") + ".xls");
     
     is = getExcel(req);
    // resp.setHeader("Content-length", is.available());
     IOUtils.copy(is, resp.getOutputStream());
   } catch (IOException e) {
     e.printStackTrace();
     try
     {
       is.close();
       resp.getOutputStream().close();
     } catch (Exception e1) {
       e1.printStackTrace();
     }
   }
   finally
   {
     try
     {
       is.close();
       resp.getOutputStream().close();
     } catch (Exception e) {
       e.printStackTrace();
     }
   }
 }
 
 protected void doPost(HttpServletRequest req, HttpServletResponse resp)
   throws ServletException, IOException
 {
   doGet(req, resp);
 }
 
 @SuppressWarnings("rawtypes")
private InputStream getExcel(HttpServletRequest req) throws IOException {
   StringBuilder columsBuilder = new StringBuilder();
   String baseparams = URLDecoder.decode(req.getParameter("baseparams"),
     "UTF-8");
   String dataurl = URLDecoder.decode(req.getParameter("dataurl"),
     "UTF-8");
   columsBuilder.append(URLDecoder.decode(req.getParameter("column"),
     "UTF-8"));
   Gson gson = new Gson();
   this.jsoncolums = gson.fromJson(columsBuilder.toString(), ArrayList.class);
  /* this.jsoncolums = ((ArrayList<Map>)StringUtil.gson.fromJson(columsBuilder.toString(),
    new TypeToken(){}.getType()));*/
   
   for (Map column : this.jsoncolums) {
    //System.out.println(column);
    /* if (!((Boolean)column.get("export_")).booleanValue()) {
       this.jsoncolums.remove(column);
     }*/
   }
   
   HashMap hm2 = (HashMap)StringUtil.gson.fromJson(baseparams, HashMap.class);
   Object hm = new HashMap();
   for (String s : dataurl.substring(dataurl.indexOf('?') + 1).split("&")) {
     int length = s.split("=").length;
     if (length > 0)
       ((HashMap)hm).put(s.split("=")[0], length > 1 ? s.split("=")[1] : "");
   }
   this.columsnum = this.jsoncolums.size();
   ((HashMap)hm).putAll(hm2);
   for (Object key : ((HashMap)hm).keySet()) {
     Object aObject = ((HashMap)hm).get(key);
     if ((aObject instanceof String)) {
       ((HashMap)hm).put(key, convertDateStringToDate((String)aObject));
     }
   }
   
   DBSession session = DataBaseUtil.getHibernateSession("zentao");
   session.beginTransaction();
   SQLQuery querytrad = session.createDySQLQuery((String)((HashMap)hm).get("sqlid"),
     (Map)hm);
   this.query = new QueryParameterImpl().initParameter(querytrad, (Map)hm);
   session.commit();
   
   initExcel();
   
   initFirstRowExcel();
   
   initOrtherRowExcel();
   return exportExcel();
 }
 
 private static Object convertDateStringToDate(String dateStr) {
   if (dateStr == null) {
     return null;
   }
   Map<String, Object> ret = new HashMap();
   
   Pattern datePattern =
     Pattern.compile("^((\\d{2}(([02468][048])|([13579][26]))[\\-\\/\\s]((((0?[13578])|(1[02]))[\\-\\/\\s]((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-\\/\\s]((((0?[13578])|(1[02]))[\\-\\/\\s]((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))(T(((0?[0-9])|([1-2][0-3]))\\:([0-5]?[0-9])((\\s)|(\\:([0-5]?[0-9])))))?$");
   
   Pattern arrayPattern = Pattern.compile("^\\s*\\[([\\w\\W]*)\\]\\s*$");
   
   if (datePattern.matcher(dateStr).matches()) {
     try {
       if (dateStr.length() <= 10)
       {
         return new IdsDate(
           new SimpleDateFormat("yyyy-MM-dd").parse(dateStr),
           "yyyy-MM-dd");
       }
       return new IdsDate(new SimpleDateFormat(
         "yyyy-MM-dd'T'HH:mm:ss").parse(dateStr),
         "yyyy-MM-dd HH:mm:ss");
     }
     catch (ParseException e1) {
       return null;
     }
   }
   return dateStr;
 }
 
 private void initExcel()
 {
   this.book = new HSSFWorkbook();
   
   this.sheet = this.book.createSheet(this.title);
   sheet.setDefaultColumnWidth((short) 15);
   this.row = this.sheet.createRow(0);
   this.row.setHeight((short)600);
   
   this.style = this.book.createCellStyle();
   this.style.setVerticalAlignment((short)1);
   this.style.setAlignment((short)2);
   this.style.setBorderBottom((short)1);
   this.style.setBorderLeft((short)1);
   this.style.setBorderRight((short)1);
   this.style.setBorderTop((short)1);
   
   this.font = this.book.createFont();
   this.font.setBoldweight((short)700);
   this.font.setFontHeight((short)(this.font.getFontHeight() * 2));
   this.style.setFont(this.font);
   
   for (int i = 0; i < this.columsnum; i++) {
     this.cell = this.row.createCell(i);
     this.cell.setCellStyle(this.style);
     this.cell.setCellValue(this.title);
   }
   
   this.sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, this.columsnum - 1));
 }
 
 private void initFirstRowExcel()
 {
   this.style = this.book.createCellStyle();
   this.style.setVerticalAlignment((short)1);
   this.style.setAlignment((short)1);
   this.style.setBorderBottom((short)1);
   this.style.setBorderLeft((short)1);
   this.style.setBorderRight((short)1);
   this.style.setBorderTop((short)1);
   this.font = this.book.createFont();
   this.font.setBoldweight((short)700);
   this.style.setFont(this.font);
   
   this.row = this.sheet.createRow(1);
   
   this.row.setHeight((short)300);
 }
 
 private void initOrtherRowExcel()
 {
   this.style = this.book.createCellStyle();
   this.style.setVerticalAlignment((short)1);
   this.style.setAlignment((short)1);
   this.style.setBorderBottom((short)1);
   this.style.setBorderLeft((short)1);
   this.style.setBorderRight((short)1);
   this.style.setBorderTop((short)1);
   
   this.query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
   List<Map<String, Object>> ls = this.query.list();
   
   /**
    * 添加一行标题
    */
   //this.sheet.autoSizeColumn((short)15);
   HSSFRow row = sheet.createRow(1);
for (short i = 0; i < this.jsoncolums.size(); i++) {
HSSFCell cell = row.createCell(i);
//cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(this.jsoncolums.get(i).get("header").toString());
cell.setCellValue(text);
cell.setCellStyle(this.style);
}
   
   
   for (int i = 0; i < ls.size(); i++) {
     this.row = this.sheet.createRow(i + 2);
     Map<String, Object> ob = (Map)ls.get(i);
     for (int j = 0; j < this.columsnum; j++) {
       this.cell = this.row.createCell(j);
       String dataIndex = (String)((Map)this.jsoncolums.get(j)).get("dataIndex");
       Map columConfig = (Map)this.jsoncolums.get(j);
       if (ob.get(dataIndex) != null) {
         if (columConfig.get("dateFormat") != null) {
           String dateFormat =
             ((String)columConfig.get("dateFormat")).replace("Y", "yyyy")
             .replace("m", "MM").replace("d", "dd")
             .replace("H", "hh").replace("i", "mm")
             .replace("s", "ss");
           SimpleDateFormat sdFormat = new SimpleDateFormat(
             dateFormat);
           this.cell.setCellValue(sdFormat.format(ob.get(dataIndex)));
         } else if (columConfig.get("numberFormat") != null) {
           DecimalFormat df2 = new DecimalFormat(
             (String)columConfig.get("numberFormat"));
           this.cell.setCellValue(df2.format(ob.get(dataIndex)));
         } else {
           this.cell.setCellValue(ob.get(dataIndex) == null ? "" : ob
             .get(dataIndex).toString());
         }
       } else {
         this.cell.setCellValue("");
       }
       this.cell.setCellStyle(this.style);
     }
   }
 }
 
 private InputStream exportExcel() throws IOException {
   ByteArrayOutputStream bos = new ByteArrayOutputStream();
   this.book.write(bos);
   ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
   bos.close();
   return bis;
 }
}



前端逻辑流定义一个post方式访问接口的构件，放一个自定义构件，设置两个参数，如图：

图片：2.png

自定义构件里代码：


var temp = document.createElement("form");
    temp.action = url;
    temp.method = "post";
    temp.style.display = "none";

    for (var x in params) {
        var opt = document.createElement("textarea");
        opt.name = x;
        opt.value = params[x];
        temp.appendChild(opt);
    }
    document.body.appendChild(temp);
    temp.submit();
    return temp;

在表格导出按钮事件里，组装数据，调用接口，实现下载。

组装的数据，定义一个json：




var columns = $$("gridWeek").colModel.columns;
columns.splice(0, 1);
var json = {
dataurl:"./data/GridData.svt?sqlid=ZenTaoSelect.selectTaskByProject",
column:encodeURIComponent(JSON.stringify(columns)),
title:encodeURIComponent(project_name+"_"+time.split('-')[0]+"年"+time.split('-')[1]+"月周计划信息"),
baseparams:encodeURIComponent(JSON.stringify(data))
};




gridWeek表示表格的ID，dataurl表示表格绑定的数据源，column表示表格的列，title，导出的文件标题，

baseparams表示传入的json参数，可作为数据的查询过滤条件

再调用自定义的post方式访问接口的构件，构件配置如下：

图片：3.png

点击页面按钮，可下载指定数据库连接的表格数据。

图片：4.png