Download Excel file with spring mvc-4.2 + excelview view view

You can download the jar package by yourself, springMvc4.2 + version + poi-3.10.1 version


1. After 4.2, AbstractXlsxView and AbstractXlsxStreamingView are used instead of the original AbstractExcelView to build the view downloaded from excel

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractXlsxStreamingView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;

public class MpManageExcelView extends AbstractXlsxStreamingView {

    protected SXSSFWorkbook createWorkbook(Map<String, Object> model, HttpServletRequest request) {
        List<?> data = (List<?>) model.get("data");
        if(CollectionUtils.isNotEmpty(data)) {
            return ExcelExportUtil.export(data);
        return super.createWorkbook(model, request);

    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
                                      HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        String filename = model.get("fileName") + ".xlsx"; // Get the name of the client Excel when downloading

        //Process Chinese filename
        String reqCharset = request.getCharacterEncoding(); /*Get the code of the request according to the getCharacterEncoding of the request*/
        filename = new String(filename.getBytes(reqCharset), "ISO8859-1");
        // If you want to fill in the file name automatically when downloading, you need to set the "content disposition" of the response header
        response.setHeader("Content-disposition", "attachment;filename=" + filename);

In fact, the method is obvious. Rewrite buildExcelDocument, modify and assign the model object and the workbook object in poi, and finally output the response. Some poi operations of createsheet and row can be performed on the workbook in the method. You can also override the createWorkbook in the parent class to create your own workbook object, as in the example. The original logic of the parent class creates an empty workbook object for the build method to use

public abstract class AbstractXlsxStreamingView extends AbstractXlsxView {

	 * This implementation creates a {@link SXSSFWorkbook} for streaming the XLSX format.
	protected SXSSFWorkbook createWorkbook(Map<String, Object> model, HttpServletRequest request) {
		return new SXSSFWorkbook();

Of course, when downloading, the whole logic has been defined in the way of template mode in the parent class AbstractXlsxView, defining the general business logic and skeleton of the method, and rewriting by the child class itself to realize its own logic

	protected final void renderMergedOutputModel(
			Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {

		// Create a fresh workbook instance for this render step.
		Workbook workbook = createWorkbook(model, request);

		// Delegate to application-provided document code.
		buildExcelDocument(model, workbook, request, response);

		// Set the content type.

		// Flush byte array to servlet output stream.
		renderWorkbook(workbook, response);


The rest is the code of some tool classes and actual controller classes:

Excel tool class:

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelExportUtil {

    private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);

    private static final DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
    private static final int MAX_SHEET_ROWS = 60000;

    public static SXSSFWorkbook export(List<?> dataList){
        // data
        if (dataList==null || dataList.size()==0) {
            throw new RuntimeException("data is empty,can not generate excel file");

        //Get object type annotation
        Class<?> targetClass = dataList.get(0).getClass();
        String sheetName = targetClass.getSimpleName();
        ExcelSheet excelSheet = targetClass.getAnnotation(ExcelSheet.class);
        if(excelSheet != null && StringUtils.isNotBlank({
            sheetName =;

        // Analytic field
        List<Field> fields = new ArrayList<Field>();
        if (targetClass.getDeclaredFields()!=null && targetClass.getDeclaredFields().length>0) {
            for (Field field: targetClass.getDeclaredFields()) {
                if (Modifier.isStatic(field.getModifiers())) {

        if (fields==null || fields.size()==0) {
            throw new RuntimeException("data field can not be empty");

        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        int dataSize = dataList.size();
        int index = 0;
            List<?> subList = dataList.subList(index,Math.min(dataSize,index+MAX_SHEET_ROWS));
            index += MAX_SHEET_ROWS;
        }while (index < dataSize);

        return wb;

     * Process a certain number of sheet s
     * @param workbook
     * @param dataList
    private static void resolveSheet(Workbook workbook,List<Field> fields,List<?> dataList,String sheetName){
        Sheet sheet = workbook.createSheet(sheetName);

        Row headRow = sheet.createRow(0);
        CellStyle headStyle = generateHeadStyle(workbook);
        for (int i = 0; i < fields.size(); i++) {
            Field field = fields.get(i);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            String fieldName = field.getName();
            if(excelField != null && StringUtils.isNotBlank(excelField.column())){
                fieldName = excelField.column();
            Cell cellX = headRow.createCell(i, Cell.CELL_TYPE_STRING);

        for (int dataIndex = 0; dataIndex < dataList.size(); dataIndex++) {
            int rowIndex = dataIndex+1;
            Object rowData = dataList.get(dataIndex);
            Row rowX = sheet.createRow(rowIndex);
            for (int i = 0; i < fields.size(); i++) {
                Field field = fields.get(i);
                try {
                    Object fieldValue = field.get(rowData);
                    Cell cellX = rowX.createCell(i, Cell.CELL_TYPE_STRING);
                } catch (IllegalAccessException e) {
                    logger.error(e.getMessage(), e);
                    throw new RuntimeException(e);

     * @param obj
     * @return
    private static String generateValue(Object obj){
        if(obj == null) return StringUtils.EMPTY;
        if(obj instanceof Date){
            return df.format(obj);
        return String.valueOf(obj);

     * Generate head style
     * @param wb
     * @return
    private static CellStyle generateHeadStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();

        /*****************************Use default color**************************************************/
        // Fill color

        // Font color
        Font font = wb.createFont();

        return style;


@RequestMapping(value = "/download", method = RequestMethod.GET)
    public ModelAndView download(@RequestParam String beginTime,
                                 @RequestParam String endTime) {
        List<MpPrizeExportVo> result = manageFade.export(beginTime,endTime);
        Map<String,Object> map = new HashMap<>();
        map.put("fileName","Data downloading-"+beginTime+"-"+endTime);
        return new ModelAndView(new MpManageExcelView(),map);



Keywords: Java Apache Excel REST

Added by Volitics on Mon, 06 Jan 2020 12:20:36 +0200