报表 导入 导出通用类

package com.tempus.modules.orm.hibernate;


import java.io.OutputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import javax.servlet.http.HttpServletResponse;


import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


import org.directwebremoting.annotations.RemoteMethod;
import org.directwebremoting.annotations.RemoteProxy;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.annotation.Transactional;


import com.tempus.modules.dwr.util.Condition;
import com.tempus.modules.dwr.util.DWRUtil;
import com.tempus.modules.dwr.util.DwrBackParams;
import com.tempus.modules.orm.Page;
import com.tempus.modules.orm.PropertyFilter;
import com.tempus.modules.orm.PropertyFilter.MatchType;
import com.tempus.userCenter.utils.DAOUtils;


/**
 * 领域对象业务管理类基类.
 * 
 * @param <T>
 *            领域对象类型
 * @param <PK>
 *            领域对象的主键类型
 * 
 * 
 * eg. public class UserManager extends EntityManager<User, Long>{ }
 * 
 * @author andy
 */
@RemoteProxy
@Transactional
public abstract class EntityManager<T, PK extends Serializable> {


protected Logger logger = LoggerFactory.getLogger(getClass());


@RemoteMethod
protected abstract HibernateDao<T, PK> getEntityDao();


// CRUD函数 //
@RemoteMethod
@Transactional(readOnly = true)
public T get(final PK id) {
return getEntityDao().get(id);
}
@Transactional(readOnly = true)
public T realGet(final PK id) {
return getEntityDao().realget(id);
}


@RemoteMethod
@Transactional(readOnly = true)
public Page<T> getAll(final Page<T> page) {
return getEntityDao().getAll(page);
}


@RemoteMethod
@Transactional(readOnly = true)
public List<T> getAll() {
return getEntityDao().getAll();
}


@RemoteMethod
@Transactional(readOnly = true)
public Page<T> search(final Page<T> page, final List<PropertyFilter> filters) {
return getEntityDao().find(page, filters);
}


@RemoteMethod
@Transactional(readOnly = true)
public List<T> search(final List<PropertyFilter> filters) {
return getEntityDao().find(filters);
}


@RemoteMethod
@Transactional(readOnly = true)
public Page<T> searchPage(final Page<T> page,
final List<PropertyFilter> filters) {
return getEntityDao().findPage(page, filters);
}


/**
* 单表的保存(保存与修改)
* 
* @param entity
*/


@RemoteMethod
public void saveOrUpdate(final T entity) {
getEntityDao().saveOrUpdate(entity);
}


/**
* 主从表修改，采取先删除后插入的方式。避免事务问题(主从表时，保存与修改分开，但单表时，采用saveOrupdate方法)
* 
* @param id
* @param entity
*/
@RemoteMethod
public void update(final PK id, final T entity) {
// getEntityDao().delete(id);
getEntityDao().update(entity);
}


/**
* 主从表新增(主从表时保存方法,单表采取saveOrUpdate)
* 
* @param entity
*/
@RemoteMethod
public void save(final T entity) {
getEntityDao().save(entity);
}


/**
* merge方法,更新(暂未用)
* 
* @param entity
*/
@RemoteMethod
public void merge(final T entity) {
getEntityDao().update(entity);
}




@RemoteMethod
public void delete(final PK id) {
getEntityDao().delete(id);
}


/**
* 用于DWR分页过滤查询
* 
* @param params
*            DWR回传参数
* @return
*/
@RemoteMethod
@Transactional(readOnly = true)
public Page<T> dwrListPage(DwrBackParams params) {
Page<T> page = new Page<T>();
try {
int pageSize = params.getLimit();
int currentPage = params.getStart() / pageSize + 1;
int queryflag = params.getQueryflag();
String orderby = params.getOrderby();
String orderDirection = params.getOrderdirection();

if (queryflag == 1) {
List<Condition> conditions = params.getConditions();
List<PropertyFilter> filters = DWRUtil
.buildPropertyFilters(conditions);
Page<T> argPage = new Page<T>(currentPage, pageSize);
if (orderby != null) {
argPage.setOrderBy(orderby);// 设置排序参数
argPage.setOrder(orderDirection);// 设置排序方向 降序或者降序
}
page = searchPage(argPage, filters);
}
} catch (Exception e) {
logger.error("dwrListPage列表查询出现异常",e);
}
return page;
}


/**
* 用于DWR分页过滤查询--QUERY方式
* 
* @param params
*            DWR回传参数
* @return
*/
@RemoteMethod
@Transactional(readOnly = true)
public Page<T> dwrListPageHQL(DwrBackParams params, String hql) {
Page<T> page = new Page<T>();
try {
int pageSize = params.getLimit();
int currentPage = params.getStart() / pageSize + 1;
int queryflag = params.getQueryflag();
String orderby = params.getOrderby();
String orderDirection = params.getOrderdirection();
if (queryflag == 1) {
List<Condition> conditions = params.getConditions();
List<PropertyFilter> filters = DWRUtil
.buildPropertyFilters(conditions);
Page<T> argPage = new Page<T>(currentPage, pageSize);
if (orderby != null) {
argPage.setOrderBy(orderby);// 设置排序参数
argPage.setOrder(orderDirection);// 设置排序方向 降序或者降序
}
StringBuffer sbHQL = new StringBuffer(hql);
List<Object> values = new ArrayList<Object>();
getEntityDao().buildFilterConditions(filters, sbHQL, values);
page = getEntityDao().findPage(sbHQL.toString(), values.toArray(),
argPage);
}
} catch (Exception e) {
logger.error("dwrListPageHQL列表查询出现异常",e);
} 
return page;
}


/**
* 根据extjs页面条件，hql语句，以及行数限制返回List
* 
* @param conditions
* @param hql
* @param rows
*            -1为不限制
* @return
*/
@RemoteMethod
@Transactional(readOnly = true)
public List<T> dwrListHQL(List<Condition> conditions, String hql, int rows) {
List<T> result = null;
try {
List<PropertyFilter> filters = DWRUtil
.buildPropertyFilters(conditions);
StringBuffer sbHQL = new StringBuffer(hql);
List<Object> values = new ArrayList<Object>();
getEntityDao().buildFilterConditions(filters, sbHQL, values);
if (rows != -1) {
int cnt = getEntityDao().getRowCount(sbHQL.toString(),
values.toArray());
if (cnt > rows)// 如果返回行数超过总行数，返回null
{
return null;
}
}


result = getEntityDao()
.findPage(sbHQL.toString(), values.toArray());
} catch (Exception ex) {
ex.printStackTrace();
}
return result;
}


/**
* 根据extjs页面条件，hql语句，以及行数限制返回List
* 
* @param conditions
* @param hql
* @param rows
*            -1为不限制
* @return
*/
@RemoteMethod
@Transactional(readOnly = true)
public String dwrGetHQL(List<Condition> conditions, String hql) {
List<PropertyFilter> filters = DWRUtil.buildPropertyFilters(conditions);
StringBuffer sbHQL = new StringBuffer(hql);
List<Object> values = new ArrayList<Object>();
getEntityDao().buildFilterConditions(filters, sbHQL, values);
return sbHQL.toString();


}


public T getData(final PK id) {
return getEntityDao().getData(id);
}


public T getDelete(final PK id) {
getEntityDao().delete(id);
return null;
}


public Long findLong(String hql) {
return getEntityDao().findLong(hql);
}


public T upData(final PK id, Long ord, String proid, String machinesid,
String machinesname, String processid, String processname) {
return getEntityDao().getData(id);
}


@RemoteMethod
@Transactional(readOnly = true)
public List getList(String k) throws Exception {
List list = new ArrayList();
return list;
}


public String findString(String hql) {
return getEntityDao().findString(hql);
}


@RemoteMethod
@Transactional(readOnly = true)
public List getListData(String id) throws Exception {
// TODO Auto-generated method stub
return null;
}


/**
* 用于获取hql方法
* 
* @param conditions
* @param hql
*            要的拼hql 例如：from Exchange where type = '1'
* @return
*/
@RemoteMethod
public String getHql(List<Condition> conditions, String hql) {
List<PropertyFilter> filters = DWRUtil.buildPropertyFilters(conditions);

if(hql.toUpperCase().indexOf("WHERE")==-1){
hql += " WHERE 1=1 ";
}
StringBuffer hqlBuf = new StringBuffer(hql);
for (PropertyFilter filter : filters) {
if (MatchType.DATEBETWEEN.equals(filter.getMatchType())) {
DAOUtils du = new DAOUtils();
String datestr = du.addDateBetween(filter.getPropertyName(),
filter.getValue().toString(), filter.getSecondValue()
.toString());
hqlBuf.append(" and " + datestr);


} else if (MatchType.BETWEEN.equals(filter.getMatchType())) {


hqlBuf.append(" and " + filter.getPropertyName() + " >= "
+ filter.getValue());
hqlBuf.append(" and " + filter.getPropertyName() + " <= "
+ filter.getSecondValue());
} else if (MatchType.EQ.equals(filter.getMatchType())) {


if ("long".equals(filter.getType())) {
filter.setValue(Long
.parseLong(filter.getValue().toString()));
}
hqlBuf.append(" and " + filter.getPropertyName() + " = '"
+ filter.getValue() + "'");
} else {
if ("long".equals(filter.getType())) {
filter.setValue(Long
.parseLong(filter.getValue().toString()));
}
hqlBuf.append(" and " + filter.getPropertyName() + " like '%"
+ filter.getValue() + "%'");
}
}
// return hqlBuf.toString().replaceAll("%", "%25");
return hqlBuf.toString();
}
/**
* 导出报表通用方法
* @param response
* @param list
* @param headerStr 表头信息 格式：ticketCode,票号,String;code,编码,String...
*/
public void exprortExcelFromAction(HttpServletResponse response, List<T> list,
String headerStr) {
if(list.isEmpty()||list==null){
return;
}
Class classType = list.get(0).getClass();


String[] headers = headerStr.split(";");
String[] header=null; 
int i = 0;
Label label=null;
OutputStream os = null;
WritableWorkbook book = null;
try {
os = response.getOutputStream();
// 取得OutputStream
response.setContentType("APPLICATION/OCTET-STREAM ");
response.setContentType("application/x-msdownload;charset=utf-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename="
+ com.tempus.userCenter.utils.DateUtils
.formatDate(new Date()) + ".xls");
book = Workbook.createWorkbook(os);
WritableSheet sheet = book.createSheet("第一页", 0);


WritableFont wf1 = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.DARK_RED);
WritableCellFormat wc1 = new WritableCellFormat(wf1);
wc1.setBorder(jxl.format.Border.ALL,
jxl.format.BorderLineStyle.THIN);
wc1.setBackground(jxl.format.Colour.GREEN);
wc1.setAlignment(Alignment.CENTRE);

WritableFont wf2 = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.AUTOMATIC);
WritableCellFormat wc2 = new WritableCellFormat(wf2);
wc2.setBorder(jxl.format.Border.ALL,
jxl.format.BorderLineStyle.THIN);

wc2.setAlignment(Alignment.CENTRE);

/*label = new Label(0, 0, "序号", wc1);
sheet.setColumnView(0, 5);
sheet.addCell(label);*/

for(int j=0;j<headers.length;j++){
header = headers[j].split(",");
//label = new Label(j+1, 0, header[1], wc1);
label = new Label(j, 0, header[1], wc1);
//sheet.setColumnView(j+1, 20);
sheet.setColumnView(j, 20);
sheet.addCell(label);
}
for (T obj : list) {
/* label = new Label(0, i, String.valueOf(i+1));
sheet.setColumnView(i, 5);
sheet.addCell(label);*/
for(int j=0;j<headers.length;j++){
exportTool(headers, sheet, wc2, i, j, obj, classType);
}
// if(i%1000==0){
// book.write();
// }
i++;
}
book.write();
} catch (Exception e) {
// TODO Auto-generated catch block
logger.error("导出发生异常exprortExcelFromAction",e);
}finally{
try {
if (book != null)
book.close();
if (os != null)
os.close();
} catch (Exception ex) {
logger.info(ex.getMessage());
}
}


}

/**
* 导出报表工具
*/
protected void exportTool(String[] headers,WritableSheet sheet,WritableCellFormat wc,int i,int j,T obj,Class classType){
try{
Label label=null;
String[] header = headers[j].split(",");
if(header[2].equals("String")){
if(header[0].indexOf(".")!=-1){
String header1 = header[0].substring(0,header[0].indexOf("."));
String header2 = header[0].substring(header[0].indexOf(".")+1);
Field field1 = classType.getDeclaredField(header1);
field1.setAccessible(true);
       Object retvalue1=field1.get(obj);
       if(retvalue1 != null){
       Class c = retvalue1.getClass(); 
       Field field2 = c.getDeclaredField(header2);
field2.setAccessible(true);
       Object retvalue2=field2.get(retvalue1);
       label = new Label(j, i+1, retvalue2==null?"":retvalue2.toString());
sheet.setColumnView(j, 20);
sheet.addCell(label);
       }
}else{
Field field = classType.getDeclaredField(header[0]);
field.setAccessible(true);
       Object retvalue=field.get(obj);
label = new Label(j, i+1, retvalue==null?"":retvalue.toString());
sheet.setColumnView(j, 20);
sheet.addCell(label);
}
}else if(header[2].equals("Date")){
if(header[0].indexOf(".")!=-1){
String header1 = header[0].substring(0,header[0].indexOf("."));
String header2 = header[0].substring(header[0].indexOf(".")+1);
Field field1 = classType.getDeclaredField(header1);
field1.setAccessible(true);
       Object retvalue1=field1.get(obj);
       if(retvalue1 != null){
       Class c = retvalue1.getClass(); 
       Field field2 = c.getDeclaredField(header2);
field2.setAccessible(true);
       Object retvalue2=field2.get(retvalue1);
       label = new Label(j, i+1, retvalue2==null?"":retvalue2.toString().substring(0,retvalue2.toString().indexOf(".")));
sheet.setColumnView(j, 20);
sheet.addCell(label);
       }
}else{
Field field = classType.getDeclaredField(header[0]);
field.setAccessible(true);
       Object retvalue=field.get(obj);
label = new Label(j, i+1, retvalue==null?"":retvalue.toString());
sheet.setColumnView(j, 20);
sheet.addCell(label);
}
}else if(header[2].equals("renderer")){
if(header[0].indexOf(".")!=-1){
String header1 = header[0].substring(0,header[0].indexOf("."));
String header2 = header[0].substring(header[0].indexOf(".")+1);
Field field1 = classType.getDeclaredField(header1);
field1.setAccessible(true);
       Object retvalue1=field1.get(obj);
       if(retvalue1 != null){
       Class c = retvalue1.getClass(); 
       Field field2 = c.getDeclaredField(header2);
field2.setAccessible(true);
       Object retvalue2=field2.get(retvalue1);
       label = new Label(j, i+1,retvalue2==null?"":renderer(retvalue2.toString()) );
sheet.setColumnView(j, 20);
sheet.addCell(label);
       }
}else{
Field field = classType.getDeclaredField(header[0]);
field.setAccessible(true);
       Object retvalue=field.get(obj);
label = new Label(j, i+1, retvalue==null?"":renderer(retvalue.toString()) );
sheet.setColumnView(j, 20);
sheet.addCell(label);
}
}else if(header[2].equals("renderer1")){
if(header[0].indexOf(".")!=-1){
String header1 = header[0].substring(0,header[0].indexOf("."));
String header2 = header[0].substring(header[0].indexOf(".")+1);
Field field1 = classType.getDeclaredField(header1);
field1.setAccessible(true);
       Object retvalue1=field1.get(obj);
       if(retvalue1 != null){
       Class c = retvalue1.getClass(); 
       Field field2 = c.getDeclaredField(header2);
field2.setAccessible(true);
       Object retvalue2=field2.get(retvalue1);
       label = new Label(j, i+1,retvalue2==null?"":renderer1(retvalue2.toString()) );
sheet.setColumnView(j, 20);
sheet.addCell(label);
       }
}else{
Field field = classType.getDeclaredField(header[0]);
field.setAccessible(true);
       Object retvalue=field.get(obj);
label = new Label(j, i+1, retvalue==null?"":renderer1(retvalue.toString()));
sheet.setColumnView(j, 20);
sheet.addCell(label);
}
}else if(header[2].equals("renderer2")){
if(header[0].indexOf(".")!=-1){
String header1 = header[0].substring(0,header[0].indexOf("."));
String header2 = header[0].substring(header[0].indexOf(".")+1);
Field field1 = classType.getDeclaredField(header1);
field1.setAccessible(true);
       Object retvalue1=field1.get(obj);
       if(retvalue1 != null){
       Class c = retvalue1.getClass(); 
       Field field2 = c.getDeclaredField(header2);
field2.setAccessible(true);
       Object retvalue2=field2.get(retvalue1);
       label = new Label(j, i+1,retvalue2==null?"":renderer2(retvalue2.toString()) );
sheet.setColumnView(j, 20);
sheet.addCell(label);
       }
}else{
Field field = classType.getDeclaredField(header[0]);
field.setAccessible(true);
       Object retvalue=field.get(obj);
label = new Label(j, i+1, retvalue==null?"":renderer2(retvalue.toString()) );
sheet.setColumnView(j, 20);
sheet.addCell(label);
}
}else if(header[2].equals("renderer3")){
if(header[0].indexOf(".")!=-1){
String header1 = header[0].substring(0,header[0].indexOf("."));
String header2 = header[0].substring(header[0].indexOf(".")+1);
Field field1 = classType.getDeclaredField(header1);
field1.setAccessible(true);
       Object retvalue1=field1.get(obj);
       if(retvalue1 != null){
       Class c = retvalue1.getClass(); 
       Field field2 = c.getDeclaredField(header2);
field2.setAccessible(true);
       Object retvalue3=field2.get(retvalue1);
       label = new Label(j, i+1,retvalue3==null?"":renderer3(retvalue3.toString()) );
sheet.setColumnView(j, 20);
sheet.addCell(label);
       }
}else{
Field field = classType.getDeclaredField(header[0]);
field.setAccessible(true);
       Object retvalue=field.get(obj);
label = new Label(j, i+1, retvalue==null?"":renderer3(retvalue.toString()) );
sheet.setColumnView(j, 20);
sheet.addCell(label);
}
}else if (header[2].equals("sequence")){
label = new Label(j, i+1,  i+1+"");
sheet.setColumnView(j, 10);
sheet.addCell(label);
}else if(header[2].toUpperCase().equals("LIST")){
String header1 = header[0].substring(0,header[0].indexOf("."));
String header2 = header[0].substring(header[0].indexOf(".")+1);
Field field = classType.getDeclaredField(header1);
field.setAccessible(true);
       Object retvalue=field.get(obj);
       List<Object> list = (List<Object>) retvalue;
       StringBuffer sb = new StringBuffer();
       for(Object o:list){
        Field subField = o.getClass().getDeclaredField(header2);
        subField.setAccessible(true);
        Object retvalue2=subField.get(o);
        sb.append(retvalue2);
        sb.append("|");
       }
       label = new Label(j, i+1, sb==null?"":sb.toString().substring(0,sb.toString().length()-1) );
sheet.setColumnView(j, 20);
sheet.addCell(label);
}
}catch(Exception ex)
{
logger.error("导出出现异常!",ex);
}
}
protected String renderer(String str){
return null;
}
protected String renderer1(String str){
return null;
}
protected String renderer2(String str){
return null;
}
protected String renderer3(String str){
return null;
}

public int getRowCnt(String hql) {
int totalCnt = getEntityDao().getRowCount(hql).intValue();  
return totalCnt;
}
public List<T> findByHqlExport(String hql) { 
return getEntityDao().find(hql);
}





}