JAVA百万条数据导出

package com.meritdata.cloud.cosp.profilemanagement.util;

import java.io.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;

/**
 * @Description: 文件压缩工具类 将指定文件/文件夹压缩成zip、rar压缩文件
 */
@Slf4j
public class CompressedFileUtil {
    public static void compressedFile(String resourcesPath, String targetPath)  {
        try {
            compressedFile(resourcesPath, targetPath, "");
        } catch (IOException e) {
            log.error("IO流异常",e);
        }
    }
    /**
     * @param
     * @param targetPath 目的压缩文件保存路径
     * @return void
     * @throws Exception
     * @desc 将源文件/文件夹生成指定格式的压缩文件,格式zip
     */
    public static void compressedFile(String resourcesPath, String targetPath, String fileName) throws IOException {
        File resourcesFile = new File(resourcesPath); // 源文件
        File targetFile = new File(targetPath); // 目的
        // 如果目的路径不存在，则新建
        if (!targetFile.exists()) {
            targetFile.mkdirs();
        }
        String targetName = (StringUtils.isEmpty(fileName) ? resourcesFile.getName() : fileName) + (fileName.indexOf(".zip") == -1 ? ".zip" : ""); // 目的压缩文件名
        ZipOutputStream out = null;
        FileOutputStream  outputStream = null;
        try {
            outputStream = new FileOutputStream(new File(targetPath, targetName));
            out = new ZipOutputStream(new BufferedOutputStream(outputStream));
            createCompressedFile(out, resourcesFile, "");
        } catch (FileNotFoundException e) {
            log.error("文件找不到",e);
        } finally {
            if(out != null ) {
                out.close();
            }
            if(outputStream!=null){
                outputStream.close();
            }
        }
    }

    /**
     * @param out  输出流
     * @param file 目标文件
     * @return void
     * @throws Exception
     * @desc 生成压缩文件。 如果是文件夹，则使用递归，进行文件遍历、压缩 如果是文件，直接压缩
     */
    @SneakyThrows
    public static void createCompressedFile(ZipOutputStream out, File file, String dir)  {
        //System.out.println(file.getPath());
        // 如果当前的是文件夹，则进行进一步处理
        if (file.isDirectory()) {
            // 得到文件列表信息
            File[] files = file.listFiles();
            // 将文件夹添加到下一级打包目录
            try {
                out.putNextEntry(new ZipEntry(dir + "/"));
            } catch (IOException e) {
                log.error("io流异常",e);
            }
            dir = dir.length() == 0 ? "" : dir + "/";
            // 循环将文件夹中的文件打包
            for (int i = 0; i < files.length; i++) {
                createCompressedFile(out, files[i], dir + files[i].getName()); // 递归处理
            }
        } else { // 当前的是文件，打包处理
            // 文件输入流
            FileInputStream fis = null ;
            try {
                fis = new FileInputStream(file);
                out.putNextEntry(new ZipEntry(dir));
                // 进行写操作
                int j = 0;
                byte[] buffer = new byte[1024];
                while ((j = fis.read(buffer)) > 0) {
                    out.write(buffer, 0, j);
                }
//                fis.close();
            } catch (IOException e) {
                log.error("io流异常",e);
            } finally {
                if(fis != null) {
                    fis.close();
                }
            }
        }
    }
}

 @ApiOperation("导出数据")
    @RequestMapping(value ="customerData",method = RequestMethod.GET)
    public ResultBody export(){
        List<Map<String,Object>> data = new ArrayList<Map<String,Object>>();
        LinkedHashMap<String, Object> keyMap = new LinkedHashMap<>();
        keyMap.put("id","编号");
        keyMap.put("age","年龄");
        keyMap.put("asset","资产");
        keyMap.put("commission","佣金");
        keyMap.put("createTime","创建时间");
        keyMap.put("custId","用户编号");
        keyMap.put("custName","姓名");
        keyMap.put("sales","出版社");
        keyMap.put("sex","性别");
        keyMap.put("states","状态");
        keyMap.put("tel","电话");
        List<CustGroupRel> all = custGroupRelRepository.findAll();
        for(CustGroupRel a :all){
            Map<String,Object> map = new LinkedHashMap<String,Object>();
            map.put("id",a.getId());
            map.put("age",a.getAge());
            map.put("asset",a.getAsset());
            map.put("commission",a.getCommission());
            map.put("createTime",a.getCreateTime());
            map.put("custId",a.getCustId());
            map.put("custName",a.getCustName());
            map.put("sales",a.getSales());
            map.put("sex",a.getSex());
            map.put("states",a.getStates());
            map.put("tel",a.getTel());
            data.add(map);
        }
        // 每个文件最大数据量
        int maxFileSize = 500000;
        // 每个sheet页最大数据量
        int maxSheetSize = 10000;

        String title = "客户表信息";
        long start = System.currentTimeMillis();
        ExportExcelUtils.exportExcel(data,keyMap,maxFileSize,maxSheetSize,title,"客户表信息",path);
        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - start) / 1000 + "秒");
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String datestr = sdf.format(date);
        String fileName = datestr + "用户表";
        try {
            CompressedFileUtil.compressedFile(path, zip,fileName);
            System.out.println("压缩文件已经生成...");
        } catch (Exception e) {
            System.out.println("压缩文件生成失败...");
            log.error("压缩失败",e);
        }
        return ResultBody.success("导出成功" + path + title + "  共耗时" + (end - start) / 1000 + "秒" );
    }

package com.meritdata.cloud.cosp.profilemanagement.entity;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;

@Data
@Table(name = "cosp_index_column_config")
@Entity
@ApiModel(value="配置", description="配置")
public class CospIndex implements Serializable {

    @Id
    @Column(name ="id")
    @ApiModelProperty(value = "id",name = "id")
    private String id;
    @ApiModelProperty(value = "columnCode",name = "编码")
    @Column(name ="column_code")
    private String columnCode;
    @ApiModelProperty(value = "columnName",name = "名字")
    @Column(name = "column_name")
    private String columnName;
    @ApiModelProperty(value = "userId",name = "用户id")
    @Column(name = "user_id")
    private String userId;
    @ApiModelProperty(value = "sortno",name = "排序号")
    @Column(name = "sortno")
    private Integer sortno;
    @ApiModelProperty(value = "createTime",name = "创建时间")
    @Column(name = "create_date")
    private Date createTime;

}

```java
package com.meritdata.cloud.cosp.profilemanagement.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.io.*;
import java.util.*;

@Slf4j
public class ExportExcelUtils {

    /**
     * @param workbook
     * @param sheetNum   (sheet的位置，0表示第一个表格中的第一个sheet)
     * @param sheetTitle （sheet的名称）
     * @param keyMap     （key 对应源数据的map的key , value 表格的标题）
     * @param result     （表格的数据）
     * @param out        （输出流）
     * @throws Exception
     * @Title: exportExcel
     * @Description: 导出Excel的方法
     */
    public static void exportExcel(HSSFWorkbook workbook, int sheetNum,
                                   String sheetTitle, LinkedHashMap<String, Object> keyMap, List<Map<String, Object>> result,
                                   OutputStream out) throws Exception {
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setFillForegroundColor(IndexedColors.WHITE.index);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        // 把字体应用到当前的样式
        style.setFont(font);

        // 指定当单元格内容显示不下时自动换行
        style.setWrapText(true);

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        // 生成表头
        int cellNum = 0;
        for (Map.Entry<String, Object> map : keyMap.entrySet()) {
            HSSFCell cell = row.createCell(cellNum);

            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(map.getValue().toString());
            cell.setCellValue(text.toString());
            cellNum++;
        }
        // 遍历集合数据，产生数据行
        if (result != null) {
            int index = 1;
            for (Map<String, Object> maps : result) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                for (Map.Entry<String, Object> map : keyMap.entrySet()) {
                    HSSFCell cell = row.createCell(cellIndex);
                    Object obj = maps.get(map.getKey());
                    if (obj != null) {
                        cell.setCellValue(obj.toString());
                    } else {
                        cell.setCellValue("");
                    }
                    cellIndex++;
                }
                index++;
            }
        }
    }

    /**
     * @param data         源数据
     * @param keyMap       excel的表头 源数据的key ("key","序号")
     * @param maxFileSize  每个文件的最大数据量  maxSheetSize 的倍数   建议100000
     * @param maxSheetSize 每个sheet的条数 建议 10000
     * @param sheetName    sheet名
     * @param fileName     文件名   不需要后缀
     * @param path         生成excel的路径
     * @return boolean 成功反回 true
     */
    public static boolean exportExcel(List<Map<String, Object>> data, LinkedHashMap<String, Object> keyMap, int maxFileSize, int maxSheetSize, String sheetName, String fileName, String path) {
        boolean result = false;
        OutputStream out = null;
        HSSFWorkbook workbook = null;
        if (data != null && data.size() > 0) {
            int zongSize = data.size();
            int zongFileCount = 0;
            // 判断需要分几个文件
            if (zongSize % maxFileSize == 0) {
                zongFileCount = zongSize / maxFileSize;
            } else {
                zongFileCount = zongSize / maxFileSize + 1;
            }
            try {
                for (int i = 0; i < zongFileCount; i++) {
                    int fileLimit = 0;
                    if ((i * maxFileSize) + maxFileSize > zongSize) {
                        fileLimit = zongSize;
                    } else {
                        fileLimit = (i * maxFileSize) + maxFileSize;
                    }
                    int fileStart = i * maxFileSize;
                    File file = new File(path);
                    if (!file.exists()) {
                        file.mkdirs();
                    }
                    out = new FileOutputStream(path + File.separator + fileName + "(" + (i + 1) + ")" + ".xls");
                    List<Map<String, Object>> fileData = new ArrayList<Map<String, Object>>();
                    for (int j = fileStart; j < fileLimit; j++) {
                        fileData.add(data.get(j));
                    }
                    int fileDataSize = fileData.size();
                    int sheetCount = 0;
                    if (fileDataSize % maxSheetSize == 0) {
                        sheetCount = fileDataSize / maxSheetSize;
                    } else {
                        sheetCount = fileDataSize / maxSheetSize + 1;
                    }
                    workbook = new HSSFWorkbook();
                    for (int j = 0; j < sheetCount; j++) {
                        int sheetStart = j * maxSheetSize;
                        int sheetLimt = 0;
                        if (j * maxSheetSize + maxSheetSize > fileDataSize) {
                            sheetLimt = fileDataSize;
                        } else {
                            sheetLimt = j * maxSheetSize + maxSheetSize;
                        }
                        List<Map<String, Object>> sheetData = new ArrayList<Map<String, Object>>();
                        for (int k = sheetStart; k < sheetLimt; k++) {
                            sheetData.add(fileData.get(k));
                        }
                        exportExcel(workbook, j, sheetName + (j + 1), keyMap, sheetData, out);
                    }
                    workbook.write(out);
                    result = true;
                }
            } catch (FileNotFoundException e) {
                result = false;
                log.info("文件找不到",e);
            } catch (IOException e) {
                result = false;

                log.info("io异常",e);
            } catch (Exception e) {
                result = false;
                log.info("错误",e);
            } finally {
                try {
                    if (out != null) {
                        out.close();
                    }
                    if (workbook != null) {
                        workbook.close();
                    }
                } catch (IOException e) {
                    result = false;
                    log.info("io异常",e);
                }

            }
        }
        return result;
    }

}