使用SXSSFWorkbook导出excel大量数据，含工具类

工具类，maplist是数据，mapkey是表格头

package com.huayi.zjjhtrain.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
@Slf4j
public class ExportExcelUtil {

    public void exportExcel(String sheetName, List<Map<String,Object>> maplist,List<Map<String,String>> mapKey, HttpServletResponse response)
    {
        Workbook workbook  = new SXSSFWorkbook();

        // 生成一个表格
        Sheet sheet = workbook.createSheet(sheetName);
        // 设置表格默认列宽度为15个字节
        //设置宽度
        //sheet.setDefaultColumnWidth(20);
        sheet.setDefaultColumnWidth(18);
        //sheet.trackAllColumnsForAutoSizing();
//        sheet.autoSizeColumn(0);
        // 生成一个样式
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//内容居中
        // 生成一个字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setBold(true);//加粗
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        CellStyle style2 = workbook.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        // 生成另一个字体
        Font font2 = workbook.createFont();
        // font2.setBold(true);//加粗
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 产生表格标题行
        Row row = sheet.createRow(0);
        Cell cellHeader;

        if(mapKey==null||mapKey.size()<=0)
        {
            cellHeader= row.createCell(0);
            cellHeader.setCellStyle(style);
            cellHeader.setCellValue("无数据");

        }else {
            //添加标题
            int cellCount=0;
            for( Map<String,String> map: mapKey){
                cellHeader=row.createCell(cellCount);
                cellHeader.setCellStyle(style);
                cellHeader.setCellValue(map.get("column_name"));
                cellCount++;
            }
            if(maplist==null||maplist.size()<=0)
            {
                row = sheet.createRow(1);
                sheet.addMergedRegion(new CellRangeAddress(1 ,1, 0, cellCount-1));//起始行，结束行，起始列，结束列
                cellHeader= row.createCell(0);
                cellHeader.setCellStyle(style2);
                cellHeader.setCellValue("无数据");
            }
            else {
                int index=0;
                Cell cell;
                for(int i=0;i<maplist.size();i++)
                {
                    index++;
                    cellCount=0;
                    row = sheet.createRow(index);

                    Map<String,Object> mapValue=maplist.get(i);
                    //遍历key
                    for(Map<String,String> map:  mapKey){
                        cell = row.createCell(cellCount);
                        cell.setCellStyle(style2);
                        String key=map.get("column_value");
                        if("tbnum".equals(key))//序号
                        {
                            cell.setCellValue(index);
                        }else {
                            if(mapValue.containsKey(key)){
                                Object value= mapValue.get(key);
                                if(value!=null){
                                    cell.setCellValue(value.toString());
                                }else {
                                    cell.setCellValue("");
                                }
                            }
                            else {
                                cell.setCellValue("");
                            }
                        }
                        cellCount++;
                    }
                }
            }
        }
        try {
            this.setResponseHeader(response, sheetName);
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭资源

                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public void exportExcel(Workbook workbook, String sheetName, List<Map<String,Object>> maplist, OutputStream out)
    {

        // 生成一个表格
        Sheet sheet = workbook.createSheet(sheetName);
        // 设置表格默认列宽度为15个字节
        //设置宽度
        //sheet.setDefaultColumnWidth(20);
        sheet.setDefaultColumnWidth(18);
        //sheet.trackAllColumnsForAutoSizing();
//        sheet.autoSizeColumn(0);
        // 生成一个样式
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//内容居中
        // 生成一个字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setBold(true);//加粗
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        CellStyle style2 = workbook.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        // 生成另一个字体
        Font font2 = workbook.createFont();
        // font2.setBold(true);//加粗
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 产生表格标题行
        Row row = sheet.createRow(0);
        Cell cellHeader;
        if(maplist.size()<=0)
        {
            cellHeader= row.createCell(0);
            cellHeader.setCellStyle(style);
            cellHeader.setCellValue("无数据");

        }else {
            //添加标题
            int cellCount=0;
            for( String key: maplist.get(0).keySet()){
                cellHeader=row.createCell(cellCount);
                cellHeader.setCellStyle(style);
                cellHeader.setCellValue(key);
                cellCount++;
            }
            int index=0;
            Cell cell;
            for(int i=0;i<maplist.size();i++)
            {
                index++;
                cellCount=0;
                row = sheet.createRow(index);
                for(Object value: maplist.get(i).values()){
                    cell = row.createCell(cellCount);
                    cell.setCellStyle(style2);
                    if(value!=null){
                        cell.setCellValue(value.toString());
                    }else {
                        cell.setCellValue("");
                    }
                    cellCount++;
                }
            }
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭资源
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }


    }


    //发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            String originalFileName = URLEncoder.encode(fileName, "utf-8");
            response.reset();
            response.setHeader("content-disposition", "attachment;filename*=utf-8''" + originalFileName+ ".xlsx" );
            response.setContentType("application/octet-stream; charset=utf-8");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

//    public static  <T> void exportExcelByHutools(List<T> dataList, Map<String,String> headerAlias, HttpServletResponse response){
//        Workbook sxssfBook = WorkbookUtil.createSXSSFBook();
//        ExcelWriter writer = new ExcelWriter(sxssfBook,"sheet1");
//        headerAlias.entrySet().forEach(entry );
//    }

    /**
     * 读取Excel文件（Map）
     * @param is 输入流
     * @param flag 表头字段（中文）
     * @return
     * @throws Exception
     */
    public  List<Map<String,Object>> readExcelAsMap(InputStream is, String[] flag) throws Exception{
        Workbook XssfWorkbook = WorkbookFactory.create(is);
        List<Map<String,Object>> result = new ArrayList<>();
        //对excel表的各个sheet进行遍历
        for(int numSheet = 0;numSheet<XssfWorkbook.getNumberOfSheets();numSheet++){
            Sheet XssfSheet = XssfWorkbook.getSheetAt(numSheet);
            if(XssfSheet==null){
                continue;
            }

            Row row = XssfSheet.getRow(0);
            if(row == null){
                continue;
            }
            int min = row.getFirstCellNum();
            int max = row.getLastCellNum();
            int[] no = new int [flag.length];
            for(int n = 0; n < flag.length; n++){
                no[n]=-1;
            }
            for(int i = min; i < max; i++){
                Cell cell = row.getCell(i);
                if(cell.getStringCellValue().equals("") || cell.getStringCellValue() == null){
                    max--;
                }

                for(int a = 0; a < flag.length; a++){
                    if(cell.getStringCellValue().equals(flag[a]))
                        no[a]=i;
                }
            }

            for(int rowNum = 1; rowNum <= XssfSheet.getLastRowNum(); rowNum++){
                row = XssfSheet.getRow(rowNum);

                Map<String,Object> rowMap = new HashMap<>();

                for(int a = 0; a < flag.length; a++){
                    if(no[a] != -1){
                        Cell cell = row.getCell(no[a]);
                        if(cell == null){
                            rowMap.put(flag[a],null);
                            continue;
                        }
                        String cellValue = "";
                        if (null != cell) {
                            // 以下是判断数据的类型
                            switch (cell.getCellTypeEnum()) {
                                case NUMERIC: // 数字
                                    if (HSSFDateUtil.isCellDateFormatted(cell)){
                                        Date d = cell.getDateCellValue();
                                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                                        cellValue = formater.format(d);
                                    }else {
                                        DecimalFormat df = new DecimalFormat("0");
                                        cellValue = df.format(cell.getNumericCellValue());
                                    }
                                    break;
                                case STRING: // 字符串
                                    cellValue = cell.getStringCellValue();
                                    break;
                                case BOOLEAN: // Boolean
                                    cellValue = cell.getBooleanCellValue() + "";
                                    break;
                                case FORMULA: // 公式
//                                    cellValue = cell.getCellFormula() + "";
                                    cellValue = cell.getStringCellValue();
                                    break;
                                case BLANK: // 空值
                                    cellValue = "";
                                    break;
                                case ERROR: // 故障
                                    cellValue = "非法字符";
                                    break;
                                default:
                                    cellValue = "未知类型";
                                    break;
                            }
                        }

                        rowMap.put(flag[a], cellValue);

                    }
                }
                result.add(rowMap);
            }

        }

        return result;
    }

}

使用

@GetMapping(value = "/importExcel")
    public void exportToExcel(TrainPlan trainPlan, HttpServletResponse response) throws IOException {
        UserVO userVO = userVOUtils.getUserVO(request);
        if(userVO ==null ||userVO.getTrainBaseList().size()==0){
            return ;
        }
        trainPlan.setTrainBaseList(userVO.getTrainBaseList());
        List<TrainPlanExcelInfo> trainPlanExcelInfos = studentPlanService.exportTrainPlanInquiryInfo(trainPlan);
        ExportExcelUtil exportExcel=new ExportExcelUtil();
        LinkedHashMap<String, String> list = excelTableTitle2();
        //导入excel
        List<Map<String, Object>> mapsList = excelTableDataMap(trainPlanExcelInfos);
        List<Map<String, String>> mapskey = excelTableTitleMap(list);
//对应参数，工作表名称，数据，表头，返回的response
        exportExcel.exportExcel("轮转结果",mapsList,mapskey,response);
    }

//定义表头的中文名称和英文名称（英文名称就是你实体类的名称）
private LinkedHashMap<String, String> excelTableTitle2() {
    LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>();
    fieldMap.put("studentName","姓名");
    fieldMap.put("studentCode","学员编号");
    fieldMap.put("recruitYear","年度");
    fieldMap.put("unitName","培训基地");
    fieldMap.put("trainStatus","培训情况");
    fieldMap.put("unitPlanText","轮转计划查看");
    fieldMap.put("planStatus","制定情况");
    fieldMap.put("unit1","第一单元");
    fieldMap.put("unit2","第二单元");
    fieldMap.put("unit3","第三单元");
    fieldMap.put("unit4","第四单元");
    fieldMap.put("unit5","第五单元");
    fieldMap.put("unit6","第六单元");
    fieldMap.put("unit7","第七单元");
    fieldMap.put("unit8","第八单元");
    fieldMap.put("unit9","第九单元");
    fieldMap.put("unit10","第十单元");
    fieldMap.put("unit11","第十一单元");
    fieldMap.put("unit12","第十二单元");
    fieldMap.put("unit13","第十三单元");
    return fieldMap;
}

//将数据转为map，英文名称为key和结果为value
private List<Map<String, Object>>  excelTableDataMap(List<TrainPlanExcelInfo> data) {
    List<Map<String, Object>> mapList = new ArrayList<>();
    for (TrainPlanExcelInfo datum : data) {
        Map<String, Object> fieldMap = new HashMap<>();
        fieldMap.put("studentName",datum.getStudentName());
        fieldMap.put("studentCode",datum.getStudentCode());
        fieldMap.put("recruitYear",datum.getRecruitYear());
        fieldMap.put("unitName",datum.getUnitName());
        fieldMap.put("trainStatus",datum.getTrainStatus());
        fieldMap.put("unitPlanText",datum.getUnitPlanText());
        fieldMap.put("planStatus",datum.getPlanStatus());
        fieldMap.put("unit1",datum.getUnit1());
        fieldMap.put("unit2",datum.getUnit2());
        fieldMap.put("unit3",datum.getUnit3());
        fieldMap.put("unit4",datum.getUnit4());
        fieldMap.put("unit5",datum.getUnit5());
        fieldMap.put("unit6",datum.getUnit6());
        fieldMap.put("unit7",datum.getUnit7());
        fieldMap.put("unit8",datum.getUnit8());
        fieldMap.put("unit9",datum.getUnit9());
        fieldMap.put("unit10",datum.getUnit10());
        fieldMap.put("unit11",datum.getUnit11());
        fieldMap.put("unit12",datum.getUnit12());
        fieldMap.put("unit13",datum.getUnit13());
        mapList.add(fieldMap);
    }
    return mapList;
}
//将表头对应的英文和中文进行格式，为excel导出做准备，参数就是上面你定义的表头数据
private List<Map<String, String>>  excelTableTitleMap(LinkedHashMap<String, String> map ) {
    List<Map<String, String>> mapList = new ArrayList<>();
    Iterator<String> iterator = map.keySet().iterator();
    while (iterator.hasNext()){
        String key = iterator.next();
        Map<String, String> m = new HashMap<>();
        m.put("column_value",key);
        m.put("column_name",map.get(key));
        mapList.add(m);
    }
    return mapList;
}