excel下载导出

忘记之前是什么地方找到了一个大数据量的导出方法，自己在项目里做了改进后粘过来，原理是分页读取数据导出保存到服务器上，然后从服务器下载excel到本地。测试30w导出是可以得。

实体类：

package com.test.model;

import lombok.Data;

import java.util.Date;

/**
 * create by kaixuan on 2017/9/20
 */
@Data
public class Test {
    /**
     * 模块布局策略编码
     */
    private String code;

    /**
     * 模块布局策略名称
     */
    private String name;

}

controller：

/**
 * 导出
 * @param response
 * @param modulePolicy
 */
@ResponseBody
@RequestMapping("/test/exportExcel")
public void exportExcel(HttpServletRequest request, HttpServletResponse response, Test test) {
    try {
        BigDataExcelUtil util = new BigDataExcelUtil();
        Date date = new Date();
        String fileName =  "测试导出" + date.getTime();
        int[] columnWidths = new int[]{15, 15};
        String[] titles = new String[]{ "编码",  "名称"};
        util.initExcel(pathConfig.getUploadPath(), fileName, titles, columnWidths);
        int countPage = 1;
        test.setCurrentPage(countPage);
        test.setLimit(2000); //数据量太大，分批读取数据，一次读取2000条，按情况可以自己修改
        Pager<Test> pager = this.paging(test); //分页

        //从数据库里循环读出所有数据
        while (pager != null && pager.getRecords() != null && !pager.getRecords().isEmpty()) {
            Map<String, Object[]> data = new TreeMap<>();
            int dataIdx = 1;
            for (Test bean : pager.getRecords()) {
                Object[] newRow = new Object[]{
                        StringUtils.isBlank(bean.getCode()) ? "" : bean.getCode(),
                        StringUtils.isBlank(bean.getName()) ? "" : bean.getName()
                };
                data.put(String.valueOf(dataIdx++), newRow);
            }

            util.writeDataExcel(data);
            test.setCurrentPage(++countPage);
            pager = this.paging(test);

        }

        //将文件写在硬盘上
        //防止直接导出造成内存溢出，所以先存放在硬盘上，再去下载
        util.writeExcelDisk();
        //从硬盘上下载文件到本地
        util.downloadFromDisk(request, response, CONTROLLER_NAME);
    } catch (IOException e) {
        LOGGER.error("exportException", e);
    }

}

封装导出工具类：

package com.test.util;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Map;
import java.util.Set;

/**
 * Created by ad,on 2017/12/08.
 */
public class BigDataExcelUtil {

    //log日志
    private static final Logger LOGGER = LoggerFactory.getLogger(BigDataExcelUtil.class);
    //sheet字符串
    private static final String SHEET_STR = "Sheet";
    //utf-8
    private static final String UTF8 = "UTF-8";
    //输出流
    private FileOutputStream output;
    //workbook
    private SXSSFWorkbook wb;
    //文件路径 暂时存放excel
    private String filePath;
    //sheet页
    private Sheet sheet;
    //sheet页行数
    private Integer countRow = 0;
    //sheet页数量
    private Integer sheetNum = 1;
    //标题列
    private String[] fieldNames;
    //列宽
    private int[] columnWidths;

    /**
     * 初始化
     * @param path
     * @throws FileNotFoundException
     */
    public void initExcel(String path, String fileName, String[] fieldNames, int[] columnWidths) throws FileNotFoundException {

        File pathfile = new File(path);
        if (!pathfile.exists()) {
            pathfile.mkdirs();
        }

        filePath = path + fileName + ".xlsx";
        this.fieldNames = fieldNames;
        this.columnWidths = columnWidths;

        output = new FileOutputStream(new File(filePath));  //读取的文件路径
        wb = new SXSSFWorkbook(10000); //内存中保留 10000 条数据，以免内存溢出，其余写入 硬盘
        this.createSheet();

    }

    /**
     * 创建sheet页
     */
    private void createSheet(){
        sheet = wb.createSheet(String.valueOf(SHEET_STR + sheetNum));
        wb.setSheetName(sheetNum - 1, SHEET_STR + sheetNum);

        //设置列宽
        for (int i = 0; i < columnWidths.length; ++i) {
            sheet.setColumnWidth(i, columnWidths[i] * 256);
        }

        Row row = sheet.createRow(countRow++);
        for (int i = 0; i < fieldNames.length; i++) {
            Cell cell = row.createCell(i);
            this.setCellValue(fieldNames[i], cell);
        }
    }

    /**
     * 写数据
     * @param data
     */
    public void writeDataExcel(Map<String, Object[]> data) {

        CellStyle cellStyle =  wb.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

        //Iterate over data and write to sheet
        Set<String> keyset = data.keySet();
        for (String key : keyset) {
            //超过30w条新建sheet页
            if (countRow == 300000) {
                sheetNum++;
                countRow = 0;
                this.createSheet();
            }

            Row row = sheet.createRow(countRow++);
            Object[] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellnum++);
                ExcelUtil.setCellValue(obj, cell);
            }
        }
    }

    /**
     * 设置单元格
     * @param obj
     * @param cell
     */
    public static void setCellValue(Object obj, Cell cell) {
        if (obj instanceof String) {
            cell.setCellValue((String) obj);
        } else if (obj instanceof Integer) {
            cell.setCellValue((Integer) obj);
        } else if (obj instanceof BigDecimal) {
            cell.setCellValue(obj.toString());
        }
    }
    
    /**
     * 写文件
     * @throws IOException
     */
    public void writeExcelDisk() throws IOException {
        wb.write(output);
        output.close();
    }

    /**
     * 从硬盘上下载文件到本地
     * @param request
     * @param response
     * @param fileName
     */
    public void downloadFromDisk(HttpServletRequest request, HttpServletResponse response, String fileName) {
        //从硬盘上下载文件到本地
        ServletContext application = request.getSession().getServletContext();
        OutputStream fos = null;
        try (InputStream fis = new BufferedInputStream(new FileInputStream(filePath))) {

            byte[] buffer = new byte[fis.available()];
            while (fis.read(buffer) > 0) {
                LOGGER.error("从buffer读取成功");
            }
            response.reset();
            String userAgent = request.getHeader("User-Agent");
            //针对IE或者以IE为内核的浏览器：
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = java.net.URLEncoder.encode(fileName, UTF8);
            } else {
                //非IE浏览器的处理：
                fileName = new String(fileName.getBytes(UTF8), "ISO-8859-1");
            }

            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));
            // 先去掉文件名称中的空格,然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名
            //response.addHeader("Content-Disposition", "attachment;filename=" + new String(execute.getResult().getFileName().replaceAll(" ", "").getBytes("utf-8"), "iso8859-1"));
            //response.addHeader("Content-Length", "" + bytes.length); //此处需要验证是否正确
            //设置文件MIME类型
            response.setContentType(application.getMimeType(fileName));
            response.setCharacterEncoding(UTF8);
            fos = new BufferedOutputStream(response.getOutputStream());
            fos.write(buffer);
            fos.flush();

        } catch (IOException e) {
            LOGGER.error("BigDataExcelUtilIOException", e);
        } catch (Exception e) {
            LOGGER.error("BigDataExcelUtilException", e);
        } finally {
            try {
                if(null != fos) fos.close();
            } catch (IOException e) {
                LOGGER.error("BigDataExcelUtilFinallyIOException", e);
            }
            this.deleteFile();
        }
    }

    /**
     * 文件删除
     *
     */
    private void deleteFile() {
        System.out.println(filePath);
        File file = new File(filePath);
        if (file.isFile() && file.exists() && !file.delete()) {
            LOGGER.info("file删除成功");
        }
    }
}