java Excel导出功能

    最近在做Excel文件导出的功能，花了一个小时研究了一下这个功能，并应用在了项目中，在小数据量导出方面符合项目需求，花个时间整理一下，方便以后使用及遗忘。

 1. 首先是jar的引用，次示例通过maven引入所需要的jar包

		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi</artifactId>
		    <version>3.9</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.9</version>
			<exclusions>
				<exclusion>
					<groupId>org.apache.poi</groupId>
					<artifactId>poi</artifactId>
				</exclusion>
			</exclusions>
		</dependency>

 2.定义一个类似于Map的key-value对象，用来存储导出的表头信息。

package yang.zheng.util.excel2;

public class Pro {

	private String name;

	private String desc;
	
	public Pro(String name, String desc) {
		super();
		this.name = name;
		this.desc = desc;
	}

	public String getName() {
		return name;
	}

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

	public String getDesc() {
		return desc;
	}

	public void setDesc(String desc) {
		this.desc = desc;
	}
}

3.定义一个excel配置类，用来配置excel的名称、表头存储集合。

package yang.zheng.util.excel2;
import java.util.ArrayList;
import java.util.List;

public class ExcelConfig {


	private String tempDir;
	
	/**
	 * 标题第一行数据1
	 */
	private String titleName1;


	/**
	 * sheetName1
	 */
	private String sheetName1;
	

	/**
	 * 文件名称
	 */
	private String fileName;
	
	/**
	 * 要显示的数据 sheetName1
	 */
	private List<Pro> pros1;
	

	
	
	public String getTempDir() {
		return tempDir;
	}

	public void setTempDir(String tempDir) {
		this.tempDir = tempDir;
	}

	public String getTitleName1() {
		return titleName1;
	}

	public void setTitleName1(String titleName1) {
		this.titleName1 = titleName1;
	}

	public String getSheetName1() {
		return sheetName1;
	}

	public void setSheetName1(String sheetName1) {
		this.sheetName1 = sheetName1;
	}

	public List<Pro> getPros1() {
		return pros1;
	}

	public void addPro1(Pro pro1){
		if(pros1==null){
			pros1 = new ArrayList<Pro>();
		}
		pros1.add(pro1);
	}
	
	public void addPro1(String name ,String desc){
		if(pros1==null){
			pros1 = new ArrayList<Pro>();
		}
		Pro pro1 = new Pro(name, desc);
		pros1.add(pro1);
	}
	
	public void setPros1(List<Pro> pros1) {
		this.pros1 = pros1;
	}

	public String getFileName() {
		return fileName;
	}

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

}

 4.抽象出一个Excel导出的模板类，定义公共信息和方法。

package yang.zheng.util.excel2;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import yang.zheng.util.common.BeanUtil;


public abstract class ExcelTemplate<E> {

	public File createExcel(String modelpath) {

		List<E> data1 = getData1();
		if (data1.size() > 50000) {
			throw new RuntimeException("数据大于50000行，请缩小查询参数", null);
		}
		ExcelConfig config = getConfig();
		File file = buildExcelFileNew(data1, config, modelpath);
		return file;
	}

	/**
	 * 生成Excel数据
	 * @param data1
	 * @param config
	 * @param modelpath
	 * @return
	 */
	private File buildExcelFileNew(List<E> data1, ExcelConfig config, String modelpath) {
		XSSFWorkbook workbook = null;
		try {
			// 创建 Excel 文件的输入流对象
			FileInputStream excelFileInputStream = new FileInputStream(modelpath);
			// XSSFWorkbook 就代表一个 Excel 文件
			// 创建其对象，就打开这个 Excel 文件
			workbook = new XSSFWorkbook(excelFileInputStream);
			// 输入流使用后，及时关闭！这是文件流操作中极好的一个习惯！
			excelFileInputStream.close();
			XSSFSheet sheet1 = workbook.getSheetAt(0);
			List<Pro> pros1 = config.getPros1();
				// 从第三行开始，显示数据
				for (int i = 0; i < data1.size(); i++) {
					E t = data1.get(i);
					XSSFRow rowdata = sheet1.createRow(i + 1);
					buildDataRowNewT(rowdata, pros1, t);
				}
			File tempDir = new File(config.getTempDir());
			if (!tempDir.exists()) {
				tempDir.mkdirs();
			}

			String fileName = config.getFileName();
			File f = new File(tempDir + "/" + System.currentTimeMillis() + "_" + fileName);
			// 将excel 写入到文件中，
			FileOutputStream fos = new FileOutputStream(f);

			workbook.write(fos);

			fos.close();

			// 若是需要压缩
			if (isNeedZip()) {
				f = compressFile(f);
				return f;
			}
			return f;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			if (workbook != null) {
				try {
					// wb.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}



	/**
	 * 压缩文件
	 * 
	 * @param
	 * @return
	 */
	protected File compressFile(File file) {
		System.err.println("压缩暂时没有实现");
		return file;
	}

	/**
	 * 显示数据
	 * 
	 * @param rowdata
	 * @param pros
	 * @param t
	 */
	private void buildDataRowNewT(XSSFRow rowdata, List<Pro> pros, E t) {
		for (int j = 0; j < pros.size(); j++) {
			try {
				Object o = BeanUtil.forceGetProperty(t, pros.get(j).getName());
				Cell cell = rowdata.createCell(j);
				if (o == null) {
					cell.setCellValue("");
				} else {
					if (o instanceof Date) {
						cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format((Date) o ));
					} else if (o instanceof BigDecimal) {
						BigDecimal a = (BigDecimal) o;
						cell.setCellValue(String.valueOf(a));
					} else {
						cell.setCellValue(String.valueOf(o));
					}
					
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取数据
	 * 
	 * @return
	 */
	protected abstract List<E> getData1();
	/**
	 * 获取数据的显示配置
	 * 
	 * @return
	 */
	protected abstract ExcelConfig getConfig();

	/**
	 * 是否需要压缩
	 * 
	 * @return
	 */
	protected boolean isNeedZip() {
		return false;
	}

}

5.新建一个实体类Student,用于Excel导出数据的映射实体。

package yang.zheng.util.excel2;

public class Student{
	
    public Student(String name, int age) {
		super();
		this.name = name;
		this.age = age;
	}

	private String name;
	
	private int age;

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

}

6.新建一个子类继承该模板类，通过实现抽象方法，定义自己的业务逻辑。

package yang.zheng.util.excel2;

import java.util.List;

public class SupExcelTemplate extends ExcelTemplate<Student>{
	private List<Student> data1;
	
	public SupExcelTemplate(List<Student> data) {
		this.data1 = data;
	}

	@Override
	protected List<Student> getData1() {
		return data1;
	}

	@Override
	protected ExcelConfig getConfig() {
		ExcelConfig excelConfig = new ExcelConfig();
		excelConfig.setFileName("数据导出.xlsx");
		excelConfig.setTempDir("D:/");
		excelConfig.addPro1("name", "姓名");
		excelConfig.addPro1("age", "年龄");
		return excelConfig;
	}

}

7.工具类BeanUtil用于表头与数据之间的映射。

package yang.zheng.util.common;

import java.lang.reflect.Field;

public class BeanUtil {
	/**
	 * 暴力获取对象变量值,忽略private,protected修饰符的限制.
	 * 
	 * @throws NoSuchFieldException
	 *             如果没有该Field时抛出.
	 */
	public static Object forceGetProperty(Object object, String propertyName)
			throws NoSuchFieldException {
		Field field = getDeclaredField(object, propertyName);

		boolean accessible = field.isAccessible();
		field.setAccessible(true);

		Object result = null;
		try {
			result = field.get(object);
		} catch (IllegalAccessException e) {

		}
		field.setAccessible(accessible);
		return result;
	}
	
	public static Field getDeclaredField(Object object, String propertyName)
			throws NoSuchFieldException {
		return getDeclaredField(object.getClass(), propertyName);
	}
	
	/**
	 * 循环向上转型,获取对象的DeclaredField.
	 * 
	 * @throws NoSuchFieldException
	 *             如果没有该Field时抛出.
	 */
	@SuppressWarnings("unchecked")
	public static Field getDeclaredField(Class clazz, String propertyName)
			throws NoSuchFieldException {
		for (Class superClass = clazz; superClass != Object.class; superClass = superClass.getSuperclass()) {
			try {
				return superClass.getDeclaredField(propertyName);
			} catch (NoSuchFieldException e) {
				// Field不在当前类定义,继续向上转型
				//将异常捕获继续向父类查找传入属性，直到找到object跳出循环
			}
		}
		throw new NoSuchFieldException("No such field: " + clazz.getName()
				+ '.' + propertyName);
	}

}

8.新建一个测试类，测试导出功能。

package yang.zheng.util;

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

import yang.zheng.util.excel2.ExcelTemplate;
import yang.zheng.util.excel2.Student;
import yang.zheng.util.excel2.SupExcelTemplate;

public class TestExcel {

	public static void main(String[] args) {
		List<Student> list = new ArrayList<Student>();
		list.add(new Student("小明", 12));
		list.add(new Student("王是", 14));
		ExcelTemplate excel = new SupExcelTemplate(list);
		excel.createExcel("D:/123.xlsx");

	}

}