本文介绍了阿里开源的EasyExcel框架，它简化了Java操作Excel的过程。提供了读（导入）和写（导出）的功能，包括依赖配置、表头映射类的定义以及具体的读写实现示例。在读取时，EasyExcel能够进行数据校验，确保导入数据的准确性。在导出时，通过简单的调用即可生成Excel文件。
官网（旧）
语雀官网地址
github仓库地址
实现
MAVEN依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>WEB - 读（导入）
-
通过url实现导入
-
url转MultipartFile对象
/**
* url转变为 MultipartFile对象
* @param url 文件路径
* @return
*/
public static MultipartFile createFileItem(String url) {
FileItem item = null;
try {
HttpURLConnection conn = (HttpURLConnection) new URL(url).openConnection();
conn.setReadTimeout(30000);
conn.setConnectTimeout(30000);
// 设置应用程序要从网络连接读取数据
conn.setDoInput(true);
conn.setRequestMethod("GET");
if (conn.getResponseCode() == HttpURLConnection.HTTP_OK) {
InputStream is = conn.getInputStream();
FileItemFactory factory = new DiskFileItemFactory(16, null);
String fileName = "fileName";
String textFieldName = "uploadfile";
item = factory.createItem(textFieldName, ContentType.APPLICATION_OCTET_STREAM.toString(), false, fileName);
OutputStream os = item.getOutputStream();
int bytesRead = 0;
byte[] buffer = new byte[8192];
while ((bytesRead = is.read(buffer, 0, 8192)) != -1) {
os.write(buffer, 0, bytesRead);
}
os.close();
is.close();
}
} catch (IOException e) {
throw new RuntimeException("文件下载失败", e);
}
return new CommonsMultipartFile(item);
}-
表头映射类
package com.haierp.easyexcel.head;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @ClassName PurchaseStockExcelHead
* @Description TODO
* @Author hzx
* @Date 2021-07-12 13:58
**/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PurchaseStockExcelHead {
@ExcelProperty(value = "供应商", index = 0)
private String supplierName;
@ExcelProperty(value = "仓库", index = 1)
private String warehouseName;
@ExcelProperty(value = "系统SKU_CODE", index = 2)
private String skuCode;
@ExcelProperty(value = "数量", index = 3)
private Integer quantity;
@ExcelProperty(value = "单价", index = 4)
private Double price;
@ExcelProperty(value = "买手费率", index = 5)
private Double commissionPoint;
}-
easyExcel 工具类
package com.haierp.easyexcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.haierp.util.JsonResult;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.util.*;
/**
* @ClassName easyExcel 工具类
* @Description TODO
* @Author hzx
* @Date 2021-07-12 14:10
**/
@Component
public class EasyExcelUtils extends AnalysisEventListener<Object> {
List<Object> list = Lists.newArrayList();
JsonResult<Object> message = new JsonResult<>();
Class clazz;
public EasyExcelUtils() {
super();
}
public EasyExcelUtils(Class clazz) {
super();
this.clazz = clazz;
}
/**
* invoke方法为一行一行读取excel内容
* @param data
* @param context
*/
@Override
public void invoke(Object data, AnalysisContext context) {
list.add(data);
}
/**
* invokeHeadMap读取excel表头，校验表头是否正确
* @param headMap 解析数据表头
* @param context
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
Map<Integer, String> head = Maps.newHashMap();
// 第二行为表头做处理，第一行不处理
Integer rowIndex = context.readRowHolder().getRowIndex();
try {
// 通过class获取到使用@ExcelProperty注解配置的字段
head = getIndexNameMap(clazz);
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
// 解析到的excel表头和实体配置的进行比对
Set<Integer> keySet = head.keySet();
for (Integer key : keySet) {
if (!headMap.containsKey(key)) {
message.setMsg("表头第"+key+1+"列为空，请参照模板填写");
throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel");
}
if ((!headMap.get(key).equals(head.get(key))) && rowIndex.equals(1)) {
message.setMsg("表头第"+key+1+"列【"+headMap.get(key)+"】与模板【"+head.get(key)+"】不一致，请参照模板填写");
throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel");
}
}
}
/**
* 读取完成之后进行的处理
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
message.setMsg("数据解析完成");
message.setSuccess(true);
}
/**
* 通过class获取类字段信息
* @param clazz
* @return
* @throws NoSuchFieldException
*/
public Map<Integer, String> getIndexNameMap(Class clazz) throws NoSuchFieldException {
Map<Integer, String> result = Maps.newHashMap();
Field field;
// //获取类中所有的属性
Field[] fields = clazz.getDeclaredFields();
for (Field item : fields) {
field = clazz.getDeclaredField(item.getName());
field.setAccessible(true);
// 获取根据注解的方式获取ExcelProperty修饰的字段
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty != null) {
// 索引值
int index = excelProperty.index();
// 字段值
String[] values = excelProperty.value();
StringBuilder value = new StringBuilder();
for (String v : values) {
value.append(v);
}
result.put(index, value.toString());
}
}
return result;
}
/**
* 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
* @param exception
* @param context
*/
@Override
public void onException(Exception exception, AnalysisContext context) {
// 如果是某一个单元格的转换异常 能获取到具体行号
// 如果要获取头的信息 配合invokeHeadMap使用
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
message.setMsg("第"+(excelDataConvertException.getRowIndex()+1)+"行，第"+(excelDataConvertException.getColumnIndex()+1)+"列解析异常，请参照模板填写");
}
}
public List<Object> getList() {
return list;
}
public void setList(List<Object> list) {
this.list = list;
}
public JsonResult<Object> getMessage() {
return message;
}
public void setMessage(JsonResult<Object> message) {
this.message = message;
}
}-
获取到MultipartFile的InputStream流
// 获取文件输出流
InputStream inputStream = CommonApi.createFileItem(fileUrl).getInputStream();
调用easyExcel的读方法
// 校验头解析数据
EasyExcel.read(inputStream, PurchaseStockExcelHead.class, easyExcelUtils).sheet().headRowNumber(2).doRead();
具体调用代码
EasyExcelUtils easyExcelUtils = new EasyExcelUtils(PurchaseStockExcelHead.class);
// 获取文件输出流
InputStream inputStream = CommonApi.createFileItem(fileUrl).getInputStream();
// 解析数据
EasyExcel.read(inputStream, PurchaseStockExcelHead.class, easyExcelUtils).sheet().headRowNumber(2).doRead();
JsonResult<Object> message = easyExcelUtils.getMessage();
if (!message.isSuccess()) { throw new ErpCommonException(message.getMsg()); }
List<Object> list = easyExcelUtils.getList();
if (CollectionUtils.isEmpty(list)) { throw new ErpCommonException("无解析数据，请检查导入文件"); }
// 得到数据后，去处理持久化
List<PurchaseStockExcelHead> purchaseStockExcelHeadList = (List<PurchaseStockExcelHead>)(Object) list;
// 持久化 - 业务逻辑 。。。WEB - 写（导出）
-
表头映射类
package com.haierp.easyexcel.write.head; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.DateTimeFormat; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.util.Date; /** * @ClassName SdReceiptExcelWriteHead * @Description TODO * @Author hzx * @Date 2021-07-14 17:01 **/ @Data @AllArgsConstructor @NoArgsConstructor public class SdReceiptExcelWriteHead { @ExcelProperty("外部订单号") private String targetNo; @ExcelProperty("系统货号") private String thirdSkuCode; @ExcelProperty("颜色") private String color; @ExcelProperty("规格") private String scale; @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒") @ExcelProperty("订单日期") private Date orderTime; @ExcelProperty("回执原因") private String reasonType; }
-
导出具体实现
package com.haierp.easyexcel.write; import com.alibaba.excel.EasyExcel; import com.haierp.api.ErpConst; import com.haierp.easyexcel.write.head.SdReceiptExcelWriteHead; import com.haierp.easyexcel.write.head.SdTaskDetailExcelWriteHead; import com.haierp.mapper.FileExportMapper; import com.haierp.model.file.FileExport; import com.haierp.util.DateUtil; import com.haierp.util.JsonResult; import org.apache.commons.io.IOUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Component; import javax.annotation.PostConstruct; import java.io.*; import java.util.Calendar; import java.util.Date; import java.util.List; /** * @ClassName ExcelWriteFactory * @Description TODO * @Author hzx * @Date 2021-07-12 13:49 **/ @Component public class ExcelWriteFactory implements ErpConst{ @Value("${erp.host}") public String host; @Value("${erp.resource}") public String resource; /** * 导出文件并更新文件表 * @param fileName 文件名称 * @param outputStream 输出流 * @return * @throws IOException */ private static JsonResult<Object> exportFileAndUpdateFile(String fileName, ByteArrayOutputStream outputStream) throws IOException { if(outputStream.size() == 0) { return JsonResult.buildFailed("数据转化失败"); } fileName += "(" + DateUtil.formatDate(new Date(), "yyyyMMdd") + ")_" + fileExport.getId() +".xlsx"; Calendar cal = Calendar.getInstance(); int month = cal.get(Calendar.MONTH) + 1; int year = cal.get(Calendar.YEAR); String filePath = excelWriteFactory.resource+"/" + year + "/" + month; File file = new File(filePath); if (!file.isDirectory()){ boolean mkdirs = file.mkdirs(); if (!mkdirs){ return JsonResult.buildFailed("目录创建失败"); } } else { FileOutputStream fileOutputStream = null; try { file = new File(filePath, fileName); fileOutputStream = new FileOutputStream(file); IOUtils.write(outputStream.toByteArray(), fileOutputStream); fileOutputStream.flush(); }catch (Exception e) { e.printStackTrace(); }finally { outputStream.close(); if (fileOutputStream != null) { try { fileOutputStream.close(); }catch (Exception e){ e.printStackTrace(); } } } } return JsonResult.buildSuccess("文件导出成功"); } /** * 回执单导出 - 导出 * @param sdReceiptExcelWriteHeadList 入参 * @param fileExport 文件表 * @return * @throws Exception */ public static JsonResult<Object> receiptExcel(List<SdReceiptExcelWriteHead> sdReceiptExcelWriteHeadList) throws Exception{ String fileName = "刷单回执情况"; ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); EasyExcel.write(outputStream, SdReceiptExcelWriteHead.class).sheet(fileName).doWrite(sdReceiptExcelWriteHeadList); return exportFileAndUpdateFile(fileName, outputStream); } }
-
调用方法
public JsonResult<Object> exportReceipt(Long taskId) throws Exception { List<SdReceiptExcelWriteHead> sdReceiptExcelWriteHeadList = this.baseMapper.exportReceipt(taskId); Assert.notEmpty(sdReceiptExcelWriteHeadList, "无导出数据"); return ExcelWriteFactory.receiptExcel(sdReceiptExcelWriteHeadList, fileExport); }
扫一扫
2637
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
