Java excel 导出 工具

  依赖：

	<!-- excel 导出 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>RELEASE</version>
		</dependency>
		

　　

 

核心类：

 

package com.cy.common.utils;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

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

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;

/**
 * excel 工具
 * 
 * @author Admin
 *
 */
public class ExcelUtils {

	@SuppressWarnings("resource")
	public static <T> void httpDownload(HttpServletResponse response, String fileName,  List<String> fields, List<T> data) throws IOException {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet1");
		
		if( data == null ) {
			data = new ArrayList<T>();
		}
		for(int i=0;i<data.size();i++) {
			Object rowData = data.get(i);
			if( rowData == null ) {
				continue;
			}
			
			Map<String, String>  map = BeanUtils.toFormatMap(rowData);
			HSSFRow row = sheet.createRow(i);
			for( int j=0;j<fields.size();j++ ) {
				HSSFCell cell = row.createCell(j);
				String value = map.get( fields.get( j ) );
				cell.setCellValue( value==null?"":value );
			}
		}

		response.setHeader("Content-Disposition","attachment;filename=" + new String( fileName.getBytes(), "iso-8859-1") + ".xlsx");
		response.setContentType("application/vnd.ms-excel;fileName=");
		ServletOutputStream out = response.getOutputStream();
		workbook.write(out);
	}
	
	
	@SuppressWarnings("resource")
	public static <T> void httpDownload(HttpServletResponse response, String fileName, List<String> titles , List<String> fields, List<T> data) throws IOException {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet1");
		
		if( data == null ) {
			data = new ArrayList<T>();
		}
		
		HSSFRow titleRow = sheet.createRow(0);
		for( int j=0;j<titles.size();j++ ) {
			HSSFCell cell = titleRow.createCell(j);
			cell.setCellValue( titles.get(j) );
		}
		
		for(int i=1;i<data.size();i++) {
			Object rowData = data.get(i);
			if( rowData == null ) {
				continue;
			}
			
			Map<String, String>  map = BeanUtils.toFormatMap(rowData);
			HSSFRow row = sheet.createRow(i);
			for( int j=0;j<fields.size();j++ ) {
				HSSFCell cell = row.createCell(j);
				String value = map.get( fields.get( j ) );
				cell.setCellValue( value==null?"":value );
			}
		}
		
		response.setHeader("Content-Disposition","attachment;filename=" + new String( fileName.getBytes(), "iso-8859-1") + ".xlsx");
		response.setContentType("application/vnd.ms-excel;fileName=");
		ServletOutputStream out = response.getOutputStream();
		workbook.write(out);
	}

	
	
	

}

　　

 

使用到的 工具类：

package com.cy.common.utils;

import java.io.File;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.github.pagehelper.Page;

/**
 * bean的工具类
 * @author ZHANGYUKUN
 *
 */
public class BeanUtils {

	
	private static Logger logger = LoggerFactory.getLogger(BeanUtils.class);
	
	/**
	 * 复制一个对象到另一个对象，忽略null值字段
	 * 
	 * 
	 * @param source
	 * @param target
	 * @param ignoreNull
	 */
	public static void copyProperties(Object source, Object target, Boolean ignoreNull) {
		if( target == null ) {
			return ;
		} 
		
		if( source == null ) {
			return ;
		}
		
		if (!ignoreNull) {
			org.springframework.beans.BeanUtils.copyProperties(source, target);
		} else {
			String[] ignoreFiled = getNullField(source);
			org.springframework.beans.BeanUtils.copyProperties(source, target, ignoreFiled);
		}

	}
	
	public static void copyProperties(Object source, Object target) {
		copyProperties(  source ,target ,false );
	}
	
	/**
	 * 创建并复制一个对象
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static <T> T copyNew(Object source, Class<T> targetCls) {
		if( source == null  ) {
			return null;
		}
		
		T rt;
		try {
			rt = targetCls.getDeclaredConstructor().newInstance();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		org.springframework.beans.BeanUtils.copyProperties(source, rt);
		return rt;
	}
	
	/**
	 * 复制信息到outList
	 * 
	 * @param list
	 * @param cls
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> List<T> copyToOutList(List<?> list, Class<T> cls) {
		if( list == null ) {
			return null;
		}
		List<T> rtList = null;
		try {
			rtList = list.getClass().getDeclaredConstructor().newInstance();
			
			if( list instanceof Page ) {
				Page<Object> pageList = (Page<Object>) rtList;
				Page<Object> temp = (Page<Object>) list;
				pageList.setPageNum( temp.getPageNum() );
				pageList.setPageSize( temp.getPageSize() );
				pageList.setPages( temp.getPages() );
				pageList.setTotal( temp.getTotal() );
			}
			
			for( Object item :  list ) {
				T rtItem =   cls.getDeclaredConstructor().newInstance();
				BeanUtils.copyProperties( item  , rtItem , false );
				rtList.add( rtItem );
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return rtList;
	}
	
	/**
	 *复制分页信息
	 *
	 * @param source
	 * @param target
	 */
	@SuppressWarnings("unused")
	private static void copyPageList(Object source, Object target ) {
		String[] ignoreFiled = new String[] {"result"};
		org.springframework.beans.BeanUtils.copyProperties(source, target, ignoreFiled);
	}

	/**
	 * 得到 值为null 的字段 （只找当前类，没找父类，因为我们的实体暂时没有继承关系）
	 * 
	 * @param source
	 * @return
	 */
	public static String[] getNullField(Object source) {
		List<String> fieldList = new ArrayList<>();
		Field[] fields = source.getClass().getDeclaredFields();

		for (Field field : fields) {
			field.setAccessible(true);
			try {
				if (field.get(source) == null) {
					fieldList.add(field.getName());
				}
			} catch (IllegalArgumentException | IllegalAccessException e) {
				e.printStackTrace();
			}

		}

		return fieldList.toArray(new String[] {});
	}
	
	/**
	 * 得到定义的所有字段（返回数组）
	 * 
	 * @return
	 */
	public static String[] getDeclareField(Class<?> cls) {
		return getDeclareFieldAsList(cls).toArray(new String[] {});
	}
	
	
	/**
	 * 得到定义的所有字段(返回list)
	 * 
	 * @return
	 */
	public static List<String> getDeclareFieldAsList(Class<?> cls) {
		List<String> fieldList = new ArrayList<>();
		Field[] fields = cls.getDeclaredFields();
		
		for (Field field : fields) {
			fieldList.add( field.getName() );
		}
		
		return fieldList;
	}
	
	
	
	

	/**
	 * 检查 in对象(如果有必填字段name就会抛出异常)
	 */
	@SuppressWarnings("unchecked")
	public static void checkInObjectRequired(Object inObj) {
		Class<?> cls = inObj.getClass();
		Field[] fields = cls.getDeclaredFields();
		for (Field field : fields) {
			
			Class<? extends Annotation> apiParamCls;
			try {
				apiParamCls = (Class<? extends Annotation>) Class.forName("io.swagger.annotations.ApiModelProperty");
			} catch (ClassNotFoundException e) {
				return ;
			}
			
			if (field.isAnnotationPresent( apiParamCls )) {
				Annotation apiParam = field.getAnnotation( apiParamCls );

				Object fieldValue = null;
				Boolean requiredValue = false;
				String allowableValues = null;
				try {
					field.setAccessible(true);
					// 取值-是否必填
					Method method = apiParamCls.getMethod("required");
					requiredValue = (Boolean) method.invoke( apiParam );
					// 取值-限制条件
					method = apiParamCls.getMethod("allowableValues");
					allowableValues = (String) method.invoke( apiParam );
					// 取值-field的值
					fieldValue = field.get(inObj);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}

		}

	}


	/**
	 * 检查某个out对象
	 * 
	 * @param cls
	 */
	public static void checkOutObject(Class<?> cls) {
		Field[] fields = cls.getDeclaredFields();
		for (Field field : fields) {
			Class<?> fieldCls = field.getType();
			if (fieldCls.equals(Long.class) || fieldCls.equals(long.class)) {
				if (!field.getName().equals("serialVersionUID")) {
					if (!field.isAnnotationPresent(JsonSerialize.class)) {
						if( logger.isWarnEnabled() ) {
							logger.warn("类" + cls +"字段" +field.getName() +"是Long，写成向前端放回的时候可能丢失精度，你大概可以在字段上面加上：@JsonSerialize(using = LongJsonSerializer.class) 来改善这个问题 "  );
						}
					}
				}
			}

		}

	}
	
	
	/**
	 * 检查这个类和他的同包下面的out对象
	 * @param clss
	 */
	public static void checkOutObjectAll( Class<?> clss ) {
		File file =new File(  clss.getResource("").getPath() ) ;
		
		if (file.exists() && file.isDirectory()) {
			for (File itemFile : file.listFiles()) {

				if (itemFile.isDirectory() || !itemFile.getName().endsWith(".class")) {
					continue;
				}

				String className = ClassUtils.getSimpleClassName(itemFile.getName());
				try {
					Class<?> cls = BeanUtils.class.getClassLoader().loadClass( clss.getPackage().getName()+"."  + className);
					
					checkOutObject( cls );
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				}

			}

		}
		
	}
	
	/**
	 * 得到枚举数组的 names
	 * @param enums
	 * @return 枚举的names
	 */
	public static List<String> enumArraysNames( Enum<?>[] enums ) {
		List<String> names = new ArrayList<>();
		for( Enum<?> em : enums ) {
			names.add( em.name() );
		}
		return names;
	}
	
	/**
	 * 对象 装换成 有格式的 Map( 打印，导出专用 )
	 * @param data
	 * @return
	 */
	public static Map<String, String> toFormatMap(Object obj) {
		Map<String, String> map = new HashMap<String, String>();
		Method[] methods = obj.getClass().getMethods();

		for (Method method : methods) {
			try {
				int mod = method.getModifiers();
				if (Modifier.isStatic(mod) || Modifier.isFinal(mod)) {
					continue;
				}
				if (!method.getName().startsWith("get")) {
					continue;
				}
				Object value = method.invoke(obj);

				if (value != null) {
					String name = method.getName().substring(3, 4).toLowerCase() + method.getName().substring(4);
					
					if( value instanceof Enum && value.getClass().getPackage().getName().contains("com.cy.order") ) {
						Method emM =  value.getClass().getMethod("getValue");
						map.put(name, emM.invoke( value )+"" );
					}else if( value instanceof Date ){
						map.put(name,  DateUtils.format( (Date)value , 3) );
					}else if( method.getName().contains("Amount") ){
						map.put(name,  MoneyJsonSerializer.format(  (Long)value) );
					}else {
						map.put(name, value.toString());
					}
				}
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			} catch (SecurityException e) {
				e.printStackTrace();
			}
		}
		return map;
	}



}

　　

 

使用例子:

	Result<List<OrderStatusView>> data = listPayOrder(in);
		
		List<String> titles  = Arrays.asList( "订单号","病人名","病人Id","交易类型","交易金额","支付方式","交易单号","交易时间","交易状态","平台商户");
		List<String> fileNames  = Arrays.asList( "id","name","cardNum","tradeType","amount","payType","payOrderId","payOrderDate","ph","merchantName");
		
		ExcelUtils.httpDownload(response, "查询支付单列表",titles, fileNames , data.getData());

　　

 

 

效果图：