[JAVA] How to cancel cell merging within a specified range with POI

environment

java

Apache Poi

Cancel cell merging within the specified range

Before execution base.PNG

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 result.PNG

After execution removeRagions.PNG

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();
    }

Recommended Posts

How to cancel cell merging within a specified range with POI
Uppercase only the specified range with substring. (How to use substring)
How to make a factory with a model with polymorphic association
How to delete a new_record object built with Rails
How to manually generate a JWT with Rails Knock
How to save a file with the specified extension under the directory specified in Java to the list
[How to insert a video in haml with Rails]
How to get started with creating a Rails app
[Java] How to start a new line with StringBuilder
How to take a screenshot with the Android Studio emulator
How to request a CSV file as JSON with jMeter
How to divide a two-dimensional array into four with ruby
How to use a foreign key with FactoryBot ~ Another solution
Learning Ruby with AtCoder 13 How to make a two-dimensional array
How to test a private method with RSpec for yourself
How to move another class with a button action of another class.
How to select a specified date by code in FSCalendar
Mapping to a class with a value object in How to MyBatis
How to set up a proxy with authentication in Feign
How to register as a customer with Square using Tomcat
How to leave a comment
How to number (number) with html.erb
How to update with activerecord-import
How to use Apache POI
How to insert a video
How to create a method
How to make a jar file with no dependencies in Maven
How to run a job with docker login in AWS batch
How to rename a model with foreign key constraints in Rails
How to open a script file from Ubuntu with VS code
How to build a little tricky with dynamic SQL query generation
How to scroll horizontally with ScrollView
How to get started with slim
How to make a Java container
How to enclose any character with "~"
How to sign a Minecraft MOD
How to make a JDBC driver
[Java] How to create a folder
How to use mssql-tools with alpine
How to write a ternary operator
[Swift] How to send a notification
How to make a splash screen
How to make a Jenkins plugin
How to make a Maven project
How to get along with Rails
How to make a Java array
How to start Camunda with Docker
How to interact with a server that does not crash the app
How to quickly create a reverse proxy that supports HTTPS with Docker
[Personal memo] How to interact with a random number generator in Java
[Rails] How to log in with a name by adding a devise name column
[Java] How to turn a two-dimensional array with an extended for statement
How to build a Ruby on Rails development environment with Docker (Rails 6.x)
How to limit IP address only to a specific directory with laravel + nginx
How to create a server executable JAR and WAR with Spring gradle
How to SSH into Ubuntu from a terminal with public key authentication
How to build a Ruby on Rails development environment with Docker (Rails 5.x)
How to make an app with a plugin mechanism [C # and Java]
How to start a Docker container with a volume mounted in a batch file