easyexcel-获取文件资源和导入导出excel

news/2024/4/17 7:48:33

1、获取本地资源文件,根据模板填充数据导出

public void exportExcel(HttpServletResponse httpResponse, @RequestBody AssayReportDayRecordQuery query) {AssayReportDayRecordDTO dto = this.queryByDate(query);ExcelWriter excelWriter = null;ExcelUtil.configResponse(httpResponse);// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替// {} 代表普通变量 {.} 代表是list的变量 {前缀.} 前缀可以区分不同的listInputStream inputStream = null;File reportFile = null;try {inputStream = new ClassPathResource("classpath:template/" + query.getTemplateCode() + ".xls").getInputStream();reportFile = new File(query.getTemplateCode() + ".xls");FileUtils.copyInputStreamToFile(inputStream, reportFile);excelWriter = EasyExcel.write(httpResponse.getOutputStream()).withTemplate(reportFile.getPath()).build();WriteSheet writeSheet = EasyExcel.writerSheet().build();// 简单的说 如果你的模板有list,且list不是最后一行,下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).direction(WriteDirectionEnum.VERTICAL).build();//填充列表数据if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(dto.getDataList())) {excelWriter.fill(dto.getDataList(), fillConfig, writeSheet);}} catch (Exception e) {log.error("导出异常:{}", e.getMessage());} finally {if (Objects.nonNull(reportFile) && reportFile.exists()) {boolean delete = reportFile.delete();log.info("文件删除" + (delete ? "成功" : "失败"));}// 千万别忘记close 会帮忙关闭流if (excelWriter != null) {excelWriter.finish();try {inputStream.close();} catch (IOException e) {log.error("文件流异常", e);}}}}

2、根据实体类导出,实体类做表头

public void generateDeviceExcel(Page<DeviceEntityVO> page, HttpServletResponse response) {List<DeviceEntityExcel> excels = new ArrayList<>();for (DeviceEntityVO vo : page) {DeviceEntityExcel excel = new DeviceEntityExcel();excel.setDeviceName(vo.getDeviceName());excel.setDeviceId(vo.getAssetsCode());excel.setCatalogue(vo.getCatalogue());excel.setCatalogueLeaf(vo.getCatalogueLeaf());excel.setModel(vo.getModel());excel.setManufactorName(vo.getManufactorName());excel.setUnit(vo.getUnit());excel.setDeviceGrade(vo.getDeviceGrade());excel.setDeviceStatusName(DeviceEntityStatus.getMessage(vo.getDeviceStatus()));excel.setCreateTime(vo.getCreateTime());excels.add(excel);}ExcelUtil.generateExcel(response, DeviceEntityExcel.class, excels);}
@Data
public class DeviceEntityExcel {@ApiModelProperty(value="设备资产名称", position=2)@ExcelProperty("设备资产名称")private String deviceName;/*** 设备资产编号*/@ApiModelProperty(value="设备资产编号", position=4)@ExcelProperty("设备资产编号")private String deviceId;@ApiModelProperty("设备分类")@ExcelProperty("设备分类")private String catalogue;@ApiModelProperty("细分种类")@ExcelProperty("细分种类")private String catalogueLeaf;/*** 设备型号*/@ApiModelProperty(value="设备型号", position=2)@ExcelProperty("设备型号")private String model;@ApiModelProperty("生产厂商")@ExcelProperty("生产厂商")private String manufactorName;@ApiModelProperty("单位")@ExcelProperty("单位")private String unit;/*** 等级*/@ApiModelProperty(value="等级", position=4)@ExcelProperty("等级")private String deviceGrade;@ApiModelProperty("状态")@ExcelProperty("状态")private String deviceStatusName;@ApiModelProperty("创建时间")@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")@JSONField(format = "yyyy-MM-dd HH:mm:ss")@ExcelProperty("创建时间")private Date createTime;
}
@Slf4j
public class ExcelUtil {public static void configResponse(HttpServletResponse response) {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf8");response.setHeader("Content-Disposition", "attachment; filename=" + System.currentTimeMillis() + ".xlsx");response.setHeader("Pragma", "public");response.setHeader("Cache-Control", "no-store");response.addHeader("Cache-Control", "max-age=0");}/*** 生成excel** @param response* @param head* @param data*/public static void generateExcel(HttpServletResponse response, Class head, List data) {ExcelUtil.generateExcel(response, head, data, "报表");}public static void generateExcel(HttpServletResponse response, Class head, List data, String sheetName) {ExcelUtil.configResponse(response);try {Map<Integer, String[]> map = DownloadTemplateUtil.downloadTemplate(head);EasyExcelFactory.write(response.getOutputStream(), head).autoCloseStream(Boolean.TRUE).registerWriteHandler(new ExportCellWriteHandler(map)).sheet(sheetName).doWrite(data);} catch (IOException e) {
//            e.printStackTrace();log.error("generateExcel{}",e.getMessage());}}/*** 生成excel(公共简易excel导出)** @param response* @param head* @param data*/public static void generateCommonExcel(HttpServletResponse response, Class head, List data, String sheetName) {ExcelUtil.configResponse(response);try {EasyExcelFactory.write(response.getOutputStream()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).head(head).autoCloseStream(Boolean.TRUE).sheet(sheetName).doWrite(data);} catch (IOException e) {log.error("ExcelUtil->generateCommonExcel", e);}}/*** 生成excel带下拉框** @param response* @param head* @param data*/public static void generateExcelWithSelect(HttpServletResponse response, Class head, List data, String[] select) {ExcelUtil.configResponse(response);try {EasyExcelFactory.write(response.getOutputStream(), head).registerWriteHandler(new SheetWriteHandler() {@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {//不需要处理}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {if (select.length > 0) {//通过sheet处理下拉信息Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();CellRangeAddressList rangeList = new CellRangeAddressList();CellRangeAddress addr = new CellRangeAddress(1, data.size(), 2, 2);rangeList.addCellRangeAddress(addr);DataValidationConstraint constraint = helper.createExplicitListConstraint(select);DataValidation validation = helper.createValidation(constraint, rangeList);sheet.addValidationData(validation);}}}).autoCloseStream(Boolean.TRUE).sheet("报表").doWrite(data);} catch (IOException e) {log.error("ExcelUtil -> generateExcelWithSelect(),异常:{}", e.getMessage());}}}

3、导入excel
监听器

public class DataListener extends AnalysisEventListener {public boolean really;public boolean repeatFlag;protected ExcelService service;List<Object> list = new ArrayList();@Overridepublic void invoke(Object o, AnalysisContext analysisContext) {list.add(o);}private void saveData() {try {this.repeatFlag = service.saveData(list,really);}catch (Exception e){throw new ValidateException("导入数据失败,请检查数据");}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {saveData();}public DataListener(ExcelService excelService,boolean really){this.service =  excelService;this.really = really;}
}
public interface ExcelService {boolean saveData(List list,boolean really);
}
DataListener listener = new DataListener(checkPointService,excelInfo.getReally());EasyExcelFactory.read(excelInfo.getFile().getInputStream(), CheckPointExcelVO.class,listener).sheet().doRead();

saveData自定义业务代码,将excel数据保存到数据库或做其他处理


https://www.xjx100.cn/news/3366008.html

相关文章

游戏引擎架构01__引擎架构图

根据游戏引擎架构预设的引擎架构来构建运行时引擎架构 ​

Tuxera NTFS for Mac2023绿色免费版 免费的ntfs for mac 免费读写硬盘U盘工具

Tuxera NTFS 2023 Mac免费版是款适合Mac用户使用的磁盘读写工具。Tuxera NTFS 2023 Mac可以很好的帮助用户在Mac上打开、编辑、复制、移动或删除存储在Windows NTFS格式的USB驱动器上的文件。并且Tuxera NTFS 2023 Mac还可以无阻碍地使用各种文件系统磁盘&#xff0c;还能解决磁…

基于深度学习的商品标签识别系统(网页版+YOLOv8/v7/v6/v5代码+训练数据集)

摘要&#xff1a;本文深入研究了基于YOLOv8/v7/v6/v5的商品标签识别&#xff0c;核心采用YOLOv8并整合了YOLOv7、YOLOv6、YOLOv5算法&#xff0c;进行性能指标对比&#xff1b;详述了国内外研究现状、数据集处理、算法原理、模型构建与训练代码&#xff0c;及基于Streamlit的交…

如何通過代理IP使用cURL?

cURL是數據傳輸領域一款極具影響力的工具&#xff0c;可以說是每位軟體工程師的必備工具。它支持包括 HTTP、HTTPS、FTP 等在內的大量協議網路。但在某些情況下&#xff0c;由於網路限制或需要保持數字隱蔽性&#xff0c;無法直接訪問相關數據&#xff0c;需要用到cURL代理IP來…

篮球竞赛预约平台的设计与实现|Springboot+ Mysql+Java+ B/S结构(可运行源码+数据库+设计文档)篮球馆,篮球赛,竞赛项目,赛事预约

本项目包含可运行源码数据库LW&#xff0c;文末可获取本项目的所有资料。 推荐阅读300套最新项目持续更新中..... 最新ssmjava项目文档视频演示可运行源码分享 最新jspjava项目文档视频演示可运行源码分享 最新Spring Boot项目文档视频演示可运行源码分享 2024年56套包含ja…

1379. 找出克隆二叉树中的相同节点

说在前面 &#x1f388;不知道大家对于算法的学习是一个怎样的心态呢&#xff1f;为了面试还是因为兴趣&#xff1f;不管是出于什么原因&#xff0c;算法学习需要持续保持。 题目描述 给你两棵二叉树&#xff0c;原始树 original 和克隆树 cloned&#xff0c;以及一个位于原始…

ubuntu16.04不能在主机和虚拟机之间拷贝文本

问题 ubuntu16.04不能在主机和虚拟机之间拷贝文本。 原因 vmware tools没安装好。 解决办法 让虚拟机加载C:\Program Files (x86)\VMware\VMware Workstation\linux.iso光盘文件&#xff0c;设置如下&#xff1a; 拷贝虚拟机光盘中的VMwareTools-10.3.22-15902021.tar.gz文…

SSH中私钥和公钥的使用

在 SSH&#xff08;Secure Shell&#xff09;中&#xff0c;密钥对用于加密和身份验证&#xff0c;保证了远程会话的安全。一个密钥对包括两部分&#xff1a;公钥和私钥。它们有不同的作用和特性&#xff1a; 私钥 私钥是一个用户保密的密钥&#xff0c;它绝不能被泄露或分享…