public class ExportExcelUtil {
/**
* 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
* @param title 表格标题名
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param fieldNames 传入的对应headers属性列名数组的具体的字段名称,javabean风格,模式如:model.user.name
* @param out 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
* @param pattern 如果有时间数据,设定输出格式。默认为"yyy-MM-dd"
*/
public void exportExcel(String title, String[] headers, String[] fieldNames, List dataset,
OutputStream out, String pattern) {
headers = this.addString("序号", 0, headers);
int column = headers.length;
HSSFWorkbook hwb = POIExcelUtil.createWorkbook();// 创建一个excel工作单
HSSFSheet hs = hwb.createSheet(title);
HSSFPatriarch patriarch = hs.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4,
2, (short) 6, 5));
comment.setString(new HSSFRichTextString("提示:数据为单页数据!"));
comment.setAuthor("liuhonglai");
hs.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (column - 1))); // 合并单元格
int listSize = dataset != null ? dataset.size() : 0;
int rows = listSize + 2;// 定义总的行数
for (int i = 0; i < rows; i++) {// 循环创建行数
HSSFRow hr = POIExcelUtil.createRow(i, hs);// 根据查询的数据在sheet中创建行
for (int j = 0; j < column; j++) {// 循环列数
HSSFCell hc = POIExcelUtil.createCell(j, hr);// 创建列
if (i == 0) {// 控制第一行的样式
HSSFCellStyle cs = POIExcelUtil.createCellStyle(hwb);
HSSFFont font = POIExcelUtil.createFont(hwb);
font.setFontHeightInPoints((short) 14);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cs.setFont(font);// 设置字体
cs.setWrapText(true);
cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
cs.setBorderLeft(HSSFCellStyle.BORDER_NONE);// 左边框
cs.setBorderRight(HSSFCellStyle.BORDER_NONE);// 右边框
cs.setBorderTop(HSSFCellStyle.BORDER_NONE);// 上边框
cs.setAlignment(CellStyle.ALIGN_CENTER);// 设置水平对齐方式
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 设置垂直对齐方式
StringBuffer buffer = new StringBuffer();
buffer.append(title);
POIExcelUtil.setSheetTitle(hs, cs, hr, hc, buffer.toString()); // 设置标题
break;
}
if (i == 1) {// 控制第二行的样式,并设置列的名称
HSSFCellStyle cs = POIExcelUtil.createCellStyle(hwb);
hr.setHeightInPoints((float) 24);
HSSFFont font = POIExcelUtil.createFont(hwb);
cs.setFont(font);// 设置字体
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cs.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置单元格背景
cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
hc.setCellType(HSSFCell.CELL_TYPE_STRING);
hc.setCellStyle(cs);
hc.setCellValue(headers[j]);
} else {// 设置内容
if (dataset != null) {
switch (j) {
case 0: // 序号
hc.setCellValue(i - 1);
break;
default:
Object t = dataset.get(i - 2);// 去掉前两行
if (j == 2 || j == 3) {
hs.setColumnWidth((int) j, (int) (35.7 * 150));
} else {
hs.setColumnWidth((int) j, (int) (35.7 * 80));
}
String fieldName = fieldNames[j - 1];
Object value = null;
// String getMethodName = "get"+ fieldName.substring(0,
// 1).toUpperCase()+ fieldName.substring(1);
// Class tCls = t.getClass();
// try {
// Method getMethod = tCls.getMethod(getMethodName,new Class[] {});
// value = getMethod.invoke(t, new Object[] {});
// } catch (SecurityException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// } catch (IllegalArgumentException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// } catch (NoSuchMethodException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// } catch (IllegalAccessException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// } catch (InvocationTargetException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
value = getColumnValue(t, fieldName);
String textValue = null;
if (value instanceof Integer) {
int intValue = ((Integer) value).intValue();
textValue = String.valueOf(intValue);
} else if (value instanceof Float) {
float fValue = ((Float) value).floatValue();
textValue = String.valueOf(fValue);
} else if (value instanceof Double) {
double dValue = ((Double) value).doubleValue();
textValue = String.valueOf(dValue);
} else if (value instanceof Long) {
long longValue = ((Long) value).longValue();
textValue = String.valueOf(longValue);
} else if (value instanceof Boolean) {
boolean bValue = ((Boolean) value).booleanValue();
textValue = String.valueOf(bValue);
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else if (value instanceof byte[]) {
// 有图片时,设置行高为60px;
hr.setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
hs.setColumnWidth(i, (short) (35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255,
(short) 6, i - 1, (short) 6, i - 1);
anchor.setAnchorType(2);
patriarch.createPicture(anchor, hwb.addPicture(bsValue,
HSSFWorkbook.PICTURE_TYPE_JPEG));
} else {
// 其它数据类型都当作字符串简单处理
if (value == null) {
textValue = "";
} else {
textValue = value.toString();
}
}
hc.setCellValue(textValue);
break;
}
}
}
}
}
POIExcelUtil.setPrintSetup(hwb, hs, rows, column);
try {
hwb.write(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 递归方式处理.
* @param obj 传入的对象
* @param fileNames 有层级javabean方式,model.user.name
* @return Object 值
* @author david
*/
private static Object getColumnValue(Object obj, String fieldNames) {
if (null != fieldNames && !"".equals(fieldNames)) {
try {
Object objVal = null;
int sp = fieldNames.indexOf(".");
String fieldName = "";
String afterFieldNames = "";
if (sp == -1) {
fieldName = fieldNames;
} else {
fieldName = fieldNames.substring(0, sp);
afterFieldNames = fieldNames.substring(sp + 1, fieldNames.length());
}
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
Class classz = obj.getClass();
Method getMethod;
getMethod = classz.getMethod(getMethodName, new Class[] {});
objVal = getMethod.invoke(obj, new Object[] {});
if (StringUtils.isBlank(afterFieldNames)) {
return objVal;
} else {
return getColumnValue(objVal, afterFieldNames);
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
return null;
}
private String[] addString(String str, int index, String[] old) {
String[] temp = new String[old.length + 1];
System.arraycopy(old, 0, temp, 0, old.length);
for (int i = old.length; i > index; i--) {
temp[i] = temp[i - 1];
}
temp[index] = str;
return temp;
}
}