基于jxl导入、导出excel

前段时间接手了一个ERP项目，其他功能基本不多，就导入导出excel就非常多，都是相对简单的，所以花了点时间写了个导入导出类。

如有高点，望多多指教，共勉！

话不多说，直接上代码。

注：需要导入jxl.jar到项目的classpath。

导入：

public class DataImport {

	private final Logger logger = LoggerFactory.getLogger(getClass());

	public <E> Collection<E> importData(Class<E> z, InputStream is) {
		Workbook wb = null;
		try {
			wb = Workbook.getWorkbook(is);//创建workbook实例
			Sheet sheet = wb.getSheet(0);//获取第一页sheet
			int rowNum = sheet.getRows();//行数
			int colNum = sheet.getColumns();//列数
			Collection<E> coll = new ArrayList<E>();
			for (int j = 1; j < rowNum; j++) { //这里从第二行开始获取，第一行一般为header
				E newInstance = z.newInstance();
				for (int k = 0; k < colNum; k++) {
					Cell cell = sheet.getCell(k, j);
					String fieldName = sheet.getCell(k, 1).getContents().trim()
							.toString();
					if (!fieldName.equals("")) {
						String setMethodName = "set" + fieldName;
						Method setMethod = z.getDeclaredMethod(setMethodName,
								String.class);
						if(!cell.getContents().trim()
								.toString().equals("")){
							setMethod.invoke(newInstance, cell.getContents().trim()
									.toString());
						}

					}

				}
				coll.add(newInstance);
			}
			return coll;

		} catch (Exception e) {
			logger.error("error occured", e);
		} finally {
			wb.close();
		}
		return null;
	}
}

导出：

package com.galaxylab.huanwei.tool;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class DataExport {
	
	
	/**
	 * 公共导出excel方法
	 * @param filename: 导出excel文件名
	 * @param sheetName: sheet名
	 * @param header: 表头和属性名("用户#user")，属性名必须和实体类一致
	 * @param coll: 数据collection
	 * @param response: HttpServletResponse response
	 * @Author xing
	 */
	public <E> void exportExcel(String filename,String sheetName,String[] header,Collection<E> coll,HttpServletResponse response) {
		
		try {
			response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"),"iso8859-1") + ".xls");
			response.setContentType("vnd.ms-excel");
			OutputStream out = response.getOutputStream();
			
			WritableWorkbook workbook = Workbook.createWorkbook(out);
			WritableSheet sheet = workbook.createSheet(sheetName, 0);
			Label label = null;
			
			ArrayList titleName = new ArrayList<String>();
			ArrayList attributeName = new ArrayList<String>();
			
			for (int i = 0; i < header.length; i++) { //这里把header作为表格的表头和get属性
				String[] str = header[i].split("#");
				titleName.add(str[0]);
				attributeName.add(str[1]);
			}
			
			for (int i = 0; i < titleName.size(); i++) { //把表头填上
				label = new Label(i, 0, (String) titleName.get(i));
				try {
					sheet.addCell(label);
				} catch (RowsExceededException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (WriteException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			for (int i = 0; i < coll.size(); i++) { //填充数据
				E obj = (E) coll.toArray()[i];
				
				for (int j = 0; j < attributeName.size(); j++) {
					String name = (String) attributeName.get(j);
					String value = null;
					try {
						value = (String) getValue(obj, name, "class java.lang.String"); //获取数据值
					} catch (NoSuchMethodException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (SecurityException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (IllegalAccessException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (IllegalArgumentException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (InvocationTargetException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					label = new Label(j,i+1,value);
					sheet.addCell(label);
				}
			}
			workbook.write();
			workbook.close();
			
			out.flush();
			out.close();
			
		} catch (IOException | WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public <E> Object getValue(E obj,String name,String type) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
		
		name = name.substring(0, 1).toUpperCase() + name.substring(1);
		
		
		if (type.equals("class java.lang.String")) {  
            Method m = obj.getClass().getMethod("get" + name);  
            // 调用getter方法获取属性值  
            String value = (String) m.invoke(obj);  
            if (value != null) {  
                return value;
            } else {  
                return null;
            }  
		}  
	    if (type.equals("class java.lang.Integer")) {  
	        Method m = obj.getClass().getMethod("get" + name);  
	        Integer value = (Integer) m.invoke(obj);  
	        if (value != null) {  
                return value;
	        } else {  
                return null;
	        }  
	    }  
	    if (type.equals("class java.lang.Short")) {  
	        Method m = obj.getClass().getMethod("get" + name);  
	        Short value = (Short) m.invoke(obj);  
	        if (value != null) {  
                return value;
	        } else {  
                return null;
	        }  
	    }  
	    if (type.equals("class java.lang.Double")) {  
	        Method m = obj.getClass().getMethod("get" + name);  
	        Double value = (Double) m.invoke(obj);  
	        if (value != null) {  
                return value;
	        } else {  
                return null;
	        }  
	    }  
	    if (type.equals("class java.lang.Boolean")) {  
	        Method m = obj.getClass().getMethod("get" + name);  
	        Boolean value = (Boolean) m.invoke(obj);  
	        if (value != null) {  
                return value;
	        } else {  
                return null;
	        }  
	    }  
	    if (type.equals("class java.util.Date")) {  
	        Method m = obj.getClass().getMethod("get" + name);  
	        Date value = (Date) m.invoke(obj);  
	        if (value != null) {  
                return value;
	        } else {  
                return null;
	        }  
	    } 
		return null;
	}
}