How to cancel cell merging within a specified range with POI

environment

java

• java7

Apache Poi

• 3.14

Cancel cell merging within the specified range

Before execution

I couldn't find such a method in the Sheet class, so I made it myself. Code to unmerge cells in A2: C6

Sample.java

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

    public static void main(String[] args) { 
        try(Workbook book = WorkbookFactory.create(Sample.class.getResourceAsStream("sample.xlsx"));
                OutputStream out = new FileOutputStream("sample.xlsx");) {
            XSSFSheet sheet = (XSSFSheet) book.getSheetAt(0);
            
            CellRangeAddress targetRange = CellRangeAddress.valueOf("A2:C6");
            int removeCount = removeMergedRagions(targetRange, sheet);
            System.out.println(removeCount + "Unbonded");
            
            book.write(out);
        }catch(Exception ex) {
            //Do something
        }

    }
    
    /**
     *Cancels cell merging within the specified range<br>
     *If even a part is within the specified range, the combination outside the range is also canceled.
     * @param targetRange Range to break the join,not {@code null}
     * @param sheet The sheet to be released,not {@code null}
     * @return Number of unjoined
     * @see org.apache.poi.xssf.usermodel.XSSFSheet#If the removeMergedRegions version is xlsx only, you can use this
     */
    private static int removeMergedRegions(CellRangeAddress targetRange ,Sheet sheet) {
        List<CellRangeAddress> mergeList = sheet.getMergedRegions();
        List<Integer> removeIndices = new ArrayList<>();
        
        for(int mergeIndex = 0; mergeIndex < mergeList.size(); mergeIndex++) {
            CellRangeAddress mergedAddress = mergeList.get(mergeIndex);
            if(targetRange.intersects(mergedAddress)) 
                removeIndices.add(mergeIndex);
        }
        //If you release it from the front, the index will not match, so delete it from the back
        Collections.reverse(removeIndices);
        for(int removeIndex : removeIndices) 
            sheet.removeMergedRegion(removeIndex);
        
        return removeIndices.size();
    }

Execution log

After execution

Merged cells that were partly in "A2: C6" have been unmerged

Method implementation:

1. Get a list of join information from the sheet and assign it to margeList
2. Extract one by one from the margeList and check if it is within the targetRange range.
3. If it is within the range, put the index in removeIndices
4. Reverse the contents of removeIndices to avoid index mismatch if deleted from the front
5. Finally delete from behind

Click here for XSSF sheet only

Sample.java

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;

    public static void main(String[] args) { 
        try(Workbook book = WorkbookFactory.create(Sample.class.getResourceAsStream("sample.xlsx"));
                OutputStream out = new FileOutputStream("sample.xlsx");) {
            XSSFSheet sheet = (XSSFSheet) book.getSheetAt(0);
            
            CellRangeAddress targetRange = CellRangeAddress.valueOf("A2:C6");
            int removeCount = removeMargedRagions(targetRange, sheet);
            System.out.println(removeCount + "Unbonded");
            
            book.write(out);
        }catch(Exception ex) {
            //Do something
        }

    }
    

    /**
     *Cancels cell merging within the specified range<br>
     *If even a part is within the specified range, the combination outside the range is also canceled.
     * @param targetRange Range to break the join,not {@code null}
     * @param xssfSheet Sheet to be released,not {@code null}
     * @return Number of unjoined
     */
    private static int removeMergedRagions(CellRangeAddress targetRange ,XSSFSheet xssfSheet) {
        List<CellRangeAddress> mergeList = xssfSheet.getMergedRegions();
        List<Integer> removeIndices = new ArrayList<>();
        
        for(int mergeIndex = 0; mergeIndex < mergeList.size(); mergeIndex++) {
            CellRangeAddress mergedAddress = mergeList.get(mergeIndex);
            if(targetRange.intersects(mergedAddress)) 
                removeIndices.add(mergeIndex);
        }
        
        xssfSheet.removeMergedRegions(removeIndices);

        return removeIndices.size();
    }