欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

POI HSSF - Excel实例 博客分类: Core Java ExcelPOIhssfpoi excel 

程序员文章站 2024-03-24 10:33:40
...
生成Excel效果图如下:
POI HSSF - Excel实例
            
    
    博客分类: Core Java ExcelPOIhssfpoi 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);
		
	}
}