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.大功告成~