java通过apache poi读取创建excel2003文件

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
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.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;


public class Excel2003Util<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<Region> regions, Collection<T> dataset, OutputStream out,
String pattern) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);


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


// 声明一个画图的顶级管理器
HSSFPatriarch 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));//指定合并区域


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


// 产生表格标题行
HSSFRow 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++) {
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(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++) {
HSSFCell 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 2003 excel
* 
* @throws IOException
* @throws FileNotFoundException
*/
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);
// 构造 HSSFWorkbook 对象，strPath 传入文件路径
HSSFWorkbook hwb = new HSSFWorkbook(is);
// 读取表格内容
for (int i = 0; i < hwb.getNumberOfSheets(); i++) {// 循环sheet
HSSFSheet childSheet = hwb.getSheetAt(i);
for (int j = 0; j <= childSheet.getLastRowNum(); j++) {// 循环该子sheet
HSSFRow row = childSheet.getRow(j); // row
if (row != null) {
if (cellLen == 0) {
cellLen = row.getLastCellNum();
}
List<Object> linked = new ArrayList<Object>(cellLen);
for (int k = 0; k < cellLen; k++) {// 循环该子sheet行对应的单元格项
HSSFCell cell = row.getCell(k);// 单元格
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_BLANK:
linked.add("");
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
linked.add(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:// 公式
linked.add(cell.getNumericCellValue());
break;
case HSSFCell.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 HSSFCell.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;
}


}