Poi快速导出百万Excel,分多Excel导出

Poi快速导出百万Excel,分多Excel导出 直接用

package com.windwardbird.util.exportExcel;

import org.apache.commons.lang3.StringUtils;
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 org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CopyOnWriteArrayList;

import static sun.net.www.protocol.http.HttpURLConnection.userAgent;

/**
 * @PackageName: com.wind.util.exportExcel
 * @Author: bird
 * @Description:
 */

@Component
public class ExportMultiExcelUtils implements Serializable {

    private static final long serialVersionUID = -3238218142023285526L;

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

    private final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd-HH");

    public static final String EXCEL_SUFFIX = ".xlsx";

    public static final Integer CURRENT_MAX_EXCEL_DATA = 500000;

    public static String EXCEL_NAME = "";

    public  Integer EXCEL_NUMBER =  0; //默认生成excel个数

   

   /**
     *@Description : 主逻辑入口
     *@Param: [headerMap, tempDataList, tableName, response]
     *@return: void
     *@Author: bird
     */
   public void ExportExcel(Map<String, Object> headerMap, List<Map<String, Object>> tempDataList, String tableName, HttpServletResponse response) throws Exception {
        logger.info("进入Excel工具类");
        long start = System.currentTimeMillis();
        EXCEL_NAME=tableName;
        List<List<Object>> excelDataResult = new CopyOnWriteArrayList<>(new ArrayList<>()); //Excel对应行的数据
        List<Object> headExcelKey = new CopyOnWriteArrayList<>(new ArrayList<>());   //获取导出表头key容器  目的为了动态获取对应数据
        List<Object> headExcelValue = new CopyOnWriteArrayList<>(new ArrayList<>()); //获取Excel表头value容器导出   目的作为表头
        getHeaderKeyAndValue(headerMap, headExcelValue, headExcelKey); //获取excel的表头key与value
        processingInputData(tempDataList, excelDataResult, headExcelKey);//处理数据
        Integer excelTotal = excelDataResult.size();
        if (excelTotal <= CURRENT_MAX_EXCEL_DATA) {
            generateOneExcel(tableName, response, excelDataResult, headExcelValue, excelTotal); //导出单Excel
        } else {
            generateMultiExcel(tableName, response, excelDataResult, headExcelValue, excelTotal);//导出多Excel
        }
       long end = System.currentTimeMillis();
       logger.info("导出Excel:{},{}",excelTotal+"条",formatDuring(end-start));
    }

    private void generateOneExcel(String tableName, HttpServletResponse response, List<List<Object>> excelDataResult, List<Object> headExcelValue, Integer excelTotal) {
        List<List<Object>> excelTempDataResult = new CopyOnWriteArrayList<>(new ArrayList<>());
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
        Sheet sheet = sxssfWorkbook.createSheet(tableName);
        excelTempDataResult.add(headExcelValue);
        for (int j = 0; j < excelTotal; j++) {
            excelTempDataResult.add(excelDataResult.get(j));
        }
        insertDataToExcel(excelTempDataResult, excelTempDataResult.size(), sheet);
        logger.info("查询数据小于"+CURRENT_MAX_EXCEL_DATA+"条");
        exportRelationExcel(EXCEL_NAME, sxssfWorkbook,response); //导出excel
    }

    private void generateMultiExcel(String tableName, HttpServletResponse response, List<List<Object>> excelDataResult, List<Object> headExcelValue, Integer excelTotal) {
        logger.info("查询到的数据大于"+CURRENT_MAX_EXCEL_DATA+"条");
        int flagNum = excelTotal % CURRENT_MAX_EXCEL_DATA; // 取模看是否需要多分Excel
        if (flagNum == 0) {
            EXCEL_NUMBER = excelTotal / CURRENT_MAX_EXCEL_DATA;
        } else {
            EXCEL_NUMBER = excelTotal / CURRENT_MAX_EXCEL_DATA + 1;
        }
        for (int i = 1; i <= EXCEL_NUMBER; i++) {
            //构造当前Excel数据
            List<List<Object>> excelTempDataResult = new CopyOnWriteArrayList<>(new ArrayList<>()); ;
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(); //在循环内部
            Sheet sheet = sxssfWorkbook.createSheet(tableName);
            if (i == 1) {
                //ToDo: 取50万整数据，单独加表头
                excelTempDataResult.add(headExcelValue);
                for (int j = 0; j < CURRENT_MAX_EXCEL_DATA; j++) {
                    excelTempDataResult.add(excelDataResult.get(j));
                }
            } else {
                excelTempDataResult.add(headExcelValue); //添加每个Excel的表头
                for (int j = CURRENT_MAX_EXCEL_DATA * (i - 1); j < CURRENT_MAX_EXCEL_DATA * i; j++) {
                    if (j > excelTotal) {
                        break;
                    } else if (j < excelTotal) {
                        excelTempDataResult.add(excelDataResult.get(j));
                    }
                }
            }
            insertDataToExcel(excelTempDataResult, excelTempDataResult.size(), sheet);
            //导出Excel
            exportRelationExcel(EXCEL_NAME, sxssfWorkbook,response); //导出excel
        }
    }

//导出一个Excel,数据分Sheet
private void generateMultiSheet(String tableName, HttpServletResponse response, List<List<Object>> excelDataResult, List<Object> headExcelValue, Integer excelTotal) {
        logger.info("查询到的数据大于"+CURRENT_MAX_EXCEL_DATA+"条");
        int flagNum = excelTotal % CURRENT_MAX_EXCEL_DATA; // 取模看是否需要多分Excel
        if (flagNum == 0) {
            EXCEL_NUMBER = excelTotal / CURRENT_MAX_EXCEL_DATA;
        } else {
            EXCEL_NUMBER = excelTotal / CURRENT_MAX_EXCEL_DATA + 1;
        }
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();  //在循环外
        for (int i = 1; i <= EXCEL_NUMBER; i++) {
            //构造当前Excel数据
            List<List<Object>> excelTempDataResult = new ArrayList<>();
            Sheet sheet = sxssfWorkbook.createSheet(tableName+i);
            if (i == 1) {
                //ToDo: 取50万整数据，单独加表头
                excelTempDataResult.add(headExcelValue);
                for (int j = 0; j < CURRENT_MAX_EXCEL_DATA; j++) {
                    excelTempDataResult.add(excelDataResult.get(j));
                }
            } else {
                excelTempDataResult.add(headExcelValue); //添加每个Excel的表头
                for (int j = CURRENT_MAX_EXCEL_DATA * (i - 1); j < CURRENT_MAX_EXCEL_DATA * i; j++) {
                    if (j > excelTotal) {
                        break;
                    } else if (j < excelTotal) {
                        excelTempDataResult.add(excelDataResult.get(j));
                    }
                }
            }
            insertDataToExcel(excelTempDataResult, excelTempDataResult.size(), sheet);
        }
        excelDataResult.clear();
        exportRelationExcel(EXCEL_NAME, sxssfWorkbook,response); //导出excel
    }
    /**
     *@Description: 给Excel赋值
     *@Param: [excelTempDataResult, size, sheet]
     *@return: void
     */
    private void insertDataToExcel(List<List<Object>> excelTempDataResult, int size, Sheet sheet) {
        for (int i = 0; i < size; i++) {
            Row row = sheet.createRow(i);
            List<Object> cellValue = excelTempDataResult.get(i);
            for (int j = 0; j < cellValue.size(); j++) {
                row.createCell(j).setCellValue(String.valueOf(null==cellValue.get(j)?"":cellValue.get(j)));
            }
        }
    }

    /**
     *@Description :
     *@Param: [tempDataList, excelDataResult, headExcelKey]
     *@return: void
     */
    private void processingInputData(List<Map<String, Object>> tempDataList, List<List<Object>> excelDataResult, List<Object> headExcelKey) {
        if(!tempDataList.isEmpty()&&tempDataList.size()>0){
            tempDataList.forEach(tempData->{
                List<Object> headExcelData = new ArrayList<>();
                headExcelKey.forEach(key->{
                    headExcelData.add(tempData.get(key));
                });
                excelDataResult.add(headExcelData);
            });
        }
    }

    /**
     *@Description : d
     *@Param: [EXCEL_NAME, sxssfWorkbook, response]
     *@return: void
     */
    private void exportRelationExcel(String EXCEL_NAME, SXSSFWorkbook sxssfWorkbook,HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        String fileName = EXCEL_NAME+"-"+dateFormat.format((new Date()))+EXCEL_SUFFIX;
        String finalFileName ;
        try {
            if (StringUtils.contains(userAgent, "MSIE")) {
                finalFileName = URLEncoder.encode(fileName, "UTF8");
            } else if (StringUtils.contains(userAgent, "Mozilla")) {
                finalFileName = new String(fileName.getBytes("GBK"), "ISO-8859-1");
            } else {
                finalFileName = URLEncoder.encode(fileName, "UTF8");
            }
            response.setHeader("Content-disposition", "attachment;filename=" + finalFileName);
            OutputStream outputStream = response.getOutputStream();
            sxssfWorkbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            sxssfWorkbook.close();
        } catch (IOException e) {
            logger.error("导出失败,失败原因:{}");e.printStackTrace();
        }
    }

    /**
     *@Description : 创建Excel并赋值给单元格
     *@Param: [excelDataResult, EXCEL_NAME, tempDataList, headExcelKey]
     *@return: org.apache.poi.xssf.streaming.SXSSFWorkbook
     */
    private SXSSFWorkbook getSheetsData(List<List<Object>> excelDataResult, String EXCEL_NAME, List<Map<String, Object>> tempDataList, List<Object> headExcelKey) {
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
        Sheet sheet = sxssfWorkbook.createSheet(EXCEL_NAME);
        if(!tempDataList.isEmpty()&&tempDataList.size()>0){
            tempDataList.forEach(tempData->{
                List<Object> headExcelData = new ArrayList<>();
                headExcelKey.forEach(key->{
                    headExcelData.add(tempData.get(key));
                });
                excelDataResult.add(headExcelData);
            });
        }
        for (int i = 0; i < excelDataResult.size(); i++) {
            Row row = sheet.createRow(i);
            List<Object> cellValue = excelDataResult.get(i);
            for (int j = 0; j < cellValue.size(); j++) {
                row.createCell(j).setCellValue(String.valueOf(null==cellValue.get(j)?"":cellValue.get(j)));
            }
        }
        return sxssfWorkbook;
    }


    /**
     *@Description :获取导出表头的key值及value值
     *@Param: [headerMap, headExcelValue, headExcelKey]
     *@return: void
     */
    private void getHeaderKeyAndValue(Map<String,Object> headerMap,List<Object> headExcelValue, List<Object> headExcelKey) {
        for(Map.Entry<String,Object> entry: headerMap.entrySet()){
            String key = entry.getKey();
            Object value = entry.getValue();
            headExcelValue.add(value);
            headExcelKey.add(key);
        }
    }

 public static String formatDuring(long time) {
        long minutes = (time % (1000 * 60 * 60)) / (1000 * 60);
        long seconds = (time % (1000 * 60)) / 1000;
        long millisecond = time % 1000;
        return "共耗时"+time+"毫秒,转为时分秒为: "+ minutes + "分钟，" + seconds + "秒，" + millisecond + "毫秒 ";
    }

/**
     *@Description :响应二
     *@Param: [EXCEL_NAME, sxssfWorkbook, response]
     *@return: void
     */
    public  ResponseEntity<InputStreamResource> exportRelationExcel(String EXCEL_NAME, SXSSFWorkbook sxssfWorkbook, HttpServletResponse response) {
        //response.setContentType("application/vnd.ms-excel");
        String fileName = EXCEL_NAME+"-"+dateFormat.format((new Date()))+ UUID.randomUUID().toString().replaceAll("-","")+EXCEL_SUFFIX;
        String filePath = "D:"+ File.separator+"sfExport";
        File tempPath = new File(filePath);
        FileSystemResource  file =null;
        if(!tempPath.exists()){
            tempPath.mkdirs();
        } 
        try {
            FileOutputStream   fileOutputStream = new FileOutputStream(filePath+File.separator+fileName);
            sxssfWorkbook.write(fileOutputStream);
            fileOutputStream.close();
            sxssfWorkbook.close();
            file = new FileSystemResource(filePath+File.separator+fileName);
            System.out.println(file.contentLength());
            HttpHeaders headers = new HttpHeaders();
            headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
            headers.add("Content-Disposition", "attachment;filename="+ new String(file.getFilename().getBytes("utf-8"), "ISO8859-1"));
            headers.add("Pragma", "no-cache");
            headers.add("Expires", "0");
            return  ResponseEntity
                    .ok()
                    .headers(headers)
                    .contentLength(file.contentLength())
                    .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
                    .body(new InputStreamResource(file.getInputStream()));
        } catch (IOException e) {
            logger.error("导出失败,失败原因:{}");
            e.printStackTrace();
            return null ;
        }

    }
}