springboot post请求 实现数据分批导出到excel

依赖：

api group: 'org.springframework.boot', name: 'spring-boot-starter-thymeleaf', version: '2.1.4.RELEASE'


api group: 'com.alibaba', name: 'easyexcel', version: '1.1.2-beta5'

 

Controller：

    @RequestMapping(path ="export")
    public void fileExport(@RequestBody PageFilterSearchModel searchModel, HttpServletResponse response) throws IOException {
        //解析元数据
        PageResultModel pageResultModel =new PageResultModel<>((Collection) dispatchMetadataServer.doService(searchModel));
        //生成excel
        fileExportAdapter.prase(pageResultModel,response);

    }

    @RequestMapping(path ="index")
    public String fileExport1(@RequestHeader(value = "X-XSK-Token", defaultValue = "4242342") String token)  {

        return "1.html";
    }

上面的token 时项目请求拦截器里需要带的

FileExportAdapter：

package com.softium.xsk.property.export;

import com.softium.xsk.cooperated.model.PageResultModel;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public interface FileExportAdapter {

    void prase(PageResultModel pageResultModel, HttpServletResponse response) throws IOException;
}

导出excel：

package com.softium.xsk.property.export;

import com.alibaba.excel.metadata.Table;
import com.softium.xsk.cooperated.model.PageResultModel;


import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;


public class ExcelExport implements FileExportAdapter {

    @Override
    public void prase(PageResultModel pageResultModel, HttpServletResponse response) throws IOException {


        //待处理结果集
        List<Map<String,Object>> list = (List) pageResultModel.getList();
        //excel每一列标题
        List<List<String>> titles = new ArrayList<List<String>>();
        List<String> cloum = new ArrayList();
        // 设置标题
        Table table = new Table(1);
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                String key = entry.getKey();
                titles.add(Arrays.asList(key));
                cloum.add(key);
            }
            break;
        }

        table.setHead(titles);
        List<List<String>> exportList = new ArrayList<>();
        List<String> valueList = null;
        //设置每行每列的值
        for (int i = 0; i < list.size(); i++) {
            valueList = new ArrayList<>();
            for(int j=0;j<cloum.size();j++){
                Map<String, Object> stringObjectMap = list.get(i);

                Object o = stringObjectMap.get(cloum.get(j));
                if(o == null){
                    valueList.add("");
                }else{

                    valueList.add(o.toString());
                }
            }
            exportList.add(valueList);
        }

        try {
            ExcelUtil.writeExcel(response, exportList, table);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }



}

excel工具类：实现分批导出

package com.softium.xsk.property.export;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;


public class ExcelUtil {

    //每次输出行数
    private static final int PAGESIZE=500;
    //excel sheet名
    private static String SHEETNAME="sheet1";
    //导出的文件名
    private static String FILENAME="人员信息";

    /**
     * 导出 Excel ：一个 sheet，带表头
     * @param response HttpServletResponse
     * @param list 数据 list，每个元素为一个 BaseRowModel
     */
    public static void writeExcel(HttpServletResponse response, List<List<String>> list, Table table)throws Exception  {
        ExcelWriter writer = new ExcelWriter(getOutputStream(response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0);
        sheet.setSheetName(SHEETNAME);
        for(int i=0;i<2000;i++){
            List<String> a = new ArrayList<>();
            for(int j=0;j<5;j++) {
                a.add(String.valueOf(j));
            }
            list.add(a);
        }
        if(list.size()> PAGESIZE){
            averageAssign(list,writer, sheet,table);
        }else{
            writer.write0(list, sheet,table);
        }

        writer.finish();
    }
    /**
     * 导出文件时为Writer生成OutputStream
     *
     * @param response
     * @return
     */
    private static OutputStream getOutputStream(HttpServletResponse response) throws Exception {
        try {
            //response.setContentType("application/vnd.ms-excel"); xls格式
            //xlsx格式
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

            response.setCharacterEncoding("utf8");
            //response.addHeader("Content-Disposition", "attachment;filename=fileName" + ".xlsx");
            response.setHeader("fileName", ""+FILENAME+".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");

            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }


    public static void averageAssign(List<List<String>> list,ExcelWriter writer,Sheet sheet, Table table) {
        List<List<String>> targetList;
        int start = 0;
        int end = PAGESIZE;
        while (end > start){
            if (end >= list.size()){
                end = list.size();
                targetList = list.subList(start, end);
                writer.write0(targetList, sheet,table);
                break;
            }else{
                targetList = list.subList(start, end);
                writer.write0(targetList, sheet,table);
                start = end+1;
                end = start+PAGESIZE;

            }
        }
    }



}

index页面：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script src="http://cdn.bootcss.com/jquery/1.11.3/jquery.min.js"></script>
    
</head>
<body>
</body>


<script type="text/javascript">
    var data = {
        "filterKey":"staff-page-filter",
        "listKey":"staff-page-export",
        "params":{

        }
    };


    var xhr = new XMLHttpRequest();
    xhr.open('post', 'http://localhost:8080/export', true);
    xhr.responseType = 'blob';
    xhr.setRequestHeader('Content-Type', 'application/json;charset=utf-8');
    xhr.onload = function () {
        if (this.status == 200) {
            var blob = this.response;
            var a = document.createElement('a');
            var url = window.URL.createObjectURL(blob);
            a.href = url;
            //设置文件名称
            a.download = 'data.xlsx';
            a.click();
        }
    }
    xhr.send(JSON.stringify(
        data
    ));

    
</script>
</html>