Excel导入导出

2016-09-24

在后台管理系统中，常遇到Excel导入导出的需求，整理了如下两个工具类。

一、导入

导入工具类

package com.demo.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.LoggerFactory;
import ch.qos.logback.classic.Logger;

public class ExcelToEntityList {

	private Logger log = (Logger) LoggerFactory.getLogger(this.getClass());
	private BeanStorage storage = new BeanStorage();
	private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	private StringBuffer error = new StringBuffer(0);

	/**
	 * 包含headMapping信息已经前端传过来的扩展信息
	 * @param entity  		一行excel内容要转换为何种对象
	 * @param excel	  		excel输入流
	 * @param titleToAttr	key为excel的中文title，value为该中文title对于的entity属性名
	 * @return
	 * @throws IOException
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws InvalidFormatException
	 */
	public  <T> ArrayList<T> transform(Class<?> entity,InputStream excel,Map<String,String> titleToAttr) throws IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, InvalidFormatException{
		ArrayList<T> result = new ArrayList<T>();

		Workbook book = create(excel);
		Sheet sheet = book.getSheetAt(0);
	    int rowCount = sheet.getLastRowNum();
	    if(rowCount < 1){
			return result;
		}
	    //加载标题栏数据,以此和headMapping对应
	    Map<Integer, String> headTitle = loadHeadTitle(sheet);
	    for(int i=1;i<=rowCount;i++){
	    	Row row = sheet.getRow(i);
	    	//空行跳过
	    	if(row==null){
	    		continue;
	    	}
			int cellCount = row.getLastCellNum();
			@SuppressWarnings("unchecked")
			T instance = (T) entity.newInstance();
			int col = 0;
			try {
		    	for(;col<cellCount;col++){
					String cellValue = getCellValue(row.getCell(col));
					if(null!=cellValue){
						this.setEntity(entity, instance,titleToAttr.get(headTitle.get(col)), cellValue);
					}
				}
				result.add(instance);
			} catch (Exception e) {
				//excel.close();
				e.printStackTrace();
				this.error.append("第"+ (i+1) +"行，"+ headTitle.get(col)+"字段，数据错误，跳过！").append("<br>");
				log.error("第"+ (i+1) +"行，"+ headTitle.get(col)+"字段，数据错误，跳过！");
			}
	    }
	    excel.close();
	    return result;
	}

	/**
	 * 加载Excel的标题栏
	 * @param sheet
	 * @return 返回列序号和对于的标题名称Map
	 */
	private Map<Integer,String> loadHeadTitle(Sheet sheet){
		Map<Integer,String> map = new HashMap<Integer, String>();
		Row row = sheet.getRow(0);
		int cellCount= row.getLastCellNum();
		for(int i = 0; i < cellCount; i++){
			String value = row.getCell(i).getStringCellValue();
			if(null == value){
				throw new RuntimeException("Excel导入：标题栏不能为空！");
			}
			map.put(i, value);
		}
		return map;
	}

	/**
	 * 获取表格列的值
	 * @param cell
	 * @return
	 */
	private String getCellValue(Cell cell){
		if(null==cell){return "";}
		String value = null;
		switch (cell.getCellType()){
		case XSSFCell.CELL_TYPE_BOOLEAN:
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case XSSFCell.CELL_TYPE_NUMERIC:
			// 判断当前的cell是否为Date
			if (DateUtil.isCellDateFormatted(cell)){
				value = dateFormat.format(cell.getDateCellValue());
			}else{
				value = String.valueOf((long) cell.getNumericCellValue());
			}
			break;
		case XSSFCell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		case XSSFCell.CELL_TYPE_FORMULA:
			log.debug("不支持函数！");
			break;
		}

		return value;
	}

	private <T> void setEntity(Class<?> clazz, T instance, String pro, String value) throws SecurityException, NoSuchMethodException, Exception{
		String innerPro = null;
		String outterPro = null;
		if (pro.contains(".")){
			String[] pros = pro.split("\\.");
			outterPro = pros[0];
			innerPro = pros[1];
			// 将成员变量的类型存储到仓库中
			storage.storeClass(instance.hashCode() + outterPro, clazz.getDeclaredMethod(this.initGetMethod(outterPro), null).getReturnType());
		}
		String getMethod = this.initGetMethod(outterPro!=null?outterPro:pro);
		Class<?> type = clazz.getDeclaredMethod(getMethod, null).getReturnType();
		Method method = clazz.getMethod(this.initSetMethod(outterPro!=null?outterPro:pro), type);
		if (type == String.class){
			method.invoke(instance, value);
		}else if (type == int.class || type == Integer.class){
			method.invoke(instance, Integer.parseInt("".equals(value) ? "0" : value));
		}else if (type == long.class || type == Long.class){
			method.invoke(instance, Long.parseLong("".equals(value) ? "0" : value));
		}else if (type == float.class || type == Float.class){
			method.invoke(instance, Float.parseFloat("".equals(value) ? "0" : value));
		}else if (type == double.class || type == Double.class){
			method.invoke(instance, Double.parseDouble("".equals(value) ? "0" : value));
		}else if (type == Date.class){
			method.invoke(instance, dateFormat.parse(value));
		}else if (type == boolean.class|| type == Boolean.class){
			method.invoke(instance, Boolean.parseBoolean("".equals(value) ? "false" : value));
		}else if (type == byte.class|| type == Byte.class){
			method.invoke(instance, Byte.parseByte(value));
		}else{
			// 引用类型数据
			Object ins = storage.getInstance(instance.hashCode() + outterPro);
			this.setEntity(ins.getClass(), ins, innerPro, value);
			method.invoke(instance, ins);
		}
	}

	public String initSetMethod(String field)
	{
		return "set" + field.substring(0, 1).toUpperCase() + field.substring(1);
	}

	public String initGetMethod(String field)
	{
		return "get" + field.substring(0, 1).toUpperCase() + field.substring(1);
	}

	/**
	 * @return true 存在错误，false 不存在错误
	 */
	public boolean hasError()
	{
		return error.capacity() > 0;
	}

	public StringBuffer getError()
	{
		return error;
	}
	/**
	 * 存储bean中的bean成员变量
	 */
	private class BeanStorage
	{
		private Map<String, Object> instances = new HashMap<String, Object>();
		public void storeClass(String key, Class<?> clazz) throws Exception{
			if (!instances.containsKey(key)){
				instances.put(key, clazz.newInstance());
			}
		}
		public Object getInstance(String key){
			return instances.get(key);
		}
	}

	//2003、2007兼容处理
	public  Workbook create(InputStream inp) throws IOException,InvalidFormatException {
	    if (!inp.markSupported()) {
	        inp = new PushbackInputStream(inp, 8);
	    }
	    if (POIFSFileSystem.hasPOIFSHeader(inp)) {
	        return new HSSFWorkbook(inp);
	    }
	    if (POIXMLDocument.hasOOXMLHeader(inp)) {
	        return new XSSFWorkbook(OPCPackage.open(inp));
	    }
	    throw new IllegalArgumentException("你的excel版本目前poi解析不了");
	}
}

使用方式

//前端部分提交excel文件与该对应关系到后端
var mapping = {
    "EntityClassName":"com.demo.entity.User",
    "用户编号":"userCode","用户名称":"userName",
    "电话":"phone","地址":"address"
}

/*后端部分在servlet中接收excel与excel头与entity属性的对应关系*/
//接收对应关系参数
HashMap<String, String> param = JSON.parseObject(URLDecoder.decode(request.getParameter("mapping"), "utf-8"), HashMap.class);
ExcelToEntityList excel = new ExcelToEntityList();
//把excel文件内容转换为List对象
ArrayList<?> list = excel.transform(Class.forName(param.get("EntityClassName")), request.getPart("file").getInputStream(), param);

二、导出

导入工具类

package com.demo.util;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class EntityListToExcel {
	private StringBuffer error = new StringBuffer(0);

	/**
	 * 将实体类列表entityList转换成excel
	 * @param param 包含headMapping信息，key为属性名，value为列名<br>
	 * @param entityList
	 * @param excel
	 * @return
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InvocationTargetException
	 * @throws IOException
	 */
	public <T> boolean transform(Map<String, String> param, List<T> entityList,OutputStream excel) throws NoSuchMethodException,
			SecurityException, IllegalAccessException,
			IllegalArgumentException, InvocationTargetException, IOException {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet();
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// 定义注释的大小和位置,详见文档
		HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
				0, 0, 0, (short) 4, 2, (short) 6, 5));
		// 设置注释内容
		comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
		// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		comment.setAuthor("admin");

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		int i = 0;
		List<String> proList = new ArrayList<String>();
		HSSFFont blueFont = workbook.createFont();
		blueFont.setColor(HSSFColor.BLUE.index);
		for (Map.Entry<String, String> entry : param.entrySet()) {
			HSSFCell cell = row.createCell(i);
			HSSFRichTextString text = new HSSFRichTextString(entry.getValue());
			text.applyFont(blueFont);
			cell.setCellValue(text);
			proList.add(entry.getKey());
			i++;
		}
		// 遍历集合数据，产生数据行
		Iterator<T> it = entityList.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
			for (i = 0; i < proList.size(); i++) {
				HSSFCell cell = row.createCell(i);
				String propertyName = proList.get(i);
				String textValue = null;
				try {
					textValue = this.getPropertyValue(t, propertyName);
				} catch (Exception e) {
					e.printStackTrace();
					this.error.append("第").append(index+1).append("行，列名：").append(param.get(propertyName)).append("，字段：").append(propertyName).append("，数据错误，跳过！").append("<br>");
				}
				// 利用正则表达式判断textValue是否全部由数字组成
				if (textValue != null) {
					Pattern p = Pattern.compile("^//d+(//.//d+)?$");
					Matcher matcher = p.matcher(textValue);
					if (matcher.matches()) {
						// 是数字当作double处理
						cell.setCellValue(Double.parseDouble(textValue));
					} else {
						HSSFRichTextString richString = new HSSFRichTextString(
								textValue);
						cell.setCellValue(richString);
					}
				}
			}

		}
		workbook.write(excel);
		workbook.close();
		return true;
	}

	/**
	 * 获取实体instance的propertyName属性的值
	 * @param instance
	 * @param propertyName
	 * @return
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InvocationTargetException
	 */
	private <T> String getPropertyValue(T instance, String propertyName)
			throws NoSuchMethodException, SecurityException,
			IllegalAccessException, IllegalArgumentException,
			InvocationTargetException {

		String getMethodName = this.initGetMethod(propertyName);
		Class<?> tCls = instance.getClass();
		Method getMethod = null;
		Object value = null;

		getMethod = tCls.getMethod(getMethodName, new Class[] {});
		value = getMethod.invoke(instance, new Object[] {});

		String returnType = getMethod.getReturnType().getName();

		// 判断值的类型后进行强制类型转换
		SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String textValue = null;
		if ("java.util.Date".equals(returnType)) {
			textValue = dateFormat.format(value);
		}else{
			textValue = value.toString();
		}
		return textValue;
	}
	/**
	 * 返回fiel属性的getXXX方法字符串
	 * @param field
	 * @return
	 */
	private String initGetMethod(String field) {
		return "get" + field.substring(0, 1).toUpperCase() + field.substring(1);
	}

	/**
	 * @return true 存在错误，false 不存在错误
	 */
	public boolean hasError() {
		return error.capacity() > 0;
	}

	/**
	 * 获得错误信息
	 * @return
	 */
	public StringBuffer getError() {
		return error;
	}
}

使用方式

//Servlet中设置返回类型
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//这里也可以通过前端传过来
LinkedHashMap<String, String> header = new LinkedHashMap<String, String>();
header.put("userCode", "用户编码");
header.put("userName", "用户名");
header.put("phone", "电话");
header.put("address", "地址");

//数据导出为excel
EntityListToExcel excel =  = new EntityListToExcel();
//这里的entityList为要转换为excel行的数据列表
excel.transform(header, entityList, response.getOutputStream());