SpringMvc实现导出Excel视图

红太狼 2021-01-22 05:56 888阅读 0赞

SpringMvc提供了AbstractXlsView提供Excel视图，它是一个抽象类，提供了一个抽象方法-buildExcelDocument要去实现，其它方法，AbstractXlsView其它方法已经实现了

buildExcelDocuent主要任务是去创建一个workbook,它要用到POI的API

<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.15</version>
    </dependency>

然后我们开始实现这个视图，这个视图不是一个逻辑视图，所以无需视图解析器也可以实现它，代码如下

package com.example.eurekaclient.excel;
    
    import org.apache.commons.lang.StringUtils;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.web.servlet.view.document.AbstractXlsView;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.util.Map;
    
    /**
     * @ClassName ExcelView
     * @Description TODO
     * @Author Mr.G
     * @Date 2018/9/20 18:19
     * @Version 1.0
     */
    public class ExcelView extends AbstractXlsView {
        private String fileName=null;
        private ExcelExportService excelExportService=null;
        public ExcelView(ExcelExportService excelExportService){
            this.excelExportService=excelExportService;
        }
        public ExcelView(String fileName,ExcelExportService excelExportService){
            this.fileName=fileName;
            this.excelExportService=excelExportService;
        }
    
        public String getFileName() {
            return fileName;
        }
    
        public void setFileName(String fileName) {
            this.fileName = fileName;
        }
    
        public ExcelExportService getExcelExportService() {
            return excelExportService;
        }
    
        public void setExcelExportService(ExcelExportService excelExportService) {
            this.excelExportService = excelExportService;
        }
    
        /**
         * Application-provided subclasses must implement this method to populate
         * the Excel workbook document, given the model.
         *
         * @param model    the model Map
         * @param workbook the Excel workbook to populate
         * @param request  in case we need locale etc. Shouldn't look at attributes.
         * @param response in case we need to set cookies. Shouldn't write to it.
         */
        @Override
        protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
           if(excelExportService==null){
               throw new RuntimeException("导出接口不能为空");
           }
           if(!StringUtils.isEmpty(fileName)){
               String reqCharset=request.getCharacterEncoding();
               reqCharset=reqCharset==null?"UTF-8":reqCharset;
               fileName=new String(fileName.getBytes(reqCharset),"ISO8859-1");
               response.setHeader("Content-disposition","attachment;fileName="+fileName);
           }
           excelExportService.makeWorkBook(model,workbook);
    
        }
    }

fileName是下载文件的名字，excelExportService是创建Workbook的规则，因为我们会根据不同需求创建不同形式的excel表格，所以我们把这个接口抽象出来当作公用接口，有不同需求的话，就用不同的方式去实现

写出这个接口

package com.example.eurekaclient.excel;
    
    import org.apache.poi.ss.usermodel.Workbook;
    
    import java.util.Map;
    
    /**
     * @ClassName ExcelExportService
     * @Description TODO
     * @Author Mr.G
     * @Date 2018/9/11 22:23
     * @Version 1.0
     */
    public interface ExcelExportService {
    
        public void makeWorkBook(Map<String,Object> model, Workbook workbook);
    }

然后去写这个接口的实现

package com.example.eurekaclient.excel;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import com.example.eurekaclient.swagger2.entity.User;
    import com.example.eurekaclient.swagger2.server.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.servlet.ModelAndView;
    
    import java.util.List;
    import java.util.Map;
    
    /**
     * @ClassName ExcelController
     * @Description TODO
     * @Author Mr.G
     * @Date 2018/9/21 10:16
     * @Version 1.0
     */
    @RestController
    @RequestMapping("excel")
    public class ExcelController {
        @Autowired
        private UserService userService;
        @RequestMapping(value="export",method=RequestMethod.GET)
        public ModelAndView export(){
            ModelAndView mv=new ModelAndView();
            ExcelView ev=new ExcelView(exportService());
            ev.setFileName("所有角色.xlsx");
            List<User> userList =userService.selectAll();
            mv.addObject("userList",userList);
            mv.setView(ev);
            return mv;
        }
        @SuppressWarnings({ "unchecked"})
        private ExcelExportService exportService(){
            return (Map<String,Object> model,Workbook workbook)->{
                List<User> userList=(List<User>)model.get("userList");
                Sheet sheet=workbook.createSheet("所有角色");
                Row title= sheet.createRow(0);
                title.createCell(0).setCellValue("姓名");
                title.createCell(1).setCellValue("年纪");
                for(int i=0;i<userList.size();i++){
                  User user=userList.get(i);
                  Row row=sheet.createRow(i+1);
                  row.createCell(0).setCellValue(user.getName());
                  row.createCell(1).setCellValue(user.getAge());
    
                }
            };
        }
    }

我们这里用了lambda表达式，去实现Workbook格式的接口，文件名是所有角色，表格有两列，用的是springCloud系列的数据库

user表

这里讲解一个我当时困惑后来想明白的点，我们的Excel视图是继承AbstactXlsView，而AbstactXlsView继承ModelAndView,所以当我们返回mv时，它会通过调用渲染的方法，而AbstactXlsView是实现了自己的渲染方法，我们规则都给了它，它会根据这些规则渲染我们需要的表格

代码结束了，上效果图

![70][]

[70]: /images/1611294952715.png