Knowledgebase (2311)
Children categories
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);
Published in
Cells
Tagged under
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);
}
}
Published in
Objects
Tagged under
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.
- Java
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.
Published in
Data
Tagged under
