POI導出Excel経典実現2
11969 ワード
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;
}
}