public class PoiUtil {
/**
* 初始化列标题
*
* @param titleRow poi行对象
* @param colNames 列名称
*/
public static void initTitle(HSSFRow titleRow,String colNames[]) {
HSSFCell colCell = null;
for(int i=0,len=colNames.length; i<len ; i++) {
short cellIndex = (short) (i);
colCell = titleRow.createCell(cellIndex);
colCell.setCellValue(new HSSFRichTextString(colNames[i]));
}
}
/**
* 生成业务数据,并输出文件流
*
* @param colNames 列名称
* @param proNames bean属性:控制数据输出顺序
* @param dataList 业务数据
* @param sheetName sheet名称
* @return
* @throws Exception
*/
public static InputStream WriteXls(String colNames[],String beanPros[],List dataList,String sheetName) throws Exception{
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet(sheetName);
HSSFRow titleRow = sheet.createRow(0);
//填充列标题
initTitle(titleRow,colNames);
//填充业务数据
writeData(beanPros,dataList,sheet);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
hwb.write(baos);
ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray());
baos.close();
return bais;
}
/**
* 填充业务数据
*
* @param beanPros bean属性:控制数据输出顺序
* @param dataList 业务数据
* @param sheet sheet
* @throws Exception
*/
public static void writeData(String beanPros[],List dataList,HSSFSheet sheet) throws Exception{
for(int i=0,len=dataList.size();i<len;i++ ) {
HSSFRow dataRow = sheet.createRow(i+1);
//插入序号
HSSFCell dataCell = dataRow.createCell((short)0);
dataCell.setCellValue((i+1));
Object dataObj = dataList.get(i);
writeDataRow(beanPros,dataObj,dataRow);
}
}
/**
*
* @param proNames
* @param dataObj
* @param dataRow
*/
public static void writeDataRow(String proNames[],Object dataObj,HSSFRow dataRow) {
HSSFCell dataCell = null;
for(int i=0,len=proNames.length; i<len; i++) {
String proName = proNames[i];
short cellIndex = (short) (i+1);
dataCell = dataRow.createCell(cellIndex);
if(BeanUtil.hasPropertyName(dataObj,proName)) {
Class proClass = BeanUtil.getProperyClass(dataObj, proName);
Object propertyValue = BeanUtil.getPropertyValue(dataObj, proName);
if(proClass == String.class) {
dataCell.setCellValue(new HSSFRichTextString((String)propertyValue));
} else if(proClass == Date.class) {
Date date = (Date)propertyValue;
String proValue = DateFormatUtilsWrapper.format(date, "yyyy/MM/dd");
dataCell.setCellValue(new HSSFRichTextString(proValue));
}
}
}
}
}
/**
* 初始化列标题
*
* @param titleRow poi行对象
* @param colNames 列名称
*/
public static void initTitle(HSSFRow titleRow,String colNames[]) {
HSSFCell colCell = null;
for(int i=0,len=colNames.length; i<len ; i++) {
short cellIndex = (short) (i);
colCell = titleRow.createCell(cellIndex);
colCell.setCellValue(new HSSFRichTextString(colNames[i]));
}
}
/**
* 生成业务数据,并输出文件流
*
* @param colNames 列名称
* @param proNames bean属性:控制数据输出顺序
* @param dataList 业务数据
* @param sheetName sheet名称
* @return
* @throws Exception
*/
public static InputStream WriteXls(String colNames[],String beanPros[],List dataList,String sheetName) throws Exception{
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet(sheetName);
HSSFRow titleRow = sheet.createRow(0);
//填充列标题
initTitle(titleRow,colNames);
//填充业务数据
writeData(beanPros,dataList,sheet);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
hwb.write(baos);
ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray());
baos.close();
return bais;
}
/**
* 填充业务数据
*
* @param beanPros bean属性:控制数据输出顺序
* @param dataList 业务数据
* @param sheet sheet
* @throws Exception
*/
public static void writeData(String beanPros[],List dataList,HSSFSheet sheet) throws Exception{
for(int i=0,len=dataList.size();i<len;i++ ) {
HSSFRow dataRow = sheet.createRow(i+1);
//插入序号
HSSFCell dataCell = dataRow.createCell((short)0);
dataCell.setCellValue((i+1));
Object dataObj = dataList.get(i);
writeDataRow(beanPros,dataObj,dataRow);
}
}
/**
*
* @param proNames
* @param dataObj
* @param dataRow
*/
public static void writeDataRow(String proNames[],Object dataObj,HSSFRow dataRow) {
HSSFCell dataCell = null;
for(int i=0,len=proNames.length; i<len; i++) {
String proName = proNames[i];
short cellIndex = (short) (i+1);
dataCell = dataRow.createCell(cellIndex);
if(BeanUtil.hasPropertyName(dataObj,proName)) {
Class proClass = BeanUtil.getProperyClass(dataObj, proName);
Object propertyValue = BeanUtil.getPropertyValue(dataObj, proName);
if(proClass == String.class) {
dataCell.setCellValue(new HSSFRichTextString((String)propertyValue));
} else if(proClass == Date.class) {
Date date = (Date)propertyValue;
String proValue = DateFormatUtilsWrapper.format(date, "yyyy/MM/dd");
dataCell.setCellValue(new HSSFRichTextString(proValue));
}
}
}
}
}