poi实现通用excel导出

本实例通过poi实现excel导出，实现会员数据的excle下载

0.导入maven依赖

		<!-- POI相关的包 -->
		<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>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.9</version>
		</dependency>

1.新建个实体SharedMember

/**
 */
package com.sml.sz.supplier.pojo;

import com.sml.sz.ExceVo;
import com.sml.sz.common.persistence.NewDataEntity;
import org.hibernate.validator.constraints.Length;

import java.util.Date;

/**
 * 会员共享Entity
 * 
 * @author xxx
 * @version 2018-10-15
 */
public class SharedMember{

	private static final long serialVersionUID = 1L;

	@ExceVo(name = "编号", sort = 1)
	private String number; // 编号

	@ExceVo(name = "姓名", sort = 2)
	private String name; // 姓名

	@ExceVo(name = "账号", sort = 3)
	private String account; // 账号

	@ExceVo(name = "来源", sort = 4)
	private String source; // 来源

	private String code; // 平台唯一编码

	@ExceVo(name = "添加时间", sort = 5)
	private Date createDate;// 添加时间

	public SharedMember() {
		super();
	}

	public SharedMember(String id) {
		super(id);
	}

	@Length(min = 1, max = 64, message = "编号长度必须介于 1 和 64 之间")
	public String getNumber() {
		return number;
	}

	public void setNumber(String number) {
		this.number = number;
	}

	@Length(min = 1, max = 255, message = "姓名长度必须介于 1 和 255 之间")
	public String getName() {
		return name;
	}

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

	@Length(min = 1, max = 64, message = "账号长度必须介于 1 和 64 之间")
	public String getAccount() {
		return account;
	}

	public void setAccount(String account) {
		this.account = account;
	}

	@Length(min = 1, max = 255, message = "来源长度必须介于 1 和 255 之间")
	public String getSource() {
		return source;
	}

	public void setSource(String source) {
		this.source = source;
	}

	@Length(min = 1, max = 30, message = "平台唯一编码长度必须介于 1 和 30 之间")
	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	@Override
	public Date getCreateDate() {
		return createDate;
	}

	public void setCreateDate(String date) {
		this.createDate = super.createDate;
	}

}

2.新建注解ExceVo

package com.sml.sz;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.FIELD, ElementType.TYPE })
public @interface ExceVo {

	/** 对应的列名称 */
	String name() default "";

	/** 列序号 */
	int sort();

	/** 字段类型对应的格式 */
	String format() default "";

	/** 是否需要校验 */
	boolean isCheck() default false;

	/** 校验字段长度 */
	int fieldLength() default 50;

	/** 校验是否可以为空 */
	boolean isEmpty() default true;
}

3.新建一个TestController

package com.sml.sz.supplier.controller;

import com.sml.sz.ExcelExportUtil;
import com.sml.sz.supplier.common.Message;
import com.sml.sz.supplier.pojo.SharedMember;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/**
 * @author xxx
 * @ClassName TestController
 * @Description: todo
 * @date 2020/7/3 17:05
 * @Version 1.0
 */
public class TestController {

    /**
     * 会员导出
     * @param response
     * @throws IOException
     */
    @RequestMapping(value = "export", method = { RequestMethod.POST, RequestMethod.GET })
    public void list(HttpServletResponse response) throws IOException {
        response.setContentType("application/binary;charset=ISO8859_1");
        ServletOutputStream outputStream = response.getOutputStream();
        String fileName = new String(("会员列表").getBytes(), "ISO8859_1");
        // 组装附件名称和格式
        response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
        //这里替换成自己的list就行
        List<SharedMember> sharedMembers = sharedMemberService.findList(new SharedMember());
        ExcelExportUtil<SharedMember> excelUtil = new ExcelExportUtil<SharedMember>(SharedMember.class);
        excelUtil.export(sharedMembers, outputStream);
    }
}

4.新建 ExcelExportUtil

package com.sml.sz;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;

public class ExcelExportUtil<T> {

	private Class claze;

	public ExcelExportUtil(Class claze) {
		this.claze = claze;
	}

	/**
	 * 基于注解导出 不需要自己封装每列的值
	 * 
	 * @param fileName
	 *            模板名称
	 * @param objs
	 *            导出实体集合
	 * @param rowIndex
	 *            excel第几行开始导出
	 */
	public void export(List<T> objs, ServletOutputStream outputStream) {
		// 带注解并排序好的字段
		List<Field> fieldList = getFieldList();
		// 创建一个workbook 对应一个excel应用文件
		XSSFWorkbook workBook = new XSSFWorkbook();
		// 在workbook中添加一个sheet,对应Excel文件中的sheet
		XSSFSheet sheet = workBook.createSheet("Sheet1");
		XSSFCellStyle headStyle = getHeadStyle(workBook);
		XSSFCellStyle bodyStyle = getBodyStyle(workBook);
		try {
			// 构建表头
			XSSFRow headRow = sheet.createRow(0);
			XSSFCell cell = null;
			for (int i = 0; i < fieldList.size(); i++) {
				cell = headRow.createCell(i);
				cell.setCellStyle(headStyle);
				Field field = fieldList.get(i);
				cell.setCellValue(getExcelHead(field));
			}
			// 构建表体数据
			for (int j = 0; j < objs.size(); j++) {
				XSSFRow bodyRow = sheet.createRow(j + 1);
				T t = objs.get(j);
				Class clazz = t.getClass();
				for (int k = 0; k < fieldList.size(); k++) {
					Field field = fieldList.get(k);
					PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
					// 获取值之前先把日期类型转化成string
					Map<String, Boolean> map = covertAttrType(field, t);
					Method getMethod = pd.getReadMethod();
					if (getMethod != null) {
						cell = bodyRow.createCell(k);
						cell.setCellStyle(bodyStyle);
						for (Map.Entry<String, Boolean> entry : map.entrySet()) {
							// 日期类型
							if (entry.getValue() == true) {
								cell.setCellValue(entry.getKey());
							} else {
								String stringCell=getMethod.invoke(t) == null ? "" : getMethod.invoke(t) + "";
								if(!StringUtils.isBlank(stringCell)){
									//如果excel内容是网络地址，需要单独处理，否则打不开excel
									if(stringCell.contains("https")){
										// 使用creationHelpper来创建XSSFHyperlink对象
										CreationHelper createHelper = workBook.getCreationHelper();
										XSSFHyperlink  link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
										link.setAddress(stringCell);
										// 设置超链接
										cell.setHyperlink(link);
									}
									cell.setCellValue(stringCell);
								}
							}
						}
					}
				}
			}
			workBook.write(outputStream);
			outputStream.flush();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				outputStream.close();
			}
			catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 设置表头的单元格样式
	 * 
	 * @return
	 */
	public XSSFCellStyle getHeadStyle(XSSFWorkbook wb) {
		// 创建单元格样式
		XSSFCellStyle cellStyle = wb.createCellStyle();
		// 设置单元格的背景颜色为淡蓝色
		cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
		cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		// 设置单元格居中对齐
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 设置单元格垂直居中对齐
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 创建单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// 设置单元格字体样式
		XSSFFont font = wb.createFont();
		// 设置字体加粗
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);
		// 设置单元格边框为细线条
		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		return cellStyle;
	}

	/**
	 * 设置表体的单元格样式
	 * 
	 * @return
	 */
	public XSSFCellStyle getBodyStyle(XSSFWorkbook wb) {
		// 创建单元格样式
		XSSFCellStyle cellStyle = wb.createCellStyle();
		// 设置单元格居中对齐
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 设置单元格垂直居中对齐
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 创建单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// 设置单元格字体样式
		XSSFFont font = wb.createFont();
		// 设置字体加粗
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);
		// 设置单元格边框为细线条
		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		return cellStyle;
	}

	/**
	 * 获取带注解的字段 并且排序
	 * 
	 * @return
	 */
	private List<Field> getFieldList() {
		Field[] fields = getAllFields(this.claze);
		// 无序
		List<Field> fieldList = new ArrayList<Field>();
		// 排序后的字段
		List<Field> fieldSortList = new LinkedList<Field>();
		int length = fields.length;
		int sort = 0;
		Field field = null;
		// 获取带注解的字段
		for (int i = 0; i < length; i++) {
			field = fields[i];
			if (field.isAnnotationPresent(ExceVo.class)) {
				fieldList.add(field);
			}
		}
		// Assert.assertNotNull("未获取到需要导出的字段", fieldList);
		length = fieldList.size();
		for (int i = 1; i <= length; i++) {
			for (int j = 0; j < length; j++) {
				field = fieldList.get(j);
				ExceVo exceVo = field.getAnnotation(ExceVo.class);
				field.setAccessible(true);
				sort = exceVo.sort();
				if (sort == i) {
					fieldSortList.add(field);
					continue;
				}
			}
		}
		return fieldSortList;
	}

	/**
	 * 获取所有属性包括父类
	 * 
	 * @param object
	 * @return
	 */
	public static Field[] getAllFields(Class clazz) {
		List<Field> fieldList = new ArrayList<>();
		while (clazz != null) {
			fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
			clazz = clazz.getSuperclass();
		}
		Field[] fields = new Field[fieldList.size()];
		fieldList.toArray(fields);
		return fields;
	}

	/**
	 * 类型转换把date类型转为String
	 */
	public Map<String, Boolean> covertAttrType(Field field, T obj) {
		Map<String, Boolean> map = new HashMap<String, Boolean>();
		try {
			if (field.get(obj) == null) {
				map.put("", false);
				return map;
			}
			String type = field.getType().getSimpleName();
			if ("Date".equals(type)) {
				map.put(DateFormatUtils.format((Date) field.get(obj), "yyyy-MM-dd HH:mm:ss"), true);
				return map;
			} else {
				map.put(field.get(obj).toString(), false);
				return map;
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取excel表头信息
	 */
	public String getExcelHead(Field field) {
		if (null == field) {
			return null;
		}
		ExceVo exceVo = field.getAnnotation(ExceVo.class);
		return exceVo.name();
	}

}

5.大功告成~