poi------合并单元格

1. 添加依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
2.
``` 
  @PostMapping(value = "/mergeCell",produces = {"application/json;charset=utf-8"})
    @ResponseBody
    public HashMap<String,String>  mergeCell(@RequestParam("file") MultipartFile file){
        String fileName = file.getOriginalFilename();
        System.out.println(fileName);
        String filePath = "D:\\WorkPlace";
        File dir = new File(filePath);
        if(!dir.exists()){
            dir.mkdir();
        }
        String originFilePath = filePath+File.separator+fileName;
        String newFilePath = filePath+File.separator+"合并后"+fileName;
        System.out.println(filePath);
        try {
            MergeCellUtil.mergeCellAndSave(originFilePath,newFilePath);
            return new HashMap<String, String>(){{put("单元格合并", "合并成功");}};
        } catch (Exception e) {
            return new HashMap<String,String>(){{put("单元格合并","合并失败");}};
        }
    }
```

3. 


/**
 * 检测 多次给付重疾险智核问卷0810.xlsx
 */
@Slf4j
public class MergeCellUtil {
    /**
     * 列方向合并相同数据的单元格. 空白单元格不合并. 将文件保存到硬盘
     *
     * @param originFilePath xlsx 原文件
     * @param newFilePath    合并后生成的 xlsx 文件保存路径
     */
    public static void mergeCellAndSave(String originFilePath, String newFilePath) {

        FileInputStream in = null;
        FileOutputStream outputStream = null;
        try {
            //Workbook newBook = MergeCellUtil.getMemoryWorkbook(originFilePath);
            //将文件读入
            in = new FileInputStream(new File(originFilePath));
            //追加写入----避免相同文件合并后生成相同文件名无法再写入
            outputStream = new FileOutputStream(newFilePath, true);
            //创建工作薄
            Workbook newBook = new XSSFWorkbook(in);
            MergeCellUtil.mergeCell(newBook);
            newBook.write(outputStream);
        } catch (Exception e) {
            throw new RuntimeException();
        } finally {
            try {
                outputStream.flush();
                outputStream.close();
                in.close();
            } catch (IOException e) {
                throw new RuntimeException();
            }
        }
    }

    /**
     * 将硬盘中的excel sheet0 的数据复制到纯内存excel中.
     * 原因: 从硬盘读入的excel操作之后, 没有写出成功
     *
     * @param originFilePath xlsx 文件路径
     * @return 内存中生成的workbook
     */
    private static Workbook getMemoryWorkbook(String originFilePath) throws Exception {
//        String originFilePath = "D:\\operate-data\\多次给付重疾险智核问卷0809.xlsx";

        Workbook originBook = new XSSFWorkbook(originFilePath);
        originBook.close();
        Sheet originSheet = originBook.getSheetAt(0);

        int lastRowIndex = originSheet.getLastRowNum();
        short columnSize = originSheet.getRow(0).getLastCellNum();

        Workbook newBook = new XSSFWorkbook();
        Sheet newSheet = newBook.createSheet();

        for (int i = 0; i <= lastRowIndex; i++) {
            Row originRowI = originSheet.getRow(i);
            Row newRowI = newSheet.createRow(i);
            for (int j = 0; j < columnSize; j++) {
                Cell newCell = newRowI.createCell(j);

                Cell originCell = originRowI.getCell(j);
                if (originCell != null) {
                    CellType originCellType = originCell.getCellType();
                    if (originCellType == CellType.STRING) {
                        newCell.setCellValue(originCell.getStringCellValue());
                    } else if (originCellType == CellType.NUMERIC) {
                        newCell.setCellValue(originCell.getNumericCellValue());
                    }
                }

            }
        }

        return newBook;
    }

    /**
     * {@code sheet} 添加合并单元格的区域. 合并方式是按列方向合并
     *
     * @param sheet    需要添加合并区域的 sheet 页
     * @param rowStart 合并区域起始行 index
     * @param rowEnd   合并区域终止行 index
     * @param column   合并区域列
     */
    public static void addMergeAddress(Sheet sheet, int rowStart, int rowEnd, int column) {
        if (rowStart < rowEnd) {
            CellRangeAddress mergeAddress = new CellRangeAddress(rowStart, rowEnd, column, column);
            sheet.addMergedRegion(mergeAddress);
        }
    }

    /**
     * 获取sheet {@code rowIndex} 行, {@code columnIndex} 列的 String 值
     *
     * @param newSheet    sheet 页
     * @param rowIndex    行索引
     * @param columnIndex 列索引
     * @return 指定单元格的值, 如果单元格为 blank, return null
     */
    public static String getCellStringValue(Sheet newSheet, int rowIndex, int columnIndex) {
        Cell cell = newSheet.getRow(rowIndex).getCell(columnIndex);
        CellType cellType = cell.getCellType();
        if (cellType == CellType.BLANK) {
            return null;
        }
        return cell.getStringCellValue();
    }

    /**
     * 合并excel 单元格.
     * 合并方式: 列方向合并相同数据的单元格, 空白单元格不合并.
     *
     * @param newBook 要合并的excel
     */
    private static void mergeCell(Workbook newBook) {
        Sheet newSheet = newBook.getSheetAt(0);

        //合并区域最大行索引
        int mergeMaxRowIndex = newSheet.getLastRowNum();
        //合并区域最大列索引
        int mergeMaxColumnIndex = newSheet.getRow(0).getLastCellNum() - 1;

        //记录单元格的值
        String value = null;
        //合并区域起始行索引
        int rowStart;

        //开始合并
        for (int j = 0; j <= mergeMaxColumnIndex; j++) {
            //找出某列, 第一个不空的单元格的行索引, 并将单元格的值赋给 value
            int i0;
            for (i0 = 1; i0 < mergeMaxRowIndex; i0++) {
                value = MergeCellUtil.getCellStringValue(newSheet, i0, j);
                if (StringUtils.isNotBlank(value)) {
                    break;
                }
            }

            if (i0 == mergeMaxRowIndex + 1) {
                //整列为空, 不需要合并操作

                continue;
            }

            rowStart = i0;
            for (int i = i0 + 1; i <= mergeMaxRowIndex; i++) {

                //循环到当前的单元格的值
                String cellValue = MergeCellUtil.getCellStringValue(newSheet, i, j);

                if (StringUtils.isBlank(cellValue)) {
                    if (StringUtils.isNotBlank(value)) {
                        //当前单元格为一组连续空白区域的第一个空白格

                        MergeCellUtil.addMergeAddress(newSheet, rowStart, i - 1, j);

                        value = null;
                        rowStart = i;
                    }
                } else {
                    if (StringUtils.isBlank(value)) {
                        //当前单元格为空白区域下第一个不为空的单元格

                        value = cellValue;
                        rowStart = i;
                    } else if (!value.equals(cellValue)) {
                        //当前单元格和记录的值不同

                        MergeCellUtil.addMergeAddress(newSheet, rowStart, i - 1, j);

                        rowStart = i;
                        value = cellValue;
                    }
                }

                if (i == mergeMaxRowIndex) {
                    //最后一行
                    MergeCellUtil.addMergeAddress(newSheet, rowStart, i, j);
                }
            }
        }

    }

}