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>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.4</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/22 23:22:00"
	}
]

就可以完成下载了。