自定义标签实现POI导出

#系统目录结构

	com.huatech
	|--common			通用工具
	|	|--annotation	自定义注解
	|	|--constant		常量类
	|	|--export		导出工具类
	|	|--filter		过滤器
	|	|--freemarker.tag	自定义freemarker标签
	|	|--listener		监听器
	|	|--util			工具类
	|
	|--controller		
	|--dao
	|--domain
	|--model
	|--service	

 

 

#导出相关类

	com.huatech.common.annotation.EnumField 枚举属性注解，用于页面显示、导出等
	com.huatech.common.export.ExportUtil	excel 导出工具类
	com.huatech.common.export.ExportModel Excel导出需要信息抽象接口
	com.huatech.common.freemarker.tag.EnumFieldFormatterDirective	枚举属性freemarker标签
	com.huatech.common.util.EnumFieldUtils	枚举属性JSON转换工具类	

 

 

#实现步骤

1、添加导出相关类；

package com.huatech.common.annotation;

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 枚举属性注解，用于页面显示、导出等
 * 举例：
 	public class SystemConfig {
		@EnumField(json="{ 1:启用, 0:未启用 }")
		private int status;
		...	
	}
 * 
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface EnumField {

	String json();
	
}

 

package com.huatech.common.export;

import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

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

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import com.github.pagehelper.PageHelper;
import com.huatech.common.constant.ExportConstant;
import com.huatech.common.util.BeanMapUtils;
import com.huatech.common.util.EnumFieldUtils;

/**
 * excel 导出工具类
 * @author lh
 * @version 3.0
 * @since 2017-5-22
 *
 */
public class ExportUtil {
	
	private ExportUtil() {
	}
	
	public static void exportExcel(ExportModel exportModel,
			    final HttpServletRequest request, final HttpServletResponse response) throws IOException {
		if(exportModel == null ){
			throw new IllegalArgumentException("export model can not be null!");
		}
		
		String exportTitle = request.getParameter(ExportConstant.PARAM_EXPORT_TITLE);
		String[] headers = request.getParameter(ExportConstant.PARAM_HEARDERS).split(",");
		String[] fields  = request.getParameter(ExportConstant.PARAM_FIELDS).split(",");
		//create workbook 
		Workbook wb =  ExportUtil.createWorkbook();
		//总记录数
		int count = exportModel.getCount();
		if(count == 0){//查询结果为空
			Sheet sheet = wb.createSheet(exportTitle + "_1");
			sheet = wb.getSheetAt(0); 
			sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
			createHeader(sheet, exportTitle, headers);	
		}else{
			for (int i = 0, j = getTotalPage(count); i < j; i++) {
				PageHelper.startPage(i+1, ExportConstant.DEFAULT_EXPORT_PAGE_SIZE, false);
				ExportUtil.exportExcel(exportTitle, headers, fields, 
						i * ExportConstant.DEFAULT_EXPORT_PAGE_SIZE, wb, exportModel.getPageRecords());
			}			
		}
		ExportUtil.responseWorkbook(exportTitle, wb, request, response);
	}
	
	private static void exportExcel(String title, String[] headers, String[] fields, 
			int startRow, Workbook wb, List<?>  data) throws IOException {
		if(headers == null || headers.length == 0){
			throw new RuntimeException("export headers must not be null");
		}
		Sheet sheet = null;
		if(CollectionUtils.isEmpty(data)){
			if(startRow == 0){
				sheet = wb.createSheet(title + "_1");
				sheet = wb.getSheetAt(0); 
				sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
				createHeader(sheet, title, headers);
			}
			return ;
		}
		startRow = startRow>0?startRow+2:startRow;
		int index = startRow, pageRowNo = startRow, columnCount = headers.length; // 行号、页码、列数
		
		//枚举类型
		Map<String, String> jsonMap = EnumFieldUtils.json2Map(data.get(0));
		for (Object obj : data) {
			int sheetIndex = index/ExportConstant.SHEET_MAX_COUNT;
			if (index % ExportConstant.SHEET_MAX_COUNT == 0) {
				sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
				sheet = wb.getSheetAt(sheetIndex); 
				sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
				pageRowNo = 2; 
				createHeader(sheet, title, headers);				
			}else{
				sheet = wb.getSheetAt(sheetIndex); 
			}
			index++;
			@SuppressWarnings("unchecked")
			Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : BeanMapUtils.bean2Map(obj);	
			Row nRow = sheet.createRow(pageRowNo++); // 新建行对象	
			for (int j = 0; j < columnCount; j++) {
				Cell cell = nRow.createCell(j);
				String field = fields[j];
				Object val = map.get(field);
				if(jsonMap != null && jsonMap.containsKey(field)){
					Object valObj = map.get(field);
					val = jsonMap.get(String.format(EnumFieldUtils.FIELD_MAP_KEY, field, isNull(valObj)));
					if (val == null) {
						val = isNull(valObj);	
					}					
				}
				setCellValue(sheet, cell, val);
			}			
		}
		
	}
	
	/**
	 * responseWorkbook
	 * @param title
	 * @param wb
	 * @param request
	 * @param response
	 * @throws IOException
	 */
	private static void responseWorkbook(String title, Workbook wb,HttpServletRequest request, HttpServletResponse response)throws IOException{
		String sFileName = title + ".xlsx";
		// 火狐浏览器导出excel乱码
		String agent = request.getHeader("User-Agent");
		// 判断是否火狐浏览器
		boolean isFirefox = agent != null && agent.contains("Firefox");
		if (isFirefox) {
			sFileName = new String(sFileName.getBytes("UTF-8"), "ISO-8859-1");
		} else {
			sFileName = URLEncoder.encode(sFileName, "UTF8");
		}
		response.setHeader("Content-Disposition", "attachment; filename=".concat(sFileName));
		response.setHeader("Connection", "close");
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");  
		//response.setHeader("Content-Type", "application/vnd.ms-excel");
		wb.write(response.getOutputStream());
	}
	/**
	 * 设置单元格的值
	 * @param cell
	 * @param cellVal
	 */
	private static void setCellValue(Sheet sheet, Cell cell, Object cellVal){
		if(cellVal == null || String.class.equals(cellVal.getClass())){
			cell.setCellValue(filterHref(cellVal));
		}else if(Integer.class.equals(cellVal.getClass()) || int.class.equals(cellVal.getClass())){
			cell.setCellValue(Integer.valueOf(cellVal.toString()));
		}else if(Long.class.equals(cellVal.getClass()) || long.class.equals(cellVal.getClass())){
			cell.setCellValue(Long.valueOf(cellVal.toString()));
		}else if(Double.class.equals(cellVal.getClass()) || double.class.equals(cellVal.getClass())){
			cell.setCellValue(Double.valueOf(cellVal.toString()));
		}else if(Float.class.equals(cellVal.getClass()) || float.class.equals(cellVal.getClass())){
			cell.setCellValue(Float.valueOf(cellVal.toString()));
		}else if(BigDecimal.class.equals(cellVal.getClass())){
			cell.setCellValue(NumberUtils.toDouble(cellVal.toString()));
		}else if(Date.class.equals(cellVal.getClass())){
			cell.setCellValue(formatDateTime((Date)cellVal));
		}else if(Timestamp.class.equals(cellVal.getClass())){
			cell.setCellValue(formatDateTime((Timestamp)cellVal));
		}else{
			cell.setCellValue(isNull(cellVal));
		}
		cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(3));
	}
	

	/**
	 * 取得总页码数
	 * @param count
	 * @return
	 */
	private static int getTotalPage(int count){
		int pageSize = ExportConstant.DEFAULT_EXPORT_PAGE_SIZE;
		return (count % pageSize == 0) ? (count / pageSize) : (count / pageSize + 1);
	}
	
	/**
	 * 将对象转为字符串
	 * 
	 * @param o
	 * @return
	 */
	public static String isNull(Object o) {
		if (o == null) {
			return "";
		}
		String str;
		if (o instanceof String) {
			str = (String) o;
		} else {
			str = o.toString();
		}
		return str.trim();
	}
	
	/**
	 * 得到日期时间字符串，转换格式（yyyy-MM-dd HH:mm:ss）
	 */
	public static String formatDateTime(Date date) {
		return DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
	}
	
	/**
	 * 将对象转为字符串，去除超链接 
	 * @param o
	 * @return
	 */
	public static String filterHref(Object o) {
		if (o == null) {
			return "";
		}
		String str;
		if (o instanceof String) {
			str = (String) o;
		} else {
			str = o.toString();
		}
		return str.contains("href")?filterHtml(str):str;
	}
	
	/**
	 * 过滤html标签
	 * @param input
	 * @return
	 */
	public static String filterHtml(final String input){
		String result = input;
		Pattern phtml = Pattern.compile("<[^>]+>", Pattern.CASE_INSENSITIVE);
		Matcher mhtml = phtml.matcher(result);
		result = mhtml.replaceAll("");
		return result;
	}

	
	/**
	 * 创建表头
	 * @param sheet
	 * @param headers
	 */
	private static void createHeader(Sheet sheet, String title, String[] headers){
		
		//设置标题
		Row tRow = sheet.createRow(0);
		Cell hc = tRow.createCell(0);
		hc.setCellValue(new XSSFRichTextString(title));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
		hc.setCellStyle(sheet.getWorkbook().getCellStyleAt(1));
		
		//设置表头
		Row nRow = sheet.createRow(1);
		for (int i = 0; i < headers.length; i++) {
			Cell cell =	nRow.createCell(i);
			cell.setCellValue(headers[i]);
			cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(2));
		}
	}
	
	
	/**
	 * 创建Workbook
	 * @return
	 */
	private static Workbook createWorkbook(){
		Workbook wb = new SXSSFWorkbook(100);
		CellStyle hcs = wb.createCellStyle();
		hcs.setBorderBottom(BorderStyle.THIN);
		hcs.setBorderLeft(BorderStyle.THIN);
		hcs.setBorderRight(BorderStyle.THIN);
		hcs.setBorderTop(BorderStyle.THIN);
		hcs.setAlignment(HorizontalAlignment.CENTER);
		Font hfont = wb.createFont();
		hfont.setFontName("宋体");
		hfont.setFontHeightInPoints((short) 16);// 设置字体大小
		hfont.setBold(true);// 加粗
		hcs.setFont(hfont);
		
		CellStyle tcs = wb.createCellStyle();
		tcs.setBorderBottom(BorderStyle.THIN);
		tcs.setBorderLeft(BorderStyle.THIN);
		tcs.setBorderRight(BorderStyle.THIN);
		tcs.setBorderTop(BorderStyle.THIN);
		Font tfont = wb.createFont();
		tfont.setFontName("宋体");
		tfont.setFontHeightInPoints((short) 12);// 设置字体大小
		tfont.setBold(true);// 加粗
		tcs.setFont(tfont);
		
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		Font font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 12);// 设置字体大小
		
		return wb;
	}
	
	public static void main(String[] args) {
		Workbook wb =  ExportUtil.createWorkbook();
		System.out.println(wb);
	}
	
}

 

package com.huatech.common.export;

import java.util.List;

/**
 * Excel导出需要信息抽象接口
 * 
 * @author lh
 *
 */
public interface ExportModel {

	/**
	 * 导出数据总记录数
	 * @return 数据总条数
	 */
	int getCount();

	/**
	 * 查询指定页的数据
	 * 
	 * @return 页面数据
	 */
	List<?> getPageRecords();
}

 

package com.huatech.common.freemarker.tag;

import java.io.IOException;
import java.io.Writer;
import java.lang.reflect.Field;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.huatech.common.annotation.EnumField;

import freemarker.core.Environment;
import freemarker.template.TemplateDirectiveBody;
import freemarker.template.TemplateDirectiveModel;
import freemarker.template.TemplateException;
import freemarker.template.TemplateModel;

/**
 * 数据字典格式化自定义标签 在freemarker配置XML中定义为：
 * 
 * <pre>
 *        <property name="freemarkerVariables">
			<map>
				<entry key="fieldFormatter" value-ref="enumFieldFormatterDirective" /> 
			</map>
		  </property>
 * </pre>
 * 
 * 在页面使用例子：
 * 
 * <pre>
 *    <script type="text/javascript"> 
 *         <@fieldFormatter field="status" bean="com.huatech.domain.SystemConfig"/>
 *         ........     
 *           
 *          //easyui columns formatter值为自定义标签中的fieldName+"Formatter"
 *          {name:'status',label:'状态',sortable:false,formatter:statusFormatter}  
 *           
 *  	</script>
 * </pre>
 * 
 * @author lh
 * @version 2.0
 * @since 2017-05-22
 * 
 */
public class EnumFieldFormatterDirective implements TemplateDirectiveModel {
	
	private static final Logger LOGGER = LoggerFactory.getLogger(EnumFieldFormatterDirective.class);

	private String fieldName;
	

	@Override
	public void execute(Environment env, @SuppressWarnings("rawtypes") Map params, TemplateModel[] loopVars, TemplateDirectiveBody body)
			throws TemplateException, IOException {
		String beanName = params.get("bean").toString();
		fieldName = params.get("field").toString();
		if (StringUtils.isBlank(fieldName) || StringUtils.isBlank(beanName)) {
			return;
		}
		// 查询字段指定分类所有条目
		Class<?> clazz;
		try {
			clazz = Class.forName(beanName);
			Field fd = clazz.getDeclaredField(fieldName);
			EnumField enumField = fd.getAnnotation(EnumField.class);
			if (enumField != null) {
				Writer out = env.getOut();
				StringBuilder buffer = new StringBuilder();
				buffer.append(getJsonData( enumField.json()));
				buffer.append(createFormatterFunction());
				out.write(buffer.toString());				
			}
		}catch (NoSuchFieldException | SecurityException e1) {
			LOGGER.error(e1.getMessage(), e1);
		} catch (ClassNotFoundException e1) {
			LOGGER.error(e1.getMessage(), e1);
		}

	}

	private String getJsonData(String json) {
		StringBuilder buffer = new StringBuilder(128);
		String objectName = fieldName + "Data_";
		buffer.append("var " + objectName + " ={ \n");
		String[] array = json.substring(1, json.length() - 1).split(",");
		for (String string : array) {
			String[] kv = string.split("\\:");
			buffer.append("\t\"").append(kv[0].trim()).append("\" : \"").append(kv[1].trim()).append("\",\n");
		}
		buffer.deleteCharAt(buffer.length() - 2);
		buffer.append("\n  } ; \n");
		return buffer.toString();
	}

	private String createFormatterFunction() {
		StringBuilder buffer = new StringBuilder(50);
		String objectName = fieldName + "Data_";
		buffer.append("function ").append(fieldName).append("Formatter(cellvalue,options,rowData){\n");
		// 默认值为空字符串
		buffer.append("\t if(cellvalue == null){\n");
		buffer.append("\t\t return \"&nbsp;\"\n");
		buffer.append("\t }\n");
		buffer.append("\t var label= ").append(objectName).append("[cellvalue];\n");
		buffer.append("\t if(!label) { \n");
		buffer.append("\t\t return cellvalue ; \n");
		buffer.append("\t }  \n");
		buffer.append("\t return label;\n ");
		buffer.append("}\n");
		return buffer.toString();
	}
}

 

package com.huatech.common.util;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import com.huatech.common.annotation.EnumField;

/**
 * 枚举属性JSON转换工具类
 * 
 * @author lh
 * @version 2.0
 * @since 2017-05-19
 */
public final class EnumFieldUtils {

	private static ConcurrentHashMap<String/* className */, Map<String/*fieldName_fieldValue*/, String/* fieldJsonValue */>> 
					enumJsonCache = new ConcurrentHashMap<>();
	
	public static final String FIELD_MAP_KEY = "%s_%s";

	private EnumFieldUtils() {
	}

	public static Map<String, String> json2Map(Object obj) {
		if (obj != null) {
			Class<?> clazz = obj.getClass();
			//类名称作为enumJsonCache的key
			String className = clazz.getName();
			//取缓存
			if (enumJsonCache.containsKey(className)) {
				return enumJsonCache.get(className);
			}
			synchronized (enumJsonCache) {
				//再次读取缓存
				if (enumJsonCache.containsKey(className)) {
					return enumJsonCache.get(className);
				}
				Map<String, String> jsonMap = new HashMap<>();
				do {
					// 得到类中的所有属性集合
					Field[] fs = clazz.getDeclaredFields();
					for (Field field : fs) {
						EnumField enumField = field.getAnnotation(EnumField.class);
						if (enumField != null) {
							jsonMap.putAll(convert(field.getName(), enumField.json()));
						}
					}
					clazz = clazz.getSuperclass();
				} while (clazz != Object.class);

				enumJsonCache.put(className, jsonMap);
				return jsonMap;
			}

		}
		return new HashMap<>();
	}

	/**
	 * json转为Map对象
	 * 
	 * @param json
	 * @return
	 */
	private static Map<String, String> convert(String fieldName, String json) {
		Map<String, String> map = new HashMap<>();
		if (json == null || "".equals(json)) {
			return map;
		}
		map.put(fieldName, fieldName);
		String[] array = json.substring(1, json.length() - 1).split(",");
		for (String string : array) {
			String[] kv = string.split("\\:");
			map.put(String.format(FIELD_MAP_KEY, fieldName, trim(kv[0])), trim(kv[1]));
		}
		return map;
	}

	/**
	 * 去除字符串前后空格
	 * 
	 * @param str
	 * @return
	 */
	public static String trim(String str) {
		if (str == null) {
			return "";
		}
		return str.trim();
	}


}

 

2、在spring配置文件中配置枚举属性freemarker标签，本示例配置文件为spring-controller.xml

		<bean id="enumFieldFormatterDirective" class="com.huatech.common.freemarker.tag.EnumFieldFormatterDirective"/>

		<property name="freemarkerVariables">
			<map>
				<entry key="fieldFormatter" value-ref="enumFieldFormatterDirective" /> 
			</map>
		</property>

 

 

3、页面使用枚举属性标签

<script type="text/javascript">
			<@fieldFormatter field="status" bean="com.huatech.domain.SystemConfig"/>
			...
			//easyui columns formatter值为自定义标签中的fieldName+"Formatter"
         {name:'status',label:'状态',sortable:false,formatter:statusFormatter}   		
</script>

 

4、Controller层Excel导出

/**
	 * 系统配置Excel导出
	 * @param model
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	@RequestMapping(value = "/modules/system/config/export" , method = RequestMethod.GET)
	public void exportSystemConfig(SystemConfigModel model, HttpServletRequest request, HttpServletResponse response)throws Exception {
		
		ExportUtil.exportExcel(new ExportModel() {
			private Map<String, Object> bean2Map(){
				return BeanMapUtils.bean2Map(model);
			}			
			@Override
			public int getCount() {
				return systemConfigService.getCount(bean2Map());
			}
			@Override
			public List<?> getPageRecords() {
				return systemConfigService.findList(bean2Map());
			}			
		}, request, response);
	}

 

 

#导出功能示例

系统配置管理页面：http://localhost:[port]/modules/system/config/manage.html

[port]改为项目部署端口