前言：开发各种统计系统以及报表系统之类的不可避免的就是导出excel功能，传统的poi用过的都知道，每个sheet每个row都需要去定义去美化，繁琐不说还很浪费时间，最近本人发现两种较快速的方法，一种是excel模版方式（模版性太强，不灵活），一种是使用阿里开源的easyexcel（灵活方便）；下面对两种方式都做一个简单使用介绍：

一、XLSTransformer模板方式导出excel

1、首先需要引入一些依赖的jar包（maven项目直接查询对应的依赖即可）：

jxls-core-0.9.7.jarjxls-reader-0.9.7.jarpoi-3.6.jarcommons-jexl-1.1.jarcommons-digester-2.0.jar

2、数据封装

将你查询的数据放入List<Map<String, Object>> 中，然后再使用Map去封装你的此list；

List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
for(int i = 0;i<10;i++){Map<String,Object> map=new HashMap<>();map.put("name","张三");map.put("sex","男");map.put("age","18");list.add(map);
}
Map data = new HashMap();
data.put("rsList", list);
String fileName="excel.xls";
XLSTransformer transformer = new XLSTransformer();
try {transformer.transformXLS("模板位置", data, "生成文件位置"+fileName);
} catch (InvalidFormatException e) {e.printStackTrace();
}

3、编写模版，准备导出

模版为一个excel文件，文件中使用<jx:forEach>标签遍历数据，如下图，各个字段的标题以及其他样式（加粗、颜色等）直接在模版excel中设置好即可，数据的字段与map中属性一致即可；

此方式导出配置完毕，之后将文件下载即可，下载方法也已放在文章最后；

二、easyExcel方式导出excel

1、同样的，先引入jar包；

<!-- easyexcel -->
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.0.5</version><exclusions><exclusion><groupId>org.ehcache</groupId><artifactId>ehcache</artifactId></exclusion></exclusions>
</dependency>
<dependency><groupId>org.ehcache</groupId><artifactId>ehcache</artifactId><version>${ehcache.version}</version><exclusions><exclusion><artifactId>slf4j-api</artifactId><groupId>org.slf4j</groupId></exclusion></exclusions>
</dependency>

2、编写导出的公共配置以及属性（可以采用我下面写好的工具类，基于springboot项目）

package util;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.edi.framework.core.utils.IdUtils;
import com.edi.nsme.admin.util.FileUtil;
import com.edi.nsme.db.constants.Constants;
import javassist.CannotCompileException;
import javassist.ClassPool;
import javassist.CtClass;
import javassist.CtField;
import javassist.CtNewMethod;
import javassist.NotFoundException;
import javassist.bytecode.AnnotationsAttribute;
import javassist.bytecode.ClassFile;
import javassist.bytecode.ConstPool;
import javassist.bytecode.FieldInfo;
import javassist.bytecode.annotation.Annotation;
import javassist.bytecode.annotation.ArrayMemberValue;
import javassist.bytecode.annotation.IntegerMemberValue;
import javassist.bytecode.annotation.ShortMemberValue;
import javassist.bytecode.annotation.StringMemberValue;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.List;@Component
public class EasyExportUtil {private final Logger logger = LoggerFactory.getLogger(getClass());// 默认字体private static final short DEFAULT_FONT_SIZE_TITLE = 12;private static final short DEFAULT_FONT_SIZE_CONTENT = 11;private static final String EXCEL_DEFAULT_FONT = "宋体-18030";private static final String EXCEL_SHEET_TITLE_FONT = "黑体";public static final int DEFAULT_SHEET_INDEX = 0;@Value("${export.filePath}")//在springboot中的yml文件中配置，为导出后的excel的存储位置private String fileFolderPath;/*** <p>方法名称: createExcel | 描述: 生成excel</p>** @param sourceDataList : 要导出的数据集合* @param beanClazz : 要导出数据对应实体的类class* @param fileName : 输出的excel名称，不必加后缀名* @param showColumns : 要显示的列名称数组，可以为null，为null默认输出全部列；注意：showColumns参数不为null时，hideColumns参数不起作用。* @param hideColumns : 要隐藏的列名称数组，可以为null；注意：showColumns参数、hideColumns参数只会有一个起作用，hideColumns参数只有在showColumns为null时会起作用* @return java.lang.String* @date:   2019/10/25 12:59*/public String createExcel(List<?> sourceDataList, Class beanClazz, String fileName, String userId, String[] showColumns, String[] hideColumns){Class suitClass = null;try {//默认样式HorizontalCellStyleStrategy tableStyle = createTableStyle();if(sourceDataList != null && sourceDataList.size() > 0){//suitClass = createSuitClass(beanClazz, showColumns, hideColumns);//要输出的数据List dataList = convertBeanList(sourceDataList, suitClass);//创建文件路径String fileFullPath = createExcelPath(userId);//创建excelExcelWriter excelWriter = EasyExcel.write(fileFullPath, suitClass).registerWriteHandler(tableStyle).build();WriteSheet writeSheet = EasyExcel.writerSheet(DEFAULT_SHEET_INDEX, fileName).build();excelWriter.write(dataList, writeSheet);excelWriter.finish();return fileFullPath;}} catch (Exception e) {e.printStackTrace();} finally {//从缓存中注销临时类if(suitClass != null){try {ClassPool classPool = ClassPool.getDefault();CtClass clazz = classPool.getCtClass(suitClass.getName());clazz.detach();} catch (NotFoundException e) {e.printStackTrace();}}}return null;}/*** <p>方法名称: createTableStyle | 描述: 创建样式</p>** @return com.alibaba.excel.metadata.TableStyle* @date:   2019/9/29 14:18*/private HorizontalCellStyleStrategy createTableStyle() {// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//设置字体WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints(DEFAULT_FONT_SIZE_TITLE);headWriteFont.setFontName(EXCEL_SHEET_TITLE_FONT);headWriteCellStyle.setWriteFont(headWriteFont);// 下边框headWriteCellStyle.setBorderBottom(BorderStyle.THIN);// 左边框headWriteCellStyle.setBorderLeft(BorderStyle.THIN);// 上边框headWriteCellStyle.setBorderTop(BorderStyle.THIN);// 右边框headWriteCellStyle.setBorderRight(BorderStyle.THIN);// 水平对齐方式headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 垂直对齐方式headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 背景绿色contentWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());WriteFont contentWriteFont = new WriteFont();// 字体大小contentWriteFont.setFontHeightInPoints(DEFAULT_FONT_SIZE_CONTENT);headWriteFont.setFontName(EXCEL_DEFAULT_FONT);contentWriteCellStyle.setWriteFont(contentWriteFont);// 下边框contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);// 左边框contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);// 上边框contentWriteCellStyle.setBorderTop(BorderStyle.THIN);// 右边框contentWriteCellStyle.setBorderRight(BorderStyle.THIN);// 水平对齐方式contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);// 垂直对齐方式contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);return horizontalCellStyleStrategy;}/*** <p>方法名称: convertBeanList | 描述: 将对象集合强转类型</p>** @param sourceList :* @param targetClazz :* @return java.util.List<org.apache.poi.ss.formula.functions.T>* @date:   2019/10/25 12:34*/private List<?> convertBeanList(List<?> sourceList, Class<?> targetClazz) {List<Object> targetList = new ArrayList<>();try {if(sourceList != null && sourceList.size() > 0){for (Object obj : sourceList) {Object targetBean = targetClazz.newInstance();BeanUtils.copyProperties(obj, targetBean);targetList.add(targetBean);}}} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}return targetList;}/*** <p>方法名称: getHiddenColIndex | 描述: 获取要隐藏的列</p>** @param beanClazz :* @param showColumns :* @return int[]* @date:   2019/10/25 12:48*/private List<Integer> getHiddenColIndex(Class beanClazz, String[] showColumns){if(showColumns != null && showColumns.length > 0){List<Integer> hiddenIndex = new ArrayList<>();List<String> showColList = Arrays.asList(showColumns);//要显示的列名称Field[] fields = beanClazz.getDeclaredFields();if(fields != null && fields.length > 0){for (int i = 0; i < fields.length; i++) {Field field = fields[i];if(!showColList.contains(field.getName())){ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if(annotation != null){hiddenIndex.add(annotation.index());}}}}return hiddenIndex;}return null;}/*** <p>方法名称: | 描述: 创建新的实体类，只有要显示的列</p>** @param beanClazz :* @param showColumns :* @return int[]* @date:   2019/10/25 12:48*/private Class createSuitClass(Class beanClazz, String[] showColumns, String[] hideColumns){try {if((showColumns != null && showColumns.length > 0) || (hideColumns != null && hideColumns.length > 0)){String newClassName = beanClazz.getName() + "_" + IdUtils.randomString(10);//ClassPool：CtClass对象的容器ClassPool pool = ClassPool.getDefault();//通过ClassPool生成一个新类CtClass ctClass = pool.makeClass(newClassName);ClassFile classFile = ctClass.getClassFile();ConstPool constpool = classFile.getConstPool();//加上类的注解ContentRowHeight contentRowHeightAnno = (ContentRowHeight)beanClazz.getAnnotation(ContentRowHeight.class);HeadRowHeight headRowHeightAnno = (HeadRowHeight) beanClazz.getAnnotation(HeadRowHeight.class);ColumnWidth columnWidthAnno = (ColumnWidth) beanClazz.getAnnotation(ColumnWidth.class);AnnotationsAttribute classAnnotationAttr = new AnnotationsAttribute(constpool, AnnotationsAttribute.visibleTag);if(contentRowHeightAnno != null){short contentRowHeightValue = contentRowHeightAnno.value();Annotation contentRowHeightAnnotation = new Annotation(ContentRowHeight.class.getName(),constpool);contentRowHeightAnnotation.addMemberValue("value", new ShortMemberValue(contentRowHeightValue, classFile.getConstPool()));classAnnotationAttr.addAnnotation(contentRowHeightAnnotation);}if(headRowHeightAnno != null){short headRowHeightAnnoValue = headRowHeightAnno.value();Annotation headRowHeightAnnotation = new Annotation(HeadRowHeight.class.getName(),constpool);headRowHeightAnnotation.addMemberValue("value", new ShortMemberValue(headRowHeightAnnoValue, classFile.getConstPool()));classAnnotationAttr.addAnnotation(headRowHeightAnnotation);}if(columnWidthAnno != null){int columnWidthAnnoValue = columnWidthAnno.value();Annotation columnWidthAnnotation = new Annotation(ColumnWidth.class.getName(),constpool);columnWidthAnnotation.addMemberValue("value", new IntegerMemberValue(classFile.getConstPool(), columnWidthAnnoValue));classAnnotationAttr.addAnnotation(columnWidthAnnotation);}classFile.addAttribute(classAnnotationAttr);//要显示的列名称List<String> showColList = (showColumns != null && showColumns.length > 0) ? Arrays.asList(showColumns) : null;//要隐藏的列明List<String> hideColList = ((showColumns == null || showColumns.length == 0) && (hideColumns != null && hideColumns.length > 0)) ? Arrays.asList(hideColumns) : null;Field[] fields = beanClazz.getDeclaredFields();if(fields != null && fields.length > 0){int index = 0;for (int i = 0; i < fields.length; i++) {Field field = fields[i];if((showColList != null && showColList.contains(field.getName())) || (hideColList != null && !hideColList.contains(field.getName()))){ExcelIgnore excelIgnore = field.getAnnotation(ExcelIgnore.class);ColumnWidth columnWidthAnnotation = field.getAnnotation(ColumnWidth.class);ExcelProperty excelPropertyAnnotation = field.getAnnotation(ExcelProperty.class);//判断是否需要生成该字段：原字段不能有@ExcelIgnore，但必须有@ExcelPropertyif(excelIgnore == null && excelPropertyAnnotation != null){//添加字段CtField enameField = new CtField(pool.getCtClass("java.lang.String"),field.getName(),ctClass);enameField.setModifiers(Modifier.PRIVATE);FieldInfo fieldInfo = enameField.getFieldInfo();//为字段添加getXXX和setXXX方法ctClass.addMethod(CtNewMethod.getter("get" + StringUtils.upcaseFirstChar(field.getName()), enameField));ctClass.addMethod(CtNewMethod.setter("set" + StringUtils.upcaseFirstChar(field.getName()), enameField));//字段上加注解AnnotationsAttribute fileAnnotationAttr = new AnnotationsAttribute(constpool, AnnotationsAttribute.visibleTag);//为字段加上ColumnWidth注解if(columnWidthAnnotation != null){int columnWidth = columnWidthAnnotation.value();//列宽值Annotation cloumnWidthAnno = new Annotation(ColumnWidth.class.getName(), constpool);cloumnWidthAnno.addMemberValue("value", new IntegerMemberValue(classFile.getConstPool(), columnWidth));fileAnnotationAttr.addAnnotation(cloumnWidthAnno);}//为字段加上ExcelProperty注解
//                                int index = excelPropertyAnnotation.index();                //indexString[] colValue = excelPropertyAnnotation.value();       //列名称Annotation excelPropertyAnno = new Annotation(ExcelProperty.class.getName(), constpool);//加入index值excelPropertyAnno.addMemberValue("index", new IntegerMemberValue(constpool, index));//加入列名称的值StringMemberValue[] elements = new StringMemberValue[colValue.length];excelPropertyAnno.addMemberValue("index", new IntegerMemberValue(constpool, index));for (int j = 0; j < colValue.length; j++) {elements[j] = new StringMemberValue(colValue[j], constpool);}ArrayMemberValue arrayMemberValue = new ArrayMemberValue(constpool);arrayMemberValue.setValue(elements);excelPropertyAnno.addMemberValue("value", arrayMemberValue);fileAnnotationAttr.addAnnotation(excelPropertyAnno);fieldInfo.addAttribute(fileAnnotationAttr);//添加字段ctClass.addField(enameField);//index递增index++;}}}}Class newClass = ctClass.toClass();return newClass;}} catch (CannotCompileException e) {e.printStackTrace();} catch (NotFoundException e) {e.printStackTrace();}return beanClazz;}
}

3、创建bean实体做查询后封装使用；

package bean;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;import java.io.Serializable;
import java.util.Date;@ContentRowHeight(15)//统一行高设置，如下述属性中还有此设置，生效属性上设置
@HeadRowHeight(20)//标题头部行高设置
@ColumnWidth(15)//统一列宽设置，如下述属性中还有此设置，生效属性上设置
public class Student implements Serializable {/*** 序号*/@ColumnWidth(10)//index表示此内容在第几列，从0开始，value表示此列的标题，从最头上的标题开始写，如果两个属性有        //公共的标题，此标题合并单元格@ExcelProperty(index = 0, value = {"学生信息表", "序号" })private String rowNum;/*** 姓名*/@ColumnWidth(20)@ExcelProperty(index = 1, value = {"学生信息表", "姓名" })private String name;/*** 展会项目名称*/@ColumnWidth(10)@ExcelProperty(index = 2, value = {"学生信息表", "性别" })private String sex;/*** 参展企业名称*/@ColumnWidth(10)@ExcelProperty(index = 3, value = {"学生信息表", "年龄" })private String age;public String getRowNum() {return rowNum;}public void setRowNum(String rowNum) {this.rowNum = rowNum;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getAge() {return age;}public void setAge(String age) {this.age = age;}
}

4、查询数据封装集合并导出

@Autowired//需要注入
private EasyExportUtill easyExportUtill;
List<Person> beans= new ArrayList<>();
for(int i=0;i<10;i++){Person bean =new Person();bean.setRowNum(i+1);bean.setName("张三");bean.setSex("男");bean.setAge("18");beans.add(bean);
}
String excelName="学生信息表";
String excelPath = easyExportUtill.createExcel(beans, Person.class, excelName, "123", null,null);

至此导出完毕；

如需将excel下载出来，可以直接调用以下方法；

最后附上下载保存好的excel文件的工具类方法：
 

public class DownloadFileUtil{/*** <p>方法名称: downloadExcel | 描述: 下载文件</p>** @param request :* @param response :* @param filePath :* @return void* @date:   2019/10/25 13:01*/public static void downloadExcel(HttpServletRequest request, HttpServletResponse response, String filePath, String excelName) {OutputStream ouputStream = null;InputStream inputStream = null;try {if(StringUtils.isNotEmpty(filePath)){File file = new File(filePath);String filename = encodeChineseDownloadFileName(request, excelName + Constants.EXCEL_SUFFIX_XLSX);response.reset();response.setHeader("Set-Cookie", "cookiename=cookievalue; path=/; Domain=domainvaule; Max-age=seconds; HttpOnly");response.setContentType("application/vnd.ms-excel;charset=UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + filename);response.setHeader("Pragma", "No-cache");ouputStream = new BufferedOutputStream(response.getOutputStream());inputStream = new BufferedInputStream(new FileInputStream(file));byte [] buffer = new byte[inputStream.available()];inputStream.read(buffer);ouputStream.write(buffer);ouputStream.flush();}} catch (Exception e) {e.printStackTrace();logger.error(e.getMessage(), e);} finally {try {if(ouputStream != null){ouputStream.close();}if(inputStream != null){inputStream.close();}} catch (IOException e) {e.printStackTrace();logger.error(e.getMessage(), e);}}}/*** 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性** @throws Exception*/private static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName) throws Exception {String filename = null;String agent = request.getHeader("USER-AGENT");if (null != agent) {if (-1 != agent.indexOf("Firefox")) {// Firefoxfilename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?=";} else if (-1 != agent.indexOf("Chrome")) {filename = new String(pFileName.getBytes(), "ISO8859-1");} else {// IE7+filename = java.net.URLEncoder.encode(pFileName, "UTF-8");filename = filename.replace("+", "%20");}} else {filename = pFileName;}return filename;}/*** <p>方法名称: createExcelPath | 描述: 创建导出excel的路径</p>** @param userId :* @return java.lang.String* @date:   2019/9/29 15:14*/private static String createExcelPath(String userId){String finalFileFolder = createFolder(userId);String excelFile = finalFileFolder + IdUtils.randomString() + Constants.EXCEL_SUFFIX_XLSX;return excelFile;}/*** <p>方法名称: createFolder | 描述: 创建文件夹</p>** @param userId :* @return java.lang.String* @date:   2019/10/17 16:51*/private static String createFolder(String userId){Calendar calendar = Calendar.getInstance();int year = calendar.get(Calendar.YEAR);int month = calendar.get(Calendar.MONTH) + 1;int day = calendar.get(Calendar.DAY_OF_MONTH);String finalFileFolder = fileFolderPath + year + File.separator + month + File.separator + day + File.separator + userId + File.separator;FileUtil.createFolder(finalFileFolder);return finalFileFolder;}
}

使用：

if(StringUtils.isNotEmpty(excelPath)){DownloadUtil.downloadExcel(request, response, excelPath, excelName);
}