Skip to content

Instantly share code, notes, and snippets.

@winse
Created March 14, 2014 16:05
Show Gist options
  • Save winse/9550760 to your computer and use it in GitHub Desktop.
Save winse/9550760 to your computer and use it in GitHub Desktop.
将数据库的层级结构导出为excel
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));
}
}
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);
}
}
}
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);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment