Change the Column Order in Excel in Java

In Excel, there may be times when you need to move a specific column to another location on the worksheet to present the data in a more organized way. Instead of manually cutting and pasting the data, this article will share how to rogrammatically change the column order in Excel using Free Spire.XLS for Java.

Import Dependency (2 Methods)

1# Download the free library 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>https://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

To change the column order, you need to first get the target worksheet using Workbook.getWorksheets().get() method, and then specify the new column order in an int array. Next, you need to create a temporary sheet and copy the columns from the temporary sheet to the target sheet and store them in the new order. The complete sample code are shown as below.

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

public class RearrangeColumns {

    public static void main(String[] args) {

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

        //Load an Excel file
        workbook.loadFromFile( "C:\\Files\\test.xlsx");

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

        //Set the new column order (the column index starts from 0)
        int[] newColumnOrder = new int[]{1, 0, 4, 2, 3};

        //Add a temporary worksheet
        Worksheet newSheet = workbook.getWorksheets().add("temp");

        //Copy data from the first worksheet to the temporary sheet
        newSheet.copyFrom(worksheet);

        //Loop through the newColumnOrder array
        for (int i = 0; i < newColumnOrder.length; i++) {

            //Copy the column from the temporary sheet to the first sheet
            newSheet.getColumns()[newColumnOrder[i]].copy(worksheet.getColumns()[i],true,true);

            //Set the width of a certain column the first sheet to that of the temporary sheet
            worksheet.getColumns()[i].setColumnWidth(newSheet.getColumns()[newColumnOrder[i]].getColumnWidth());
        }

        //Remove temporary sheet
        workbook.getWorksheets().remove(newSheet);

        //Save the workbook to another Excel file
        workbook.saveToFile("MoveColumn.xlsx", FileFormat.Version2016);
    }
}