开源操作操作Exceleasy-excel使用学习教程下载Excel生产下载Java代码

以你之姓@ 2023-07-24 02:04 58阅读 0赞

后台Service层实现

public Workbook downloadExcel(List<EventTagProcessVo>  eventTagProcessVos) throws CorpusException {
            File resultFile = null;
            //1、查询返回List限制200条数据
            File tempPathDir = null;
            XSSFWorkbook resultXSSFWorkbook = null;
            try {
                List<EventTagProcess> eventTagProcesses= dozerGenerator.convert(eventTagProcessVos, EventTagProcess.class);
    
                String specialId = DateUtils.getSysDateId();
                //创建临时文件夹
                String tempPath = path + specialId;
                tempPathDir = new File(tempPath);
                if (!tempPathDir.isDirectory()) {
                    tempPathDir.mkdir();
                }
                //2、生成映射成excel
                String templatePath = tempPath + File.separator + specialId + ".xlsx";
                OutputStream ops = new FileOutputStream(templatePath);
                ExcelExportResult exportResult = context.createExcelForPart(excelId, eventTagProcesses);
                //假设这是第二次从数据库或其他平台查询到到数据
                Workbook workbook = exportResult.build();
                workbook.write(ops);
                ops.close();
                workbook.close();
                //3、保存之后返回
                File result[] = tempPathDir.listFiles();
                for (int i = 0; i < result.length; i++) {
                    if (result[i].getName().equals(specialId + ".xlsx")) {
                        resultFile = result[i];
                        break;
                    }
                }
                FileInputStream fis = new FileInputStream(resultFile);
                resultXSSFWorkbook = new XSSFWorkbook(fis);
            } catch (Exception ex) {
                if (null != tempPathDir) {
                    tempPathDir.delete();
                }
                throw new CorpusException("文件下载出现错误：" + ex.getMessage());
            }
            return resultXSSFWorkbook;
        }
    
    
        @Override
        public Workbook downloadExcelByIds(String[] ids) throws CorpusException {
            File resultFile = null;
            //1、查询返回List限制200条数据
            File tempPathDir = null;
            XSSFWorkbook resultXSSFWorkbook = null;
            try {
                List<EventTagProcess> eventTagProcesses = eventTagProcessDao.queryEventTagProcessList(ids);
                String specialId = DateUtils.getSysDateId();
                //创建临时文件夹
                String tempPath = path + specialId;
                tempPathDir = new File(tempPath);
                if (!tempPathDir.isDirectory()) {
                    tempPathDir.mkdir();
                }
                //2、生成映射成excel
                String templatePath = tempPath + File.separator + specialId + ".xlsx";
                //需求概述.数据量较大,可能大批量数据导出,会对DB造成压力,这里分批次检索数据,一部分一部分向Excel中写
                OutputStream ops = new FileOutputStream(templatePath);
                ExcelExportResult exportResult = context.createExcelForPart(excelId, eventTagProcesses);
                //假设这是第二次从数据库或其他平台查询到到数据
                Workbook workbook = exportResult.build();
                workbook.write(ops);
                ops.close();
                workbook.close();
                //3、保存之后返回
                File result[] = tempPathDir.listFiles();
                for (int i = 0; i < result.length; i++) {
                    if (result[i].getName().equals(specialId + ".xlsx")) {
                        resultFile = result[i];
                        break;
                    }
                }
                FileInputStream fis = new FileInputStream(resultFile);
                resultXSSFWorkbook = new XSSFWorkbook(fis);
            } catch (Exception ex) {
                if (null != tempPathDir) {
                    tempPathDir.delete();
                }
                throw new CorpusException("文件下载出现错误：" + ex.getMessage());
            }
            return resultXSSFWorkbook;
        }

后台查询之后数据传入：

public File downloadInfoTemplate(EventTagProcessVo eventTagProcessVo) throws CorpusException {
            File resultFile = null;
            //1、查询返回List限制200条数据
            File tempPathDir = null;
            EventTagProcess eventTagProcess = dozerGenerator.convert(eventTagProcessVo, EventTagProcess.class);
            XSSFWorkbook resultXSSFWorkbook = null;
            try {
                List<EventTagProcess> eventTagProcesses = eventTagProcessDao.queryEventTagProcessList(eventTagProcess);
                String specialId = DateUtils.getSysDateId();
                //创建临时文件夹
                String tempPath = path + specialId;
                tempPathDir = new File(tempPath);
                if (!tempPathDir.isDirectory()) {
                    tempPathDir.mkdir();
                }
                //2、生成映射成excel
                String templatePath = tempPath + File.separator + specialId + ".xlsx";
                //需求概述.数据量较大,可能大批量数据导出,会对DB造成压力,这里分批次检索数据,一部分一部分向Excel中写
                OutputStream ops = new FileOutputStream(templatePath);
                ExcelExportResult exportResult = context.createExcelForPart(excelId, eventTagProcesses);
                Workbook workbook = exportResult.build();
                workbook.write(ops);
                ops.close();
                workbook.close();
                //3、保存之后返回
                resultFile = new File(templatePath);
            } catch (Exception ex) {
                if (null != tempPathDir) {
                    tempPathDir.delete();
                }
                throw new CorpusException("文件下载出现错误：" + ex.getMessage());
            }
            return resultFile;
        }

excelTemplate.xml映射配置：

<?xml version="1.0" encoding="UTF-8"?>
    <excels>
        
        <excel id="information" class="com.gildata.threebody.corpus.api.vo.InformationVo"
               sheetname="资讯信息" enableStyle="true"  sheetIndex="0">
    
            <field name="newsTitle" title="标题" columnWidth="15000"  uniformStyle="true"/>
            <field name="content" title="内容"/>
        </excel>
    
        
        <excel id="eventtagprocess" class="com.gildata.threebody.corpus.dao.model.EventTagProcess"
               sheetname="标注列表信息" enableStyle="true"   sheetIndex="0">
    
            <field name="id" title="主键"  uniformStyle="true" columnWidth="10000"   regex=""  decimalFormatPattern="#.####"   />
            <field name="status" title="状态"     format="0:待处理,1:待审核,2:已审核,3已退回" />
            <field name="tagFlag" title="事件标注"    format="0:否,1:是"/>
            <field name="newsTitle" title="资讯标题"   columnWidth="15000" />
            <field name="source" title="来源"      format="1:本地导入,0:JYP,null:本地导入"  />
            <field name="subjects" title="主体"  columnWidth="15000" />
    
        </excel>
    
    
    
    
    
    </excels>

Controller层实现下载代码：

/*****
         * 条件查询之后进行导出Excel
         * @param httpServletResponse
         * @return
         * @throws CorpusException
         */
        @ApiOperation(value = "批量下载标注列表根据对象查询最多导出200条数据")
        @RequestMapping(value = "/downloadEventTagProcessExcel", method = RequestMethod.GET)
        public CorpusResponse downloadEventTagProcessExcel(@RequestBody @ApiParam(value = "条件查询进行下载") EventTagProcessQueryParam param, HttpServletResponse httpServletResponse) throws CorpusException {
            CorpusResponse response = CorpusResponseBuilder.newSuccessResponse();
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhMMss");
                String date = sdf.format(new Date());
                String fileName = "标注列表" + date + ".xlsx";
                fileName = URLEncoder.encode(fileName, "utf-8");
                httpServletResponse.setHeader("Content-disposition", "attachment;filename=" + fileName);
                httpServletResponse.setCharacterEncoding("utf-8");
                httpServletResponse.setContentType("application/octet-stream;charset=utf-8");
                String beginDate = param.getBeginDate();
                String endDate = param.getEndDate();
                Integer status = param.getStatus();
                PageVo<EventTagProcessVo> pageVo = eventTagProcessFacade.conditionalQuery(beginDate, endDate, status, param.getCurrent(), param.getSize());
                Workbook workbook = eventTagProcessFacade.downloadExcel(pageVo.getRecords());
                OutputStream stream = httpServletResponse.getOutputStream();
                workbook.write(stream);
                stream.flush();
                stream.close();
            } catch (IOException e) {
                response = CorpusResponseBuilder.newErrorResponse(e);
            }
            return response;
        }
    
        /*******
         * 选择ID进行多项选择ID进行后台下载操作
         * @param ids
         * @param httpServletResponse
         * @return
         * @throws CorpusException
         */
        @RequestMapping(value = "/downloadMultiEventTagProcessVo", method = RequestMethod.GET)
        @ApiOperation(value = "选择ID进行多项选择ID进行后台下载操作")
        public CorpusResponse downloadMultiEventTagProcessVo(@RequestParam @ApiParam(value = "id主键数组") String[] ids, HttpServletResponse httpServletResponse) throws CorpusException {
            CorpusResponse response = CorpusResponseBuilder.newSuccessResponse();
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhMMss");
                String date = sdf.format(new Date());
                String fileName = "标注列表" + date + ".xlsx";
                fileName = URLEncoder.encode(fileName, "utf-8");
                httpServletResponse.setHeader("Content-disposition", "attachment;filename=" + fileName);
                httpServletResponse.setCharacterEncoding("utf-8");
                httpServletResponse.setContentType("application/octet-stream;charset=utf-8");
                Workbook workbook = eventTagProcessFacade.downloadExcelByIds(ids);
                OutputStream stream = httpServletResponse.getOutputStream();
                workbook.write(stream);
                stream.flush();
                stream.close();
            } catch (IOException e) {
                response = CorpusResponseBuilder.newErrorResponse(e);
            }
            return response;
        }