JEEWEB中自动生成代码里添加可导出excel文档功能

        JEEWEB具有代码生成功能，在阅读源码及使用其开发的过程中，想将列表页查询出的符合条件的数据导出为excel文档，遂实践之，步骤如下：

1.cn.jeeweb.core.tags.grid.DataGridToolbarTag.java中改动：

private static String[] INNER_DEFAULT_FUNCTION = { "create", "update", "delete", "search", "reset","upload","exports" };//添加exports

dealDefault()方法：

private void dealDefault(DataGridTag parent) {
		if (!StringUtils.isEmpty(this.function) && isFunction(this.function)) {
			// 预处理Url问题
			if (StringUtils.isEmpty(url)) {
				String url = "";
				if (this.function.equals("delete")) {
					url = parent.getBaseUrl() + "/batch/delete";
				} else if (this.function.equals("update")) {
					url = parent.getBaseUrl() + "/{id}/" + this.function;
				} else {
					url = parent.getBaseUrl() + "/" + this.function;
				}
				staticAttributes.put("url", url);
			}

			if (StringUtils.isEmpty(title)) {
				String title = "sys.common." + this.function;
				staticAttributes.put("title", MessageUtils.getMessageOrSelf(title));
			}

			if (StringUtils.isEmpty(this.icon)) {
				String icon = "";
				if (this.function.equals("create")) {
					// btn-info
					icon = "fa-plus";
				} else if (this.function.equals("update")) {
					icon = "fa-file-text-o";
				} else if (this.function.equals("delete")) {
					icon = "fa-trash-o";
				} else if (this.function.equals("search")) {
					icon = "fa-search";
				} else if (this.function.equals("reset")) {
					icon = "fa-refresh";
				}else if(this.function.equals("exports")){  //添加exports的图标
                    icon = "fa-file-excel-o";
                }  
				staticAttributes.put("icon", icon);
			}

			if (StringUtils.isEmpty(this.btnclass)) {
				String btnclass = "";
				if (this.function.equals("create")) {
					// btn-info
					btnclass = "btn-primary";
				} else if (this.function.equals("update")) {
					btnclass = "btn-success";
				} else if (this.function.equals("delete")) {
					btnclass = "btn-danger";
				} else if (this.function.equals("search")) {
					btnclass = "btn-info";
				} else if (this.function.equals("reset")) {
					btnclass = "btn-warning";
				} else {
					btnclass = "btn-info";
				}
				staticAttributes.put("btnclass", btnclass);
			}

			if (this.function.equals("search") || this.function.equals("reset")) {
				staticAttributes.put("layout", "right");//|| this.function.equals("exports")
			}
		}
	}

3.src/main/resources/condegen/template/code文件夹下修改:

（1）ControllerTemplate.ftl
添加导入的包：

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import cn.jeeweb.core.utils.ExcelWrite;

添加方法：

@RequestMapping(value = "exports", method = { RequestMethod.GET, RequestMethod.POST })
    @PageableDefaults(sort = "id=desc")
    private void exports(Queryable queryable, PropertyPreFilterable propertyPreFilterable, HttpServletRequest request,
                          HttpServletResponse response) throws IOException {
        EntityWrapper<${entityName?cap_first}> entityWrapper = new EntityWrapper<${entityName?cap_first}>(entityClass);
        propertyPreFilterable.addQueryProperty("id");
        // 预处理
        QueryableConvertUtils.convertQueryValueToEntityValue(queryable, entityClass);
        SerializeFilter filter = propertyPreFilterable.constructFilter(entityClass);
        PageJson<${entityName?cap_first}> pagejson = new PageJson<${entityName?cap_first}>(${entityName?uncap_first}Service.listWithNoPage(queryable,entityWrapper));
        List<${entityName?cap_first}> list = pagejson.getResults();
        //请自行将想要导出的字段放入json里封装好,封装格式见下
        JSONObject json = new JSONObject();
        /**for(int i=0;i<list.size();i++){
        	JSONArray array = new JSONArray();
        	ShopCart sc = list.get(i);
        	array.add(sc.getPackName());
        	array.add(sc.getUserName());
        	array.add(sc.getDinnerDate());
        	array.add(sc.getType());
        	array.add(sc.getQuantity());
        	json.put(i, array);
        }*/
        String titles[] = {"","","",""};//导出excel表头
        try {
			ExcelWrite.writeXlsx(json,response,request,titles);
		} catch (Exception e) {
			e.printStackTrace();
		}
    }

（2）viewListTemplate.ftl添加：

	<grid:toolbar function="exports"/> 

（3）code下的onetomany及tree文件夹中的上述两模板的修改同上

4.src/main/webapp/static/common/js/curdtools_jqgrid.js添加方法：

/** 
 * 导出 
 * @param gridId 
 */  
function exports(gridId,url,gridId,width,height) {  
    var queryParams = {};  
    var queryFields=$('#queryFields').val();  
    queryParams['queryFields'] = queryFields;  
    //普通的查询  
    $('#' + gridId + "Query").find(":input").each(function() {  
        var val = $(this).val();  
        if (queryParams[$(this).attr('name')]) {  
            val = queryParams[$(this).attr('name')] + "," + $(this).val();  
        }  
        queryParams[$(this).attr('name')] = val;  
    });  
  
    // 普通的查询  
    $('#' + gridId + "Query").find(":input").each(function() {  
        var condition = $(this).attr('condition');  
        if (!condition) {  
            condition = "";  
        }  
        var key = "query." + $(this).attr('name') + "||" + condition;  
        queryParams[key] = queryParams[$(this).attr('name')];  
    });  
    //console.log(url);
    var result = $.param(queryParams);  
    location.href=url+"?"+result;  //url
    }  

5.src/main/resources/i18n/messages.properties添加：

sys.common.exports=导出

6.在cn.jeeweb.core.utils包下添加ExcelWrite.java

package cn.jeeweb.core.utils;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

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

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelWrite {

	public static String getStringVal(Cell cell){
		switch(cell.getCellType()){
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue()?"TRUE":"FALSE";
		case Cell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		case Cell.CELL_TYPE_NUMERIC:
			cell.setCellType(Cell.CELL_TYPE_STRING);
			return cell.getStringCellValue();
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		default:
			return "";
		}
	}
	/**
	 * 填充单元格
	 * 
	 * @date 2015年8月21日
	 * @author ren
	 * @param sheet
	 * @param point
	 * @param columns
	 * @param startRow
	 */
	public static void pointDataInsert(Sheet sheet, String[] columns, int startRow) {
		Row row = sheet.createRow(startRow);
		for (int i = 0; i < columns.length; i++) {
			String value = columns[i];
			if (value != null && !value.equalsIgnoreCase("null")) {
				row.createCell(i, Cell.CELL_TYPE_STRING).setCellValue(value.toString());
			}
		}
	}
	
	public static void writeXlsx(JSONObject json,HttpServletResponse response, HttpServletRequest request,String[] titles) throws Exception{		
		XSSFWorkbook wb = new XSSFWorkbook();
		
		Sheet sheet = wb.createSheet();
		//String[] title = new String[]{"套餐名","用户名","日期","用餐类型","数量"};
		wb = setHeader(wb,sheet,titles);
		
		System.out.println("excelReader's array:"+json.toString());
		for(int i = 1;i<=json.size();i++){
			JSONArray array = json.getJSONArray(String.valueOf(i-1));
			String[] data = new String[array.size()];
			Object[] ar = array.toArray();
			for(int j=0;j<array.size();j++){
				data[j] = ar[j]+"";
			}
			pointDataInsert(sheet,data,i);
		}		
		outPutExcel("testExcel",wb,response,request);
	}
	
	public static XSSFWorkbook setHeader(XSSFWorkbook workbook,Sheet sheet,String[] titles){
		XSSFCellStyle style = null;
		  // 创建表头style
		XSSFCellStyle cellStyleTitle = workbook.createCellStyle();
		//cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
		XSSFColor color = new XSSFColor();
		byte[] rgb = {127,0,13};
		color.setRgb(rgb);
		cellStyleTitle.setFillForegroundColor(color);
		cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// //居中显示
		XSSFRow titleRow = (XSSFRow) sheet.createRow(0);
		   for (int i = 0; i < titles.length; i++) {
		    XSSFCell cell = titleRow.createCell(i);
		    // cell.setCellStyle(createCellColorStyle(workbook));
		    cell.setCellStyle(cellStyleTitle);
		    cell.setCellValue(titles[i]);// 给单元格赋值
		   }
		return workbook;
	}
	//导出excel
		public static void outPutExcel(String fileName,XSSFWorkbook wb,HttpServletResponse response, HttpServletRequest request) throws IOException{
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			wb.write(baos);
			byte[] content = baos.toByteArray();
			response.reset();
			response.setContentType("application/x-msdownload");
			response.addHeader("Content-Disposition", "attachment; filename=\""+fileNameSolve(request,fileName)+".xlsx\"");
			ServletOutputStream outputStream = response.getOutputStream();
			outputStream.write(content, 0, content.length);
			outputStream.flush();
		}
		//下载文件中文乱码解决
	    public static String fileNameSolve(HttpServletRequest request,String beforeFileName) throws UnsupportedEncodingException{
	    	boolean isMSIE = isMSBrowser(request);
			if (isMSIE) {
				beforeFileName = URLEncoder.encode(beforeFileName, "UTF-8");
	        }else {
	        	beforeFileName = new String(beforeFileName.getBytes("UTF-8"), "ISO-8859-1");
	        }
			return beforeFileName;
	    }
	    private static String[] IEBrowserSignals = {"MSIE", "Trident", "Edge"};
	  //判断浏览器是否是微软浏览器
	    public static boolean isMSBrowser(HttpServletRequest request) {
	        String userAgent = request.getHeader("User-Agent");
	        for (String signal : IEBrowserSignals) {
	            if (userAgent.contains(signal))
	                return true;
	        }
	        return false;
	    }
	
}