java poi 生成 Xlsx格式的Excel工具类

目的

应对Excel生成的不同需求，使用一套Excel生成工具，避免重复造轮子。

工具类的功能

1、生成Xlsx格式的Excel文件
2、解的Xlsx格式的Excel文件
3、合并多个Xlsx格式的Excel文件

功能逻辑

设计思路：
1、先将数据集合转换为ExcelSheet 对象
2、再将ExcelSheet 对象生成Excel的字节流对象

类图如下：

类具有的功能：
AbstractGenerateExcelSheet 抽象类
1、将数据集合生成ExcelSheet对象（generateSheet方法）
2、ExcelSheet 对象转换（convertExcelSheet方法），将其他 ExcelSheet 转换为自己需要的 ExcelSheet

AbstractExportXlsxExcel 抽象类
1、将ExcelSheet 对象生成Xlsx格式的Excel文件字节流（exportExcel方法）
2、将多个Xlsx格式的Excel文件合并为一个ExcelSheet对象（mergeFirstSheetExcel方法）
3、生成Excel文件的特殊逻辑（addHook方法+hook方法）

代码实现

maven依赖：

 <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>

注解类ExcelCellType
用途：用于标记单元格的数据类型

/**
 * @author huijing xu
 * @date 2020/8/24
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelCellType {
    enum Type {
        NUMBER_DOUBLE,
        NUMBER_INTEGER,
        STRING
    }

    Type cellTyp() default Type.STRING;
}

实体类ExcelSheet
用途：承载Excel单元格的数据，并根据此对象，生成Excel

/**
 * @author huijing xu
 * @date 2020/8/24
 */
public class ExcelSheet {
    // Excel中Sheet的name
    private String sheetName;
    // 标题行
    private LinkedList<String> rowNameList;
    // 数据行
    private List<Map<String, CellEntity>> rowValueList;

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public LinkedList<String> getRowNameList() {
        return rowNameList;
    }

    public void setRowNameList(LinkedList<String> rowNameList) {
        this.rowNameList = rowNameList;
    }

    public List<Map<String, CellEntity>> getRowValueList() {
        return rowValueList;
    }

    public void setRowValueList(List<Map<String, CellEntity>> rowValueList) {
        this.rowValueList = rowValueList;
    }

    // Excel中单元格对象
    public class CellEntity {
        // 单元格的值
        private Object cellValue;
        // 单元格的类型
        private ExcelCellType.Type type;

        public Object getCellValue() {
            return cellValue;
        }

        public void setCellValue(Object cellValue) {
            this.cellValue = cellValue;
        }

        public ExcelCellType.Type getType() {
            return type;
        }

        public void setType(ExcelCellType.Type type) {
            this.type = type;
        }
    }

}

生成Excel的抽象类
用途：生成Excel，解析Excel

/**
 * @author huijing xu
 * @date 2020/8/24
 */
@Slf4j
public abstract class AbstractExportXlsxExcel {

    protected Map<ExcelCellType.Type, XSSFCellStyle> cellStyleMap;

    /**
     * 将ExcelSheet，生成Xlsx格式的Excel文件字节流
     *
     * @param excelSheet
     * @return
     */
    public ByteArrayInputStream exportExcel(ExcelSheet excelSheet) {
        if (excelSheet == null) {
            return null;
        }
        return exportExcel(Arrays.asList(excelSheet));
    }


    /**
     * 将多个ExcelSheet，生成对应多个Sheet的Xlsx格式的Excel文件字节流
     *
     * @param sheetList
     * @return
     */
    public ByteArrayInputStream exportExcel(List<ExcelSheet> sheetList) {
        if (CollectionUtils.isEmpty(sheetList)) {
            return null;
        }

        try {
            XSSFWorkbook workbook = new XSSFWorkbook();

            generateCellStyle(workbook);

            for (ExcelSheet excelSheet : sheetList) {
                createSheet(workbook, excelSheet);
            }

            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            workbook.write(byteArrayOutputStream);
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            return inputStream;
        } catch (Exception e) {
            log.error("export excel of xlsx error:{}", e);
            throw new DataOperateException(e);
        }
    }

    /**
     * 将多个Xlsx格式的Excel文件合并为一个ExcelSheet对象
     * 合并后的标题取自第一个文件
     * 合并后的单元格对象，不具有单元格格式
     *
     * @param byteList
     * @return
     */
    public ExcelSheet mergeFirstSheetExcel(List<byte[]> byteList) {
        ExcelSheet excelSheet = new ExcelSheet();
        if (CollectionUtils.isEmpty(byteList)) {
            return excelSheet;
        }

        LinkedList<String> titleList = new LinkedList<>();
        List<Map<String, ExcelSheet.CellEntity>> excelSheetList = new ArrayList<>();

        for (int i = 0; i < byteList.size(); i++) {
            byte[] bytes = byteList.get(i);

            // 第一个文件
            if (i == 0) {
                titleList = parseTitle(bytes);
            }

            List<Map<String, ExcelSheet.CellEntity>> excelSheetMap = parseContent(bytes, titleList);
            excelSheetList.addAll(excelSheetMap);
        }

        excelSheet.setRowNameList(titleList);
        excelSheet.setRowValueList(excelSheetList);

        return excelSheet;
    }

    /**
     * 根据标题列表，解析文件的内容
     * 从第二行开始，默认第一行为标题行
     *
     * @param bytes
     * @param titleList
     * @return
     */
    private List<Map<String, ExcelSheet.CellEntity>> parseContent(byte[] bytes, LinkedList<String> titleList) {
        List<Map<String, ExcelSheet.CellEntity>> rowValueList = new ArrayList<>();
        if (bytes == null || bytes.length == 0 || CollectionUtils.isEmpty(titleList)) {
            return rowValueList;
        }
        try {
            ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
            XSSFWorkbook workbook = new XSSFWorkbook(byteArrayInputStream);

            // 获取第一个sheet
            XSSFSheet sheet = workbook.getSheetAt(0);

            // 获取此文件title映射的位置
            HashMap<String, Integer> titleIndexMap = generateTitleIndexMap(sheet);

            // 这个sheet最大的行
            int rowMax = sheet.getLastRowNum() + 1;
            // 内容行，从第二行开始
            for (int i = 1; i < rowMax; i++) {
                Row row = sheet.getRow(i);

                Map<String, ExcelSheet.CellEntity> cellEntityMap = new HashMap<>();
                for (String title : titleList) {
                    ExcelSheet.CellEntity cellEntity = generateCellEntity(title, titleIndexMap, row);
                    cellEntityMap.put(title, cellEntity);
                }

                rowValueList.add(cellEntityMap);
            }

            return rowValueList;
        } catch (Exception e) {
            log.error("parse Byte xlsx error:{}", e);
            throw new DataOperateException(e);
        }
    }

    /**
     * 根据标题，设置对应此标题的文本内容映射到第几列
     *
     * @param sheet
     * @return
     */
    private HashMap<String, Integer> generateTitleIndexMap(XSSFSheet sheet) {
        HashMap<String, Integer> titleIndexMap = new HashMap<>();
        Row titleRow = sheet.getRow(0);
        short titleRowSize = titleRow.getLastCellNum();
        for (int j = 0; j < titleRowSize; j++) {
            Cell cell = titleRow.getCell(j);
            if (cell != null) {
                String title = cell.getStringCellValue();
                titleIndexMap.put(title, j);
            }
        }
        return titleIndexMap;
    }

    private ExcelSheet.CellEntity generateCellEntity(String title, HashMap<String, Integer> titleIndexMap, Row row) {
        ExcelSheet.CellEntity cellEntity = null;

        Integer integer = titleIndexMap.get(title);
        if (integer != null) {
            Cell cell = row.getCell(integer);
            if (cell != null) {
                cellEntity = new ExcelSheet().new CellEntity();

                CellType cellType = cell.getCellType();
                switch (cellType) {
                    case NUMERIC:
                        cellEntity.setCellValue(cell.getNumericCellValue());
                        break;
                    case STRING:
                        cellEntity.setCellValue(cell.getStringCellValue());
                        break;
                    case BOOLEAN:
                        cellEntity.setCellValue(cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        cellEntity.setCellValue(cell.getCellFormula());
                        break;
                }
            }
        }

        return cellEntity;
    }

    /**
     * 解析出excel的title，以第一个文件的title为准
     *
     * @return
     */
    private LinkedList<String> parseTitle(byte[] bytes) {
        try {
            ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
            XSSFWorkbook workbook = new XSSFWorkbook(byteArrayInputStream);

            // 获取第一个sheet
            XSSFSheet sheet = workbook.getSheetAt(0);

            LinkedList<String> titleNameList = new LinkedList<>();

            Row titleRow = sheet.getRow(0);
            short titleRowSize = titleRow.getLastCellNum();
            for (int j = 0; j < titleRowSize; j++) {
                Cell cell = titleRow.getCell(j);
                if (cell != null) {
                    String title = cell.getStringCellValue();
                    titleNameList.add(title);
                }
            }

            return titleNameList;
        } catch (Exception e) {
            log.error("parse Byte xlsx error:{}", e);
            throw new DataOperateException(e);
        }
    }

    /**
     * 创建sheet
     *
     * @param workbook
     * @param excelSheet
     */
    private void createSheet(XSSFWorkbook workbook, ExcelSheet excelSheet) {
        XSSFSheet xssfSheet;
        if (StringUtils.isNotEmpty(excelSheet.getSheetName())) {
            xssfSheet = workbook.createSheet(excelSheet.getSheetName());
        } else {
            xssfSheet = workbook.createSheet();
        }

        createHeadRow(xssfSheet, excelSheet);

        createContentRow(xssfSheet, excelSheet);
    }

    /**
     * 创建表头行
     *
     * @param xssfSheet
     * @param excelSheet
     */
    private void createHeadRow(XSSFSheet xssfSheet, ExcelSheet excelSheet) {
        XSSFRow headRow = xssfSheet.createRow(0);
        int columnIndex = 0;

        for (String title : excelSheet.getRowNameList()) {
            XSSFCell headCell = headRow.createCell(columnIndex++);
            headCell.setCellStyle(cellStyleMap.get(ExcelCellType.Type.STRING));
            headCell.setCellValue(title);
        }
    }


    /**
     * 创建内容行
     *
     * @param xssfSheet
     * @param excelSheet
     */
    private void createContentRow(XSSFSheet xssfSheet, ExcelSheet excelSheet) {
        // 文本行开始位置，从第二行开始
        int index = 1;
        List<Map<String, ExcelSheet.CellEntity>> rowValueList = excelSheet.getRowValueList();
        if (CollectionUtils.isEmpty(rowValueList)) {
            return;
        }

        LinkedList<String> rowNameList = excelSheet.getRowNameList();
        for (Map<String, ExcelSheet.CellEntity> row : rowValueList) {
            XSSFRow textRow = xssfSheet.createRow(index++);
            int j = 0;
            for (String title : rowNameList) {
                Object cellValue = null;
                ExcelCellType.Type cellType = null;

                ExcelSheet.CellEntity cellEntity = row.get(title);
                if (cellEntity != null) {
                    cellValue = row.get(title).getCellValue();
                    cellType = row.get(title).getType();
                }

                XSSFCell cell = textRow.createCell(j++);

                setContentCellValue(cell, cellValue, cellType);
            }
        }

        if (addHook(excelSheet)) {
            hook(index, xssfSheet, excelSheet);
        }
    }

    /**
     * 是否增加钩子方法，增加特殊逻辑
     *
     * @return
     */
    protected abstract boolean addHook(ExcelSheet excelSheet);


    /**
     * 钩子方法，实现特殊逻辑
     *
     * @param startRow
     * @param xssfSheet
     * @param excelSheet
     */
    protected abstract void hook(int startRow, XSSFSheet xssfSheet, ExcelSheet excelSheet);

    /**
     * 设置单元格格式以及单元格的内容
     *
     * @param cell
     * @param cellValue
     * @param cellType
     */
    private void setContentCellValue(XSSFCell cell, Object cellValue, ExcelCellType.Type cellType) {
        if (cellValue != null && cellType != null) {
            switch (cellType) {
                case NUMBER_DOUBLE:
                    XSSFCellStyle number = cellStyleMap.get(ExcelCellType.Type.NUMBER_DOUBLE);
                    cell.setCellStyle(number);
                    cell.setCellValue(cellValue != null ? Double.parseDouble(cellValue.toString()) : 0);
                    break;
                case NUMBER_INTEGER:
                    XSSFCellStyle integer = cellStyleMap.get(ExcelCellType.Type.NUMBER_INTEGER);
                    cell.setCellStyle(integer);
                    cell.setCellValue(cellValue != null ? Double.parseDouble(cellValue.toString()) : 0);
                    break;
                case STRING:
                    XSSFCellStyle stringStyle = cellStyleMap.get(ExcelCellType.Type.STRING);
                    cell.setCellStyle(stringStyle);
                    cell.setCellValue(cellValue != null ? cellValue.toString() : "");
            }
        }
    }

    /**
     * 生成cell的格式
     *
     * @param workbook
     */
    private void generateCellStyle(XSSFWorkbook workbook) {
        cellStyleMap = new HashMap<>();

        XSSFCellStyle numberStyle = generateNumberStyle(workbook);
        cellStyleMap.put(ExcelCellType.Type.NUMBER_DOUBLE, numberStyle);

        XSSFCellStyle integerStyle = generateIntegerStyle(workbook);
        cellStyleMap.put(ExcelCellType.Type.NUMBER_INTEGER, integerStyle);

        XSSFCellStyle stringStyle = generateStringStyle(workbook);
        cellStyleMap.put(ExcelCellType.Type.STRING, stringStyle);
    }

    /**
     * 统一的具有小数点精度（两位）数字类型cell格式
     *
     * @param workbook
     * @return
     */
    private XSSFCellStyle generateNumberStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();

        XSSFDataFormat df = workbook.createDataFormat();
        // 保留两位小数点
        short format = df.getFormat("#,##0.00");
        cellStyle.setDataFormat(format);

        XSSFFont font = workbook.createFont();
        font.setFontName("Calibri");
        cellStyle.setFont(font);

        return cellStyle;
    }

    /**
     * 统一的不具有小数点精度数字类型cell格式
     *
     * @param workbook
     * @return
     */
    private XSSFCellStyle generateIntegerStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();

        XSSFDataFormat df = workbook.createDataFormat();
        // 数字类型，不保留小数
        short format = df.getFormat("#,#0");
        cellStyle.setDataFormat(format);

        XSSFFont font = workbook.createFont();
        font.setFontName("Calibri");
        cellStyle.setFont(font);

        return cellStyle;
    }

    /**
     * 统一的字符串类型cell格式
     *
     * @param workbook
     * @return
     */
    private XSSFCellStyle generateStringStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();

        XSSFFont font = workbook.createFont();
        font.setFontName("Calibri");
        cellStyle.setFont(font);

        return cellStyle;
    }

}

生成ExcelSheet对象的抽象类
用途：将数据集转为ExcelSheet对象，用于Excel的生成

/**
 * @author huijing xu
 * @date 2020/8/24
 */
public abstract class AbstractGenerateExcelSheet<T> {

    private LinkedHashMap<String, Field> fieldNameMap = new LinkedHashMap<>();
    private Map<String, ExcelCellType.Type> fieldCellTypeMap = new HashMap<>();

    @PostConstruct
    public void init() {
        Field[] fields = initFieldList();

        for (int i = 0; i < fields.length; i++) {
            Column column = fields[i].getAnnotation(Column.class);
            if (column != null) {
                fields[i].setAccessible(true);
                fieldNameMap.put(column.name(), fields[i]);

                ExcelCellType type = fields[i].getAnnotation(ExcelCellType.class);
                if (type != null) {
                    fieldCellTypeMap.put(column.name(), type.cellTyp());
                }
            }
        }
    }

    abstract Field[] initFieldList();

    /**
     * 根据数据集，生成ExcelSheet对象
     *
     * @param data
     * @param <T>
     * @return
     */
    public <T> ExcelSheet generateSheet(List<T> data) {
        return generateSheet(data, null);
    }

    public <T> ExcelSheet generateSheet(List<T> data, String sheetName) {
        ExcelSheet excelSheet = new ExcelSheet();
        excelSheet.setSheetName(sheetName);

        LinkedList<String> rowNameList = generateRowNameList();
        excelSheet.setRowNameList(rowNameList);

        List<Map<String, ExcelSheet.CellEntity>> rowValueList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(data)) {
            rowValueList = generateRowValueList(rowNameList, data);
        }
        excelSheet.setRowValueList(rowValueList);

        return excelSheet;
    }

    public ExcelSheet convertExcelSheet(ExcelSheet otherSheet) {
        return convertExcelSheet(otherSheet, null);
    }

    /**
     * 将其他 ExcelSheet 转换为自己需要的 ExcelSheet
     *
     * @param otherSheet 需要转换的ExcelSheet
     * @param sheetName  sheet的名称
     * @return
     */
    public ExcelSheet convertExcelSheet(ExcelSheet otherSheet, String sheetName) {
        ExcelSheet excelSheet = new ExcelSheet();
        excelSheet.setSheetName(sheetName);

        LinkedList<String> rowNameList = generateRowNameList();
        excelSheet.setRowNameList(rowNameList);

        List<Map<String, ExcelSheet.CellEntity>> rowValueList = new ArrayList<>();
        if (otherSheet != null && CollectionUtils.isNotEmpty(otherSheet.getRowValueList())) {
            otherSheet.getRowValueList().stream().forEach(v -> {
                Map<String, ExcelSheet.CellEntity> cellEntityMap = new HashMap<>();
                for (String fieldName : rowNameList) {
                    ExcelSheet.CellEntity cellEntity = v.get(fieldName);

                    if (cellEntity != null && cellEntity.getCellValue() != null) {
                        ExcelCellType.Type type = fieldCellTypeMap.get(fieldName);
                        cellEntity.setType(type);
                    }

                    cellEntityMap.put(fieldName, cellEntity);
                }
                rowValueList.add(cellEntityMap);
            });
        }
        excelSheet.setRowValueList(rowValueList);

        return excelSheet;
    }

    /**
     * 生成excel各列的名称
     *
     * @return
     */
    private LinkedList<String> generateRowNameList() {
        LinkedList<String> rowNameList = new LinkedList<>();
        if (MapUtils.isNotEmpty(fieldNameMap)) {
            for (String fieldName : fieldNameMap.keySet()) {
                rowNameList.add(fieldName);
            }
        }
        return rowNameList;
    }


    /**
     * 根据列名以及数据集，生成行数据 List<Map<String, ExcelSheet.CellEntity>>
     * 每行中列数据 Map<String, ExcelSheet.CellEntity> ,key为列名，value为ExcelSheet.CellEntity对象
     *
     * @param rowNameList
     * @param data
     * @param <T>
     * @return
     */
    private <T> List<Map<String, ExcelSheet.CellEntity>> generateRowValueList(LinkedList<String> rowNameList,
                                                                              List<T> data) {
        List<Map<String, ExcelSheet.CellEntity>> rowValueList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(data)) {
            for (T t : data) {
                Map<String, ExcelSheet.CellEntity> cellEntityMap = new HashMap<>();
                for (String rowName : rowNameList) {
                    ExcelSheet.CellEntity cellEntity = new ExcelSheet().new CellEntity();

                    Field field = fieldNameMap.get(rowName);
                    ExcelCellType.Type type = fieldCellTypeMap.get(rowName);
                    try {
                        cellEntity.setCellValue(field.get(t));
                        cellEntity.setType(type);

                        cellEntityMap.put(rowName, cellEntity);
                    } catch (IllegalAccessException e) {
                        throw new DataOperateException(e);
                    }
                    cellEntityMap.put(rowName, cellEntity);
                }
                rowValueList.add(cellEntityMap);
            }
        }
        return rowValueList;
    }

}

具体使用

1、实现 AbstractExportXlsxExcel 抽象类

/**
 * @author huijing xu
 * @date 2020/8/25
 */
@Component
public class NormalExportXlsxExcel extends AbstractExportXlsxExcel {

    @Override
    protected boolean addHook(ExcelSheet excelSheet) {
        return false;
    }

    @Override
    protected void hook(int startRow, XSSFSheet xssfSheet, ExcelSheet excelSheet) {

    }
}

2、定义数据实体

**
 * @author huijing xu
 * @date 2020/9/30
 */
public class OrderDetails {
    @Column(name = "OrderId")
    @ExcelCellType(cellTyp = ExcelCellType.Type.NUMBER_INTEGER)
    private Long orderId;

    @Column(name = "OrderAmount")
    @ExcelCellType(cellTyp = ExcelCellType.Type.NUMBER_DOUBLE)
    private BigDecimal orderAmount;

    @Column(name = "OrderName")
    @ExcelCellType(cellTyp = ExcelCellType.Type.STRING)
    private String orderName;

    public Long getOrderId() {
        return orderId;
    }

    public void setOrderId(Long orderId) {
        this.orderId = orderId;
    }

    public BigDecimal getOrderAmount() {
        return orderAmount;
    }

    public void setOrderAmount(BigDecimal orderAmount) {
        this.orderAmount = orderAmount;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }
}

3、实现AbstractGenerateExcelSheet 抽象类

/**
 * @author huijing xu
 * @date 2020/9/30
 */
@Component
public class OrderExcelSheet extends AbstractGenerateExcelSheet<OrderDetails> {

    @Override
    Field[] initFieldList() {
        return OrderDetails.class.getDeclaredFields();
    }
}

测试1：将数据集生成Excel

/**
 * @author huijing xu
 * @date 2020/9/30
 */
public class TestExcel  {
    @Autowired
    private OrderExcelSheet orderExcelSheet;
    @Autowired
    private NormalExportXlsxExcel normalExportXlsxExcel;

    @Test
    public void test() {
        // 填充数据
        OrderDetails order1 = new OrderDetails();
        order1.setOrderId(123456L);
        order1.setOrderAmount(new BigDecimal("2.5"));
        order1.setOrderName("棒棒糖");

        OrderDetails order2 = new OrderDetails();
        order2.setOrderId(666666L);
        order2.setOrderAmount(new BigDecimal("6.3"));
        order2.setOrderName("雪糕");

        List<OrderDetails> orderDetails = Arrays.asList(order1, order2);

        // 生成ExcelSheet对象
        ExcelSheet excelSheet = orderExcelSheet.generateSheet(orderDetails, "orders");

        // 生成Xlsx格式的Excel文件
        ByteArrayInputStream byteArrayInputStream = normalExportXlsxExcel.exportExcel(excelSheet);
        
        byte[] bytes = generateByte(byteArrayInputStream);
        FileOutputStream fileOutputStream = new FileOutputStream(new File("D:\\order.xlsx"));
        fileOutputStream.write(bytes);
    }

    private static byte[] generateByte(InputStream is) throws IOException {
        ByteArrayOutputStream output = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int n = 0;

        while (-1 != (n = is.read(buffer))) {
            output.write(buffer, 0, n);
        }
        return output.toByteArray();
}

生成的效果：

测试2：将多个文件合并为一个文件

注意：标题行必须一致，否则取不到这列的值

文件order_other：

文件order_other_2：

@Test
public void test2() throws IOException {
    byte[] bytes = readFromByteFile("D:\\order_other.xlsx");
    byte[] bytes2 = readFromByteFile("D:\\order_other_2.xlsx");
    List<byte[]> list = Arrays.asList(bytes, bytes2);

    // 将文件转换为ExcelSheet对象
    ExcelSheet orderOtherSheet = normalExportXlsxExcel.mergeFirstSheetExcel(list);

    // 转换为自己所需要的ExcelSheet对象
    ExcelSheet excelSheet = orderExcelSheet.convertExcelSheet(orderOtherSheet, "orders2");

    // 生成Xlsx格式的Excel文件
    ByteArrayInputStream byteArrayInputStream = normalExportXlsxExcel.exportExcel(excelSheet);

    byte[] bytesOut = generateByte(byteArrayInputStream);
    FileOutputStream fileOutputStream = new FileOutputStream(new File("D:\\order2.xlsx"));
    fileOutputStream.write(bytesOut);

}


public byte[] readFromByteFile(String pathname) throws IOException {
    File filename = new File(pathname);
    BufferedInputStream in = new BufferedInputStream(new FileInputStream(filename));
    ByteArrayOutputStream out = new ByteArrayOutputStream(1024);
    byte[] temp = new byte[1024];
    int size = 0;
    while ((size = in.read(temp)) != -1) {
        out.write(temp, 0, size);
    }
    in.close();
    byte[] content = out.toByteArray();
    return content;
}


private static byte[] generateByte(InputStream is) throws IOException {
    ByteArrayOutputStream output = new ByteArrayOutputStream();
    byte[] buffer = new byte[1024];
    int n = 0;

    while (-1 != (n = is.read(buffer))) {
        output.write(buffer, 0, n);
    }
    return output.toByteArray();
}

生成的效果：