java通过apache poi 读取创建excel2007

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;


import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.hssf.util.CellRangeAddress;


public class Excel2007Util<T> {

/**
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
* 
* @param title
*            表格标题名
* @param headers
*            表格属性列名数组
* @param regions
*            要合并的单元格
* @param dataset
*            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
*            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param out
*            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
* @param pattern
*            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
@SuppressWarnings("unchecked")
public void write(String title, String[][] headers,
List<CellRangeAddress> regions, Collection<T> dataset, OutputStream out,
String pattern) {
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);


// 生成一个样式
XSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 生成一个字体
XSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
XSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style2.setBorderRight(XSSFCellStyle.BORDER_THIN);
style2.setBorderTop(XSSFCellStyle.BORDER_THIN);
style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
XSSFFont font2 = workbook.createFont();
font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);


// 声明一个画图的顶级管理器
XSSFDrawing patriarch = sheet.createDrawingPatriarch();


// 设置合并单元格
if (regions != null && regions.size() > 0) {
for (int i = 0; i < regions.size(); i++) {
sheet.addMergedRegion(regions.get(i));// 指定合并区域
}
}
// sheet.addMergedRegion(new Region(1,(short)0,3,(short)0));//指定合并区域


// 定义注释的大小和位置,详见文档
//patriarch.createCellComment(arg0)
XSSFComment comment = patriarch.createCellComment(new XSSFClientAnchor(0,
0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new XSSFRichTextString("可以在POI中添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("leno");


// 产生表格标题行
XSSFRow row = null;
for (int i = 0; i < headers.length; i++) {
row = sheet.createRow(i);
row.setHeight((short) 500);
for (short j = 0; j < headers[i].length; j++) {
XSSFCell cell = row.createCell(j);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(headers[i][j]);
cell.setCellValue(text);
sheet.setColumnWidth(
(short) j,
(short) (35.7 * headers[i][j].length() * 18 + 35.7 * 25));
}
}


// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = headers.length - 1;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,
new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Integer) {
int intValue = (Integer) value;
cell.setCellValue(intValue);
} else if (value instanceof Float) {
float fValue = (Float) value;
cell.setCellValue(fValue);
} else if (value instanceof Double) {
double dValue = (Double) value;
cell.setCellValue(dValue);
} else if (value instanceof Long) {
long longValue = (Long) value;
cell.setCellValue(longValue);
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
cell.setCellValue(textValue);
} else if (value instanceof String) {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
cell.setCellValue(textValue);
} else {
textValue = "";
cell.setCellValue(textValue);
}


} 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();
} finally {
// 清理资源
}
}


}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}


}


/**
* 读取Office 2007 excel
* 
* @param file
* @return
* @throws IOException
*/
public static List<List<Object>> read(File file, int cellLen) {
List<List<Object>> list = new ArrayList<List<Object>>();
FileInputStream is = null;


try {
is = new FileInputStream(file);
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
XSSFWorkbook xwb = new XSSFWorkbook(is);
// 读取表格内容
for (int i = 0; i < xwb.getNumberOfSheets(); i++) {// 循环sheet
XSSFSheet childSheet = xwb.getSheetAt(i);
for (int j = 0; j <= childSheet.getLastRowNum(); j++) {// 循环该子sheet
// row
XSSFRow row = childSheet.getRow(j);


if (row != null) {
if (cellLen == 0) {
cellLen = row.getLastCellNum();


}
List<Object> linked = new ArrayList<Object>(cellLen);
for (int k = 0; k < cellLen; k++) {// 循环该子sheet行对应的单元格项
XSSFCell cell = row.getCell(k);// 单元格
if (cell != null) {
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_BLANK:
linked.add("");
break;
case XSSFCell.CELL_TYPE_ERROR:
linked.add("");
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
linked.add(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_FORMULA:// 公式
linked.add(cell.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil
.isCellDateFormatted(cell)) {
linked.add(cell.getDateCellValue());
} else {
linked.add(Double.valueOf(
cell.getNumericCellValue())
.longValue());
}
break;
case XSSFCell.CELL_TYPE_STRING:
linked.add(cell.getRichStringCellValue());
break;
default:
linked.add(cell);
}
} else {
linked.add("");
}
}


list.add(linked);


}


}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (is != null) {
is.close();
}
} catch (Exception e) {
}
}


return list;
}




}