The Freeze Panes feature in Excel allows users to lock specific rows and columns while scrolling, ensuring that critical information remains visible regardless of the dataset's size. However, there are instances where unfreezing panes becomes necessary. Unfreezing rows and columns grants users the freedom to navigate large datasets seamlessly, facilitating comprehensive data analysis, editing, and formatting. the contents of frozen panes are often important information, and being able to obtain the range of frozen panes can facilitate easier access to this content. This article demonstrates how to use Spire.XLS for Java to unfreeze panes and obtain frozen rows and columns in Excel worksheets with Java code.

• Unfreeze Panes in Excel Worksheets with Java
• Obtain Frozen Rows and Columns in Excel Worksheets with Java

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's 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>14.4.1</version>
    </dependency>
</dependencies>
    

Unfreeze Panes in Excel Worksheets with Java

With Spire.XLS for Java, developers get a worksheet using Workbook.getWorksheets().get() method and unfreeze the panes using Worksheet.RemovePanes() method. The detailed steps for unfreezing panes in an Excel worksheet are as follows:

• Create an object of Workbook class.
• Load an Excel workbook using Workbook.loadFromFile() method.
• Get a worksheet from the workbook using Workbook.getWorksheets().get() method.
• Unfreeze panes in the worksheet using Worksheet.removePanes() method.
• Save the workbook using Workbook.saveToFile() method.

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

public class UnfreezePanes {
    public static void main(String[] args) {
        // Create an object of Workbook class
        Workbook wb = new Workbook();

        // Load an Excel workbook
        wb.loadFromFile("Sample.xlsx");

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

        // Unfreeze the panes
        sheet.removePanes();

        // Save the workbook
        wb.saveToFile("output/UnfreezePanes.xlsx");
        wb.dispose();
    }
}

Obtain Frozen Rows and Columns in Excel Worksheets with Java

Spire.XLS for Java provides the Worksheet.getFreezePanes() method to get the row and column indexes of the frozen panes, which allows developers to conveniently extract, remove, or format the content of the frozen panes. The parameters obtained are in the format of an int list: [int rowIndex, int columnIndex]. For example, [1, 0] indicates that the first row is frozen.

The detailed steps for obtaining the row and column parameters of the frozen panes are as follows:

• Create an object of Workbook class.
• Load an Excel workbook using Workbook.loadFromFile() method.
• Get the first worksheet using Workbook.getWorksheets().get() method.
• Get the indexes of the frozen rows and columns using Worksheet.getFreezePanes() method.
• Output the result.

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

public class GetFrozenCellRange {
    public static void main(String[] args) {
        // Create an object of Document clas
        Workbook wb = new Workbook();

        // Load an Excel file
        wb.loadFromFile("Sample.xlsx");

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

        // Get the indexes of the frozen rows and columns
        int[] index = ws.getFreezePanes();

        // Output the result
        System.out.println("Frozen Rows: " + index[0] + "\r\nFrozen Columns: " + index[1]);
        wb.dispose();
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

When you are dealing with the data in a worksheet, you may need to rearrange the columns so as to make it easier to find and read the specific data. It is easy to move columns in MS Excel by using Shift and Drag. This article introduces how to programmatically reorder columns in Excel using Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's 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>14.4.1</version>
    </dependency>
</dependencies>
    

Rearrange Columns in Excel in Java

The following are the steps to reorder columns in Excel using Spire.XLS for Java.

• Create a Workbook object, and load a sample Excel file using Workbook.loadFromFile() method.
• Get the target worksheet where you’d like to adjust the order using Workbook.getWorksheets().get() method.
• Specify the new column order in an int array.
• Create a temporary sheet and copy the data from the target sheet into it.
• Copy the columns from the temporary sheet to the target sheet and store them in the new order.
• Remove the temporary sheet.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

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:\\Users\\Jack\\Desktop\\sample.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[]{3, 0, 1, 2, 4, 5};

        //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("output/MoveColumn.xlsx", FileFormat.Version2016);
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Worksheets with a lot of complex and detailed information are difficult to read and analyze. To create a more compact and understandable spreadsheet view, you can organize data in groups and collapse the rows with similar content.

This article demonstrates how to programmatically group or ungroup rows and columns in Excel using Spire.XLS for Java.

• Group Rows and Columns in Java
• Ungroup Rows and Columns in Java

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's 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>14.4.1</version>
    </dependency>
</dependencies>
    

Group Rows and Columns in Java

The following are the steps to group rows and columns using Spire.XLS for Java.

• Create a Workbook object.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get the specific sheet using Workbook.getWorksheets().get() method.
• Group rows using Worksheet.groupByRows() method.
• Group columns using Worksheet.groupByColumns() method.
• Save the result to another Excel file using Workbook.saveToFile() method.

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

public class GroupRowsAndColumns {

    public static void main(String[] args) {

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

        //Load a sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

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

        //Group rows
        sheet.groupByRows(2, 5, false);
        sheet.groupByRows(7, 10, false);

        //Group columns
        sheet.groupByColumns(5, 6, false);

        //Save to another Excel file
        workbook.saveToFile("GroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
    }
}

Ungroup Rows and Columns in Java

The following are the steps to ungroup rows and columns using Spire.XLS for Java.

• Create a Workbook object.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get the specific sheet using Workbook.getWorksheets().get() method.
• Ungroup rows using Worksheet.ungroupByRows() method.
• Ungroup columns using Worksheet.ungroupByColumns() method.
• Save the result to another Excel file using Workbook.saveToFile() method.

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

public class UngroupRowsAndColumns {

    public static void main(String[] args) {

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

        //Load a sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\ample.xlsx");

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

        //Ungroup rows
        sheet.ungroupByRows(2, 5);
        sheet.ungroupByRows(7, 10);

        //Ungroup columns
        sheet.ungroupByColumns(5, 6);

        //Save to a different Excel file
        workbook.saveToFile("UngroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

In Microsoft Excel, suppose a workbook contains lots of worksheets and you need to count them, you can use the Sheets Function, the Define Name Command or a simple VBA code to achieve it. Within this tutorial, I’ll show you how to programmatically count the number of worksheets in Excel using Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's 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>14.4.1</version>
    </dependency>
</dependencies>
    

Count the Number of Worksheets in Excel

Spire.XLS for Java supports counting the number of worksheets in Excel using the getCount() method provided by the IWorksheets interface. The following are detailed steps.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a collection of worksheets using Workbook.getWorksheets() method and obtain the number of worksheets in the collection using the getCount() method.

import com.spire.xls.Workbook;

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

        //Load a sample Excel file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //Get the number of worksheets
        int sheetCount=workbook.getWorksheets().getCount();

        //Output the result
        System.out.println("The number of sheets is "+sheetCount);
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Spire.XLS for Java provides the getStyle() method and setStyle() method under the IXLSRange interface to get or set the style of a specific cell range. To copy formatting from one cell to another, get the style first and then apply it to another cell.

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

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

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

        //Load the sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

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

        //Get the number of rows used
        int rowCount = sheet.getRows().length;
        
        //Loop through the rows
        for (int i = 1; i < rowCount + 1; i++)
        {
            //Copy the formatting from a certain cell to another
            sheet.getRange().get(String.format("C%d",i)).setStyle(sheet.getRange().get(String.format("A%d",i)).getStyle());
        }

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

Copying worksheet involves duplicating an existing worksheet within the same workbook or across different workbooks. This valuable feature enables developers to create an exact replica of the original worksheet effortlessly, including its structure, formatting, data, formulas, charts, and other objects without any mistake. It proves especially beneficial when dealing with extensive data files, as it significantly reduces time and effort required for backing up files and creating templates. In this article, we will introduce how to copy worksheets in Excel using Spire.XLS for Java. With this method, all the cell formats in the original Excel worksheets will be completely remained.

• Copy Worksheets between Workbooks
• Copy Worksheets within Workbooks

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's 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>14.4.1</version>
    </dependency>
</dependencies>
    

Copy Worksheets between Workbooks

Spire.XLS for Java library allows you copy worksheets from one workbook to another file easily by using Worksheet.copyFrom() method. The following are detailed steps.

• Create a new Workbook object.
• Load the source Excel file from disk using Workbook.loadFromFile() method.
• Get the first worksheet of the source file by using Workbook.getWorksheets().get() method.
• Create an another Workbook object.
• Load the target file from disk using Workbook.loadFromFile() method.
• Add a new sheet to the target file using Workbook.getWorksheets().add() method.
• Copy the first worksheet of the source file to the new added sheet of the target file through Worksheet.copyFrom() method.
• Finally, specify the output path and save the target file using Workbook.saveToFile() method.

import com.spire.xls.*;

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

        //Create a Workbook
        Workbook sourceWorkbook = new Workbook();

        //Load the source Excel file from disk
        sourceWorkbook.loadFromFile("sample1.xlsx");

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

        //Create a another Workbook
        Workbook targetWorkbook = new Workbook();

        //Load the target Excel file from disk
        targetWorkbook.loadFromFile("sample2.xlsx");

        //Add a new worksheet
        Worksheet targetWorksheet = targetWorkbook.getWorksheets().add("added");

        //Copy the first worksheet of sample1 to the new added sheet of sample2
        targetWorksheet.copyFrom(srcWorksheet);

        //String for output file
        String outputFile = "output/CopyWorksheet.xlsx";

        //Save the result file
        targetWorkbook.saveToFile(outputFile, ExcelVersion.Version2013);
        sourceWorkbook.dispose();
        targetWorkbook.dispose();
    }
}

Copy Worksheets within Workbooks

You can also copy a worksheet within the same workbook by adding a new worksheet to this workbook and then copying the desired sheet to the new one. The following are the steps to duplicate worksheets within an Excel workbook.

• Create a new Workbook object.
• Load the source Excel file from disk using Workbook.loadFromFile() method.
• Get the first worksheet by using Workbook.getWorksheets().get() method and add a new sheet called "MySheet" using Workbook.getWorksheets().add() method.
• Copy the first worksheet to the second one through Worksheet.copyFrom() method;
• Finally, specify the output path and save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

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

        //Load the sample file from disk
        workbook.loadFromFile("sample1.xlsx");

        //Get the first sheet and add a new worksheet to this file
        Worksheet sheet = workbook.getWorksheets().get(0);
        Worksheet sheet1 = workbook.getWorksheets().add("MySheet");

        //Copy the first worksheet to the second one
        sheet1.copyFrom(sheet);

        //String for output file
        String result = "output/CopySheetWithinWorkbook.xlsx";

        //Save to file
        workbook.saveToFile(result, ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

This article shows you how to duplicate a worksheet inside a workbook using Spire.XLS for Java.

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

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

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

        //Load the sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

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

        //Add a new worksheet
        Worksheet newSheet = workbook.getWorksheets().add(originalSheet.getName()+" - Copy");

        //Copy the worksheet to new sheet
        newSheet.copyFrom(originalSheet);

        //Save to file
        workbook.saveToFile("DuplicateSheet.xlsx");
    }
}

This article demonstrates how to split a worksheet into several Excel documents by using Spire.XLS for Java.

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

public class SplitWorksheet {

    public static void main(String[] args) {

        //Create a Workbook object to load the original Excel document
        Workbook bookOriginal = new Workbook();
        bookOriginal.loadFromFile("C:\\Users\\Administrator\\Desktop\\Emplyees.xlsx");

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

        //Get the header row
        CellRange headerRow = sheet.getCellRange(1, 1, 1, 5);

        //Get two cell ranges
        CellRange range1 = sheet.getCellRange(2, 1, 6, 5);
        CellRange range2 = sheet.getCellRange(7, 1, 11, 5);

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

        //Copy the header row and range 1 to the new workbook
        sheet.copy(headerRow, newBook1.getWorksheets().get(0), 1, 1, true, false);
        sheet.copy(range1, newBook1.getWorksheets().get(0), 2, 1, true, false);

        //Copy the column width from the original workbook to the new workbook
        for (int i = 0; i < sheet.getLastColumn(); i++) {

            newBook1.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
        }

        //Save the new workbook to an Excel file
        newBook1.saveToFile("Sales.xlsx", ExcelVersion.Version2016);

        //Copy the header row and range 2 to another workbook, and save it to another Excel file
        Workbook newBook2 = new Workbook();
        sheet.copy(headerRow, newBook2.getWorksheets().get(0), 1, 1, true, false);
        sheet.copy(range2, newBook2.getWorksheets().get(0), 2, 1, true, false);
        for (int i = 0; i < sheet.getLastColumn(); i++) {

            newBook2.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
        }
        newBook2.saveToFile("Technicians.xlsx", ExcelVersion.Version2016);
    }
}

This tutorial will demonstrate how to set font and background color for Excel textbox using Spire.XLS for Java.

The following is a screenshot of a sample document:

Using the code

import java.awt.*;
import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.shapes.*;

public class SetFontAndBackground {
    public static void main(String[] args) {
        //Load an Excel sample
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

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

        //Get the textbox which will be edited
        XlsTextBoxShape shape = (XlsTextBoxShape) sheet.getTextBoxes().get(0);

        //Set the font and background color for the textbox
        //Set font
        ExcelFont font = workbook.createFont();
        font.setFontName("Calibri");
        font.setSize(14);
        font.isBold(false);
        font.setColor(Color.MAGENTA);
        (new RichText(shape.getRichText())).setFont(0, shape.getText().length() - 1, font);

        //Set background color
        shape.getFill().setFillType(ShapeFillType.SolidColor);
        shape.getFill().setForeKnownColor(ExcelColors.Yellow);

        //Save to file
        workbook.saveToFile("output/setFontAndBackgroundForTextBox.xlsx", ExcelVersion.Version2013);
    }
}

Output

This article demonstrates how to get names of worksheets in a workbook using Spire.XLS for Java.

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

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

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

        //Load a sample Excel file
        wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Loop through the worksheets 
        for (Object sheet: wb.getWorksheets()
             ) {

            //Get worksheet name
            String sheetName = ((Worksheet) sheet).getName();
            System.out.println(sheetName);
        }
    }
}