导出excel那些事

我们做系统时经常遇到到处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其实也可以直接搞定，不用再写代码。