EasyExcel是一款阿里开源的一款非常优秀Excel导入导出工具，使用内存小，处理速度快，使用方便，正好最近项目中有用到多个excel导出下载的功能，便记录下
EasyExcel使用git地址：GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具https://github.com/alibaba/easyexcel
1. 首先导出pom依赖
org.apache.poi
poi-ooxml
${poi.version}
com.alibaba
easyexcel
3.1.1
cn.hutool
hutool-all
${hutool.version}
org.projectlombok
lombok
2. 添加使用的工具类
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.util.IdUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.ruoyi.common.convert.ExcelBigNumberConvert;
import com.ruoyi.common.excel.CellMergeStrategy;
import com.ruoyi.common.excel.DefaultExcelListener;
import com.ruoyi.common.excel.ExcelListener;
import com.ruoyi.common.excel.ExcelResult;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.file.FileUtils;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.Collection;
import java.util.List;
import java.util.Map;
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class ExcelUtil {
public static List importExcel(InputStream is, Class clazz) {
return EasyExcel.read(is).head(clazz).autoCloseStream(false).sheet().doReadSync();
}
public static ExcelResult importExcel(InputStream is, Class clazz, boolean isValidate) {
DefaultExcelListener listener = new DefaultExcelListener<>(isValidate);
EasyExcel.read(is, clazz, listener).sheet().doRead();
return listener.getExcelResult();
}
public static ExcelResult importExcel(InputStream is, Class clazz, ExcelListener listener) {
EasyExcel.read(is, clazz, listener).sheet().doRead();
return listener.getExcelResult();
}
public static void exportExcel(List list, String sheetName, Class clazz, HttpServletResponse response) {
exportExcel(list, sheetName, clazz, false, response);
}
public static void exportExcel(List list, String sheetName, Class clazz, boolean merge, HttpServletResponse response) {
try {
resetResponse(sheetName, response);
ServletOutputStream os = response.getOutputStream();
ExcelWriterSheetBuilder builder = EasyExcel.write(os, clazz)
.autoCloseStream(false)
// 自动适配
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// 大数值自动转换 防止失真
.registerConverter(new ExcelBigNumberConvert())
.sheet(sheetName);
if (merge) {
// 合并处理器
builder.registerWriteHandler(new CellMergeStrategy(list, true));
}
builder.doWrite(list);
} catch (IOException e) {
throw new RuntimeException("导出Excel异常");
}
}
public static void exportTemplate(List
3. 添加导出模板，注意一定要添加在resource下面！！
- 单表导出模板
- 多表导出模板
- 单表数据打包模板（和单表的模板是一样的，同理，也可以多表打包导出）
4. 测试excelUtil工具类以及导出结果
import cn.hutool.core.collection.CollUtil;
import com.包名.ExcelUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Api(value = "测试Excel功能", tags = {"测试Excel功能"})
@RestController
@RequestMapping("/demo/excel")
public class TestExcelController {
@ApiOperation(value = "单列表多数据")
@GetMapping("/exportTemplateOne")
public void exportTemplateOne(HttpServletResponse response) {
Map map = new HashMap<>();
map.put("title","单列表多数据");
map.put("test1","数据测试1");
map.put("test2","数据测试2");
map.put("test3","数据测试3");
map.put("test4","数据测试4");
map.put("testTest","666");
List list = new ArrayList<>();
list.add(new TestObj("单列表测试1", "列表测试1", "列表测试2", "列表测试3", "列表测试4"));
list.add(new TestObj("单列表测试2", "列表测试5", "列表测试6", "列表测试7", "列表测试8"));
list.add(new TestObj("单列表测试3", "列表测试9", "列表测试10", "列表测试11", "列表测试12"));
ExcelUtil.exportTemplate(CollUtil.newArrayList(map,list),"单列表.xlsx", "excel/单列表.xlsx", response);
}
@ApiOperation(value = "多列表多数据")
@GetMapping("/exportTemplateMuliti")
public void exportTemplateMuliti(HttpServletResponse response) {
Map map = new HashMap<>();
map.put("title1","标题1");
map.put("title2","标题2");
map.put("title3","标题3");
map.put("title4","标题4");
map.put("author","lgd");
List list1 = new ArrayList<>();
list1.add(new TestObj1("list1测试1", "list1测试2", "list1测试3"));
list1.add(new TestObj1("list1测试4", "list1测试5", "list1测试6"));
list1.add(new TestObj1("list1测试7", "list1测试8", "list1测试9"));
List list2 = new ArrayList<>();
list2.add(new TestObj1("list2测试1", "list2测试2", "list2测试3"));
list2.add(new TestObj1("list2测试4", "list2测试5", "list2测试6"));
List list3 = new ArrayList<>();
list3.add(new TestObj1("list3测试1", "list3测试2", "list3测试3"));
List list4 = new ArrayList<>();
list4.add(new TestObj1("list4测试1", "list4测试2", "list4测试3"));
list4.add(new TestObj1("list4测试4", "list4测试5", "list4测试6"));
list4.add(new TestObj1("list4测试7", "list4测试8", "list4测试9"));
list4.add(new TestObj1("list4测试10", "list4测试11", "list4测试12"));
Map multiListMap = new HashMap<>();
multiListMap.put("map",map);
multiListMap.put("data1",list1);
multiListMap.put("data2",list2);
multiListMap.put("data3",list3);
multiListMap.put("data4",list4);
ExcelUtil.exportTemplateMultiList(multiListMap, "多列表.xlsx", "excel/多列表.xlsx", response);
}
@ApiOperation(value = "测试单表数据打包zip")
@GetMapping("/exportTemplateZip")
public void exportTemplateZip(HttpServletResponse response) {
Map map = new HashMap<>();
map.put("title","单列表多数据");
map.put("test1","数据测试1");
map.put("test2","数据测试2");
map.put("test3","数据测试3");
map.put("test4","数据测试4");
map.put("testTest","666");
List list = new ArrayList<>();
list.add(new TestObj("单列表测试1", "列表测试1", "列表测试2", "列表测试3", "列表测试4"));
list.add(new TestObj("单列表测试2", "列表测试5", "列表测试6", "列表测试7", "列表测试8"));
list.add(new TestObj("单列表测试3", "列表测试9", "列表测试10", "列表测试11", "列表测试12"));
ExcelUtil.exportTemplateZip(CollUtil.newArrayList(map,list),"多个单列表打包.xlsx", "excel/导出使用的模板.xlsx", response);
}
@Data
@AllArgsConstructor
static class TestObj1 {
private String test1;
private String test2;
private String test3;
}
@Data
@AllArgsConstructor
static class TestObj {
private String name;
private String list1;
private String list2;
private String list3;
private String list4;
}
}
5. 导出功能完毕！有什么不懂的，欢迎留言~
