源码可以从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();
    }
}

谷歌插件进行接口调试

http://localhost:8080/export

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

posted @ 2022-11-23 00:09 红尘过客2022 阅读(217) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

江洋大盗

在这个世界上最没用的就是道理和知识

EasyExcel导入导出