生成Excel效果图如下:
[img]http://dl.iteye.com/upload/picture/pic/94896/282433e9-f6cb-3396-8612-8b47caef2297.png[/img]
[img]http://dl.iteye.com/upload/picture/pic/94896/282433e9-f6cb-3396-8612-8b47caef2297.png[/img]
package com.kjlink.util;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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;
public class HSSFUtil {
private HSSFWorkbook workbook = null; // Excel工作簿
private HSSFSheet sheet = null;
private HSSFRow row = null;
private HSSFCell cell = null;
private HSSFCellStyle style = null;
private HSSFFont font = null;
private List<?> list = null;
private String[] header = null; // 标题行
private String[][] body =null; // body内容
private String[] properties = null; // List集合中的对象属性名集合
private static HSSFUtil hssf = new HSSFUtil();
public static final String FILE_PATH = "D:\\领还记录.xls";
private HSSFUtil() {
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet("sheet1");
this.style = workbook.createCellStyle();
this.font = workbook.createFont();
}
public void exportExcel(String[] header,List<?> list, String[] properties) {
this.header = header;
this.list = list;
this.properties = properties;
this.createHead();
this.createBody();
this.writeXls();
System.out.println("生成Excel成功!");
}
private void createHead() { // Excel格式头
if (header!=null&&header.length>0) {
row = sheet.createRow(0);
for (int i = 0; i<header.length; i++) {
sheet.setColumnWidth(i, 5000);
cell = row.createCell(i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(header[i]);
this.defaultStyle();
this.defaultFont();
cell.setCellStyle(this.style);
}
}
}
private void createBody() {
if (this.listToArray()!=null) {
int lastnum = sheet.getLastRowNum();
for (int i = 0; i < body.length; i++) {
row = sheet.createRow(++lastnum);
for (int j = 0; j < body[i].length; j++) {
cell = row.createCell(j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(body[i][j]);
}
}
}
}
private void writeXls() {
FileOutputStream out = null;
try {
out = new FileOutputStream(FILE_PATH);
workbook.write(out);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
if (out!=null) {
try {
out.flush();
out.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
}
private void defaultStyle () {
style.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}
private void defaultFont () {
font.setColor(HSSFColor.WHITE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
}
private String[][] listToArray() {
if (list!=null&&!list.isEmpty()) {
if (properties!=null&&properties.length>0) {
body = new String[list.size()][properties.length]; // 二维数组Body
for (int i = 0; i < body.length; i++) {
for (int j = 0; j < body[i].length; j++) {
Object obj = list.get(i); // 获取当前对象
Class<?> clzz = obj.getClass(); // 获取当前对象的Clas对象
try {
String getterName = properties[j];
if (getterName.indexOf(".")!=-1) { // getter名称为【对象.属性】
String[] subPro = getterName.split("\\.");
String subName = null;
Object subObj = obj; // 子对象
for (int k = 0; k < subPro.length; k++) {
subName = "get" + firstWordUpper(subPro[k]);
Method method = subObj.getClass().getDeclaredMethod(subName);
if (!method.isAccessible()) {
subObj = method.invoke(subObj);
if (subObj==null) break;
if (k==subPro.length-1) {
body[i][j] = subObj.toString();
}
}
}
} else { // getter名称为【属性】
getterName = "get" + firstWordUpper(properties[j]); // getter方法名
Method method = clzz.getDeclaredMethod(getterName); // 获取指定方法名的Method对象
if(!method.isAccessible()) { // 表示该方法可以访问 修饰符不是private
Object value = method.invoke(obj); // 调用obj对象的getter方法
if (value!=null) {
if (value instanceof java.sql.Timestamp||value instanceof java.util.Date) {
body[i][j] = timeToStr(value);
} else {
body[i][j] = value.toString();
}
}
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
}
}
return body;
}
private String firstWordUpper(String word) {
if (word!=null&&word.length()>1) {
return word.substring(0,1).toUpperCase() + word.substring(1);
}
return word;
}
private String timeToStr(Object obj) {
if (obj!=null) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sdf.format(obj);
}
return null;
}
public static void main(String[] args) {
List<Consum> list = new ArrayList<Consum>();
Consum consum1 = new Consum();
consum1.setAsset(new Asset("KJ-AA-0001"));
consum1.setUser(new User("小兵"));
consum1.setOperator(new User("信息中心"));
consum1.setDate(new Date());
consum1.setRemark("去北京出差");
Consum consum2 = new Consum();
consum2.setAsset(new Asset("KJ-AA-0002"));
consum2.setUser(new User("博海"));
consum2.setOperator(new User("信息中心"));
consum2.setDate(new Date());
consum2.setRemark("去上海出差");
list.add(consum1);
list.add(consum2);
String[] header = new String[] {"固定资产名称","领用人","操作人","领用日期","备注"};
String[] properties = new String[] {"asset.name", "user.name", "operator.name", "date", "remark"};
hssf.exportExcel(header, list, properties);
}
}