由于数据量过大时，生成Excel速度很慢，过程中内存占用量很大。因此当数据条数大于10000时，不支持生成Excel文件，并且使用CopyManager（postgresql中的工具）来生成CSV文件。

利用查询后的结果生成Excel和CSV文件的代码。

import com.unicom.simpledemo.dao.UserDefinedSQLMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.*;

/**
 * Author by BayMin, Date on 2018/11/15.
 */
@Slf4j
@Component
public class ExportUtils {
    private static String driverClass;
    private static String dbUrl;
    private static String dbUserName;
    private static String dbPassword;

    @Value("${spring.datasource.druid.driver-class-name}")
    public void setDriverClass(String str) {
        driverClass = str;
    }

    @Value("${spring.datasource.druid.url}")
    public void setDbUrl(String str) {
        dbUrl = str;
    }

    @Value("${spring.datasource.druid.username}")
    public void setDbUserName(String str) {
        dbUserName = str;
    }

    @Value("${spring.datasource.druid.password}")
    public void setDbPassword(String str) {
        dbPassword = str;
    }

    /**
     * 根据查询结果生成Excel
     *
     * @return 生成的Excel
     */
    @SuppressWarnings("unchecked")
    public static SXSSFWorkbook mapListToExcel(HttpSession session, UserDefinedSQLMapper userDefinedSQLMapper) {
        List<LinkedHashMap<String, Object>> linkedHashMaps = null;
        int rows = (int) session.getAttribute("rows");
        if (rows >= 10000) {
            log.error("行数为" + rows + "，不小于10000行，无法导出为Excel表格！");
            return null;
        }
        linkedHashMaps = (List<LinkedHashMap<String, Object>>) session.getAttribute("linkedHashMaps");
        log.info("正在生成Excel表格...");
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        SXSSFSheet sheet = workbook.createSheet("result");
        SXSSFRow row = sheet.createRow(0);
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        LinkedList<String> keys = (LinkedList<String>) session.getAttribute("keys");
        int columnNum = keys.size();
        for (int i = 0; i < columnNum; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellValue(keys.get(i));
            cell.setCellStyle(style);
            sheet.trackAllColumnsForAutoSizing();
            sheet.autoSizeColumn(i);
        }
        for (int i = 0; i < linkedHashMaps.size(); i++) {
            row = sheet.createRow(i + 1);
            if (linkedHashMaps.get(i) != null) {
                Iterator<Map.Entry<String, Object>> iterator = linkedHashMaps.get(i).entrySet().iterator();
                int j = 0;
                while (iterator.hasNext()) {
                    Map.Entry<String, Object> next = iterator.next();
                    if (next.getValue() != null) {
                        row.createCell(j++).setCellValue(next.getValue().toString());
                    } else {
                        row.createCell(j++).setCellValue("");
                    }
                }
            } else {
                row.createCell(0).setCellValue("");
            }
        }
        log.info("生成Excel表格完成！");
        return workbook;
    }


    /**
     * 导出Excel
     *
     * @param workbook 需要导出的Excel
     */
    public static void exportExcel(HttpServletRequest request, HttpServletResponse response, SXSSFWorkbook workbook) {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=Table-" + Utils.parseDate(new Date()) + ".xlsx");
        try {
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出CSV文件
     *
     * @param session session(其中包含sql)
     */
    @SuppressWarnings("unchecked")
    public static void exportCSV(HttpServletRequest request, HttpServletResponse response, HttpSession session) {
        LinkedList<String> keys = (LinkedList<String>) session.getAttribute("keys");
        StringBuilder tableHead = new StringBuilder();
        for (String str : keys) {
            tableHead.append(str).append(",");
        }
        String headString = tableHead.substring(0, tableHead.length() - 1);
        headString = headString.concat("\n");
        try {
            response.setContentType("text/csv");
            response.setHeader("Content-disposition", "attachment;filename=Table-" + Utils.parseDate(new Date()) + ".csv");
            OutputStream outputStream = response.getOutputStream();
            if ((int) session.getAttribute("rows") >= 10000) {
                exportCSVBySQL(request, response, session, headString, outputStream);
            } else {
                exportCSVBySession(request, response, session, headString, outputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @SuppressWarnings("unchecked")
    private static void exportCSVBySession(HttpServletRequest request, HttpServletResponse
            response, HttpSession session, String headString, OutputStream outputStream) {
        StringBuilder stringBuilder = new StringBuilder(headString);
        String csv = "";
        List<LinkedHashMap<String, Object>> linkedHashMapsTop500 = (List<LinkedHashMap<String, Object>>) session.getAttribute("linkedHashMaps");
        for (LinkedHashMap<String, Object> linkedHashMap : linkedHashMapsTop500) {
            for (Map.Entry<String, Object> next : linkedHashMap.entrySet()) {
                if (next.getValue() != null) {
                    stringBuilder.append(next.getValue().toString());
                }
                stringBuilder.append(",");
            }
            stringBuilder.deleteCharAt(stringBuilder.length() - 1).append("\n");
        }
        csv = stringBuilder.substring(0, stringBuilder.length() - 1);
        outputStreamWrite(csv, outputStream);
        try {
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void exportCSVBySQL(HttpServletRequest request, HttpServletResponse response, HttpSession
            session, String headString, OutputStream outputStream) {
        outputStreamWrite(headString, outputStream);
        String sql = session.getAttribute("sql").toString();
        try {
            Class.forName(driverClass);
            Connection connection = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
            CopyManager copyManager = new CopyManager((BaseConnection) connection);
            copyManager.copyOut("copy (" + sql + ") to stdout with csv", outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            log.error("使用COPY命令导出CSV文件出错，当前SQL为：" + sql);
            e.printStackTrace();
        }
    }

    private static void outputStreamWrite(String info, OutputStream outputStream) {
        byte[] bytes = info.getBytes();
        InputStream inputStream = new ByteArrayInputStream(bytes);
        byte[] buff = new byte[1024];
        int len;
        try {
            while ((len = inputStream.read(buff)) != -1) {
                outputStream.write(buff, 0, len);
                outputStream.flush();
            }
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}