使用Apache POI数据导出及EasyExcel进行十万、百万的数据导出

Apache POI

Apache POI 是基于 Office Open XML 标准（ OOXML ）和 Microsoft 的 OLE 2 复合⽂档
格式（ OLE2 ）处理各种⽂件格式的开源项⽬。 简⽽⾔之，您可以使⽤ Java 读写
MS Excel ⽂件，可以使⽤ Java 读写 MS Word 和 MS PowerPoint ⽂件。

1.HSSF － 提供读写 Microsoft Excel XLS 格式 (Microsoft Excel 97 (-2003)) 档案的功
能。
2. XSSF － 提供读写 Microsoft Excel OOXML XLSX 格式 (Microsoft Excel XML (2007+))
档案的功能。
3.SXSSF － 提供低内存占⽤量读写 Microsoft Excel OOXML XLSX 格式档案的功能。
4.HWPF - 提供读写 Microsoft Word DOC97 格式 (Microsoft Word 97 (-2003)) 档案的
功能。
5.XWPF － 提供读写 Microsoft Word DOC2003 格式 (WordprocessingML (2007+)) 档
案的功能。
6.HSLF/XSLF － 提供读写 Microsoft PowerPoint 格式档案的功能。
7.HDGF/XDGF － 提供读 Microsoft Visio 格式档案的功能。
8.HPBF － 提供读 Microsoft Publisher 格式档案的功能。
9.HSMF － 提供读 Microsoft Outlook 格式档案的功能。

缺点：
java解析，生成Excel比较有名的框架有 POI ，JXL，但他们有一个严重的问题，就是 非常消耗内存， 也就是说数据量比较大的情况下有的时候会出现OOM（全称“Out Of Memory”，翻译成中文就是“内存用完了” ）的问题。

使用

导入依赖包：

 <!--EXCEL导入导出 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17-beta1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17-beta1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17-beta1</version>
</dependency>

注意：3.8版本才引入了SXSSF的使用
工具类：ExportExcel.java(放在文章结尾)

使用：

public ResponseResult reportUploadListPage(String structureName, String monitorName, String deviceName, String
            state, String startTime, String endTime) {
        //查询数量
        int count = this.getUploadListCount(structureName, monitorName, deviceName, state, startTime, endTime);
        String[] showName = null;
        String[] resourceFild = null;
        List<UploadRecord> uploadRecordDtos = null;
        ArrayList<String> strings = new ArrayList<>();
        try {
            if (count > 200000) {
                return ResponseResult.failure("数据量过大！请控制在20万以内");
            }
            if (count > 0) {
                showName = new String[]{"监测项目名称", "监测时间", "水工建筑物", "测点名称(设备名称)",
                        "测点编号", "测值1说明", "测值1", "测值2说明", "测值2", "测值3说明", "测值3",
                        "状态", "上传时间"};
                resourceFild = new String[]{"getMonitorName", "getMonitorTime", "getStructureName", "getDeviceName",
                        "getDeviceName2", "getMonitorExplain1", "getMonitorValue1", "getMonitorExplain2", "getMonitorValue2", "getMonitorExplain3", "getMonitorValue3",
                        "getStateValue", "getUploadTime"};
                int num = 0;
                if (count % 10000 == 0) {
                    num = count % 10000;
                } else {
                    num = count / 10000 + 1;
                }
                for (int k = 0; k < num; k++) {
                    Page<UploadRecord> uploadListPage = this.getUploadListPage(k + 1, 10000, structureName, monitorName, deviceName, state, startTime, endTime);
                    uploadRecordDtos = uploadListPage.getRecords();
                    List<UploadRecordDto> collect = uploadRecordDtos.stream().map(uploadRecord -> {
                        UploadRecordDto uploadRecordDto = new UploadRecordDto();
                        BeanUtil.copyProperties(uploadRecord, uploadRecordDto);
                        if (uploadRecord.getState() == 0) {
                            uploadRecordDto.setStateValue("未上传");
                        } else {
                            uploadRecordDto.setStateValue("已上传");
                        }
                        return uploadRecordDto;
                    }).collect(Collectors.toList());
                    Workbook workbook = ExportExcel.getWorkbookXlsx(collect, showName, resourceFild, UploadRecordDto.class,
                            null);
                    ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
                    workbook.write(byteArrayOutputStream);
                    byte[] bytes = byteArrayOutputStream.toByteArray();
                    MinioUploadDto minioUploadDto = minioService.upload("水利部导出数据" + (k + 1) + ".xlsx", bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                    byteArrayOutputStream.flush();
                    byteArrayOutputStream.close();
                    strings.add(minioUploadDto.getUrl());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException("数据导出异常");
        }
        return ResponseResult.success(strings);
    }

由于基本的XSSF单次只能导出65535，数据量有限而且极其容易报OOM的异常。测试的时候甚至2万条数据就OOM,当然和jvm的内存是有关的。后来使用SXSSF可以解决这个问题。

EasyExcel

EasyExcel 是⼀个基于 Java 的简单、省内存的读写 Excel 的开源项⽬。在尽可能节约内
存的情况下⽀持读写百 M 的 Excel 。
github 地址： https://github.com/alibaba/easyexcel
官⽅⽹站： https://easyexcel.opensource.alibaba.com/

使用：

<!--EXCEL导入导出 -->
 <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.0.5</version>
  </dependency>

 Integer count = uploadRecordService.getUploadListCount(structureName, monitorName, deviceName, state, startTime, endTime);
        int num = 0;
        List<UploadRecordDto> uploadRecordDtos = new ArrayList<>(); 
        //集合赋值的步骤已省略
        if (count > 300000) {
            return ResponseResult.failure("数据量过大，请控制在30万内！");
        }
        try {
            return ResponseResult.success(
                    EasyExcelUtils.exportExcel(response, uploadRecordDtos, UploadRecordDto.class, "sheetName"));
        } catch (Exception e) {
            e.printStackTrace();
            return ResponseResult.failure("导出失败");
        }

UploadRecordDto.java中需要增加注解

@Data
public class UploadRecordDto implements Serializable {

    @ExcelIgnore
    private static final long serialVersionUID = 2545145641431449276L;
    /**
     * 监测项目编码
     */
    @ExcelIgnore     //导出时忽视该字段
    private String monitorCode;

    /**
     * 监测项目名称
     */
    @ExcelProperty(value = "监测项目名称", index = 0)  
    //value 导出的标题    index为顺序索引
    private String monitorName;

    /**
     * 水工建筑物名称
     */
    @ExcelProperty(value = "水工建筑物", index = 2)
    private String structureName;

    /**
     * 测点设备名
     */
    @ExcelProperty(value = "测点名称(设备名称)", index = 3)
    private String deviceName;

    /**
     * 测点设备别名
     */
    @ExcelProperty(value = "测点编号", index = 1)
    private String deviceName2;
}

EasyExcel的速度要比poi快很多，并且不会出现oom的异常。

工具类easyExcel

EasyExcelUtils.java

package com.youming.shuiku.upload.business.utils;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;


import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;

/****
 * easyExcel工具类
 * 可用于文件的上传以及下载
 */
@Slf4j
public class EasyExcelUtils {

    // 时间格式化
    private static final String DEFAULT_DATE_FORMAT = "yyyyMMddHHmm";

    static DateTimeFormatter formatter3 = DateTimeFormatter.ofPattern(DEFAULT_DATE_FORMAT);

    static  DateTimeFormatter formatter2 = DateTimeFormatter.ofPattern("yyyyMM");

    // 每个sheet的容量，超过100000就会把数据分给其他sheet
    private static final int PAGE_SIZE = 100000;

    //随机数
    public static Random random = new Random();

    /***
     * 文件的导出（使用之前需要传入生成excel的数据，以及当前数据的类别以及当前数据的实体类----可以使用注解生成表头的标题@ExcelProperty）
     * @param response
     * @param data 生成excel的数据
     * @param excelClass 当前数据的实体类，方便使用注解生成表头
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> String exportExcel(HttpServletResponse response, List<?> data,Class<?> excelClass,String sheetName) throws Exception{
        // 获取当前的时间
        LocalDateTime localDateTime=LocalDateTime.now();

        //获取应该随机数，避免文件名称一致
        int randomCode = random.nextInt(899999) + 100000;

        //文件名
        String fileName=String.format("%s%d.xlsx",formatter3.format(localDateTime),randomCode);

        //文件路径
        String newName = String.format("data-export/timescard/%s/%s", formatter2.format(localDateTime), fileName);


        // 把查询到的数据按设置的sheet的容量进行切割
        List<? extends List<?>> lists = splitList(data, PAGE_SIZE);
        // 设置响应头
        setHead(response, fileName);
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), excelClass).registerWriteHandler(formatExcel()).registerConverter(new ListConverter()).registerConverter(new LocalDateStringConverter()).registerWriteHandler(formatExcel()).build();

        ExcelWriterSheetBuilder excelWriterSheetBuilder;
        WriteSheet writeSheet;
        for (int i = 1; i <= lists.size(); ++i) {
            excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
            excelWriterSheetBuilder.sheetNo(i).sheetName(sheetName+i);
            writeSheet = excelWriterSheetBuilder.build();
            excelWriter.write(lists.get(i - 1), writeSheet);
        }
        // 必须要finish才会写入，不finish只会创建empty的文件
        excelWriter.finish();
        return "导出成功";
    }

    /**
     * 写入数据
     * @param excelWriter excelWriter
     */
    public static void writeData(ExcelWriter excelWriter,List<? extends List<?>> lists,List<String> excelClass,String summary){

        WriteSheet writeSheet = new WriteSheet();
        //设置写到第几个sheet
        //设置表头
        List<List<String>> headList = new ArrayList<>();
        String name = summary;
        for (String excelCla : excelClass) {
            headList.add(Arrays.asList(name,excelCla));
        }
        writeSheet.setHead(headList);

        for (int i = 1; i <= lists.size(); ++i) {
            writeSheet.setSheetNo(i);
            writeSheet.setSheetName("sheetName"+i);
            excelWriter.write(lists.get(i - 1), writeSheet);
        }
    }


    /**
     * 设置响应头
     * @param response 回应的请求数据
     * @param fileName 文件名字
     */
    public static void setHead(HttpServletResponse response, String fileName) {
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            log.error("编码异常");
        }
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
    }

    /**
     * 设置Excel的格式
     *
     * @return 格式化后的Excel
     */
    public static HorizontalCellStyleStrategy formatExcel() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 设置自动换行
        contentWriteCellStyle.setWrapped(false);
        // 设置垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 切割查询的数据
     * @param list 需要切割的数据
     * @param len 按照什么长度切割
     * @param <T>
     * @return
     */
    public static <T> List<List<T>> splitList(List<T> list, int len) {
        if (list == null || list.size() == 0 || len < 1) {
            return null;
        }
        List<List<T>> result = new ArrayList<List<T>>();
        int size = list.size();
        int count = (size + len - 1) / len;
        for (int i = 0; i < count; i++) {
            List<T> subList = list.subList(i * len, (Math.min((i + 1) * len, size)));
            result.add(subList);
        }
        return result;
    }
}

ListConverter.java

package com.youming.shuiku.upload.business.utils;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.util.ArrayList;
import java.util.List;

public class ListConverter implements Converter<List> {
    @Override
    public Class supportJavaTypeKey() {
        return List.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public List convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String stringValue = cellData.getStringValue();
        String[] split = stringValue.split(",");
        List<String> enterpriseList = new ArrayList<>();
        for(int i = 0; i < split.length; i++){
            enterpriseList.add(split[i]);
        }
        return enterpriseList;
    }

    @Override
    public CellData convertToExcelData(List list, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        StringBuilder stringBuilder = new StringBuilder();
        list.forEach(o -> {
            String s = o.toString();
            stringBuilder.append(s+",");
        });
        return new CellData(stringBuilder.toString());
    }
}

LocalDateStringConverter.java

package com.youming.shuiku.upload.business.utils;



import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class LocalDateStringConverter implements Converter<LocalDateTime> {

    @Override
    public Class supportJavaTypeKey() {
        return LocalDateTime.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));
    }

    @Override
    public CellData convertToExcelData(LocalDateTime localDateTime, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
        String format = formatter.format(localDateTime);
        return new CellData(format);
    }
}

工具类poi

ExportExcel.java文章来源地址https://www.toymoban.com/news/detail-619216.html

//import org.apache.poi.hssf.usermodel.HSSFCellStyle;

@SuppressWarnings("all")
public class ExportExcel implements Serializable {

    public static String getFileName() {
        return com.youming.shuiku.system.utils.excel.DateUtil.toString(new Date(), "yyyyMMdd-HHmmss");
    }

    @SuppressWarnings("deprecation")
    public static HSSFWorkbook getWorkbookXls(List<?> resultList, String[] showName, String[] resourceField,
                                              Class<?> resultObj, Map<String, Map<String, String>> formatMap) throws SecurityException,
            NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        sheet.setDefaultColumnWidth((short) 20);
        HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
        centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
        Font font = workbook.createFont();
        font.setBold(true); // 粗体
        centerStyle.setFont(font);

        HSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        contentStyle.setBorderTop(BorderStyle.THIN);// 上边框
        contentStyle.setBorderRight(BorderStyle.THIN);// 右边框

        HSSFRow row;
        HSSFCell cell;
        createTitleXls(showName, sheet, centerStyle);
        // 下面是输出各行的数据 下面是输出各行的数据 下面是输出各行的
        for (int i = 0; i < resultList.size(); i++) {
            Object result = resultList.get(i);
            row = sheet.createRow(i + 1);
            // 创建第 i+1 行
            for (int j = 0; j < resourceField.length; j++) {
                cell = row.createCell(j);// 创建第 j 列
                Method method;
                method = resultObj.getMethod(resourceField[j]);
                // 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象
                Object obj = method.invoke(result);
                if (obj != null) {
                    if (formatMap != null && formatMap.containsKey(resourceField)) {
                        cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));
                        cell.setCellStyle(contentStyle);
                    } else {
                        String type = method.getGenericReturnType().toString();
                        if ("class java.util.Date".equals(type)) {
                            cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,
                                    com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));
                            cell.setCellStyle(contentStyle);
                        } else if ("class java.time.LocalDateTime".equals(type)) {
                            cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtils.formatLocalDateTime(
                                    (LocalDateTime) obj,
                                    com.youming.shuiku.system.utils.excel.DateUtils.DATETIME_FORMAT));
                            cell.setCellStyle(contentStyle);
                        } else {
                            cell.setCellValue(obj.toString());
                            cell.setCellStyle(contentStyle);
                        }
                    }
                } else {
                    cell.setCellStyle(contentStyle);
                }
            }
        }
        return workbook;
    }

    @SuppressWarnings("deprecation")
    public static XSSFWorkbook getWorkbookXlsx(List<?> resultList, String[] showName, String[] resourceField,
                                               Class<?> resultObj, Map<String, Map<String, String>> formatMap) throws SecurityException,
            NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("sheet1");
        sheet.setDefaultColumnWidth((short) 20);// 设置宽度
        XSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
        centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
        Font font = workbook.createFont();
        font.setBold(true); // 粗体
        centerStyle.setFont(font);

        XSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        contentStyle.setBorderTop(BorderStyle.THIN);// 上边框
        contentStyle.setBorderRight(BorderStyle.THIN);// 右边框

        XSSFRow row;
        XSSFCell cell;
        createTitleXlsx(showName, sheet, centerStyle);
        // 下面是输出各行的数据 下面是输出各行的数据 下面是输出各行的
        for (int i = 0; i < resultList.size(); i++) {
            Object result = resultList.get(i);
            row = sheet.createRow(i + 1);
            // 创建第 i+1 行
            for (int j = 0; j < resourceField.length; j++) {
                cell = row.createCell(j);// 创建第 j 列
                Method method;
                method = resultObj.getMethod(resourceField[j]);
                // 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象
                Object obj = method.invoke(result);
                if (obj != null) {
                    if (formatMap != null && formatMap.containsKey(resourceField)) {
                        cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));
                        cell.setCellStyle(contentStyle);
                    } else {
                        String type = method.getGenericReturnType().toString();
                        if ("class java.util.Date".equals(type)) {
                            cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,
                                    com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));
                            cell.setCellStyle(contentStyle);
                        } else if ("class java.time.LocalDateTime".equals(type)) {
                            cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtils.formatLocalDateTime(
                                    (LocalDateTime) obj,
                                    com.youming.shuiku.system.utils.excel.DateUtils.DATETIME_FORMAT));
                            cell.setCellStyle(contentStyle);
                        } else {
                            cell.setCellValue(obj.toString());
                            cell.setCellStyle(contentStyle);
                        }
                    }
                } else {
                    cell.setCellStyle(contentStyle);
                }
            }
        }
        return workbook;
    }

  
    @SuppressWarnings("deprecation")
    public static XSSFWorkbook getWorkbookXlsx(String[] showName, Map<String, Map<String, String>> formatMap)
            throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException,
            InvocationTargetException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("sheet1");
        sheet.setDefaultColumnWidth((short) 20);
        XSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
        centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
        Font font = workbook.createFont();
        font.setBold(true); // 粗体
        centerStyle.setFont(font);

        XSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        contentStyle.setBorderTop(BorderStyle.THIN);// 上边框
        contentStyle.setBorderRight(BorderStyle.THIN);// 右边框

        XSSFRow row;
        XSSFCell cell;
        createTitleXlsx(showName, sheet, centerStyle);
        return workbook;
    }

    @SuppressWarnings("deprecation")
    public static XSSFWorkbook getWorkbookXlsxContract(String[] showName, Map<String, Map<String, String>> formatMap)
            throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException,
            InvocationTargetException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("sheet1");
        CellStyle textStyle = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        textStyle.setDataFormat(format.getFormat("@"));
        for (int i = 0; i < showName.length; i++) {
            sheet.setDefaultColumnStyle(0, textStyle);
        }
        sheet.setDefaultColumnWidth((short) 20);
        XSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
        centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
        Font font = workbook.createFont();
        font.setBold(true); // 粗体
        centerStyle.setFont(font);

        XSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        contentStyle.setBorderTop(BorderStyle.THIN);// 上边框
        contentStyle.setBorderRight(BorderStyle.THIN);// 右边框

        XSSFRow row;
        XSSFCell cell;
        createTitleXlsx(showName, sheet, centerStyle);
        return workbook;
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param sheet    要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow   结束行
     * @param firstCol 开始列
     * @param endCol   结束列
     * @return 设置好的sheet.
     */
    public static XSSFSheet setXSSFValidation(XSSFSheet sheet, String[] textlist, int firstRow, int endRow,
                                              int firstCol, int endCol) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(data_validation_list);
        return sheet;
    }

    private static void createTitleXls(String[] showName, HSSFSheet sheet, HSSFCellStyle cellStyle) {
        HSSFRow row = sheet.createRow(0); // 创建第 1 行，也就是输出表头 创建第
        HSSFCell cell;
        for (int i = 0; i < showName.length; i++) {
            cell = row.createCell(i);
            // 创建第 i 列 创建第
            cell.setCellValue(new HSSFRichTextString(showName[i]));
            cell.setCellStyle(cellStyle);
        }
    }

    private static void createTitleXlsx(String[] showName, XSSFSheet sheet, XSSFCellStyle cellStyle) {
        XSSFRow row = sheet.createRow(0); // 创建第 1 行，也就是输出表头 创建第
        XSSFCell cell;
        for (int i = 0; i < showName.length; i++) {
            cell = row.createCell(i);
            // 创建第 i 列 创建第
            cell.setCellValue(new XSSFRichTextString(showName[i]));
            cell.setCellStyle(cellStyle);
        }
    }
    private static void createTitleSXXlsx(String[] showName, SXSSFSheet sheet, CellStyle cellStyle) {
        SXSSFRow row = sheet.createRow(0);// 创建第 1 行，也就是输出表头 创建第
        SXSSFCell cell;
        for (int i = 0; i < showName.length; i++) {
            cell = row.createCell(i);
            // 创建第 i 列 创建第
            cell.setCellValue(new XSSFRichTextString(showName[i]));
            cell.setCellStyle(cellStyle);
        }
    }

    private static void createTitle2(String[] showName, HSSFSheet sheet, HSSFCellStyle centerStyle,
                                     HSSFCellStyle style) {
        HSSFRow row = sheet.createRow(3); // 创建第 1 行，也就是输出表头 创建第
        HSSFCell cell;
        for (int i = 0; i < showName.length; i++) {
            cell = row.createCell(i);
            // 创建第 i 列 创建第
            cell.setCellValue(new HSSFRichTextString(showName[i]));
            cell.setCellStyle(centerStyle); // 样式，居中
            cell.setCellStyle(style); // 填充亮橘色
        }
    }

    /**
     * @param @param  resultList
     * @param @param  showName
     * @param @return 设定文件
     * @return HSSFWorkbook 返回类型
     * @throws
     * @Title: createWorkbook
     * @Description: 创建Workbook
     */
    @SuppressWarnings("deprecation")
    public static HSSFWorkbook createWorkbook(List<List<Cell>> resultList, String[] showName) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
        // centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        createTitleXls(showName, sheet, centerStyle);
        HSSFRow row = null;
        HSSFCell cell = null;
        if (resultList.size() > 0) {
            int[][] arraSort = new int[resultList.get(0).size()][resultList.size()];
            for (int i = 0; i < resultList.size(); i++) {
                row = sheet.createRow(i + 1);
                // sheet.setColumnWidth(i + 1, 15);
                List<Cell> cellList = resultList.get(i);
                for (int j = 0; j < cellList.size(); j++) {
                    cell = row.createCell(j);// 创建第 j 列
                    cell.setCellValue(cellList.get(j).getValue());
                    int b = cell.getStringCellValue().getBytes().length;
                    arraSort[j][i] = b;
                    if (cellList.get(j).getStyle() != null) {
                        cell.setCellStyle(cellList.get(j).getStyle());
                    }
                }
            }
            // 列的最大列宽值（不包括标题）
            int widthInfo[] = TwoMaxInfo(arraSort);
            // 与标题在比较列宽
            for (int i = 0; i < showName.length; i++) {
                // sheet.autoSizeColumn(i);
                // 算出列（包括标题的最大列宽）
                int maxWidthInfo = showName[i].getBytes().length > widthInfo[i] ? showName[i].getBytes().length
                        : widthInfo[i];
                sheet.setColumnWidth(i, maxWidthInfo > 255 ? 255 * 256 : maxWidthInfo * 256);
            }
        }
        return workbook;
    }

    public static int[] TwoMaxInfo(int[][] arraSort) {
        int[] arraySortInfo = null;
        arraySortInfo = new int[arraSort.length];
        int count = 0;
        for (int[] is : arraSort) {
            int[] arraInfo = is;
            Arrays.sort(arraInfo);
            arraySortInfo[count] = arraInfo[arraInfo.length - 1];
            count++;
        }
        return arraySortInfo;
    }

    /**
     * @param @param  resultList
     * @param @param  showName
     * @param @return 设定文件
     * @return HSSFWorkbook 返回类型
     * @throws
     * @Title: createWorkbookAll
     * @Description: 创建Workbook
     * @author: 张燕
     * @date 2015-06-23 11:13:23 +0800
     */
    @SuppressWarnings("deprecation")
    public static HSSFWorkbook createWorkbookAll(Map<String, List<List<Cell>>> vMap, String[] showName) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        for (Map.Entry<String, List<List<Cell>>> entry : vMap.entrySet()) {
            HSSFSheet sheet = workbook.createSheet(entry.getKey());
            sheet.setDefaultColumnWidth((short) 15);
            HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
            // centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            centerStyle.setAlignment(HorizontalAlignment.CENTER);
            centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            createTitleXls(showName, sheet, centerStyle);
            HSSFRow row;
            HSSFCell cell;
            for (int i = 0; i < entry.getValue().size(); i++) {
                row = sheet.createRow(i + 1);
                List<Cell> cellList = entry.getValue().get(i);
                for (int j = 0; j < cellList.size(); j++) {
                    cell = row.createCell(j);// 创建第 j 列
                    cell.setCellValue(cellList.get(j).getValue());
                    if (cellList.get(j).getStyle() != null) {
                        cell.setCellStyle(cellList.get(j).getStyle());
                    }
                }
            }
            for (int i = 0; i < showName.length; i++) {
                sheet.autoSizeColumn(i);
            }
        }
        return workbook;
    }

    public static InputStream workbook2InputStreamXls(HSSFWorkbook workbook, String fileName) throws Exception {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        baos.flush();
        byte[] aa = baos.toByteArray();
        InputStream excelStream = new ByteArrayInputStream(aa, 0, aa.length);
        baos.close();
        return excelStream;
    }

    public static InputStream workbook2InputStreamXlsx(XSSFWorkbook workbook, String fileName) throws Exception {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        baos.flush();
        byte[] aa = baos.toByteArray();
        InputStream excelStream = new ByteArrayInputStream(aa, 0, aa.length);
        baos.close();
        return excelStream;
    }

    /**
     * @param @param  resultList 导出的数据集合
     * @param @param  showName 导出的字段名称
     * @param @param  headerName Excel表头参数
     * @param @param  resourceField 实例类对象get方法名，通过反射获取值
     * @param @param  resultObj 实例类
     * @param @param  formatMap
     * @param @return 返回workbook
     * @param @throws SecurityException
     * @param @throws NoSuchMethodException
     * @param @throws IllegalArgumentException
     * @param @throws IllegalAccessException
     * @param @throws InvocationTargetException 设定文件
     * @return HSSFWorkbook 返回类型
     * @throws
     * @Title: createWorkbookVariety
     * @Description: 导出Excel报表
     */

    public static HSSFWorkbook createWorkbookVariety(List<?> resultList, String[] showName,
                                                     ArrayList<String> headerName, String[] resourceField, Class<?> resultObj,
                                                     Map<String, Map<String, String>> formatMap) throws SecurityException, NoSuchMethodException,
            IllegalArgumentException, IllegalAccessException, InvocationTargetException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        sheet.setDefaultColumnWidth((short) 15);
        HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
        // centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        /**
         * 设置表头的样式
         */
        HSSFCellStyle titylStyle = workbook.createCellStyle();
        createTitleVariety(showName, headerName, sheet, titylStyle);
        HSSFRow row;
        HSSFCell cell;
        for (int i = 0; i < resultList.size(); i++) {
            Object result = resultList.get(i);
            if (headerName != null && headerName.size() > 0) {
                row = sheet.createRow(i + 1 + headerName.size());
            } else {
                row = sheet.createRow(i + 1);
            }
            // 创建第 i+1 行
            for (int j = 0; j <= resourceField.length; j++) {
                cell = row.createCell(j);// 创建第 j 列
                cell.setCellStyle(centerStyle);
                if (j == 0) {
                    // 为Excel表的第一列添加编号，表头为：序号；eg:1,2,3,4……
                    cell.setCellValue(i + 1);
                } else {
                    Method method;
                    method = resultObj.getMethod(resourceField[j - 1]);
                    // 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象
                    Object obj = method.invoke(result);
                    if (obj != null) {
                        if (formatMap != null && formatMap.containsKey(resourceField)) {
                            cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));
                        } else {
                            String type = method.getGenericReturnType().toString();
                            if ("class java.util.Date".equals(type)) {
                                cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,
                                        com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));
                            } else {
                                cell.setCellValue(obj.toString());
                            }
                        }
                    }
                }
            }
        }
        return workbook;
    }

    /**
     * @param @param showName
     * @param @param headerName
     * @param @param sheet 设定文件
     * @return void 返回类型
     * @throws
     * @Title: createTitleVariety
     * @Description: 多行表头
     */
    private static void createTitleVariety(String[] showName, ArrayList<String> headerName, HSSFSheet sheet,
                                           HSSFCellStyle titylStyle) {
        HSSFRow row;
        HSSFCell cell;
        // titylStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // titylStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        titylStyle.setAlignment(HorizontalAlignment.CENTER);
        titylStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        if (headerName != null && headerName.size() > 0) {
            for (int i = 0; i < headerName.size(); i++) {
                row = sheet.createRow((short) i);
                if (i == 0) {
                    cell = row.createCell(i);
                    sheet.addMergedRegion(new CellRangeAddress(i, i, (short) 0, (short) showName.length));
                    cell.setCellStyle(titylStyle);
                    if (headerName.get(i) != null) {
                        cell.setCellValue(new HSSFRichTextString(headerName.get(i).toString()));
                    } else {
                        cell.setCellValue(new HSSFRichTextString(""));
                    }
                } else {
                    cell = row.createCell(i - 1);
                    sheet.addMergedRegion(new CellRangeAddress(i, i, (short) 0, (short) showName.length));
                    if (headerName.get(i) != null) {
                        cell.setCellValue(new HSSFRichTextString(headerName.get(i).toString()));
                    } else {
                        cell.setCellValue(new HSSFRichTextString(""));
                    }
                }
            }
        }
        // 设置Excel字段
        if (headerName != null && headerName.size() > 0) {
            row = sheet.createRow((short) headerName.size());
        } else {
            row = sheet.createRow(0);
        }
        for (int n = 0; n <= showName.length; n++) {
            if (n == 0) {
                cell = row.createCell(n);
                cell.setCellStyle(titylStyle);
                cell.setCellValue(new HSSFRichTextString("序号"));
            } else {
                cell = row.createCell(n);
                cell.setCellStyle(titylStyle);
                cell.setCellValue(new HSSFRichTextString(showName[n - 1]));
            }
        }
    }

    public static HSSFWorkbook createWorkbookVarietyParam(ArrayList<ArrayList<String>> resultList, String[] showName,
                                                          ArrayList<String> headerName) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        sheet.setDefaultColumnWidth((short) 15);
        HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
        // centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        /**
         * 设置表头的样式
         */
        HSSFCellStyle titylStyle = workbook.createCellStyle();
        createTitleVariety(showName, headerName, sheet, titylStyle);
        HSSFRow row;
        HSSFCell cell;
        if (resultList != null && resultList.size() > 0) {
            for (int i = 0; i < resultList.size(); i++) {
                ArrayList<String> rowResultList = resultList.get(i);
                if (headerName != null && headerName.size() > 0) {
                    row = sheet.createRow((short) (i + 1 + headerName.size()));
                } else {
                    row = sheet.createRow((short) (i + 1));
                }
                if (rowResultList != null && rowResultList.size() > 0) {

                    for (int n = 0; n <= rowResultList.size(); n++) {
                        cell = row.createCell(n);// 创建第 j 列
                        cell.setCellStyle(centerStyle);
                        if (n == 0) {
                            // 为Excel表的第一列添加编号，表头为：序号；eg:1,2,3,4……
                            cell.setCellValue(i + 1);
                        } else if (rowResultList.get(n - 1) != null) {
                            cell.setCellValue(rowResultList.get(n - 1).toString());
                        } else {
                            cell.setCellValue("");
                        }
                    }

                }
            }
        }

        return workbook;
    }

    /**
     * @param @param  resultList
     * @param @param  headList
     * @param @param  sumList
     * @param @param  showName
     * @param @param  resourceField
     * @param @param  resultObj
     * @param @param  formatMap
     * @param @return
     * @param @throws SecurityException
     * @param @throws NoSuchMethodException
     * @param @throws IllegalArgumentException
     * @param @throws IllegalAccessException
     * @param @throws InvocationTargetException 设定文件
     * @return HSSFWorkbook 返回类型
     * @throws
     * @Title: getWorkbook2
     */
    @SuppressWarnings("deprecation")
    public static HSSFWorkbook getWorkbook2(List<?> resultList, List<?> headList, List<?> sumList, String[] showName,
                                            String[] resourceField, Class<?> resultObj, Map<String, Map<String, String>> formatMap)
            throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException,
            InvocationTargetException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        sheet.setDefaultColumnWidth((short) 20);
        HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
        // centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
        centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
        HSSFDataFormat format = workbook.createDataFormat();
        // 这样才能真正的控制单元格格式，@就是指文本型
        centerStyle.setDataFormat(format.getFormat("@"));

        HSSFCellStyle style = workbook.createCellStyle();
        // style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN); // 下边框
        style.setBorderLeft(BorderStyle.THIN);// 左边框
        style.setBorderTop(BorderStyle.THIN);// 上边框
        style.setBorderRight(BorderStyle.THIN);// 右边框
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_ORANGE.getIndex());// 填亮橘色

        HSSFCellStyle greenStyle = workbook.createCellStyle();
        // greenStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // greenStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        greenStyle.setAlignment(HorizontalAlignment.CENTER);
        greenStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        greenStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        greenStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        greenStyle.setBorderTop(BorderStyle.THIN);// 上边框
        greenStyle.setBorderRight(BorderStyle.THIN);// 右边框
        greenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格
        // greenStyle.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);//填亮绿色
        greenStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());// 填深绿色
        Font greenfont = workbook.createFont();
        greenfont.setBold(true); // 粗体
        greenStyle.setFont(greenfont);

        HSSFCellStyle overGreenStyle = workbook.createCellStyle();
        // overGreenStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // overGreenStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        overGreenStyle.setAlignment(HorizontalAlignment.CENTER);
        overGreenStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        overGreenStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        overGreenStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        overGreenStyle.setBorderTop(BorderStyle.THIN);// 上边框
        overGreenStyle.setBorderRight(BorderStyle.THIN);// 右边框
        overGreenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格
        overGreenStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());// 填深绿色

        HSSFCellStyle fontStyle = workbook.createCellStyle();// 字体样式
        fontStyle.setAlignment(HorizontalAlignment.CENTER);
        fontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        fontStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        fontStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        fontStyle.setBorderTop(BorderStyle.THIN);// 上边框
        fontStyle.setBorderRight(BorderStyle.THIN);// 右边框
        fontStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格
        fontStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());// 填深绿色
        Font font = workbook.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()); // 白字
        fontStyle.setFont(font);

        HSSFRow row;
        HSSFCell cell;
        // createTitle2(showName, sheet, centerStyle, style);

        for (int j = 0; j < 3; j++) {
            row = sheet.createRow(j);
            for (int i = 0; i < showName.length; i++) {
                cell = row.createCell(i);
                if (j == 0) {
                    if (i == 0) {
                        cell.setCellValue(new HSSFRichTextString("查询时间"));
                        cell.setCellStyle(greenStyle);
                    } else if (i == 1) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(j, j, (short) i, (short) (i + 3));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString(headList.get(0).toString()));
                        cell.setCellStyle(fontStyle);
                    }
                } else if (j == 1) {
                    if (i == 0) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(j, (j + 1), (short) 0, (short) 0);
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("车辆信息"));
                        cell.setCellStyle(greenStyle);
                    } else if (i == 1) {
                        cell.setCellValue(new HSSFRichTextString("车牌号"));
                    } else if (i == 2) {
                        cell.setCellValue(new HSSFRichTextString("所属公司"));
                    } else if (i == 3) {
                        cell.setCellValue(new HSSFRichTextString("车辆类型"));
                    } else if (i == 4) {
                        cell.setCellValue(new HSSFRichTextString("入网时间"));
                    } else if (i == 5) {
                        cell.setCellValue(new HSSFRichTextString("车身颜色"));
                    } else if (i == 6) {
                        cell.setCellValue(new HSSFRichTextString("型号"));
                    } else if (i == 7) {
                        cell.setCellValue(new HSSFRichTextString("司机"));
                    } else if (i == 8) {
                        cell.setCellValue(new HSSFRichTextString("手机号"));
                    }
                    if (i > 0 && i < 9) {
                        cell.setCellStyle(overGreenStyle);
                    }
                } else if (j == 2) {
                    if (i == 1) {
                        cell.setCellValue(new HSSFRichTextString(headList.get(1).toString()));
                    } else if (i == 2) {
                        cell.setCellValue(new HSSFRichTextString(headList.get(2).toString()));
                    } else if (i == 3) {
                        cell.setCellValue(new HSSFRichTextString(headList.get(3).toString()));
                    } else if (i == 4) {
                        cell.setCellValue(new HSSFRichTextString(headList.get(4).toString()));
                    } else if (i == 5) {
                        cell.setCellValue(new HSSFRichTextString(headList.get(5).toString()));
                    } else if (i == 6) {
                        cell.setCellValue(new HSSFRichTextString(headList.get(6).toString()));
                    } else if (i == 7) {
                        cell.setCellValue(new HSSFRichTextString(headList.get(7).toString()));
                    } else if (i == 8) {
                        cell.setCellValue(new HSSFRichTextString(headList.get(8).toString()));
                    }
                    if (i > 0 && i < 9) {
                        cell.setCellStyle(fontStyle);
                    }
                }
            }
        }

        for (int k = 0; k < 3; k++) {
            row = sheet.createRow(k + 3);
            for (int i = 0; i < showName.length; i++) {
                cell = row.createCell(i);
                if (k == 0) {
                    if (i == 0) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i + 1));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("时间区间"));
                    } else if (i == 2) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i + 1));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("时长"));
                    } else if (i == 4) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("位置信息"));
                    } else if (i == 5) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("车辆状态"));
                    } else if (i == 6) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("行驶里程（km）"));
                    } else if (i == 7) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("超速（次数）"));
                    } else if (i == 8) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 3, (short) (i), (short) (i + 3));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("疲劳驾驶"));
                    } else if (i == 12) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("急加速（次数）"));
                    } else if (i == 13) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("急减速（次数）"));
                    } else if (i == 14) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("急转弯（次数）"));
                    } else if (i == 15) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("平均速度（km/h）"));
                    } else if (i == 16) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("最高速度（km/h）"));
                    }
                } else if (k == 1) {
                    if (i == 8) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("次数"));
                    } else if (i == 9) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 3, (short) (i), (short) (i + 1));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("时长"));
                    } else if (i == 11) {
                        // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                        CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));
                        sheet.addMergedRegion(region1);
                        cell.setCellValue(new HSSFRichTextString("里程（km）"));
                    }
                } else if (k == 2) {
                    if (i == 0) {
                        cell.setCellValue(new HSSFRichTextString("开始时间"));
                    } else if (i == 1) {
                        cell.setCellValue(new HSSFRichTextString("结束时间"));
                    } else if (i == 2) {
                        cell.setCellValue(new HSSFRichTextString("小时"));
                    } else if (i == 3) {
                        cell.setCellValue(new HSSFRichTextString("分钟"));
                    } else if (i == 4) {
                        cell.setCellValue(new HSSFRichTextString("详细地址"));
                    } else if (i == 5) {
                        cell.setCellValue(new HSSFRichTextString("状态"));
                    } else if (i == 9) {
                        cell.setCellValue(new HSSFRichTextString("小时"));
                    } else if (i == 10) {
                        cell.setCellValue(new HSSFRichTextString("分钟"));
                    }
                }
                cell.setCellStyle(style); // 填充亮橘色
            }
        }

        // 下面是输出各行的数据 下面是输出各行的数据 下面是输出各行的
        for (int i = 0; i < resultList.size(); i++) {
            Object result = resultList.get(i);
            row = sheet.createRow(i + 6);
            // 创建第 i+1 行
            for (int j = 0; j < resourceField.length; j++) {
                cell = row.createCell(j);// 创建第 j 列
                Method method;
                method = resultObj.getMethod(resourceField[j]);
                // 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象
                Object obj = method.invoke(result);
                if (obj != null) {
                    if (formatMap != null && formatMap.containsKey(resourceField)) {
                        cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));
                        cell.setCellStyle(centerStyle); // 样式，居中
                    } else {
                        String type = method.getGenericReturnType().toString();
                        if ("class java.util.Date".equals(type)) {
                            cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,
                                    com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));
                        } else {
                            cell.setCellValue(obj.toString());
                        }
                        cell.setCellStyle(centerStyle); // 样式，居中
                    }
                } else {
                    cell.setCellStyle(centerStyle); // 样式，居中
                }
            }
        }

        // 参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
        // CellRangeAddress region1 = new CellRangeAddress(showName.length,
        // showName.length, (short) 0, (short) 11);
        row = sheet.createRow(resultList.size() + 6);
        for (int i = 0; i < showName.length; i++) {
            cell = row.createCell(i);
            if (i == 0) {
                // 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                CellRangeAddress region1 = new CellRangeAddress(resultList.size() + 6, resultList.size() + 6,
                        (short) (i), (short) (i + 1));
                sheet.addMergedRegion(region1);
                cell.setCellValue(new HSSFRichTextString("总计"));
                cell.setCellStyle(greenStyle);
            } else if (i == 2) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(0).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 3) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(1).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 4) {// 折合时间（min）
                cell.setCellValue(new HSSFRichTextString("折合时间（min）"));
                cell.setCellStyle(greenStyle);
            } else if (i == 5) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(2).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 6) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(3).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 7) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(4).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 8) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(5).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 9) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(6).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 10) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(7).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 11) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(8).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 12) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(9).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 13) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(10).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 14) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(11).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 15) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(12).toString()));
                cell.setCellStyle(fontStyle);
            } else if (i == 16) {
                cell.setCellValue(new HSSFRichTextString(sumList.get(13).toString()));
                cell.setCellStyle(fontStyle);
            }
        }

        return workbook;
    }

}

到了这里，关于使用Apache POI数据导出及EasyExcel进行十万、百万的数据导出的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！