jQuery JSON JAVA 导出EXCEL
用的插件其实是POI,具体看import引入的相关类
涉及到的JAVA文件
ExportServlet.java
ExcelUtil.JAVA
SqlDef.JAVA
UniversalAppManagerImpl.java
UniversalAppDaoHibernate.java
JqGridHandler.java
SearchRule.java
FilterSearch.java
JsonUtils.java
有耐心的朋友慢慢看...
因篇幅过长,分成了好几篇文章...共11篇
HTML页面
用到的JS
<script src="../js/jquery.min.js" type="text/javascript"></script>
<script src="../js/json2.js" type="text/javascript"></script>
<script src="../js/jquery-ui-1.7.2.custom.min.js" type="text/javascript"></script>
<script src="../js/grid.locale-cn.js" type="text/javascript"></script>
<script src="../js/jquery.jqGrid.min.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="../css/jquery-ui-1.7.2.custom.css" />
<link rel="stylesheet" type="text/css" href="../css/ui.multiselect.css" />
<link rel="stylesheet" type="text/css" href="../css/ui.jqgrid.css" />
<div id="tabs-1">
<div id="pager"></div>
<table id="list"></table>
</div>
//关键部门JS代码
//注意colModel中加了个title属性,用来在导出成EXCEL的标题时会用到.
var PREPARE_URL = "http://" + location.hostname + ":" + location.port + '/slsupweb';
var mygrid = jQuery("#list").jqGrid({
url : PREPARE_URL + '/BasItemListServlet?'+ Math.random(),
datatype : "json",
colNames : ['ID', '编码', '品名', '规格型号', '计量单位', '集采日期', '状态', '限制入库', '价格'],
colModel : [{
name : 'ID',
index : 'ID',
hidden : true,
width : 50,
searchoptions : {
sopt : ['eq', 'ne', 'lt', 'le', 'gt', 'ge']
}
}, {
name : 'BI_CODE',
index : 'BI_CODE',
title:'编码',
width : 40,
align : "left"
}, {
name : 'BI_CN',
index : 'BI_CN',
title : '品名',
width : 80,
align : "left"
}, {
name : 'BI_SPEC',
index : 'BI_SPEC',
title : '规格型号',
width : 40,
align : "left"
},{
name : 'BI_UNIT',
index : 'BI_UNIT',
title : '计量单位',
width : 25,
align : "left"
},{
name : 'BI_GATHER_DATE',
index : 'BI_GATHER_DATE',
title : '集采日期',
formatter : "date",
width : 40,
align : "left"
}, {
name : 'BI_STATUS_C',
index : 'BI_STATUS_C',
title :'状态',
width : 25,
formatter:'select',
formatoptions:{value:{draft:'草稿',official:'正式'}},
stype:'select',
editoptions:{value:{draft:'草稿',official:'正式'},size:32},
searchoptions:{sopt : ['eq']},
align : "left"
}, {
name : 'BI_NOT_IN',
index : 'BI_NOT_IN',
width : 25,
title : '限制入库',
formatter:'select',
formatoptions:{value:{0:'否',1:'是'}},
stype:'select',
editoptions:{value:{0:'否',1:'是'},size:32},
searchoptions:{sopt : ['eq']},
align : "left"
},{
name : 'BI_PRICE',
index : 'BI_PRICE',
width : 25,
title : '价格',
formatter:"currency",
align : "right"
}],
rowNum : 15,
rowList : [15, 25, 30],
pager : '#pager',
sortname : 'BI_CODE',
viewrecords : true,
sortable : true,
autowidth:true,
sortorder : "asc",
caption : "集采物品列表",
multiselect : true,
rownumbers : true,
rownumWidth : 20,
editurl : "../BasItemEditServlet",
jsonReader : {
id : 'ID',
repeatitems : false
},
height : 390
});
//JQGRID,你懂的
//导出按钮
jQuery("#list").jqGrid('navButtonAdd', '#pager',
{
caption : "excel",
title : "导出xls",
buttonicon : "ui-icon-calculator",
onClickButton : function(e)
{
var $params = this.p.postData;
var col = $.grep(jQuery("#list").getGridParam('colModel'),function(n,is){
return n["hidden"]!=true;
});
$params.repDef = encodeURI(JSON2.stringify(col));
$params.rptCode = 'basitemlist';//传递一个编码
var form = $('<form action="/slsupweb/ExportServlet" method="post"></form>');//调用一个SERVLET
form.append('<input type="text" name="_search" value="'+$params._search+'"/>');
form.append('<input type="text" name="nd" value="'+$params.nd+'"/>');
form.append('<input type="text" name="oper" value="excel"/>');
form.append('<input type="text" name="page" value="'+$params.page+'"/>');
form.append("<input type='text' name='repDef' value='"+$params.repDef+"'/>");
form.append('<input type="text" name="rows" value="'+$params.rows+'"/>');
form.append('<input type="text" name="rptCode" value="'+$params.rptCode+'"/>');
form.append('<input type="text" name="sidx" value="'+$params.sidx+'"/>');
form.append('<input type="text" name="sord" value="'+$params.sord+'"/>');
form.append('<input type="text" name="filters" value="'+$params.filters+'"/>');
form.appendTo("body");
form.css('display','none');
form.submit();
}
});
});
//ExportServlet.JAVA的主要方法
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLDecoder;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.efuture.zhpt.basic.service.UniversalAppManager;
import com.efuture.zhpt.basic.util.ExcelUtil;
import com.efuture.zhpt.basic.util.SqlDef;
import com.efuture.zhpt.basic.util.jqgrid.JqGridHandler;
import com.google.gson.JsonArray;
import com.google.gson.JsonParser;
public void doPost(HttpServletRequest request,HttpServletResponse response)
{
ExcelUtil excelUtil = new ExcelUtil();
SqlDef sqlDef = SqlDef.getInstance();
UniversalAppManager universalAppManager = ( UniversalAppManager )getSpringContext().getBean( "universalAppManager" );//获取spring配置文件中的bean
JsonParser jsonParser = new JsonParser();
JsonArray cols = null;
try
{
OutputStream outputStream = response.getOutputStream();
String oper = request.getParameter("oper");
String rptDef = URLDecoder.decode( request.getParameter("repDef"), "UTF-8" );
if( rptDef != null && rptDef.length()>0 )
{
cols = jsonParser.parse(rptDef).getAsJsonArray();
}
String rptCode = request.getParameter( "rptCode" );
String sql = sqlDef.getSqls( rptCode );//获取MAP对象中的SQL字符串
JqGridHandler handler = new JqGridHandler( request );
String order = handler.getOrders( false );
String condition = handler.getWheres( true );
sql = sql + condition + order;
if( oper != null && oper.length() > 0 && oper.equals( "excel" ) )
{
String filename = rptCode + ".xls";
response.setContentType("application/excel");
response.setHeader( "Content-disposition", "attachment;filename=" + filename );
response.setHeader( "Cache-Control", "private" );
response.setHeader( "Connection", "close" );
excelUtil.export( outputStream, cols, sql, universalAppManager );
}
outputStream.close();
}
catch( IOException ioe )
{
ioe.printStackTrace();
}
}
//JqGridHandler.JAVA
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
public class JqGridHandler
{
private HttpServletRequest request = null;
private String _search = "false";
private String searchField;
private String searchOper;
private String searchString;
private String filters;
private String sidx = "1";
private String sord = "desc";
// 存储总体的search
FilterSearch filterSearch = null;
//GET,SET方法
public JqGridHandler( HttpServletRequest request )
{
this.request = request;
}
/**
* 初始化查询条件
*
*/
private void init()
{
if ( request != null )
{
_search = request.getParameter( "_search" );
searchOper = request.getParameter( "searchOper" );
searchString = request.getParameter( "searchString" );
searchField = request.getParameter( "searchField" );
filters = request.getParameter( "filters" );
try
{
if ( filters != null )
{
filters = URLDecoder.decode( filters, "UTF-8" );
}
}
catch ( UnsupportedEncodingException e )
{
e.printStackTrace();
}
sidx = request.getParameter( "sidx" );
sord = request.getParameter( "sord" );
}
}
}
//JqGridHandler.JAVA的其它方法
/**
* 装载查询条件
*
*/
private void conditions()
{
// 初始化，如果request为空，说明是从set进来的。
init();
// 分拆，全部写入filersearch
if ( null != _search && "true".equalsIgnoreCase( _search ) )
{
// 先写多选择的，一般有多选择就不会有单选择。
if ( null != filters && filters.length() > 0 )
{
Map map = new HashMap();
map.put( "rules", SearchRule.class );
filterSearch = (FilterSearch) JsonUtils.getDTOList( filters, FilterSearch.class, map );
}
else
{
if ( null != searchOper && null != searchString && null != searchField )
{
SearchRule rule = new SearchRule();
rule.setData( searchString );
rule.setOp( searchOper );
rule.setField( doTables( searchField ) );
filterSearch = new FilterSearch();
filterSearch.setGroupOp( null );
List rules = new ArrayList();
rules.add( rule );
filterSearch.setRules( rules );
}
}
}
}
/**
* 获取排序条件的SQL语句
* @param isOrder 是否已经包含order
* @return 排序条件的SQL语句
*/
public String getOrders( boolean isOrder )
{
init();
StringBuffer stringBuffer = new StringBuffer( "" );
if( !isOrder )
{
stringBuffer.append( " order by " );
}
return stringBuffer.append( doTables( sidx ) ).append( " " ).append( sord ).toString();
}
/**
* 获取查询条件的SQL语句
* @param isWhere 是否已经包含where
* @return 查询条件的SQL语句
*/
public String getWheres( boolean isWhere )
{
conditions();
if ( tranToSQL().trim().equals( "" ) )
{
return "";
}
if ( !isWhere )
{
return new StringBuffer( " where " ).append( tranToSQL() ).toString();
}
return new StringBuffer( " and " ).append( tranToSQL() ).toString();
}
//JqGridHandler.JAVA的其它方法
* 根据查询条件转换SQL语句
* @return 转换后的SQL语句
*/
public String tranToSQL()
{
StringBuffer stringBuffer = new StringBuffer("");
if ( null != filterSearch )
{
List rules = filterSearch.getRules();
int count = 0;
if ( null != rules && ( count = rules.size() ) > 0 )
{
for( int i = 0; i < rules.size(); i++ )
{
SearchRule rule = ( SearchRule )rules.get( i );
if ( null != rule.getField() && null != rule.getData() && null != rule.getOp() )
{
if ( "eq".equalsIgnoreCase( rule.getOp() ) )
{
stringBuffer.append( rule.getField()).append(" = ");
isDate(rule,stringBuffer);
}
else if ( "nq".equalsIgnoreCase( rule.getOp() ) )
{
stringBuffer.append( rule.getField() ).append( " != " );
isDate(rule,stringBuffer);
}
else if ( "lt".equalsIgnoreCase( rule.getOp() ) )
{
stringBuffer.append( rule.getField() ).append(" < ");
isDate(rule,stringBuffer);
}
else if ( "le".equalsIgnoreCase( rule.getOp() ) )
{
stringBuffer.append( rule.getField() ).append( " <= " );
isDate(rule,stringBuffer);
}
else if ( "gt".equalsIgnoreCase( rule.getOp() ) )
{
stringBuffer.append( rule.getField() ).append( " > " );
isDate(rule,stringBuffer);
}
else if ( "ge".equalsIgnoreCase( rule.getOp() ) )
{
stringBuffer.append( rule.getField() ).append( " >= " );
isDate(rule,stringBuffer);
}
else if ( "bw".equalsIgnoreCase( rule.getOp() ) )
{
stringBuffer.append( rule.getField() ).append( " like " ).append( "'" ).append( rule.getData() ).append( "%" ).append( "'" );
}
else if ( "ew".equalsIgnoreCase( rule.getOp() ) )
{
stringBuffer.append( rule.getField()).append( " like " ).append( "'" ).append( "%" ).append( rule.getData() ).append( "'" );
}
else if ( "cn".equalsIgnoreCase( rule.getOp() ) )
{
stringBuffer.append( rule.getField() ).append( " like " ).append( "'" ).append( "%" ).append( rule.getData() ).append( "%" ).append( "'" );
}
count--;
if ( count > 0 )
{
if ( null != filterSearch.getGroupOp() )
{
if ( filterSearch.getGroupOp().trim().equalsIgnoreCase( "and" ) )
{
stringBuffer.append(" and ");
}
else
{
stringBuffer.append(" or ");
}
}
}
}
}
}
}
return stringBuffer.toString();
}
//FilterSearch.java
import java.util.List;
public class FilterSearch
{
private String groupOp; //多字段查询时分组类型，主要是AND或者OR
private List rules; //多字段查询时候，查询条件的集合
public String getGroupOp() {
return groupOp;
}
public void setGroupOp(String groupOp) {
this.groupOp = groupOp;
}
public List getRules() {
return rules;
}
public void setRules(List rules) {
this.rules = rules;
}
}
// SearchRule.JAVA
public class SearchRule {
private String field; //查询字段
private String op; //查询操作
private String data; //选择的查询值
public String getField() {
return field;
}
public void setField(String field) {
this.field = field;
}
public String getOp() {
return op;
}
public void setOp(String op) {
this.op = op;
}
public String getData() {
return data;
}
public void setData(String data) {
this.data = data;
}
}
