exportexcel.java

企业人力资源管理

package com.y2.hr.human.common;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.y2.hr.human.file.bean.HumanFile;

/*******************************************************************************
 * 此类可实现将页面的集合数据导出为EXCEL报表
 * 
 * @author yangjiahui
 * @create time 2008.11.26
 * @see 说明：该类是在apache的poi组件来实现的 用户只要调用writeExcel方法,提供文件名，表头名,列名和一个集合对象
 *      (暂只提供ResultSet和List集合)就可以实现写成 excel文件格式
 * 
 */
public class ExportExcel {

	/**
	 * 
	 * @param fileName
	 *            文件保存的位置及文件名，无须提供后缀名
	 * @param tableName
	 *            表头名
	 * @param fieldName
	 *            列名数组
	 * @param list
	 *            要显示的数据集合
	 * 
	 * 注意: 导出到Excel时按照fieldName数组中属性的先后顺序逐个显示
	 */
	public static void writeExcel(OutputStream fos, String tableName,
			String[] fieldName, List<?> list) {
		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet s = wb.createSheet();
			createTag(tableName, fieldName, s);// 创建Excel表头
			createValue(list, fieldName, s);
			wb.write(fos);
			fos.flush();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fos != null) {
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 写Excel操作
	 * 
	 * @param fileName
	 *            文件名，文件要写入到的盘符和文件名，但不需要后缀名
	 * @param tableName
	 *            要导出的表头名
	 * @param fieldName
	 *            表列名(务必为要导出的实体类的属性名)
	 * @param res
	 *            数据对象,java.sql.ResultSet
	 */
	public static void writeExcel(String fileName, String tableName,
			String[] fieldName, java.sql.ResultSet res) {
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(fileName + ".xls");
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet s = wb.createSheet();
			// 设置表格默认列宽度为40个字节
			s.setDefaultColumnWidth((short) 40);
			System.out.println("Width:40");
			createTag(tableName, fieldName, s);
			createValue(res, s);
			wb.write(fos);
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fos != null) {
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 创建表格表头
	 * 
	 * @param tableName
	 *            表名
	 * @param tags
	 *            列名数组
	 * @param s
	 *            要操作的表对象
	 */
	private static void createTag(String tableName, String[] tags, HSSFSheet s) {
		HSSFRow row = s.createRow(0);
		HSSFCell cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(tableName);
		row = s.createRow(2);
		for (int i = 0; i < tags.length; i++) {
			String keyName = tags[i].split("_")[1];
			cell = row.createCell((short) i);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(keyName);
		}
	}

	/**
	 * 设置表格内容
	 * 
	 * @param res
	 * @param s
	 */
	private static void createValue(java.sql.ResultSet res, HSSFSheet s) {
		try {
			int flag = 3;
			int count = res.getMetaData().getColumnCount();
			HSSFRow row = null;
			HSSFCell cell = null;
			while (res.next()) {
				row = s.createRow(flag);
				for (int i = 1; i <= count; i++) {
					cell = row.createCell((short) (i - 1));
					Object obj = res.getObject(i);
					cell.setCellValue(obj + "");
				}
				flag++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 把List中实体类中所有属性全部导出到Excel中(按实体类属性先后顺序排序)
	 * 
	 * @param list
	 * @param s
	 */
	private static void createValue(List list, HSSFSheet s) {
		int rowIndex = 3;
		HSSFRow row = null;
		HSSFCell cell = null;
		for (Object obj : list) {
			Field[] fields = obj.getClass().getDeclaredFields();
			Method[] methods = obj.getClass().getMethods();
			row = s.createRow(rowIndex);
			for (int i = 0; i < fields.length; i++) {
				String methodName = "get" + fields[i].getName().toLowerCase();
				Object value = null;
				for (Method m : methods) {
					if (m.getName().toLowerCase().equals(
							methodName.toLowerCase())) {
						try {
							value = m.invoke(obj, null);
							// System.out.println("value: " + value);
							cell = row.createCell((short) i);
							cell.setEncoding(HSSFCell.ENCODING_UTF_16);
							try {
								if (!"NULL".equals(value.toString()))
									cell.setCellValue(value.toString());
							} catch (Exception e) {
								cell.setCellValue(" ");
							}
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}

			}
			rowIndex++;
		}
	}

	/**
	 * 按设定的字段把数据导出到Excel中
	 * 
	 * @param list
	 *            数据集
	 * @param needDisplay
	 *            要导出的字段
	 * @param s
	 */
	private static void createValue(List<?> list, String[] needDisplay,
			HSSFSheet s) {
		int rowIndex = 3;
		HSSFRow row = null;
		HSSFCell cell = null;
		for (Object obj : list) {
			Method[] methods = obj.getClass().getMethods();
			row = s.createRow(rowIndex);
			for (int i = 0; i < needDisplay.length; i++) {
				String primaryKey = needDisplay[i].split("_")[0];
				// String methodName = "get" + needDisplay[i].toLowerCase();
				String methodName = "get" + primaryKey.toLowerCase();
				Object value = null;
				for (Method m : methods) {
					if (m.getName().toLowerCase().equals(methodName)) {
						try {
							value = m.invoke(obj, null);
							cell = row.createCell((short) i);
							try {
								cell.setEncoding(HSSFCell.ENCODING_UTF_16);
								if (!"NULL".equals(value.toString()))
									cell.setCellValue(value.toString());
							} catch (Exception e) {
								cell.setCellValue(" ");
							}
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}
			}
			rowIndex++;
		}
	}

	/**
	 * 解码
	 * 
	 * @param obj
	 * @return
	 */
	private static String encoding(Object obj) {
		String str = "";
		try {
			str = new String(obj.toString().getBytes("ISO-8859-1"), "GBK");
		} catch (UnsupportedEncodingException e) {
		}
		return str;
	}
}