poi拆分Excel单元格并填充内容

合并之前文件

//合并并上传fastdfs
public void uploadExcelDataSource(MultipartFile file) throws IOException {
    List<String> list = Arrays.asList("xls,xlsx");
    if (list.contains(FilenameUtils.getExtension(file.getOriginalFilename()))) {
        InputStream in = file.getInputStream();
        byte[] bytes = ExcelUtil.handleExcel(in);
        fdfsService.uploadFile(file.getOriginalFilename(), bytes, file.getSize());
    }
    
}



import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author thq
 * @description 处理工具类
 * @create 2023-03-01 17:05
 */
@Slf4j
public class ExcelUtil {

    /**
     * 处理合并的单元格
     * @date: 2023/3/1 17:09
     * @param: inputStream
     * @return: java.util.Map<java.lang.Integer,java.util.List<java.lang.Object>>
     **/
    public static byte[] handleExcel(InputStream inputStream) throws IOException {
        ByteArrayOutputStream byteArrayOutputStream = null;
        OutputStream os = null;
        try{
            //创建工作簿
            Workbook workbook = WorkbookFactory.create(inputStream);
            //获取sheet的个数
            int sheetNum = workbook.getNumberOfSheets();
            //遍历每一个sheet
            for(int i = 0;i<sheetNum;i++){
                Sheet sheet = workbook.getSheetAt(i);
                splitCell(sheet);
            }
            //字节数组输出流
            byteArrayOutputStream = new ByteArrayOutputStream();
            //输出缓冲流
            os = new BufferedOutputStream(byteArrayOutputStream);
            //将Excel写出到输出流中
            workbook.write(os);
            //刷新缓冲区
            os.flush();
            //获取输出流的数据
            byte[] bytes = byteArrayOutputStream.toByteArray();
            return bytes;
        }catch (Exception e){
            log.error("excel文件解析失败",e.getMessage());
        }finally {
           if(byteArrayOutputStream!=null){
               byteArrayOutputStream.close();
           }
           if (os != null){
               os.close();
           }
            if (os != null){
                os.close();
            }
        }
        return null;
    }

    /**
     * 拆分单元格并填充数据
     * @date: 2023/3/2 10:58
     * @param: sheet 
     **/
    public static void splitCell(Sheet sheet)  {
        //获取合并单元格的数量
        int numMergedRegions = sheet.getNumMergedRegions();
        for(int z = numMergedRegions-1;  z>=0; z--) {
            //获取合并单元格
            CellRangeAddress merge = sheet.getMergedRegion(z);
            sheet.removeMergedRegion(z);
            //获取合并单元格起始行、结束行、起始列、结束列
            int firstCol = merge.getFirstColumn();
            int lastCol = merge.getLastColumn();
            int firstRow = merge.getFirstRow();
            int lastRow = merge.getLastRow();
            Cell cell = null;
            int rowNum = 1;
            for (int k = firstRow; k <= lastRow; k++) {
                int repeatNum = 1;
                for (int l = firstCol; l <= lastCol; l++) {
                    int colNum = 0;
                    //拿到第一个值就是合并单元格的值
                    if (cell == null){
                        cell = sheet.getRow(k).getCell(l);
                    }else {
                        Cell cell1 = sheet.getRow(k).getCell(l);
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC:
                                //判断是否为日期类型
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                                    cell1.setCellValue(formater.format(date));
                                } else {
                                    DecimalFormat df = new DecimalFormat("####.####");
                                    cell1.setCellValue( df.format(cell.getNumericCellValue()));
                                }
                                break;
                            case Cell.CELL_TYPE_STRING:
                                if(firstCol == lastCol){
                                    cell1.setCellValue(cell.getStringCellValue()+rowNum);
                                    rowNum++;
                                }else if(firstRow == lastRow){
                                    cell1.setCellValue(cell.getStringCellValue()+repeatNum);
                                }else{
                                    cell1.setCellValue(cell.getStringCellValue()+(rowNum+colNum));
                                    rowNum++;
                                    colNum++;
                                }
                                repeatNum ++;
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                cell1.setCellValue(cell.getBooleanCellValue() + "");
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                cell1.setCellValue("");
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                cell1.setCellValue("");
                                break;
                            default:
                                break;
                        }
                    }
                }
            }
        }
    }

}

合并处理之后的文件