jxl导出excel数据工具类

package com.yihaodian.tms.framework.util;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import scm.common.util.StringUtils;

import com.yihaodian.tms.master.model.SysEnumerate;


public class ExcelExportUtil<E> {

	public static final Log log = LogFactory.getLog(ExcelExportUtil.class);

	/* Exce文件后缀 */
	public static final String EXECL_FILE_SUFFIX = ".xls";

	/* 需要导出的list */
	protected List<E> list;

	/* 导出文件存放目录 */
	protected String dir;

	/* 文件名(不包含后缀) */
	protected String fileNamePrefix;

	/* 标题 */
	protected String title;

	/* 副标题（可选） */
	protected String subTitle;

	/* 需要导出的列  */
	protected Column[] columns;

	/* 标题样式 */
	protected WritableCellFormat titleFormat;

	/* 副标题样式 */
	protected WritableCellFormat subTitleFormat;

	/* 列数据样式 */
	protected WritableCellFormat[] columnFormat;

	/* 普通默认样式 */
	protected WritableCellFormat normalFormat;

	/* 标题样式 */
	protected WritableCellFormat headerFormat;

	/* 列宽 */
	protected Integer[] columnWidth;

	/* 分栏数目 */
	protected int subfieldNum = 1;

	/* 当前行号 */
	protected int currentRowNum = 0;

	/* 时间格式 */
	protected String dateFormat = "yyyy/MM/dd HH:mm:ss";

	protected Map dataDict = null;

	/**
	 * 
	 * @param list 需要导出的列表
	 * @param dir 导出文件的存放目录
	 * @param fileNamePrefix 文件名（不含后缀）
	 * @param title 标题
	 * @param columns 列
	 * @throws WriteException
	 */
	public ExcelExportUtil(List<E> list, String dir, String fileNamePrefix, String title, Column[] columns) throws WriteException {
		this.list = list;
		this.dir = dir;
		this.fileNamePrefix = fileNamePrefix;
		this.title = title;
		this.columns = columns;
		File dirFile = new File(this.dir);
		if (!dirFile.exists()) {
			dirFile.mkdirs();
		}
	}
	/**
	 * 
	 * @param list 需要导出的列表
	 * @param dir 导出文件的存放目录
	 * @param fileNamePrefix 文件名（不含后缀）
	 * @param title 标题
	 * @param columns 列
	 * @throws WriteException
	 */
	public ExcelExportUtil(List<E> list, String dir, String fileNamePrefix, String title, Column[] columns, Map dataDict) throws WriteException {
		this(list, dir,fileNamePrefix,title,columns);
		this.dataDict = dataDict;
	}

	/**
	 * 
	 * <pre>
	 * 导出excel并返回文件名(包含路径)
	 * </pre>
	 *
	 * @param withIndex 是否需要在每条记录前加上序号
	 * @return
	 * @throws IOException
	 * @throws WriteException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 */
	public String exportToExcel(boolean withIndex) throws IOException, WriteException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {

		// 初始化单元格样式
		initCellFormat();

		// 创建文件
		String fileName = dir + File.separatorChar + fileNamePrefix + EXECL_FILE_SUFFIX;
		File file = new File(fileName);
		WritableWorkbook book = null;
		try {
			book = Workbook.createWorkbook(file);
			WritableSheet sheet = book.createSheet("Sheet_1", 0);

			// 设置列宽
			if (columnWidth != null) {
				for (int i = 0; i < columnWidth.length * subfieldNum; i++) {
					sheet.setColumnView(i, columnWidth[i % columnWidth.length]);
				}
			}
			// 写入标题
			exportTitle(sheet, withIndex);
			// 写入表头
			exportTableHeader(sheet, withIndex);
			// 写入数据
			exportData(sheet, withIndex);
			book.write();
		} catch (IOException e) {
			log.error("创建文件出错：", e);
		} catch (WriteException e) {
			log.error("写文件出错：", e);
		} catch (RuntimeException e) {
			log.error("运行时错误", e);
        } catch (NoSuchFieldException e) {
        	log.error("无此属性", e);
        } finally {
			if (book != null) {
				book.close();
			}
		}

		return fileName;
	}

	/**
	 * <pre>
	 * 导出excel并返回字节数组
	 * </pre>
	 * @param withIndex
	 * @return
	 * @throws IOException
	 * @throws WriteException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 */
	public byte[] exportExcelToByteArray(boolean withIndex) throws IOException, WriteException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {

		// 初始化单元格样式
		initCellFormat();

		  // 创建工作薄
	    ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
		WritableWorkbook book = null;
		try {
			book = Workbook.createWorkbook(byteOut);
			WritableSheet sheet = book.createSheet("Sheet_1", 0);

			// 设置列宽
			if (columnWidth != null) {
				for (int i = 0; i < columnWidth.length * subfieldNum; i++) {
					sheet.setColumnView(i, columnWidth[i % columnWidth.length]);
				}
			}
			// 写入标题
			exportTitle(sheet, withIndex);
			// 写入表头
			exportTableHeader(sheet, withIndex);
			// 写入数据
			exportData(sheet, withIndex);
			book.write();
		} catch (IOException e) {
			log.error("创建文件出错：", e);
		} catch (WriteException e) {
			log.error("写文件出错：", e);
		} catch (RuntimeException e) {
			log.error("运行时错误", e);
        } catch (NoSuchFieldException e) {
        	log.error("无此属性", e);
        } finally {
			if (book != null) {
				book.close();
			}
		}

        return  byteOut.toByteArray();//将输出流转换成字节数组
	}

	/**
	 * 
	 * <pre>
	 * 写入标题
	 * </pre>
	 *
	 * @param sheet
	 * @param tableWidth 表格宽度
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	protected void exportTitle(WritableSheet sheet, boolean witdhIndex) throws RowsExceededException, WriteException {
		int tableWidth = 0;
		// 计算表格宽度
		if (witdhIndex) {
			tableWidth = (columns.length + 1) * subfieldNum;
		} else {
			tableWidth = columns.length * subfieldNum;
		}
		// 写入标题（如果有）
		if (this.title != null) {
		    sheet.mergeCells(0, currentRowNum, tableWidth - 1, currentRowNum);
		    Label titleLb = new Label(0, currentRowNum++, title, titleFormat);
		    sheet.addCell(titleLb);
        }

        // 写入副标题（如果有）
        if (this.subTitle != null) {
            sheet.mergeCells(0, currentRowNum, tableWidth - 1, currentRowNum);
            Label subTitleLb = new Label(0, currentRowNum++, subTitle, subTitleFormat);
            sheet.addCell(subTitleLb);
        }
	}

	/**
	 * 
	 * <pre>
	 * 写入表头
	 * </pre>
	 *
	 * @param sheet
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	protected void exportTableHeader(WritableSheet sheet, boolean withIndex)
			throws RowsExceededException, WriteException {
		for (int i = 0; i < subfieldNum; i++) {
			if (withIndex) {
				Label indexName = new Label((columns.length + 1) * i,
						currentRowNum, "序号", headerFormat);
				sheet.addCell(indexName);
				for (int j = (columns.length + 1) * i + 1; j < (columns.length + 1)
						* (i + 1); j++) {
					Label columnHead = new Label(j, currentRowNum,
							this.columns[j % (columns.length + 1) - 1]
									.getColName(), headerFormat);
					sheet.addCell(columnHead);
				}
			} else {
				for (int j = columns.length * i; j < columns.length * (i + 1); j++) {
					String content = this.columns[j % columns.length]
							.getColName();
					Label columnHead = new Label(j, currentRowNum, content,
							headerFormat);
					sheet.addCell(columnHead);
				}
			}

		}
	}

	/**
	 * 
	 * <pre>
	 * 写入数据
	 * </pre>
	 *
	 * @param sheet
	 * @param widthIndex 是否需要在每条记录前加上序号
	 * @throws NoSuchMethodException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws WriteException
	 * @throws NoSuchFieldException 
	 * @throws SecurityException 
	 */
	protected void exportData(WritableSheet sheet, boolean widthIndex) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, WriteException, SecurityException, NoSuchFieldException {
		// 写入数据
		if (list == null || list.isEmpty())
			return;
		// ----------------取得数据并添加到excel中------------------
		// 记录条数
		Integer count = 0;
		// 列号
		int columnNum = 0;

		for (E element : list) {
			if (count % subfieldNum == 0) {
				// 换行
				currentRowNum++;
				// 列号置0
				columnNum = 0;
			}
			count++;
			// 写入序号
			if (widthIndex) {
				Label index = new Label(columnNum++, currentRowNum,
						count.toString(), normalFormat);
				sheet.addCell(index);
			}
			for (int i = 0; i < columns.length; i++) {
				String colContent = "";
				Object obj =null;
				String field = columns[i].getColField();

				//递归获取对象多级关联的字段值   重构 by zhaowen
				obj = getObjectByField(field, element);
				if (obj != null) {
					if (obj instanceof Date) {
						Date date = (Date) obj;
						WritableCellFormat defaultDateFormat =  new WritableCellFormat(DateFormats.FORMAT9);
						defaultDateFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
						defaultDateFormat.setAlignment(Alignment.CENTRE);
						DateTime labelDT = new DateTime(columnNum++, currentRowNum, date, defaultDateFormat);
						sheet.addCell(labelDT);
						continue;
					} else {
						//做数据 值->名称 转换
						if(columns[i].getColType()!=null){
							colContent = convertValue2Name(obj,columns[i].getColType());
						}else{
							colContent = obj.toString();
						}
					}
				}
				Label columnData = new Label(columnNum++, currentRowNum,
						colContent, columnFormat[i]);
				sheet.addCell(columnData);
			}
		}
	}

	/**
	 * 递归获取对象多级关联的字段值
	 * by zhaowen
	 * @param field
	 * @param target
	 * @return
	 * @throws NoSuchMethodException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 * @throws SecurityException 
	 * @throws IllegalArgumentException 
	 * @throws Exception
	 */
	private Object getObjectByField(String field, Object target) throws IllegalArgumentException, SecurityException, IllegalAccessException, InvocationTargetException, NoSuchMethodException{
		if(StringUtils.isEmpty(field)||target==null){
			return null;
		}
		if(field.contains(".")){
			String subObjectName = null;
			int split=field.indexOf(".");
			subObjectName = field.substring(0,split);
			String getSubObjMethod="get" + StringUtils.upperCaseFirstCharacter(subObjectName);
            Object subObject=target.getClass().getMethod(getSubObjMethod, new Class[] {}).invoke(target);
            return getObjectByField(field.substring(split+1), subObject);
		}
		else{
			String getSubObjMethod="get" + StringUtils.upperCaseFirstCharacter(field);
            return target.getClass().getMethod(getSubObjMethod, new Class[] {}).invoke(target);
		}
	}


	/**
	 * 做数据 值->名称 转换
	 * @param obj
	 * @param dataType
	 * @return
	 */
	private String convertValue2Name(Object obj,String dataType){
		Map<Integer,SysEnumerate> typeMap = (Map<Integer,SysEnumerate>)this.dataDict.get(dataType);
		return typeMap.get(obj).getEnumValue();
	}

	/**
	 * 
	 * <pre>
	 * 列对象
	 * </pre>
	 *
	 * @author gujinrong
	 * @version $Id: ExcelExporter.java, v 0.1 2011-11-7 下午04:38:08 gujinrong Exp $
	 */
	public static class Column {

		private String colField;

		private String colName;

		private String colType;

		public Column(String colField, String colName) {
			this.colField = colField;
			this.colName = colName;
		}
		public Column(String colField, String colName, String colType) {
			this.colField = colField;
			this.colName = colName;
			this.colType = colType;
		}

		public String getColType() {
			return colType;
		}

		public void setColType(String colType) {
			this.colType = colType;
		}

		public String getColField() {
			return colField;
		}

		public void setColField(String colField) {
			this.colField = colField;
		}

		public String getColName() {
			return colName;
		}

		public void setColName(String colName) {
			this.colName = colName;
		}
	}

	/**
	 * 
	 * <pre>
	 * 单元格格式初始化
	 * </pre>
	 *
	 * @throws WriteException
	 */
	private void initCellFormat() throws WriteException {

		// 设置默认单元格格式
		if (normalFormat == null) {
			normalFormat = new WritableCellFormat();
			normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
			normalFormat.setAlignment(Alignment.CENTRE);
		}

		// 设置默认标题格式
		if (titleFormat == null) {
			WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
					15);
			titleFormat = new WritableCellFormat(font);
			titleFormat.setAlignment(Alignment.CENTRE);
			titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN,
					Colour.BLACK);
		}

		// 设置默认副标题格式
		if (subTitleFormat == null) {
			subTitleFormat = normalFormat;
		}

		// 设置默认副标题格式
		if (headerFormat == null) {
			headerFormat = normalFormat;
		}

		// 设置默认列数据格式
		if (columnFormat == null) {
			columnFormat = new WritableCellFormat[columns.length];
			for (int i = 0; i < columns.length; i++) {
				columnFormat[i] = normalFormat;
			}
		}
	}

	/**
	 * 
	 * <pre>
	 *  设置某一列数据的格式
	 * </pre>
	 *
	 * @param format
	 * @param index
	 */
	public void setColumnFormatWithIndex(WritableCellFormat format, int index) {
		if (columnFormat == null) {
			columnFormat = new WritableCellFormat[columns.length];
			for (int i = 0; i < columns.length; i++) {
				columnFormat[i] = normalFormat;
			}
		}
		columnFormat[index] = format;
	}


	// --------------------------get/set方法-------------------------
	public void setSubTitle(String subTitle) {
		this.subTitle = subTitle;
	}

	public void setTitleFormat(WritableCellFormat titleFormat) {
		this.titleFormat = titleFormat;
	}

	public void setSubTitleFormat(WritableCellFormat subTitleFormat) {
		this.subTitleFormat = subTitleFormat;
	}

	public void setColumnFormat(WritableCellFormat[] columnFormat) {
		this.columnFormat = columnFormat;
	}

	public void setNormalFormat(WritableCellFormat normalFormat) {
		this.normalFormat = normalFormat;
	}

	public void setColumnWidth(Integer[] columnWidth) {
		this.columnWidth = columnWidth;
	}

	public void setSubfieldNum(int subfieldNum) {
		this.subfieldNum = subfieldNum;
	}

	public void setHeaderFormat(WritableCellFormat headerFormat) {
		this.headerFormat = headerFormat;
	}

	public void setDateFormat(String dateFormat) {
		this.dateFormat = dateFormat;
	}

}