Easyexcel导入导出，支持导出多个sheet到一个excel文件和导出多个sheet到多个excel文件并压缩到一个zip文件
本文地址：https://www.cnblogs.com/muphy/p/16254922.html
- 可以为每个excel文件指定模板，如果模板不存在则使用默认导出
- 支持导出多个sheet到一个excel文件
- 导出多个sheet到多个excel文件并压缩到一个zip文件
- ExcelData和ExcelShellData只是为了简化参数指定文件名、模板名和数据列表、sheet列表
maven导入Easyexcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- excel工具 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
例子代码结构
![]()
模板内容
![]()
导出效果
创建三个实体类数据（Excel1Sheet1和Excel1Sheet2分别是excelA的两个sheet的数据，Excel2Sheet1是excelB的数据）
package com.example.demo.excel.entity;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class Excel1Sheet1 {
@ExcelProperty(value = "用户账号", order = 2)
private String userName;
@ExcelProperty(value = "用户密码", order = 3)
private String password;
}
package com.example.demo.excel.entity;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class Excel1Sheet2 {
@ExcelProperty(value = "姓名", order = 1)
private String realName;
@ExcelProperty(value = "毕业学校", order = 6)
private String schoolName;
}
package com.example.demo.excel.entity;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class Excel2Sheet1 {
@ExcelProperty(value = "姓名", order = 1)
private String realName;
@ExcelProperty(value = "政治面貌", order = 3)
private String politicalStatus;
}
控制层ExcelController.java
package com.example.demo.excel.controller;
import com.example.demo.excel.entity.Excel1Sheet1;
import com.example.demo.excel.entity.Excel1Sheet2;
import com.example.demo.excel.entity.Excel2Sheet1;
import com.example.demo.excel.util.ExcelUtils;
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.List;
@RestController
@RequestMapping("/excel")
public class ExcelController {
// 导出到一个压缩文件 包含两个excel，其中一个包含两个sheet，另一个使用模块
@GetMapping("/export/zip")
public void exportZip(HttpServletResponse response) {
List<ExcelUtils.ExcelData> excelData = exportAllData();
ExcelUtils.exportZip("压缩导出", excelData, response);
}
// 导出到一个excel文件 包含两个sheet
@GetMapping("/export")
public void exportExcel(HttpServletResponse response) {
List<ExcelUtils.ExcelData> excelData = exportAllData();
ExcelUtils.ExcelData excelA = excelData.get(0);//只要excelA
ExcelUtils.exportExcel(excelA, response);
}
// 构建数据 Excel1Sheet1和Excel1Sheet2分别是excelA的两个sheet的数据，Excel2Sheet1是excelB的数据
public List<ExcelUtils.ExcelData> exportAllData() {
List<ExcelUtils.ExcelData> excelDataList = new ArrayList<>();
//excelA
ExcelUtils.ExcelData excelA = new ExcelUtils.ExcelData();
excelA.setFilename("excelA");
//excelA->Excel1Sheet1
List<Excel1Sheet1> excel1Sheet1s = new ArrayList<>();
excel1Sheet1s.add(new Excel1Sheet1("azi", "pwd123"));
excel1Sheet1s.add(new Excel1Sheet1("ruphy", "123456"));
excelA.addShellData(new ExcelUtils.ExcelShellData<>(excel1Sheet1s, "Excel1Sheet1", Excel1Sheet1.class));
//excelA->Excel1Sheet2
List<Excel1Sheet2> excel1Sheet2s = new ArrayList<>();
excel1Sheet2s.add(new Excel1Sheet2("阿紫", "北大"));
excel1Sheet2s.add(new Excel1Sheet2("子安", "清华"));
excelA.addShellData(new ExcelUtils.ExcelShellData<>(excel1Sheet2s, "Excel1Sheet2", Excel1Sheet2.class));
excelDataList.add(excelA);
//excelB
ExcelUtils.ExcelData excelB = new ExcelUtils.ExcelData();
excelB.setFilename("excelB");//设置excelB的文件名
excelB.setTemplateFilename("excel-template/excelB.xls");//设置excelB的模板
//excelB-Excel2Sheet1
List<Excel2Sheet1> excel2Sheet1s = new ArrayList<>();
excel2Sheet1s.add(new Excel2Sheet1("阿紫", "群众"));
excel2Sheet1s.add(new Excel2Sheet1("子安", "武当会员"));
excelB.addShellData(new ExcelUtils.ExcelShellData<>(excel2Sheet1s, "Excel2Sheet1", Excel2Sheet1.class));//设置excelB的sheet数据
excelDataList.add(excelB);
return excelDataList;
}
}
导出工具类ExcelUtils.java
点击查看代码
package com.example.demo.excel.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
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 lombok.Data;
import org.springframework.core.io.Resource;
import org.springframework.util.StringUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
/**
* Excel相关处理
*
* @author 子安
*/
public class ExcelUtils {
/**
* 同步导入(适用于小数据量)
*
* @param is 输入流
* @return 转换后集合
*/
public static <T> List<T> importExcel(InputStream is, Class<T> clazz) {
return EasyExcel.read(is).head(clazz).autoCloseStream(false).sheet().doReadSync();
}
/**
* 导出excel
*
* @param list 导出数据集合
* @param filename 工作表的名称
* @return 结果
*/
public static <T> void exportExcel(List<T> list, String filename, Class<T> clazz, HttpServletResponse response) {
if (list == null) {
list = new ArrayList<>();
}
try {
response.reset();
setAttachmentResponseHeader(response, filename);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
ServletOutputStream os = response.getOutputStream();
EasyExcel.write(os, clazz).autoCloseStream(false)
// 自动适配
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// 大数值自动转换 防止失真
.registerConverter(new ExcelBigNumberConvert()).sheet(filename).doWrite(list);
} catch (IOException e) {
response.reset();
throw new RuntimeException("导出Excel异常", e);
}
}
public static void exportExcel(ExcelData excelData, OutputStream os) {
if (excelData == null) {
throw new RuntimeException("导出数据为空！");
}
ExcelWriter excelWriter = null;
try {
ExcelWriterBuilder writerBuilder = EasyExcel.write(os).autoCloseStream(false)
// 自动适配
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// 大数值自动转换 防止失真
.registerConverter(new ExcelBigNumberConvert());
if (excelData.getTemplateFile() != null) {
writerBuilder.withTemplate(excelData.getTemplateFile());
}
excelWriter = writerBuilder.build();
for (int i = 0; i < excelData.getSheetDataList().size(); i++) {
ExcelSheetData<?> sheetData = excelData.getSheetDataList().get(i);
ExcelWriterSheetBuilder sheetBuilder;
String sheetName = sheetData.getKey();
if (StringUtils.isEmpty(sheetName)) {
sheetBuilder = EasyExcel.writerSheet(i);
} else {
sheetBuilder = EasyExcel.writerSheet(sheetName);
}
WriteSheet sheet = sheetBuilder.build();
Object value = sheetData.getValue();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
if (value instanceof Collection) {
if (excelData.getTemplateFile() == null) {
sheetBuilder.head(sheetData.getClazz());
excelWriter.write((Collection<?>) value, sheet);
} else {
// 多表导出必须使用 FillWrapper
excelWriter.fill(new FillWrapper(sheetData.getKey(), (Collection<?>) value), fillConfig, sheet);
}
} else {
if (excelData.getTemplateFile() != null) {
excelWriter.fill(value, sheet);
}
}
}
} catch (Exception e) {
throw new RuntimeException("导出Excel异常", e);
} finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
}
/**
* 导出多个sheet到一个excel文件 支持模板和直接创建
*/
public static void exportExcel(ExcelData excelData, HttpServletResponse response) {
if (excelData == null) {
throw new RuntimeException("导出数据为空！");
}
try {
String filename = excelData.getFilename();
response.reset();
setAttachmentResponseHeader(response, filename);
ServletOutputStream os = response.getOutputStream();
exportExcel(excelData, os);
} catch (Exception e) {
response.reset();
throw new RuntimeException("导出Excel异常", e);
}
}
public static void exportZip(String zipFilename, ExcelData excelData, HttpServletResponse response) {
exportZip(zipFilename, Collections.singletonList(excelData), response);
}
public static void exportZip(ExcelData excelData, OutputStream os) {
exportZip(Collections.singletonList(excelData), os);
}
/**
* 导出多个sheet到多个excel文件，并压缩到一个zip文件 支持模板和直接创建
*/
public static void exportZip(String zipFilename, List<ExcelData> excelDataList, HttpServletResponse response) {
if (zipFilename == null || zipFilename.isEmpty()) {
zipFilename = "export";
} else if (zipFilename.toLowerCase(Locale.ROOT).endsWith(".zip")) {
zipFilename = zipFilename.substring(0, zipFilename.length() - 4);
}
if (excelDataList == null || excelDataList.isEmpty()) {
throw new RuntimeException("导出数据为空！");
}
try {
// 这里URLEncoder.encode可以防止中文乱码
zipFilename = URLEncoder.encode(zipFilename, "utf-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + zipFilename + ".zip");
response.setContentType("application/x-msdownload");
response.setCharacterEncoding("utf-8");
//开始存入
ServletOutputStream os = response.getOutputStream();
exportZip(excelDataList, os);
} catch (Exception e) {
response.reset();
throw new RuntimeException("导出Excel异常", e);
}
}
public static void exportZip(List<ExcelData> excelDataList, OutputStream os) {
if (excelDataList == null || excelDataList.isEmpty()) {
throw new RuntimeException("导出数据为空！");
}
try {
//开始存入
try (ZipOutputStream zipOut = new ZipOutputStream(os)) {
try {
for (ExcelData excelData : excelDataList) {
zipOut.putNextEntry(new ZipEntry(excelData.getFilename()));
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
try {
exportExcel(excelData, outputStream);
} catch (Exception e) {
throw new RuntimeException("导出Excel异常", e);
}
outputStream.writeTo(zipOut);
zipOut.closeEntry();
}
} catch (Exception e) {
throw new RuntimeException("导出Excel异常", e);
}
}
} catch (IOException e) {
throw new RuntimeException("导出Excel异常", e);
}
}
/**
* excel数据
*/
@Data
public static class ExcelData {
private List<ExcelSheetData<?>> sheetDataList = new ArrayList<>();
private String filename = "export.xlsx";
private String templateFilename;
public File getTemplateFile() {
if (templateFilename == null) {
return null;
}
File templateFile = new File(templateFilename);
if (templateFile.exists() && templateFile.isFile()) {
return templateFile;
}
Resource resource = new org.springframework.core.io.ClassPathResource(templateFilename);
if (resource.exists()) {
File file = null;
try {
file = resource.getFile();
} catch (IOException e) {
e.printStackTrace();
}
if (file != null) {
if (file.exists() && file.isFile()) {
return file;
}
return null;
}
}
return null;
}
public void setSheetDataList(List<ExcelSheetData<?>> sheetDataList) {
if (sheetDataList != null) {
this.sheetDataList = sheetDataList;
}
}
public String getFilename() {
if (filename == null || filename.isEmpty()) {
filename = "export.xlsx";
} else {
String fn = filename.toLowerCase(Locale.ROOT);
if (!(fn.endsWith(".xlsx") || fn.endsWith(".xls"))) {
filename = filename + ".xlsx";
}
}
return filename;
}
public void addSheetData(ExcelSheetData<?> excelSheetData) {
this.sheetDataList.add(excelSheetData);
}
}
/**
* sheet数据
*/
@Data
public static class ExcelSheetData<T> {
// private List<T> list;
private Object value;
private String key;
private Class<T> clazz;
//public ExcelSheetData(List<T> list, String sheetName, Class<T> clazz) {
public ExcelSheetData(Object value, String key, Class<T> clazz) {
this.value = value;
this.key = key;
this.clazz = clazz;
}
}
private static void setAttachmentResponseHeader(HttpServletResponse response, String filename) throws UnsupportedEncodingException {
String encode = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString());
String percentEncodedFileName = encode.replaceAll("\\+", "%20");
String contentDispositionValue = "attachment; filename=" +
percentEncodedFileName + ";filename*=utf-8''" +
percentEncodedFileName;
response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");
response.setHeader("Content-disposition", contentDispositionValue);
response.setHeader("download-filename", percentEncodedFileName);
}
/**
* Excel 数值长度位15位 大于15位的数值转换位字符串
*/
public static class ExcelBigNumberConvert implements Converter<Long> {
@Override
public Class<Long> supportJavaTypeKey() {
return Long.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Long convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
Object data = cellData.getData();
if (data == null) {
return null;
}
String s = String.valueOf(data);
if (s.matches("^\\d+$")) {
return Long.parseLong(s);
}
return null;
}
@Override
public WriteCellData<Object> convertToExcelData(Long object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
if (object != null) {
String str = object.toString();
if (str.length() > 15) {
return new WriteCellData<>(str);
}
}
WriteCellData<Object> cellData = new WriteCellData<>(new BigDecimal(object));
cellData.setType(CellDataTypeEnum.NUMBER);
return cellData;
}
}
}
浙公网安备 33010602011771号