封装JAVA 通过反射 POI导出Excel文件(直接可用 含测试代码)

 

啥也不说了  直接上代码：

 

Student.java主要测试使用的

package cn.sup.cd.test;

import java.util.Date;

public class Student {
	private int id;
	private String name;
	private String sex;
	private int age;
	private Date birth;

	public Student() {
	}

	public Student(int id, String name, int age,String sex, Date birth) {
		this.id = id;
		this.name = name;
		this.age = age;
		this.sex = sex;
		this.birth = birth;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public Date getBirth() {
		return birth;
	}

	public void setBirth(Date birth) {
		this.birth = birth;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

}

 封装列名与属性名对应类:

package cn.sup.cd.common.util;

public class ColnameToField {
	private String colname;//列名
	
	private String fieldName;//类属性名
	
	public ColnameToField(String fieldName, String colname){
		this.colname = colname;
		this.fieldName = fieldName;
	}

	public String getColname() {
		return colname;
	}

	public void setColname(String colname) {
		this.colname = colname;
	}

	public String getFieldName() {
		return fieldName;
	}

	public void setFieldName(String fieldName) {
		this.fieldName = fieldName;
	}
	
}

 

 

下面是单元测试代码：

 到时间可根据自己的情况定义

List<Student> students = null;
	SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");

	@Before
	public void before() {
		try {
			Student student1 = new Student(1, "小李", 20, "男", df
					.parse("1992-12-30"));
			Student student2 = new Student(2, "小张", 24, "男", df
					.parse("1945-02-30"));
			Student student3 = new Student(3, "小孙", 25, "男", df
					.parse("1988-11-20"));
			Student student4 = new Student(4, "小王", 21, "男", df
					.parse("1992-05-30"));
			Student student5 = new Student(5, "小刘", 30, "男", df
					.parse("2012-08-30"));
			Student student6 = new Student(6, "小单", 28, "男", df
					.parse("1992-12-30"));
			Student student7 = new Student(7, "小红", 22, "男", df
					.parse("1560-10-30"));
			Student student8 = new Student(8, "小明", 29, "男", df
					.parse("2001-07-30"));
			students = new ArrayList<Student>();
			students.add(student1);
			students.add(student2);
			students.add(student3);
			students.add(student4);
			students.add(student5);
			students.add(student6);
			students.add(student7);
			students.add(student8);

		} catch (Exception e) {
		}
	}





@Test
	public void test2() throws FileNotFoundException {
		// 获取xml数据存放位置
		// String outputPath =
		// ServletActionContext.getServletContext().getRealPath("/");
		String title = "学生表";
		String fileName = "学生表.xls";
		String path = "d:\\aaaaa";
		StudentExportExcel excel = new StudentExportExcel(students, path,
				fileName, title);
		excel.exportExcel();
	}

 

下面是比较主要代码了.......... 主要按自己的业务封装一些导出文件的信息

package cn.sup.cd.common.exceExport;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import cn.sup.cd.common.util.ColnameToField;
import cn.sup.cd.test.Student;

@SuppressWarnings("serial")
public class StudentExportExcel extends ExportExcel<Student> {

	/**
	 * 
	 * @param dataset数据源
	 * @param path
	 *            导出路径
	 * @param fileName
	 *            导出文件名称
	 * @param title
	 *            文件标题
	 */
	public StudentExportExcel(Collection<Student> dataset, String path,
			String fileName, String title) {
		super(dataset, path);

		/* 设置Excel基本数据 */

		// 定义列名与类属性名的对应关系
		//
		List<ColnameToField> colNameToField = new ArrayList<ColnameToField>() {
			{
				add(new ColnameToField("id", "学号"));
				add(new ColnameToField("name", "姓名"));
				add(new ColnameToField("age", "年龄"));
				add(new ColnameToField("sex", "性别"));
				add(new ColnameToField("birth", "出生日期"));
			}
		};
		super.setTitle(title);// 标题，以查询的起止日期作为标题
		super.setFileName(fileName);// Excel文件名(以站点名作为文件名)
		super.setDateFormat("yyyy-MM-dd HH:mm:ss");

		// 设置列名对应的属性名集合(其顺序与表头列名属性一致)
		super.setColNameToField(colNameToField);
	}
}

 

最核心代码如下所示：。

package cn.sup.cd.common.exceExport;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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;

import cn.sup.cd.common.util.ColnameToField;

/**
 * 利用开源组件POI3.0.2动态导出EXCEL文档 转载时请保留以下信息，注明出处！
 * 
 * @author TL
 * @version v1.0
 * @param <T>
 *            应用泛型，代表任意一个符合javabean风格的类
 *            注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
 *            byte[]表jpg格式的图片数据
 */
public class ExportExcel<T> {
	/**
	 * xls文件存放路径
	 */
	public static final String EXCEL_FILE_PATH = "xls";
	/**
	 * xls数据映射类
	 */
	private Class<T> classType;

	private Collection<T> dataset;

	private String fileName;// 文件名

	private String title;// 文档标题

	private String dateFormat = "yyyy-MM-dd";// 默认日期格式

	private String path;// 项目运行物理路径

	private List<ColnameToField> colNameToField;// 列名与类属性名的对应关系

	private Map<String, Map<String, String>> colValueToChange;// 将列值转换为另外的值

	/**
	 * 构造方法，初始化数据
	 * 
	 * @param dataset
	 *            数据集合
	 * @param path
	 *            Tomcat项目运行路径
	 */
	@SuppressWarnings("unchecked")
	public ExportExcel(Collection<T> dataset, String path) {
		try {
			// 获取泛型的实际类型
			Type type = this.getClass().getGenericSuperclass();
			if (type instanceof ParameterizedType) {
				// 转换成泛型类
				ParameterizedType pType = (ParameterizedType) type;
				this.classType = (Class<T>) pType.getActualTypeArguments()[0];
			}
			this.path = path;
			this.dataset = dataset;
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 生成Excel文件
	 */
	public void exportExcel() {
		File file = new File(path + fileName);

		if (!file.exists()) {

			try {
				file.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		// 获取文件输出流
		OutputStream out = null;
		try {
			out = new FileOutputStream(path + fileName);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return;
		}

		// 设置表格标题行的列名数组并生成Excel
		String[] header = new String[colNameToField.size()];
		int index = 0;
		for (ColnameToField colField : colNameToField) {

			header[index] = colField.getColname();
			index++;
		}
		//导出
		exportExcel(title, header, out, dateFormat);
	}

	/**
	 * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
	 * 
	 * @param title
	 *            表格标题名
	 * @param headers
	 *            表格属性列名数组
	 * @param out
	 *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern
	 *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 */
	@SuppressWarnings("unused")
	private void exportExcel(String title, String[] headers, OutputStream out,
			String pattern) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
		// 生成并设置另一个样式
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体
		HSSFFont font2 = workbook.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style2.setFont(font2);

		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// 定义注释的大小和位置,详见文档
		HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
				0, 0, 0, (short) 4, 2, (short) 6, 5));
		// 设置注释内容
		comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
		// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		comment.setAuthor("leno");

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (short i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}

		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {

			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();// 取出每个对象
			// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
			short cellIndex = 0;// Excel列索引

			for (ColnameToField colField : colNameToField) {

				String fieldName = colField.getFieldName();// 取属性名

				HSSFCell cell = row.createCell(cellIndex);
				cell.setCellStyle(style2);

				String getMethodName = "get"
						+ fieldName.substring(0, 1).toUpperCase()
						+ fieldName.substring(1);
				try {
					// Class tCls = classType.getClass();
					Method getMethod = classType.getMethod(getMethodName,
							new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});
					// 判断值的类型后进行强制类型转换
					String textValue = null;
					// if (value instanceof Integer) {
					// int intValue = (Integer) value;
					// cell.setCellValue(intValue);
					// } else if (value instanceof Float) {
					// float fValue = (Float) value;
					// textValue = new HSSFRichTextString(
					// String.valueOf(fValue));
					// cell.setCellValue(textValue);
					// } else if (value instanceof Double) {
					// double dValue = (Double) value;
					// textValue = new HSSFRichTextString(
					// String.valueOf(dValue));
					// cell.setCellValue(textValue);
					// } else if (value instanceof Long) {
					// long longValue = (Long) value;
					// cell.setCellValue(longValue);
					// }
					if (value == null) {

						textValue = "";
					} else if (value instanceof Boolean) {

						/*
						 * boolean bValue = (Boolean) value; textValue = "男"; if
						 * (!bValue) { textValue = "女"; }
						 */
					} else if (value instanceof Date) {

						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat(pattern);
						textValue = sdf.format(date);
					} else if (value instanceof byte[]) {

						// 有图片时，设置行高为60px;
						row.setHeightInPoints(60);
						// 设置图片所在列宽度为80px,注意这里单位的一个换算
						sheet.setColumnWidth(cellIndex, (short) (35.7 * 80));
						// sheet.autoSizeColumn(i);
						byte[] bsValue = (byte[]) value;
						HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
								1023, 255, (short) 6, index, (short) 6, index);
						anchor.setAnchorType(2);
						patriarch.createPicture(anchor, workbook.addPicture(
								bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
					} else {

						// 其它数据类型都当作字符串简单处理
						textValue = value.toString();

						// 判断属性值是否需要转换为其他的内容
						if (this.colValueToChange != null) {

							// 取出属性值转换Map
							Map<String, String> changeMap = colValueToChange
									.get(fieldName);
							if (changeMap != null && changeMap.size() > 0) {
								textValue = changeMap.get(textValue);
							}
						}

					}
					// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
					if (textValue != null) {

						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
							// 是数字当作double处理
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							HSSFRichTextString richString = new HSSFRichTextString(
									textValue);
							HSSFFont font3 = workbook.createFont();
							font3.setColor(HSSFColor.BLUE.index);
							richString.applyFont(font3);
							cell.setCellValue(richString);
						}
					}
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} finally {
					// 清理资源
					cellIndex++;
				}
			}

		}

		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 设置日期格式
	 * 
	 * @param dateFormat
	 */
	public void setDateFormat(String dateFormat) {
		this.dateFormat = dateFormat;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public void setColNameToField(List<ColnameToField> colNameToField) {
		this.colNameToField = colNameToField;
	}

	public void setColValueToChange(
			Map<String, Map<String, String>> colValueToChange) {
		this.colValueToChange = colValueToChange;
	}
}

 

效果图：

 

评论

2 楼 final7tifa 2016-05-18  

感觉写的挺好

1 楼 漆停停 2014-01-18