前言:开发各种统计系统以及报表系统之类的不可避免的就是导出excel功能,传统的poi用过的都知道,每个sheet每个row都需要去定义去美化,繁琐不说还很浪费时间,最近本人发现两种较快速的方法,一种是excel模版方式(模版性太强,不灵活),一种是使用阿里开源的easyexcel(灵活方便);下面对两种方式都做一个简单使用介绍:
一、XLSTransformer模板方式导出excel
1、首先需要引入一些依赖的jar包(maven项目直接查询对应的依赖即可):
jxls-core-0.9.7.jar
jxls-reader-0.9.7.jar
poi-3.6.jar
commons-jexl-1.1.jar
commons-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}")//在springboo