EasyExcel导出（单Sheet导出、多Sheet导出、多文件导出）

叁歲伎倆 2024-03-17 15:20 21阅读 0赞

[EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel][EasyExcel_ - _Java_Excel_ _ Easy Excel]

#### 一、单Sheet导出 ####

@ApiOperation("对话数据导出(单话题)")
        @PostMapping("downloadByChatDialogueId")
        public void downloadByChatDialogueId(@RequestParam String chatDialogueId, HttpServletResponse response) throws IOException {
            // 话题基本信息
            ChatDialogue dialogue = chatDialogueService.getById(chatDialogueId);
            // 对话数据列表
            Result<List<ChatDialogueMappingMagic>> listResult = getById(chatDialogueId);
            List<ChatDialogueMappingMagic> mappingMagicList = listResult.getData();
            List<ChatDialogueMappingExport> exportList = converter.convert(mappingMagicList, ChatDialogueMappingExport.class);
    
            // 设置响应头信息
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=chatEduExport.xlsx");
            // 使用EasyExcel进行导出
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ChatDialogueMappingExport.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(dialogue.getTitle()).build();
            excelWriter.write(exportList, writeSheet);
            excelWriter.finish();
        }

#### 二、多Sheet导出 ####

@ApiOperation("对话数据导出(单用户)")
        @PostMapping("downloadByUserId")
        public void downloadByUserId( @RequestParam Integer userId, HttpServletResponse response) throws IOException {
            
            ChatUser chatUser = chatUserService.getById(userId);
            Assert.notNull(chatUser, "用户不存在");
    
    
            // 设置响应头信息
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=chatEduExport.xlsx");
            // 使用EasyExcel进行导出
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ChatDialogueMappingExport.class).build();
    
            // 查出用户的所有话题
            List<ChatDialogue> dialogueList = chatDialogueService.lambdaQuery()
                    .eq(ChatDialogue::getUserId, userId)
                    .orderByDesc(ChatDialogue::getChatDialogueFunctionUsedId)
                    .list();
            // 循环写入多个Sheet
            for (ChatDialogue dialogue : dialogueList) {
                Integer functionUsedId = dialogue.getChatDialogueFunctionUsedId();
                if (functionUsedId == null) {
                    continue;
                }
                ChatDialogueFunctionUsed functionUsed = chatDialogueFunctionUsedService.getById(functionUsedId);
                if (functionUsed == null) {
                    continue;
                }
                // 对话数据列表
                Result<List<ChatDialogueMappingMagic>> listResult = getById(dialogue.getId());
                List<ChatDialogueMappingMagic> mappingMagicList = listResult.getData();
                List<ChatDialogueMappingExport> exportList = converter.convert(mappingMagicList, ChatDialogueMappingExport.class);
                WriteSheet writeSheet = EasyExcel.writerSheet("(" + functionUsed.getTitle() + ")" + dialogue.getTitle()).build();
                excelWriter.write(exportList, writeSheet);
            }
    
            excelWriter.finish();
        }

#### 三、多文件导出 ####

@ApiOperation("对话数据导出(所有用户)")
        @PostMapping("downloadAll")
        public void downloadAll(HttpServletResponse response) throws IOException {
            
            // 所有用户
            List<ChatUser> chatUsers = chatUserService.list();
    
            response.setContentType("application/zip");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=chatEduExports.zip");
    
            ServletOutputStream outputStream = response.getOutputStream();
            ZipOutputStream zipOut = new ZipOutputStream(outputStream);
            // 循环写入多个文件
            for (ChatUser chatUser : chatUsers) {
    
                // 查出用户的所有话题
                List<ChatDialogue> dialogueList = chatDialogueService.lambdaQuery()
                        .eq(ChatDialogue::getUserId, chatUser.getId())
                        .isNotNull(ChatDialogue::getChatDialogueFunctionUsedId)
                        .orderByDesc(ChatDialogue::getChatDialogueFunctionUsedId)
                        .list();
    
                if (dialogueList.size() == 0) {
                    continue;
                }
    
                // 创建一个临时的ByteArrayOutputStream用于存储生成的Excel文件数据
                ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
    
                // 使用EasyExcel进行导出
                ExcelWriter excelWriter = EasyExcel.write(byteArrayOutputStream, ChatDialogueMappingExport.class).build();
                // 循环写入多个Sheet
                for (ChatDialogue dialogue : dialogueList) {
                    Integer functionUsedId = dialogue.getChatDialogueFunctionUsedId();
                    if (functionUsedId == null) {
                        continue;
                    }
                    ChatDialogueFunctionUsed functionUsed = chatDialogueFunctionUsedService.getById(functionUsedId);
                    if (functionUsed == null) {
                        continue;
                    }
                    // 对话数据列表
                    Result<List<ChatDialogueMappingMagic>> listResult = getById(dialogue.getId());
                    List<ChatDialogueMappingMagic> mappingMagicList = listResult.getData();
    
                    List<ChatDialogueMappingExport> exportList = converter.convert(mappingMagicList, ChatDialogueMappingExport.class);
                    
                    WriteSheet writeSheet = EasyExcel.writerSheet(IdUtil.fastUUID()).build();
                    excelWriter.write(exportList, writeSheet);
                }
                excelWriter.finish();
                // 创建文件
                zipOut.putNextEntry(new ZipEntry(chatUser.getUsername()+".xlsx"));
                zipOut.write(byteArrayOutputStream.toByteArray());
                zipOut.closeEntry();
    
                // 关闭临时的ByteArrayOutputStream
                byteArrayOutputStream.close();
            }
            zipOut.finish();
            zipOut.close();
            outputStream.flush();
            outputStream.close();
        }

#### 四、扫一扫下方微信名片即可+博主徽信哦 **↓↓ ↓↓ ↓↓ ↓↓ ↓↓ ↓↓ ↓↓ ↓↓ ↓↓↓** ####

[EasyExcel_ - _Java_Excel_ _ Easy Excel]: https://easyexcel.opensource.alibaba.com/