This article demonstrates how to freeze or unfreeze rows and columns in Excel using Spire.XLS for Java.

Freeze top row

//Create a Workbook instance
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);

//Freeze top row
sheet.freezePanes(2,1);

//Save to file
workbook.saveToFile("FreezeTopRow.xlsx", ExcelVersion.Version2016);

Freeze fisrt column

//Create a Workbook instance
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);

//Freeze frist column
sheet.freezePanes(1,2);

//Save to file
workbook.saveToFile("FreezeFirstColumn.xlsx", ExcelVersion.Version2016);

Freeze few rows and columns

//Create a Workbook instance
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);

//Freeze the second row and the second column
sheet.freezePanes(3,3);

//Save to file
workbook.saveToFile("FreezeFewRowsAndColumns.xlsx", ExcelVersion.Version2016);

Unfreeze panes

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

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

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

//Unfreeze panes
sheet.removePanes();

//Save to file
workbook.saveToFile("UnfreezePanes.xlsx", ExcelVersion.Version2016);

Spire.XLS for Java enables developers to add and manipulate multiple types of form controls, e.g. text box, option button, check box and combo box in Excel files in Java applications.

The following examples will show you how to add and remove text box, option button, check box and combo box form controls in an Excel file using Spire.XLS for Java.

Add Form Controls

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

public class AddFormControls {
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        sheet.getCellRange("A2").setText("Name: ");
        //Add a text box
        ITextBoxShape textbox = sheet.getTextBoxes().addTextBox(2, 2, 18, 65);
        textbox.setText("Shaun");
        textbox.getFill().setForeColor(Color.PINK);
        textbox.setHAlignment(CommentHAlignType.Center);
        textbox.setVAlignment(CommentVAlignType.Center);

        sheet.getCellRange("A4").setText("Gender: ");
        //Add an option button
        IRadioButton radiobutton1 = sheet.getRadioButtons().add(4, 2, 18, 65);
        radiobutton1.setText("Male");
        //Add an option button
        IRadioButton radiobutton2 = sheet.getRadioButtons().add(4, 4, 18, 65);
        radiobutton2.setText("Female");

        sheet.getCellRange("A6").setText("Hobby: ");
        //Add a check box
        ICheckBox checkbox1 = sheet.getCheckBoxes().addCheckBox(6, 2, 18, 100);
        checkbox1.setCheckState(CheckState.Checked);
        checkbox1.setText("Photography");
        //Add a check box
        ICheckBox checkbox2 = sheet.getCheckBoxes().addCheckBox(6, 4, 18, 65);
        checkbox2.setCheckState(CheckState.Checked);
        checkbox2.setText("Travel");

        sheet.getCellRange("A8").setText("Profession: ");
        sheet.getCellRange("A20").setText("Student");
        sheet.getCellRange("A21").setText("Teacher");
        sheet.getCellRange("A22").setText("Doctor");
        //Add a combo box
        IComboBoxShape combobox = sheet.getComboBoxes().addComboBox(8, 2, 18, 65);
        combobox.setListFillRange(sheet.getCellRange("A20:A22"));
        combobox.setSelectedIndex(1);

        for (int column = 1; column < 5; column ++)
        {
            sheet.setColumnWidth(column, 15f);
        }

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

Remove Form Controls

import com.spire.xls.*;

public class RemoveFormControls {
    public static void main(String[] args){
        //Load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("AddControls.xlsx");
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Remove option buttons from the worksheet
        for(int j = 0; j < sheet.getRadioButtons().getCount(); j ++){
            sheet.getRadioButtons().get(j).remove();
        }
        
        //Remove check boxes from the worksheet
        for(int i = 0; i < sheet.getCheckBoxes().getCount(); i ++){
            sheet.getCheckBoxes().get(i).remove();
        }

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

When working with a large workbook containing dozens of columns and rows, it may often be necessary to use the "Find" function to quickly locate specific values. This article will demonstrate how to programmatically find all cells with a specific value and highlight them with a background color 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>15.12.15</version>
    </dependency>
</dependencies>

Find and Highlight Data in Excel

The detailed steps are as follows.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Find all cells with matching text using Worksheet.findAllString(java.lang.String stringValue, boolean formula, boolean formulaValue) method.
• Set color to highlight the cells using CellRange.getCellStyle().setColor() method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;
import java.awt.*;

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

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

        //Load the sample document
        workbook.loadFromFile("Test.xlsx");

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

        //Find all cells with the text "Regulator System"
        CellRange[] ranges = worksheet.findAllString("Regulator System", true, true);
        for (CellRange range : ranges)
        {

            //Set color to highlight the cells
            range.getCellStyle().setColor(Color.yellow);
        }

        //Save the result file
        workbook.saveToFile("FindandHighlight.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.

Copying cell ranges is a fundamental and highly beneficial function in spreadsheet management, which empowers users to effortlessly duplicate a range of cells, including data, formatting, and formulas, to a specified position. With it, users can efficiently copy identical content throughout their spreadsheets, while mitigating the potential for input errors. Importantly, the relative relationships between cells are preserved when copying cell ranges, ensuring the consistency of the copied data with the original. As a result, this feature holds immense value for tasks such as file backups and template creation, making it an indispensable tool in spreadsheet. This article will demonstrate how to copy a cell range within a worksheet or between two worksheets in a single Excel file by using Spire.XLS for Java.

• Copy a Specific Cell Range within a Worksheet
• Copy a Specific Cell Range between Worksheets

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>15.12.15</version>
    </dependency>
</dependencies>

Copy a Specific Cell Range within a Worksheet

Spire.XLS for Java provides Worksheet.copy() method, which supports copying a specific cell range in the same worksheet. The following are detailed steps.

• Create a new Workbook object.
• Load an Excel file from disk using Workbook.loadFromFile() method.
• Get the first worksheet of this file by using Workbook.getWorksheets().get() method.
• Get the source range and target range of the first sheet using Worksheet.getCellRange() method.
• Copy the specific cell range within a worksheet by calling Worksheet.copy() method.
• Finally, specify the output path and save the result file using Workbook.saveToFile() method.

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

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

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

        //Load a sample Excel file from disk
        wb.loadFromFile("sample.xlsx", ExcelVersion.Version2013);

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

        //Get the source range and target range
        CellRange sourceRange = sheet.getCellRange("A1:E1");
        CellRange destRange = sheet.getCellRange("A10:E10");

        //Copy a specific cell range within a worksheet
        sheet.copy (sourceRange,destRange,true);

        //Save the result file
        wb.saveToFile("CopyRangeWithinSheet.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

Copy a Specific Cell Range between Worksheets

Spire.XLS for Java library also allows you to copy cell range from one sheet to another sheet effortlessly. The following are the steps to duplicate cell range between different worksheets.

• Create a new Workbook object.
• Load an Excel file from disk using Workbook.loadFromFile() method.
• Get the first and second worksheets of this file by using Workbook.getWorksheets().get() method.
• Get the source range and target range using Worksheet.getCellRange() method.
• Copy the specific cell range from sheet1 to sheet2 by calling Worksheet.copy() method.
• Auto fit the column width in sheet2 by using Worksheet.autoFitColumn() method
• Finally, specify the output path and save the result file using Workbook.saveToFile() method.

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

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

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

        //Load a sample Excel file from disk
        wb.loadFromFile("sample.xlsx", ExcelVersion.Version2013);

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

        //Get the second worksheet 
        Worksheet sheet2 = wb.getWorksheets().get(1);

        //Get the source range and target range
        CellRange sourceRange = sheet1.getCellRange("A1:E1");
        CellRange destRange = sheet2.getCellRange("A1:E1");

        //Copy a specific cell range from sheet1 to sheet2
        sheet1.copy (sourceRange,destRange,true);

        //Auto fit column width in sheet 2
        for (int i = 0; i < 8; i++) {
            sheet2.autoFitColumn(i+1);
        }

        //Save the result file
        wb.saveToFile("CopyRangeBetweenSheets.xlsx", ExcelVersion.Version2013);
        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.