exsl mysql 视图,SpringMvc实现导出Excel视图

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

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

org.apache.poi

poi

3.15

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

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 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 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 userList =userService.selectAll();

mv.addObject("userList",userList);

mv.setView(ev);

return mv;

}

@SuppressWarnings({ "unchecked"})

private ExcelExportService exportService(){

return (Map model,Workbook workbook)->{

List userList=(List)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

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