When manipulating Excel data, the "Text to Columns" feature in MS Excel is a handy tool that allows users to separate text in a single cell into multiple columns. This functionality is extremely useful when dealing with data that imported in a less structured format. For Java developers, being able to replicate this operation programmatically can significantly enhance the automation of data processing tasks involving Excel spreadsheets.

This guide will walk you through how to utilize the Spire.XLS for Java library to split text into multiple columns in Excel in Java.

• Java Library for Working with Excel
• Step-by-Step Guide to Splitting Excel Text to Columns
• Handling Different Delimiters

Java Library for Working with Excel

The Spire.XLS for Java library is a practical solution for reading, writing and converting Excel XLS or XLSX files in Java. To start using it, you need to add the appropriate dependency. There are two common ways to import:

Method 1: Install via Maven

If you are using Maven, add the following to your pom.xml file:

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

Method 2: Manual Installation

• Download Spire.XLS for Java package through the official website.
• Unzip it to get the Spire.Xls.jar file and then add the JAR file to your project.

Step-by-Step Guide to Splitting Excel Text to Columns

• Load Excel File and Access a Worksheet

  Use the Workbook.loadFromFile() method to load the input Excel file, and then access the specific worksheet in it.

• Access Cells and Get Cell Data

  Iterate through each row in the sheet. Access a specified cell and then get its text through the CellRange.getText() method.

• Split Text in Excel Cells

  Use the String.split(String regex) method to split the cell text based on the specified delimiter (e.g., ",").

• Write the Split Text to Columns

  Iterate through each split data and then write it into different columns.

• Save the Modified Excel File

  Use the Workbook.saveToFile() method to save the modified Excel file.

Sample Java Code:

import com.spire.xls.*;

public class splitDataIntoMultipleColumns {
    public static void main(String[] args) {

        // Create a Workbook object
        Workbook workbook = new Workbook();

        // Load an Excel file
        workbook.loadFromFile("Data.xlsx");

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

        // Iterate through each row in the worksheet
        for (int i = 0; i < sheet.getLastRow(); i++)
        {
            // Get the text of the first cell in the current row
            String text = sheet.getRange().get(i + 1, 1).getText();

            // Split the text by comma
            String[] splitText = text.split(",");

            // Iterate through each split data
            for (int j = 0; j < splitText.length; j++)
            {
                // Write the split data to different columns
                sheet.getRange().get(i + 1, j + 3).setText(splitText[j]);
            }
        }

        // Autofit column widths
        sheet.getAllocatedRange().autoFitColumns();

        // Save the result file
        workbook.saveToFile("SplitExcelData.xlsx", ExcelVersion.Version2016);
    }
}

Result File:

Handling Different Delimiters

To split text by other delimiters such as spaces, semicolons, or tabs, modify the regex in the split() method. Examples:

• Spaces: String.split(" ")
• Semicolons: String.split(";")
• Tabs: String.split("\t")

Conclusion

Splitting text into columns in Excel using Java is effortless with Spire.XLS for Java API. By automating this task, you can enhance productivity and ensure data consistency. Whether you’re processing user inputs, logs, or reports, this approach adapts to various delimiters and use cases.

Get a Free License

To fully experience the capabilities of Spire.XLS for Java without any evaluation limitations, you can request a free 30-day trial license.

We have already introduced how to add animation effect to shape in PowerPoint, in this article, we will introduce how to add animation effect to paragraph in PowerPoint using Spire.Presentation for Java.

import com.spire.presentation.*;
import com.spire.presentation.drawing.FillFormatType;
import com.spire.presentation.drawing.animation.*;

import java.awt.*;
import java.awt.geom.Rectangle2D;

public class AddAnimationOnParagraph {
    public static void main(String[] args) throws Exception {
        //Create a Presentation instance
        Presentation ppt = new Presentation();

        //Get the first slide
        ISlide slide = ppt.getSlides().get(0);

        //Add a shape to the slide
        IAutoShape shape = slide.getShapes().appendShape(ShapeType.RECTANGLE, new Rectangle2D.Double(150, 150, 450, 100));
        shape.getFill().setFillType(FillFormatType.SOLID);
        shape.getFill().getSolidColor().setColor(Color.gray);
        shape.getShapeStyle().getLineColor().setColor(Color.white);
        shape.appendTextFrame("This demo shows how to apply animation on paragraph in PPT document.");

        //Add animation effect to the first paragraph in the shape
        AnimationEffect animation = shape.getSlide().getTimeline().getMainSequence().addEffect(shape, AnimationEffectType.FLOAT);
        animation.setStartEndParagraphs(0, 0);

        //Save the result document
        ppt.saveToFile("AddAnimationOnPara.pptx", FileFormat.PPTX_2013);
        ppt.dispose();
    }
}

Output:

This article demonstrates how to create pivot chart in an Excel file in Java using Spire.XLS for Java.

The input Excel file:

import com.spire.xls.*;
import com.spire.xls.core.IPivotTable;

public class CreatePivotChart {
    public static void main(String[] args) {
        //Load the Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //get the first pivot table in the worksheet
        IPivotTable pivotTable = sheet.getPivotTables().get(0);

        //Add a clustered column chart based on the pivot table data to the second worksheet
        Chart chart = workbook.getWorksheets().get(1).getCharts().add(ExcelChartType.ColumnClustered, pivotTable);
        //Set chart position
        chart.setTopRow(2);
        chart.setBottomRow(15);
        //Set chart title
        chart.setChartTitle("Total");

        //Save the result file
        workbook.saveToFile("CreatPivotChart.xlsx", ExcelVersion.Version2013);
    }
}

Output: