导入依赖
在导入依赖钱看是否项目中存在poi相关的版本。直接从pom文件中去除exclusion。
本次使用的是4.2.0 。maven依赖如下:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.2.0</version>
</dependency>
<dependency>
<groupId>org.hibernate.validator</groupId>
<artifactId>hibernate-validator</artifactId>
<version>6.1.6.Final</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
<exclusions>
<exclusion>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
貌似没用到easypoi-base 。先不管了
按照官方说明。创建实体
官方文档;
public class MaterialExport implements Serializable {
// 物资名称
@Excel(name = "物资名称", width = 15)
private String name;
// 物资规格
@Excel(name = "物资规格")
private String specs;
// 物资类型
@Excel(name = "物资类型")
private String type;
// 单位
@Excel(name = "单位")
private String unit;
// 库存
@Excel(name = "库存")
private long num;
//单价
@Excel(name = "单价")
private BigDecimal unitprice;
// 厂家
@Excel(name = "厂家", width = 20)
private String manufactor;
// 库存位置
@Excel(name = "库存位置", width = 20)
private String address;
// 用途
@Excel(name = "用途", width = 20)
private String purpose;
@Excel(name = "入库时间",format = "yyyy年MM月dd日", width = 25)
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
// 备注
@Excel(name = "备注", width = 40)
private String remarks;
//getter setter方法省略。
}
在导入时需要注意:实体中用到的属性为java基本属性或者是在“java.lang”、“java.math”、“java.sql”、“java.util”包中具体easypoi中逻辑代码为:
/**
* 是不是java基础类
*
* @param field
* @return
*/
public static boolean isJavaClass(Field field) {
Class<?> fieldType = field.getType();
boolean isBaseClass = false;
if (fieldType.isArray()) {
isBaseClass = false;
} else if (fieldType.isPrimitive() || fieldType.getPackage() == null
|| "java.lang".equals(fieldType.getPackage().getName())
|| "java.math".equals(fieldType.getPackage().getName())
|| "java.sql".equals(fieldType.getPackage().getName())
|| "java.util".equals(fieldType.getPackage().getName())) {
isBaseClass = true;
}
return isBaseClass;
}
导出功能
获取数据后直接调用Util方法
controller:
public Result export(HttpServletRequest request, HttpServletResponse response) {
System.out.println(sdf.format(new Date()));
//此处为从数据库获取list
List<MaterialExport> materialExports = materialService.export();
response.setContentType("application/vnd.ms-excel");
OutputStream out = null;
try {
response.setHeader("Content-disposition",
"attachment;filename=" + URLEncoder.encode("物资导出" + sdf.format(new Date()) + ".xls","ISO-8859-1" ));
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("物资库存信息", "物资"),
MaterialExport.class, materialExports);
out = response.getOutputStream();
workbook.write(out);
} catch (Exception e) {
System.out.println(e.getMessage());
System.out.println(e);
} finally {
try {
if (out != null)
out.close();
} catch (IOException e) {
}
}
return Response.success();
}
导入
从文件流中获取数据用easypoi解析数据:
public Result inport(HttpServletRequest request,@RequestParam("file") MultipartFile fileItem) {
ImportParams importParams = new ImportParams();
importParams.setHeadRows(2);
// 获取模板
ExcelImportResult<MaterialExport> data = null;
try {
data = ExcelImportUtil.importExcelMore(fileItem.getInputStream(), MaterialExport.class, importParams);
} catch (Exception e) {
e.printStackTrace();
}
// 获取模板数据
List<MaterialExport> successList = data.getList();
int total = successList.size();
// 循环数据
System.out.println(total);
for (MaterialExport excelDTO : successList) {
System.out.println(excelDTO.toString());
}
return Response.success();
}
完毕。