springboot将数据库内数据导出为excel

最新推荐文章于 2022-04-02 14:54:54 发布

安达鲁狗

最新推荐文章于 2022-04-02 14:54:54 发布

阅读量202

点赞数 2

分类专栏： java springboot 导出为EXCEL 文章标签：数据库 java

本文链接：https://blog.csdn.net/ljh981124/article/details/117228560

版权

java 同时被 3 个专栏收录

9 篇文章 0 订阅

订阅专栏

springboot

4 篇文章 0 订阅

订阅专栏

导出为EXCEL

1 篇文章 0 订阅

订阅专栏

//service层
List toExcel();
返回对象按自己的需求自己封装吧
//service实现层

public List<SendMessageDto> toExcel() {

    List<PhoneMessageDTO> all = sendMessageDAO.findAll();
    
    List<SendMessageDto> list = new ArrayList<>();
    
    for (int i = 0;i<all.size();i++){
    
        SendMessageDto dto = new SendMessageDto();
        
        PhoneMessageDTO phoneMessageDTO = all.get(i);
        
        dto.setId(phoneMessageDTO.getId());
        
        dto.setPhoneNumber(phoneMessageDTO.getPhoneNumber());
        
        dto.setMessage(phoneMessageDTO.getMessage());
        
        dto.setIsSent(phoneMessageDTO.getIsSent());
        
        dto.setErrorContent(phoneMessageDTO.getErrorContent());
        
        dto.setErrorCode(phoneMessageDTO.getErrorCode());
        
        list.add(dto);
    }
    
    return list;
    
}

实现层主要其实就是对象封装，我是在controller层做的处理

//controller层
@GetMapping("/toExcel")

public void downloadAllClassmate(@RequestParam List<String> list ,HttpServletResponse response) throws IOException {

    HSSFWorkbook workbook = new HSSFWorkbook();
    
    String[] head = new String[list.size()];
    
    HSSFSheet sheet = workbook.createSheet("发送信息表");

    List<SendMessageDto> sendMessageDtos = sendMessageService.toExcel();

    String fileName = "sendMessage"  + ".xls";//设置要导出的文件的名字
    //新增数据行，并且设置单元格数据

    int rowNum = 1;

    HSSFRow row = sheet.createRow(0);
    
    //在excel表中添加表头
    
    List<String> a = new ArrayList<>();

//以下是定义的字段根据自己的需要定义不同字段将想要的字段进行set

    for(int i = 0;i<list.size();i++){
    
        if ("phoneNumber".equals(list.get(i))){
        
            row.createCell(i).setCellValue("电话号码");
            
            a.add("phoneNumber");
            
        }
        if("message".equals(list.get(i))){
        
            row.createCell(i).setCellValue("内容");
            
            a.add("message");
            
        }
        if("issend".equals(list.get(i))){
        
            row.createCell(i).setCellValue("是否发送");
            
            a.add("issend");
        }
        if("errorCode".equals(list.get(i))){
        
            row.createCell(i).setCellValue("发送状态码");
            
        }
        if("errorContent".equals(list.get(i))){
        
            row.createCell(i).setCellValue("发送反馈信息");
            
        }
    }

    for (SendMessageDto sendMessageDto : sendMessageDtos){
    
        HSSFRow row1 = sheet.createRow(rowNum);

        for(int j = 0;j<a.size();j++){
        
            if("phoneNumber".equals(a.get(j))){
            
                if(sendMessageDto.getPhoneNumber() == null){
                
                    row1.createCell(j).setCellValue("");
                    
                }else {
                    row1.createCell(j).setCellValue(sendMessageDto.getPhoneNumber());
                    
                }
                
            }
            if ("message".equals(a.get(j))){
            
                if(sendMessageDto.getMessage() == null){
                
                    row1.createCell(j).setCellValue("");
                    
                }else {
                    row1.createCell(j).setCellValue(sendMessageDto.getMessage());
                    
                }
            }
            if("issend".equals(a.get(j))){
            
                if(sendMessageDto.getIsSent() == null){
                
                    row1.createCell(j).setCellValue("");
                    
                }else {
                    row1.createCell(j).setCellValue(sendMessageDto.getIsSent());
                }
            }
            if("errorCode".equals(a.get(j))){
            
                if(sendMessageDto.getErrorCode() == null){
                
                    row1.createCell(j).setCellValue("");
                    
                }else {
                    row1.createCell(j).setCellValue(sendMessageDto.getErrorCode());
                }
            }
            if("errorContent".equals(a.get(j))){
            
                if(sendMessageDto.getErrorContent() == null){
                
                    row1.createCell(j).setCellValue("");
                    
                }else {
                    row1.createCell(j).setCellValue(sendMessageDto.getErrorContent());
                }
            }
        }
        rowNum++;
    }
    response.setContentType("application/octet-stream");
    
    response.setHeader("Content-disposition", 
    "attachment;filename=" + fileName);
    
    response.flushBuffer();
    
    workbook.write(response.getOutputStream());
}

//核心思想就是一块一块拼起来跟垒墙一样，第一个是什么这样子。