EasyExcel介绍
官方网站:https://easyexcel.opensource.alibaba.com/
API:https://easyexcel.opensource.alibaba.com/docs/current/api/
EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。
他能让你在不用考虑性能、内存的等因素的情况下,快速完成Excel的读、写等功能。
实例
读Excel内容
controller
/**
* 导入资产解析Excel表格内容
* @param file
* @return
* @throws IOException
*/
@PostMapping("/importAssets")
public ApiResponseBody importAssets(@RequestParam MultipartFile file) throws IOException {
// doRead()和doReadSync()区别在于doReadSync()里面配了一个自定义的监听,并且返回读取到excel数据的List集合
List<ImportInventoryAssetsParam> list =
Arrays.asList(
EasyExcel.read(file.getInputStream())
.sheet(0).head(ImportInventoryAssetsParam.class)
.registerReadListener(new ImportAssetsListener())
.doReadSync().toArray(new ImportInventoryAssetsParam[0])
);
return ApiResponseBody.defaultSuccess(list);
}
ImportInventoryAssetsParam
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.math.BigDecimal;
@Data
@ExcelIgnoreUnannotated
public class ImportInventoryAssetsParam {
/**
* 资产id
*/
@ApiModelProperty("资产id")
@ExcelProperty(value = "资产id")
private Long assetId;
/**
* 资产名称
*/
@ApiModelProperty("资产名称")
@ExcelProperty(value = "资产名称")
private String assetName;
/**
* 资产类别名称
*/
@ApiModelProperty("资产类别名称")
@ExcelProperty(value = "资产类别名称")
private String classificationName;
/**
* 资产类别id
*/
@ApiModelProperty("资产类别id")
@ExcelProperty(value = "资产类别id")
private Long classificationId;
/**
* 入库金额
*/
@ApiModelProperty("入库金额")
@ExcelProperty(value = "入库金额")
private BigDecimal receiptAmount;
/**
* 入库数量
*/
@ApiModelProperty("入库数量")
@ExcelProperty(value = "入库数量")
private Integer receiptQuantity;
/**
* 规格型号
*/
@ApiModelProperty("规格型号")
@ExcelProperty(value = "规格型号")
private String specification;
/**
* 存放仓库
*/
@ApiModelProperty("存放仓库")
@ExcelProperty(value = "存放仓库")
private String depositWarehouse;
/**
* 计量单位
*/
@ApiModelProperty("计量单位")
@ExcelProperty(value = "计量单位")
private String measurementName;
}
ImportAssetsListener
监听器
监听器,里面声明了读取Excel每一行之后执行的方法(invoke)、读取单个Excel文件结束之后执行的方法(doAfterAllAnalysed)、单行数据读取异常处理方法(onException)、一行一行读取Excel表头方法(invokeHeadMap)、读取Excel单元格超链接批注等额外信息的方法(extra)等
import java.util.ArrayList;
import java.util.List;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.fawkes.common.inventory.inventoryassets.common.model.InventoryAssets;
import com.fawkes.common.inventory.inventoryassets.common.param.ImportInventoryAssetsParam;
import lombok.extern.slf4j.Slf4j;
/**
* 模板的读取类
*
* @author Jiaju Zhuang
*/
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class ImportAssetsListener extends AnalysisEventListener<ImportInventoryAssetsParam> {
List<ImportInventoryAssetsParam> list = new ArrayList<ImportInventoryAssetsParam>();
@Override
public void invoke(ImportInventoryAssetsParam importInventoryAssetsParam, AnalysisContext analysisContext) {
list.add(importInventoryAssetsParam);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("表数据解析完成");
}
@Override
public void onException(Exception exception, AnalysisContext context) {
// 如果是某一个单元格的转换异常 能获取到具体行号
// 如果要获取头的信息 配合invokeHeadMap使用
/* if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
log.error("第{}行,第{}列解析异常,数据为:{}", excelDataConvertException.getRowIndex()+1,
excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
}*/
}
}
写Excel
controller
/**
* 导出资产
* @param
* @return
* @throws IOException
*/
@PostMapping("/exportAssets")
public void exportAssets(@RequestBody String [] ids, HttpServletResponse response) throws IOException {
List<InventoryAssets> list = (List<InventoryAssets>) inventoryAssetsService.importAssets(Arrays.asList(ids)).getData();
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("库存", "UTF-8") + System.currentTimeMillis() + ".xlsx";
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
response.setContentType("application/octet-stream;charset=utf-8");
// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
// finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
EasyExcel.write(outputStream, InventoryAssets.class).sheet("库存列表").doWrite(list);
} catch (IOException e) {
e.printStackTrace();
} finally {
if(outputStream!=null){
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
InventoryAssets
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
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.fawkes.core.base.model.BaseEntity;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
/**
* 库存资产 实体类
*
* @author root
* @createDate 2023/06/09 15:47
*/
@Data
@EqualsAndHashCode(callSuper = true)
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
@ExcelIgnoreUnannotated
public class InventoryAssets extends BaseEntity implements Serializable {
/**
* 资产id
*/
@ApiModelProperty("资产id")
@ExcelProperty(value = "资产id", index = 1)
private Long assetId;
/**
* 资产名称
*/
@ApiModelProperty("资产名称")
@ExcelProperty(value = "资产名称", index = 2 )
private String assetName;
/**
* 资产来源
*/
@ApiModelProperty("资产来源")
@ExcelProperty(value = "资产来源", index = 3 )
private String assetSource;
/**
* 资产状态 0 可用 1 在用 2 借用
*/
@ApiModelProperty("资产状态 0 可用 1 在用 2 借用")
@ExcelProperty(value = "资产状态", index = 4 )
private Integer assetStatus;
/**
* 所属部门
*/
@ApiModelProperty("所属部门")
@ExcelProperty(value = "所属部门", index = 5 )
private String belongDepartment;
/**
* 资产类别id
*/
@ApiModelProperty("资产类别id")
@ExcelProperty(value = "资产类别id", index = 6 )
private Long classificationId;
/**
* 资产类别名称
*/
@ApiModelProperty("资产类别名称")
@ExcelProperty(value = "资产类别名称", index = 7 )
private String classificationName;
/**
* 存放仓库
*/
@ApiModelProperty("存放仓库")
@ExcelProperty(value = "存放仓库", index = 8 )
private String depositWarehouse;
/**
* 计量单位
*/
@ApiModelProperty("计量单位")
@ExcelProperty(value = "计量单位", index = 9 )
private String measurementName;
/**
* 备注
*/
@ApiModelProperty("备注")
@ExcelProperty(value = "备注", index = 10 )
private String notes;
/**
* 入库金额
*/
@ApiModelProperty("入库金额")
@ExcelProperty(value = "入库金额", index = 11 )
private BigDecimal receiptAmount;
/**
* 入库数量
*/
@ApiModelProperty("入库数量")
@ExcelProperty(value = "入库数量", index = 12 )
private Integer receiptQuantity;
/**
* 规格型号
*/
@ApiModelProperty("规格型号")
@ExcelProperty(value = "规格型号", index = 13 )
private String specification;
/**
* 入库编码
*/
@ApiModelProperty("入库编码")
@ExcelProperty(value = "入库编码", index = 14 )
private String stockCode;
/**
* 修改人
*/
/* @ApiModelProperty("修改人")
// 忽略这个字段,不写入Excel当中
@ExcelIgnore
private String updateBy;*/
/**
* 可用数量
*/
@ApiModelProperty("可用数量")
@ExcelProperty(value = "可用数量", index = 15 )
private Integer availableQuantity;
/**
* 创建人id
*/
@ApiModelProperty("创建人id")
@ExcelProperty(value = "创建人id", index = 16 )
private String createId;
/**
* 修改人id
*/
@ApiModelProperty("修改人id")
// 忽略这个字段,不写入Excel当中
@ExcelIgnore
private String updateId;
}