EasyExcel导入导出
https://easyexcel.opensource.alibaba.com/docs/current/
https://github.com/alibaba/easyexcel
源码可以从github下载调试，相对于poi确实好用很多，但是本质上还是使用的poi做的改造，所以3X之后被动依赖还是poi
1、EasyExcel导出数据
1.1、核心元素
- excel模板
- vo
- ExcelWriter
1.2、导入pom
build 是为了 模板能够编译到target下
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
</dependency>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.*</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.*</include>
</includes>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
1.3、模板
src\main\resources\excel\主机记录模板.xlsx
execl内容比较简单不再展示
1.4、两个vo
一个用来接收请求参数，一个用来做excel导出用
import lombok.Data;
import java.util.Date;
@Data
public class NodeVO {
private String hostName;
private String hostIp;
private int status; // 主机状态 0离线 1在线
private Date lastUpdateTime;
}
导出的数据模型，注意相关excle注解，详细可参考官网说明，或者github中源码的ReadMe。
这里主要完成字段和excel的对应关系，以及两个核心的format注解。
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;
import java.util.Date;
@Data
public class NodeExportVO {
@ExcelProperty(value = "主机名", index = 0)
private String hostName;
@ExcelProperty(value = "主机IP", index = 1)
private String hostIp;
@ExcelIgnore
private int status; // 主机状态 0离线 1在线
@ExcelProperty(value = "主机状态", index = 2)
private String statusName;
@ExcelProperty(value = "上报时间", index = 3)
@DateTimeFormat("YYYY-MM-dd")
private Date lastUpdateTime;
}
1.5、控制层
/**
* EasyExcel测试
*/
@RestController
public class EasyExcelController {
@Autowired
private IEasyExcelService easyExcelService;
/**
* 导出
*/
@RequestMapping(value = "/export", method = RequestMethod.POST)
public void export(@RequestBody List<NodeVO> nodeVOList) {
easyExcelService.export(nodeVOList);
}
}
1.6、服务层实现类
@Service
public class EasyExcelService implements IEasyExcelService {
@Override
public void export(List<NodeVO> nodeVOList) {
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
String fileName = null;
try {
fileName = URLEncoder.encode("主机记录", "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
List<NodeExportVO> nodeExportVOList = nodeVOList.stream().map(nodeVO -> {
NodeExportVO nodeExportVO = new NodeExportVO();
BeanUtils.copyProperties(nodeVO, nodeExportVO);
return nodeExportVO;
}).collect(Collectors.toList());
if (!CollectionUtils.isEmpty(nodeExportVOList)) {
for (NodeExportVO nodeExportVO : nodeExportVOList) {
int status = nodeExportVO.getStatus();
String statusName = status == 0 ? "离线" : "在线";
nodeExportVO.setStatusName(statusName);
}
}
ClassPathResource resource = new ClassPathResource("excel/主机记录模板.xlsx");
System.out.println(resource.exists());
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(resource.getInputStream()).build();
} catch (IOException e) {
e.printStackTrace();
}
WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
excelWriter.write(nodeExportVOList, writeSheet);
excelWriter.finish();
}
}
谷歌插件进行接口调试
post
body
[
{
"hostName": "jdit-aliyun",
"hostIp": "3.182.55.7",
"status": 1,
"lastUpdateTime": "2022-11-22T15:25:30.334Z"
}
]
就可以完成下载了。
2、导入数据
导入数据首先需要下载模板
然后进行导入
2.1、下载Excel模板
2.1.1、准备模板
src\main\resources\excel
linux主机记录模板.xlsx
windows主机记录模板.xlsx
2.1.1、提供下载接口（Get请求即可）
2.1.1.1 控制层
/**
* 下载模板
*/
@RequestMapping(value = "/downloadTemplate", method = RequestMethod.GET)
public void downloadTemplate() {
easyExcelService.downloadTemplate();
}
2.1.1.2、业务层
@Override
public void downloadTemplate() {
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
String systemType = request.getParameter("systemType");
String fileName = "linux主机记录模板";
if ("windows".equalsIgnoreCase(systemType)) {
fileName = "windows主机记录模板";
}
ClassPathResource resource = new ClassPathResource("excel/" + fileName+ ".xlsx");
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
try (BufferedInputStream fis = new BufferedInputStream(resource.getInputStream());
OutputStream out = response.getOutputStream();
) {
byte[] bytes = new byte[1024 * 1024 * 10];
int length = 0;
while ((length = fis.read(bytes)) != -1) {
out.write(bytes, 0, length);
}
} catch (IOException e) {
e.printStackTrace();
}
}
2.1.1.3、测试下载
浏览器地址栏输入url地址请求即可
http://localhost:8080/downloadTemplate
2.2、EasyExcel导入数据
遗忘的笔记，突然团队20+人员需要裁员到只保留一个开发，突然感觉又有时间补充下笔记了。
2.2.1、导入入口
-
这里最核心的设计一个技术，IO流，IT系统如何处理文件。
从用户选择文件，要知道浏览器做了什么，网络做了什么，springboot容器又是做了如何转换，才能识别文件，接受解析整个文件的。
/**
* 导入
*/
@RequestMapping(value = "/import", method = RequestMethod.POST)
public ResultDataVO importNode(@RequestParam("file") MultipartFile file,
HttpServletRequest request,
HttpServletResponse response) {
return easyExcelService.importNode(file, request, response);
}
2.2.2、具体实现逻辑
@Override
public ResultDataVO importNode(MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
try (InputStream inputStream = file.getInputStream()) {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html");
List<NodeExportVO> importList = excelToList(inputStream);
return CommonUtil.result(ResultDataVO.SUCCESS_CODE, "导入成功", importList);
} catch (IOException ioException) {
log.error("导入失败", ioException);
return CommonUtil.result(ResultDataVO.FAILED_CODE, "导入失败", "");
}
}
private List<NodeExportVO> excelToList(InputStream inputStream) {
ExcelListener<NodeExportVO> listener = new ExcelListener<>();
ExcelReader reader = EasyExcelFactory.read(inputStream, NodeExportVO.class, listener)
.headRowNumber(2).build();
reader.readAll();
List<NodeExportVO> importList = listener.getList();
return importList;
}
2.2.3、核心组件ExcelListener
/**
* desc
*
* @Author 红尘过客
* @DateTime 2023-08-02 17:08:51
*/
@Slf4j
public class ExcelListener<T> extends AnalysisEventListener<T> {
private List<T> list = new ArrayList<>();
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
/**
* 一行一行读取文件内容
*
* @param t
* @param analysisContext
*/
@Override
public void invoke(T t, AnalysisContext analysisContext) {
list.add(t);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
super.onException(exception, context);
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
super.extra(extra, context);
}
@Override
public boolean hasNext(AnalysisContext context) {
return super.hasNext(context);
}
}
2.2.4、验证上传
http://localhost:10005/test/import
