将数据库的层级结构导出为excel

BaseExcelExportHandler.java

package com.gdcn.bpaf.urms.web.handle;

import java.io.Closeable;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import com.gdcn.bpaf.common.helper.StringHelper;

import jxl.Cell;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * 
 * @author LFQ
 * @version 2013-7-19 上午11:59:12
 *
 */
public class BaseExcelExportHandler<U> implements Closeable {

	public static final String CHARSET = "GBK";
	
	private final OutputStream out;
	protected WritableWorkbook workbook;
	
	public BaseExcelExportHandler(OutputStream out) {
		this.out = out;
		// lazy create workbook
	}

	public static void configCommonCellFormat(WritableCellFormat format) throws WriteException {
		// bodyFormat.setAlignment(jxl.format.Alignment.CENTRE);
		format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
		format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
	}

	private WritableCellFormat bodyFormat;

	protected WritableCellFormat getBodyFormat() throws WriteException {
		if (bodyFormat == null) {
			// 设置内容的格式
			WritableFont fontBody = new WritableFont(WritableFont.createFont(CHARSET), 10);
			WritableCellFormat bodyFormat = new WritableCellFormat(fontBody);
			configCommonCellFormat(bodyFormat);
	
			this.bodyFormat = bodyFormat;
		}
		return this.bodyFormat;
	}

	public static void writeHeaderAndSetColumnWidth(WritableSheet sheet, String[] headers) throws WriteException {
		if (headers == null) {
			return;
		}
	
		// 标题格式
		WritableFont fontHead = new WritableFont(WritableFont.createFont(CHARSET), 12, WritableFont.BOLD);
		WritableCellFormat formatHead = new WritableCellFormat(fontHead);
		configCommonCellFormat(formatHead);
		formatHead.setAlignment(jxl.format.Alignment.CENTRE);
	
		for (int i = 0; i < headers.length; i++) {
			Label label = new Label(i, 0, headers[i], formatHead);
			sheet.addCell(label);
	
			sheet.setColumnView(i, 40);
		}
	}

	protected void fillBlankCell(WritableSheet sheet, int maxColumn, int maxRow) throws WriteException {
		for (int i = 0; i < maxColumn; i++) {
			for (int j = 0; j < maxRow; j++) {
				Cell cell = sheet.getCell(i, j);
				if (cell == null || cell.getContents().isEmpty()) {
					sheet.addCell(createBlankCell(i, j));
				}
			}
		}
	}

	WritableCell createBlankCell(int column, int row) throws WriteException {
		return new Label(column, row, " ", getBodyFormat());
	}
	
	/**
	 * 第一个sheet从0开始
	 */
	protected int getNextSheetIndex() {
		return workbook.getNumberOfSheets();
	}

	/**
	 * 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象 <br>
	 * 使用这种方式创建的Workbook需要自己关闭out
	 */
	protected void createWorkbookIfNeed() throws WriteException, IOException {
		if (workbook == null) {
			workbook = Workbook.createWorkbook(out);
		}
	}

	public void close() throws IOException {
		if (workbook != null)
			workbook.write();
		out.flush();
		
		if (workbook != null)
			workbook.close();
		out.close();
	}
	
	private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 把对象序列化为字符串
	 * 处理时间类型 & .
	 */
	protected String stringValueOf(Object object) {
		// 包括了java.sql.Date
		if (object instanceof java.util.Date) {
			String wrapper = DATE_FORMAT.format((java.util.Date) object);
			return stringValueOf(wrapper);
		} else if (object instanceof Calendar) {
			Date wrapper = ((Calendar) object).getTime();
			return stringValueOf(wrapper);
		}

		return StringHelper.convertStringNull(String.valueOf(object));
	}

}

ExcelCommonExportHandler.java

package com.gdcn.bpaf.urms.web.handle;

import java.io.Closeable;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.LinkedHashMap;
import java.util.Set;

import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WriteException;

/**
 * 根据用户指定的字段和顺序导出表格形式
 * 
 * @author LFQ
 * @version 2013-7-19 下午4:11:15
 * 
 */
public class ExcelCommonExportHandler<U> extends BaseExcelExportHandler<U> implements Closeable {

	public ExcelCommonExportHandler(OutputStream out) {
		super(out);
	}

	/**
	 * @param headers <filed, caption>
	 */
	public void execute(String mdmtype, String mdmtypeCaption, U[] entityArr, LinkedHashMap<String, String> headers) throws Exception {
		createWorkbookIfNeed();

		WritableSheet sheet = workbook.createSheet(mdmtypeCaption + "(" + mdmtype + ")" + "-主数据", getNextSheetIndex());

		writeHeaderAndSetColumnWidth(sheet, headers.values().toArray(new String[0]));

		int row = 1;
		for(U entity : entityArr){
			handleElement(entity, headers.keySet(), row, sheet);
			row ++ ;
		}

		fillBlankCell(sheet, sheet.getColumns(), sheet.getRows());
	}

	private void handleElement(U entity, Set<String> fields, int row, WritableSheet sheet) throws WriteException {
		int column = 0;
		for(String field : fields) {
			sheet.addCell(createCell(entity, field, column, row));
			column ++ ;
		}
	}

	private WritableCell createCell(U model, String field, int column, int row) throws WriteException {
		return new Label(column, row, getPropertyText(model, field), getBodyFormat());
	}

	private String getPropertyText(U model, String field) {
		return stringValueOf(getProperty(model, field));
	}
	
	// ////////////////////
	//
	// 反射获取字段属性值
	//
	// ////////////////////

	private static Object getProperty(Object entity, String property) {
		try {
			Field field = entity.getClass().getDeclaredField(property);
			field.setAccessible(true);
			return field.get(entity);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

}

ExcelTreeExportHandler.java

package com.gdcn.bpaf.urms.web.handle;

import java.io.Closeable;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * 树形导出
 * 
 * @author LFQ
 * @version 2013-7-9 下午4:11:15
 * 
 */
public class ExcelTreeExportHandler<U> extends BaseExcelExportHandler<U> implements Closeable {

	// 不太可能超过十级咯！
	private static final String[] CHINESE_NUMBER = { "一", "二", "三", "四", "五", "六", "七", "八", "九", "十" };

	public ExcelTreeExportHandler(OutputStream out) {
		super(out);
	}

	/**
	 * @param mdmtype 该类型的root根元素的id！即第一层元素的parentid的值，同时也是mdmtype的值。
	 */
	public void execute(String mdmtype, String mdmtypeCaption, U[] entityArr) throws Exception {
		createWorkbookIfNeed();

		WritableSheet sheet = workbook.createSheet(mdmtypeCaption + "(" + mdmtype + ")" + "-主数据", getNextSheetIndex());

		List<U> roots = getRoots(entityArr, mdmtype);
		Map<String, Entry<U>> collector = buildCollector(entityArr);
		Cursor cursor = new Cursor();
		handleTreeElements(roots, collector, cursor, sheet);

		// 用于写第几层标题, column从0开始
		int maxColumn = sheet.getColumns();
		List<String> headers = new ArrayList<String>(maxColumn);
		for (int i = 0; i < maxColumn; i++) {
			headers.add("第" + CHINESE_NUMBER[i] + "层");
		}
		writeHeaderAndSetColumnWidth(sheet, headers.toArray(new String[0]));

		fillBlankCell(sheet, sheet.getColumns(), sheet.getRows());
	}

	private void handleTreeElements(List<U> elements, Map<String, Entry<U>> collector, Cursor cursor, WritableSheet sheet)
			throws RowsExceededException, WriteException {
		if (elements.size() == 0) {
			return;
		}

		for (U ele : elements) {
			Entry<U> entry = collector.get(getObjectResourceid(ele));

			sheet.addCell(createCell(entry.getModel(), cursor.column, cursor.row));

			Cursor childrenCursor = new Cursor(cursor);
			handleTreeElements(entry.getChildren(), collector, childrenCursor, sheet);

			// 合并！
			if (cursor.row != childrenCursor.row)
				sheet.mergeCells(cursor.column, cursor.row, cursor.column, childrenCursor.row);

			// @修复！最后一个元素是不需要加1的，在循环完后，进行修复！
			cursor.row = childrenCursor.row + 1;
		}
		// @修复@！循环完成后，需要减去1！
		cursor.row--;
	}

	private static class Cursor {
		public int column = 0;
		public int row = 1; // 注意rowindex=0(第一列)为标题

		public Cursor() {
		}

		/**
		 * 在父节点的基础上column+1
		 */
		public Cursor(Cursor last) {
			this.column = last.column + 1;
			this.row = last.row;
		}

		@Override
		public String toString() {
			return "{column=" + column + ", row=" + row + "}";
		}
	}

	private WritableCell createCell(U model, int column, int row) throws WriteException {
		return new Label(column, row, getText(model), getBodyFormat());
	}

	private String getText(U model) {
		return getObjectDirectoryname(model) + "(" + getObjectDirectorytype(model) + ")";
	}

	/**
	 * INTERNAL
	 */
	private List<U> getRoots(U[] entities, String rootName) {
		List<U> first = new ArrayList<U>();

		for (U entity : entities) {
			if (rootName.equals(getObjectParentid(entity))) {
				first.add(entity);
			}
		}

		return first;
	}

	/**
	 * INTERNAL
	 * @return map<resourceid, childrenEntry>
	 */
	private Map<String, Entry<U>> buildCollector(U[] entities) {
		Map<String, Entry<U>> collector = new HashMap<String, Entry<U>>(entities.length);

		for (U entity : entities) {
			collector.put(getObjectResourceid(entity), new Entry<U>(entity));
		}

		for (U entity : entities) {
			Entry<U> parentEntry = collector.get(getObjectParentid(entity));

			// 第一层元素没有parent
			if (parentEntry == null)
				continue;

			parentEntry.addChild(entity);
		}

		return collector;
	}

	private static class Entry<E> {
		private final E model;
		// 有序！先进先出！
		private final List<E> children;

		public Entry(E model) {
			this.model = model;
			this.children = new ArrayList<E>();
		}

		public E getModel() {
			return model;
		}

		public List<E> getChildren() {
			return Collections.unmodifiableList(children);
		}

		public void addChild(E child) {
			this.children.add(child);
		}

	}

	// ////////////////////
	//
	// 反射获取字段属性值
	//
	// ////////////////////

	private String getObjectParentid(U entity) {
		return com.gdcn.bpaf.common.helper.DictTypeHelper.getPid(entity);
	}

	private String getObjectResourceid(U entity) {
		return com.gdcn.bpaf.common.helper.DictTypeHelper.getId(entity);
	}

	private String getObjectDirectorytype(U entity) {
		return com.gdcn.bpaf.common.helper.DictTypeHelper.getCode(entity);
	}

	private String getObjectDirectoryname(U entity) {
		return com.gdcn.bpaf.common.helper.DictTypeHelper.getName(entity);
	}

}