我们做系统时经常遇到到处excel的需求,比如表格的导出,报表的导出。
表格的导出相对简单点,excel没有特殊要求,就是一行一列一行一列的导出,我们的系统前端表格用jqgrid,所以点击导出时用下面的js重新调用表格数据的加载方法
/**
HZ.expGrid(ggridId,['no'],["id:id"],false);
*/
HZ.expGrid=function(ggridId,filterCols,addCols,currentPage){
var url = jQGridSupport.getParam(ggridId,"url");
var colModel = jQGridSupport.getParam(ggridId,"colModel");
var pstrlist=[];
for(var i=0;i<colModel.length;i++){
if(colModel[i].label && colModel[i].name && colModel[i].name!='edit'){
var add=true;
if(filterCols)//过滤不需要导出的列
for(var j=0;j<filterCols.length;j++){
if(filterCols[j]==colModel[i].name){add=false;break;}
}
if(add)pstrlist.push(colModel[i].name+":"+colModel[i].label);
}
}
//添加页面没有显示的列
if(addCols)for(var k=0;k<addCols.length;k++)pstrlist.push(addCols[k]);
var data = jQGridSupport.getParam(ggridId,"postData");
var rowNum=false;
if(currentPage)
rowNum= jQGridSupport.getParam(ggridId,"rowNum");
HZ.exportExcel(url,pstrlist.join(","),data,rowNum);
}
HZ.exportExcel=function(url,expCols,postData,rownum){
var rows=60000;
if(rownum)rows=rownum;
var data={page:1,rows:rows,exportFlg:"true",expCols:expCols};
if(postData)
{
postData.page=1;
data=$.extend(true,{},postData,data);
}
$.ajax({
url : url,
success:function(downUrl){
var path=ctx+"/static-content?download=true&contentPath="+downUrl.url;
window.location.href=path;
},
type:"POST",
cache:false,
data:data,
beforeSend:function(){
$("<img id='loading_img' src='"+ctx+"/static/img/loading/006.gif' style='position: absolute;top:50px;left:300px;z-index:1000;'/>").appendTo(document.body) ;
},
complete:function(){ $("#loading_img").remove();}
});
}
当然服务端的数据加载方法要做aop拦截,当知道这是导出请求时做导出动作,请看aop类
package org.hzjun.hlt.aop;
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.hzjun.hlt.security.ShiroDbRealm.ShiroUser;
import org.hzjun.hlt.utils.DateUtil;
import org.hzjun.hlt.utils.PropertiesUtils;
import org.hzjun.hlt.web.LtSecurityUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
/**
* 基于注解的AOP日志示例 ,顺便封装导出功能
*
* @author
*/
@Component
@Aspect
public class AopLog {
private static Loggerlogger = LoggerFactory.getLogger( AopLog.class );
// 方法执行的前后调用
@SuppressWarnings("unchecked")
public Object runOnAround(ProceedingJoinPoint point) throws Throwable {
long start = System.currentTimeMillis();
Object object = point.proceed();
ShiroUser su = LtSecurityUtils.getShiroUser();
String str = "";
if (point.getArgs().length > 0) {
Object[] args = point.getArgs();
for (Object o : args)
if (o instanceof HttpServletRequest)
// if (point.getArgs()[0] instanceof HttpServletRequest)
{
HttpServletRequest request = (HttpServletRequest) o;
String export = request.getParameter( "exportFlg" );
String expCols = request.getParameter( "expCols" );
if (StringUtils.isNotBlank( export )) {// 客户端掉的导出。。。。
if (StringUtils.isBlank( expCols )) {
} else if (object instanceof Map) {
Map<String, Object> map = (Map<String, Object>) object;
List list = (List) map.get( "rows" );
List<List<Object>> rowList = new ArrayList<List<Object>>();
// 解析要导出的列
String[] cols = StringUtils.split( expCols, "," );
List<Object> row = new ArrayList<Object>();
List<String> colid = new ArrayList<String>();
for (int i = 0; i < cols.length; i++) {
String[] colStrs = cols[ i ].split( ":" );
String col = colStrs[ 0 ];
String colName = colStrs[ 1 ];
row.add( colName );
colid.add( col );
}
rowList.add( row );
for (int i = 0; i < list.size(); i++) {
Object obj = list.get( i );
List<Object> beanList = new ArrayList<Object>();
for (int j = 0; j < colid.size(); j++) {
Object colVal = "";
try {
colVal = PropertyUtils.getProperty( obj, colid.get( j ) );
} catch (Exception e) {
logger.info( "导出出现错误:" + e.getMessage() );
}
beanList.add( colVal );
}
rowList.add( beanList );
}
String fileUrl = createExcel( rowList );
Map map2 = new HashMap();
map2.put( "url", fileUrl );
object = map2;
str = "导出";
}
}
}
}
long end = System.currentTimeMillis();
return object;
}
/***************************************************************************
* 返回下载路径
*/
private String createExcel(List<List<Object>> rows) throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();// 建立新HSSFWorkbook对象
HSSFSheet sheet = wb.createSheet( "export_sheet" );// 建立新的sheet对象
int nk = 1;
for (int i = 0; i < rows.size(); i++) {
if (i % 60000 == 0 && i > 0) {
// sheet = wb.createSheet("export_sheet" + nk);//建立新的sheet对象
// nk++;
break;
}
HSSFRow row = sheet.createRow( i );// 建立新行
List<Object> rowObj = rows.get( i );
for (int j = 0; j < rowObj.size(); j++) {
HSSFCell cell = row.createCell( j );// 建立新cell
Object o = rowObj.get( j );
// cell.esetEncoding(HSSFCell.ENCODING_UTF_16);
if (o == null)
cell.setCellValue( "" );
else if (o instanceof Date)
cell.setCellValue( DateUtil.formatDateToString( (Date) rowObj.get( j ) ) );// 设置cell的整数类型的值
else
cell.setCellValue( o.toString() );
}
}
String path = PropertiesUtils.getFileDir() + "/temp/";
File f = new File( path );
if (!f.exists())
f.mkdir();
String conpath = "workbook" + System.currentTimeMillis() + ".xls";
// String fileUrl = PropertiesUtils.getFileDir() + "/temp/workbook" +
// System.currentTimeMillis() + ".xls";
FileOutputStream fileOut = new FileOutputStream( path + conpath );
wb.write( fileOut );
// wb.
fileOut.flush();
fileOut.close();
fileOut = null;
rows = null;
// 貌似垃圾回收很慢,算了,显示调用下,据说不好,但是没办法,不知道咋弄
System.gc();
// wb.c
return "temp/" + conpath;
}
}
至于有时候我们客户没钱买报表系统,但是又要报表,我们可能只能自己画页面来生成,
这时我们只要把页面搞出来,后面的导出excel其实也可以直接搞定,不用再写代码。