在入职公司做项目期间,发现很多功能需求需要导出或是导入Excel文件,因此就想要写一篇文章来总结一下导出导入Excle的步骤。
导出Excel
1、创建一个XxxExcel的POJO类,用来保存Excel中的相关信息,主要有表的ID、表名、错误信息、以及列名等字段,如果有很多功能模块需要导出Excel,可以创建一个静态实体类保存公共字段,其它的Excel实体类只需要继承这个静态类,之后则只需要Excel文件中的列对应的字段即可;
Excel基类:
public abstract class Sheet {
@ColumnWidth(30)
@HeadStyle(fillForegroundColor = Font.COLOR_RED)
@ContentFontStyle(color = Font.COLOR_RED)
@ExcelProperty("错误信息")
private String error;
/**
* 表格ID由各个模块进行自定义,各个模块ID不能重复,且定义后不可修改
*
* @return 表格ID
*/
public abstract String getSheetId();
/**
* 模块名称
*
* @return 表格名称
*/
public abstract String getSheetName();
public String getError() {
return error;
}
public Sheet setError(String error) {
this.error = error;
return this;
}
}
@ColumnWidth() :列宽 @ExcelProperty(""):表头 @HeadStyle(fillForegroundColor = Font.COLOR_RED):表头风格 @ContentFontStyle(color = Font.COLOR_RED):内容风格 @HeadRowHeight(40):表头高度 @ContentRowHeight(20):内容行高度
XxxExcel POJO类:
@HeadRowHeight(40)
@ContentRowHeight(20)
public class XxxExcel extends Sheet {
@ColumnWidth(10)
@ExcelProperty("姓名")
private String userName;
@ColumnWidth(20)
@ExcelProperty("电话")
private String phoneNum;
@ColumnWidth(30)
@ExcelProperty("地址")
private String address;
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName= userName;
}
public String getPhoneNum() {
return phoneNum;
}
public void setPhoneNum(String phoneNum) {
this.phoneNum= phoneNum;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address= address;
}
}
2、将需要导出的信息保存为一个List<XxxPojo>
;
3、将List<XxxPojo>
转换为List<XxxExcel>
;
4、编写表格导入组件;
public class DocumentService {
@Resource
private OSSConfigProperties ossConfigProperties;
@Resource
private OSS oss;
private static final List<String> DEFAULT_EXCLUDE_FIELDS = Collections.singletonList("error");
public ByteArrayOutputStream exportRecord(List<?> list) {
return exportRecord(list, DEFAULT_EXCLUDE_FIELDS);
}
public ByteArrayOutputStream exportRecord(List<?> list, Collection<String> excludeFields) {
Sheet sheet = (Sheet) list.get(0);
try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
EasyExcel.write(outputStream, list.get(0).getClass()).excludeColumnFieldNames(excludeFields).sheet(sheet.getSheetName()).doWrite(list);
return outputStream;
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public String exportErrorRecord(List<?> list) {
return exportErrorRecord(list, null);
}
public String exportErrorRecord(List<?> list, List<String> excludeFields) {
Sheet sheet = (Sheet) list.get(0);
try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
if (CollectionUtils.isNotEmpty(excludeFields)) {
EasyExcel.write(outputStream, list.get(0).getClass())
.sheet(sheet.getSheetName())
.excludeColumnFieldNames(excludeFields)
.doWrite(list);
} else {
EasyExcel.write(outputStream, list.get(0).getClass())
.sheet(sheet.getSheetName())
.doWrite(list);
}
try (ByteArrayInputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray())) {
String documentName = String.format("xxx/temp/excel/%s/%s/%s.xlsx", sheet.getSheetId(),
LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")), UUID.randomUUID());
oss.putObject(ossConfigProperties.getBucketName(), documentName, inputStream);
return String.format("%s/%s/%s", ossConfigProperties.getPublicEndpoint().endsWith("/") ? ossConfigProperties.getPublicEndpoint().substring(ossConfigProperties.getPublicEndpoint().length() - 1) : ossConfigProperties.getPublicEndpoint(),
ossConfigProperties.getBucketName(), documentName);
}
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public ByteArrayOutputStream exportDocx(Docx docx) {
InputStream inputStream;
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
try {
inputStream = new ClassPathResource(docx.getTemplateName()).getInputStream();
IXDocReport report = XDocReportRegistry
.getRegistry()
.loadReport(inputStream, TemplateEngineKind.Freemarker);
FieldsMetadata metadata = report.createFieldsMetadata();
metadata.addFieldAsImage("images", "image", NullImageBehaviour.RemoveImageTemplate);
report.setFieldsMetadata(metadata);
IContext context = report.createContext();
Field[] fields = docx.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
String key = field.getName();
String method = String.format("%s%s", key.substring(0, 1).toUpperCase(), key.substring(1));
Method getMethod = docx.getClass().getMethod("get" + method);
if ("getImages".equals(getMethod.getName())) {
List<String> imageUrls = (List<String>) getMethod.invoke(docx);
List<IImageProvider> images = new ArrayList<>();
for (String imageUrl : imageUrls) {
String fileName = imageUrl.replace(ossConfigProperties.getPublicEndpoint().concat("/").concat(ossConfigProperties.getBucketName()).concat("/"), "");
OSSObject object = oss.getObject(ossConfigProperties.getBucketName(), fileName);
IImageProvider imageProvider = new ByteArrayImageProvider(object.getObjectContent());
images.add(imageProvider);
context.put("image1", imageProvider);
}
context.put(key, images);
} else {
context.put(key, getMethod.invoke(docx));
}
}
report.process(context, outputStream);
inputStream.close();
return outputStream;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
5、使用exportRecord(List<XxxExcel>)
方法获取ByteArrayOutputStream的对象out;
6、将out对象转为byte[]返回给前端,byte[] body = out.toByteArray()
。
导入Excel
1、同导入Excel,需要XxxExcel Pojo类;
2、配置单行数据读取带列数据验证监听器;
public class ColumnValidationListener<T> implements ReadListener<T> {
private static final Logger log = LoggerFactory.getLogger(ColumnValidationListener.class);
private RowReadExceptionConsumer exceptionConsumer;
private Consumer<T> rowDataConsumer;
private ColumnValidationRule<T> rules;
private int batchSize = 0;
private Consumer<List<T>> batchRowConsumer;
private int rowCount = 0;
private List<T> batchRowList;
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
if (batchSize > 0 && rowCount % batchSize == 0) {
batchRowList.clear();
}
if (Objects.nonNull(exceptionConsumer)) {
exceptionConsumer.accept(new RowReadException(exception, context));
return;
}
ReadListener.super.onException(exception, context);
}
@Override
public void invoke(T data, AnalysisContext context) {
rowCount++;
if (Objects.nonNull(rules)) {
rules.validate(data);
}
if (Objects.nonNull(rowDataConsumer)) {
rowDataConsumer.accept(data);
}
if (Objects.isNull(batchRowConsumer)) {
return;
}
batchRowList.add(data);
if (rowCount % batchSize == 0) {
batchRowConsumer.accept(batchRowList);
batchRowList.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (batchRowList.size() > 0 && Objects.nonNull(batchRowConsumer)) {
batchRowConsumer.accept(batchRowList);
batchRowList.clear();
}
log.info("导入成功,已导入{}条数据", rowCount);
}
public void setExceptionConsumer(RowReadExceptionConsumer exceptionConsumer) {
this.exceptionConsumer = exceptionConsumer;
}
public void setRowDataConsumer(Consumer<T> rowDataConsumer) {
this.rowDataConsumer = rowDataConsumer;
}
public void setRules(ColumnValidationRule<T> rules) {
this.rules = rules;
}
public void setBatchRowConsumer(int batchSize, Consumer<List<T>> batchRowConsumer) {
this.batchSize = batchSize;
this.batchRowConsumer = batchRowConsumer;
this.batchRowList = new ArrayList<>(batchSize);
}
public static class Builder<T> {
private RowReadExceptionConsumer exceptionConsumer;
private Consumer<T> rowDataConsumer;
private ColumnValidationRule<T> rules;
private int batchSize = 0;
private Consumer<List<T>> batchRowConsumer;
public Builder<T> exceptionConsumer(RowReadExceptionConsumer exceptionConsumer) {
this.exceptionConsumer = exceptionConsumer;
return this;
}
public Builder<T> rowDataConsumer(Consumer<T> rowDataConsumer) {
this.rowDataConsumer = rowDataConsumer;
return this;
}
public Builder<T> batchRowConsumer(int batchSize, Consumer<List<T>> batchRowConsumer) {
this.batchRowConsumer = batchRowConsumer;
this.batchSize = batchSize;
return this;
}
public Builder<T> rules(ColumnValidationRule<T> rules) {
this.rules = rules;
return this;
}
public ColumnValidationListener<T> build() {
ColumnValidationListener<T> listener = new ColumnValidationListener<>();
listener.setExceptionConsumer(exceptionConsumer);
listener.setRules(rules);
listener.setRowDataConsumer(rowDataConsumer);
listener.setBatchRowConsumer(batchSize, batchRowConsumer);
return listener;
}
}
}
3、使用getInputStream()
方法获取MultipartFile对象的inputStream();
4、通过inputStream导入Excel,若有error,则返回errorRecord的url;
public String xxxExcelImport(InputStream inputStream) {
List<XxxExcel> errList = new ArrayList<>();
EasyExcel.read(
inputStream,
XxxExcel.class,
new ColumnValidationListener.Builder<XxxExcel>()
.rules(ColumnValidationRule.<XxxExcel>rules()
.func(XxxExcel::getUserName, Objects::nonNull, ERROR_NULL)
.func(XxxExcel::getPhoneNum, Objects::nonNull, ERROR_NULL)
.func(XxxExcel::getAddress, Objects::nonNull, ERROR_NULL)
).exceptionConsumer(exception -> {
exception.printStackTrace();
XxxExcel errRow = (XxxExcel) exception
.getContext()
.readRowHolder()
.getCurrentRowAnalysisResult();
errRow.setError(exception.getMessage());
errList.add(errRow);
})
.batchRowConsumer(100, bathRow -> {
List<XxxPojo> list = new ArrayList<>();
bathRow.forEach(row -> {
XxxPojo one = new XxxPojo();
one.setUserName(row.getUserName());
one.setPhoneNum(row.getPhoneNum());
one.setAddress(row.getAddress());
list.add(one);
});
saveOrUpdateBatch(list);
})
.build()
).sheet().doRead();
if (errList.size() > 0) {
return documentService.exportErrorRecord(errList);
}
return null;
}
这是一套导出/导入Excel的通用模板,需要根据具体业务需求进行修改!