Wednesday, 27 May 2020 07:48

Convert Text to Columns in Excel in Java

Written by  support iceblue
Rate this item
(0 votes)

This article demonstrates how to convert text to columns in Excel using Spire.XLS for Java. The following screenshot shows the sample Excel file before converting:

Convert Text to Columns in Excel in Java

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ConvertTextToColumns {
    public static void main(String[] args){
        //Create a workbook instance
        Workbook workbook = new Workbook();

        //Load the Excel file
        workbook.loadFromFile("Template.xlsx");

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

        //Convert text into columns by the delimited characters of space
        String[] splitText = null;
        String text = null;
        for (int i = 1; i < sheet.getLastRow()+1; i++)
        {
            text = sheet.getRange().get(i, 1).getText();
            splitText = text.split(" ");
            for (int j = 0; j < splitText.length; j++)
            {
                sheet.getRange().get(i, 1 + j + 1).setText(splitText[j]);
            }
        }

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

The following screenshot shows the output Excel file after converting:

Convert Text to Columns in Excel in Java

Additional Info

  • tutorial_title:
More in this category: « Sort Excel Data in Java