POI HSSF - Excel实例

生成Excel效果图如下：

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);
		
	}
}