Split Contents in One Cell into Multiple Columns in Java

When you receive an Excel document with contents in one cell, it is necessary to split the data into mutiple columns so that each column can be processed separately in the next step. This article will introduce how to use Free Spire.XLS for Java (a 3rd party free API) to split the text or numbers in one Excel cell into multiple columns by delimiters. The delimiter characters could be Space, Comma, Semicolon, etc.

Import Dependency (2 Method)

1# Download the free API and unzip it, then add the Spire.Xls.jar file to your project as dependency.

2# Directly add the jar dependency to maven project by adding the following configurations to the pom.xml.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>http://repo.e iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>5.1.0</version>
    </dependency>
</dependencies>

Sample Code

import com.spire.xls.*;

public class splitDataIntoMultipleColumns {
    public static void main(String[] args) {
        //Load the sample document from file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("source data.xlsx");

        //Get the first worksheet.
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Split data into separate columns by the delimiter characters of space.
        String[] splitText = null;
        String text = null;
        for (int i = 1; i < sheet.getLastRow(); i++)
        {
            text = sheet.getRange().get(i + 1, 1).getText();
            splitText = text.split(" ");
            for (int j = 0; j < splitText.length; j++)
            {
                sheet.getRange().get(i + 1, 1 + j + 1).setText(splitText[j]);
            }
        }
        //Save to file
        workbook.saveToFile("SplitCell.xlsx", ExcelVersion.Version2013);
    }
}