Java (481)
The data validation in Excel helps control what kind of data can or should be entered into a worksheet. In other words, any input entered into a particular cell must meet the criteria set for that cell. For example, you can create a validation rule that restricts a cell to accept only whole numbers. In this article, you will learn how to apply or remove data validation in Excel by 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>
Apply Data Validation to Excel Cells
The following are the main steps to add various types of data validation to cells.
- Create a Workbook object, and get the first worksheet using Workbook.getWorksheets().get() method.
- Get a specific cell range using Worksheet.getCellRange() method to add data validation.
- Set the data type allowed in the cell using CellRange.getDataValidation().setAllowType() method. You can choose the data type as Integer, Time, Date, TextLength, Decimal, etc.
- Set the comparison operator using CellRange.getDataValiation().setCompareOperator() method. The comparison operators include Between, NotBetween, Less, Greater, Equal, etc.
- Set one or two formulas for the data validation using CellRange.getDataValidation().setFormula1() and CellRange.getDataValidation().setFormula2() methods.
- Set the input prompt using CellRange.getDataValidation().setInputMessage() method.
- Save the workbook to an Excel file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class DataValidation {
public static void main(String[] args) {
//Create a Workbook object
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Insert text in cells
sheet.getCellRange("B2").setText("Number Validation:");
sheet.getCellRange("B4").setText("Date Validation:");
sheet.getCellRange("B6").setText("Text Length Validation:");
sheet.getCellRange("B8").setText("List Validation:");
sheet.getCellRange("B10").setText("Time Validation:");
//Add a number validation to C2
CellRange rangeNumber = sheet.getCellRange("C2");
rangeNumber.getDataValidation().setAllowType(CellDataType.Integer);
rangeNumber.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
rangeNumber.getDataValidation().setFormula1("1");
rangeNumber.getDataValidation().setFormula2("10");
rangeNumber.getDataValidation().setInputMessage("Enter a number between 1 and 10");
rangeNumber.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
//Add a date validation to C4
CellRange rangeDate = sheet.getCellRange("C4");
rangeDate.getDataValidation().setAllowType(CellDataType.Date);
rangeDate.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
rangeDate.getDataValidation().setFormula1("1/1/2010");
rangeDate.getDataValidation().setFormula2("12/31/2020");
rangeDate.getDataValidation().setInputMessage("Enter a date between 1/1/2010 and 12/31/2020");
rangeDate.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
//Add a text length validation to C6
CellRange rangeTextLength = sheet.getCellRange("C6");
rangeTextLength.getDataValidation().setAllowType(CellDataType.TextLength);
rangeTextLength.getDataValidation().setCompareOperator(ValidationComparisonOperator.LessOrEqual);
rangeTextLength.getDataValidation().setFormula1("5");
rangeTextLength.getDataValidation().setInputMessage("Enter text lesser than 5 characters");
rangeTextLength.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
//Apply a list validation to C8
CellRange rangeList = sheet.getCellRange("C8");
rangeList.getDataValidation().setValues(new String[]{ "United States", "Canada", "United Kingdom", "Germany" });
rangeList.getDataValidation().isSuppressDropDownArrow(false);
rangeList.getDataValidation().setInputMessage("Choose an item from the list");
rangeList.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
//Apply a time validation to C10
CellRange rangeTime= sheet.getCellRange("C10");
rangeTime.getDataValidation().setAllowType(CellDataType.Time);
rangeTime.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
rangeTime.getDataValidation().setFormula1("9:00");
rangeTime.getDataValidation().setFormula2("12:00");
rangeTime.getDataValidation().setInputMessage("Enter a time between 9:00 and 12:00");
rangeTime.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
//Auto fit width of column 2
sheet.autoFitColumn(2);
//Set the width of column 3
sheet.setColumnWidth(3, 20);
//Save to file
workbook.saveToFile("output/ApplyDataValidation.xlsx", ExcelVersion.Version2016);
}
}
Remove Data Validation from Selected Cell Ranges
The following are the steps to remove data validation from selected cell ranges.
- Create a Workbook object.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Get the first worksheet using Workbook.getWorksheets().get() method.
- Create an array of rectangles, which is used to locate the cell ranges where the validation will be removed.
- Remove the data validation from the selected cell ranges using Worksheet.getDVTable().remove() method.
- Save the workbook to another Excel file using Workbook.saveToFile() method.
- Java
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import java.awt.*;
public class RemoveDataValidation {
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\\ApplyDataValidation.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Create an array of rectangles, which is used to locate the ranges in worksheet
Rectangle[] rectangles = new Rectangle[]{
//One Rectangle(columnIndex, rowIndex) specifies a specific cell，the column or row index starts at 0
//To specify a cell range, use Rectangle(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex)
new Rectangle(2,1),
new Rectangle(2,3),
new Rectangle(2,5),
new Rectangle(2,7),
new Rectangle(2,9)
};
//Remove the data validation from the selected cells
worksheet.getDVTable().remove(rectangles);
//Save the workbook to an Excel file
workbook.saveToFile("output/RemoveDataValidation.xlsx");
}
}
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
Cells
Tagged under
When you are creating a report by referencing data from multiple Excel files, you may find that the process is quite time-consuming and may also cause confusion or lead to errors as you need to switch between different opened files. In such a case, combining these separate Excel files into a single Excel workbook is a great option to simplify your work. This article will demonstrate how to merge multiple Excel files into one using Spire.XLS for Java.
Install Spire.XLS for Java
First, 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>
Merge Multiple Excel Workbooks into One in Java
With Spire.XLS for Java, you can merge data from different Excel files into different worksheets of one Excel Workbook. The following are the steps to merge multiple Excel workbooks into one.
- Specify the input Excel files that need to be merged.
- Initialize a Workbook object to create a new Excel workbook, and then clear all default worksheets in the workbook using Workbook.getWorksheets().clear() method.
- Initialize another temporary Workbook object.
- Loop through all input Excel files, and load the current workbook into the temporary Workbook object using Workbook.loadFromFile() method.
- loop through the worksheets in the current workbook, and then copy each worksheet from the current workbook to the new workbook using Workbook.getWorksheets().addCopy() method.
- Save the new workbook to file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class MergeExcels {
public static void main(String[] args){
//Specify the input Excel files
String[] inputFiles = new String[]{"Budget Summary.xlsx", "Income.xlsx", "Expenses.xlsx"};
//Initialize a new Workbook object
Workbook newBook = new Workbook();
//Clear the default worksheets
newBook.getWorksheets().clear();
//Initialize another temporary Workbook object
Workbook tempBook = new Workbook();
//Loop through all input Excel files
for (String file : inputFiles)
{
//Load the current workbook
tempBook.loadFromFile(file);
//Loop through the worksheets in the current workbook
for (Worksheet sheet : (Iterable) tempBook.getWorksheets())
{
//Copy each worksheet from the current workbook to the new workbook
newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
}
}
//Save the result file
newBook.saveToFile("MergeFiles.xlsx", ExcelVersion.Version2013);
}
}
The input Excel files:
The merged Excel workbook:
Merge Multiple Excel Worksheets into One in Java
An Excel workbook can contain multiple worksheets, and there are times you may also need to merge these worksheets into a single worksheet. The following are the steps to merge multiple Excel worksheets in the same workbook into one worksheet.
- Initialize a Workbook object and load an Excel file using Workbook.loadFromFile() method.
- Get two worksheets that need to be merged using Workbook.getWorksheets().get(int Index) method. Note that the sheet index is zero-based.
- Get the used range of the second worksheet using Worksheet.getAllocatedRange() method.
- Specify the destination range in the first worksheet using Worksheet.getCellRange(int row, int column) method. Note that the row and column indexes are 1-based.
- Copy the used range of the second worksheet to the destination range in the first worksheet using CellRange.copy(CellRange destRange) method.
- Remove the second worksheet using Worksheet.remove() method.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class MergeExcelWorksheets {
public static void main(String[] args){
//Create a Workbook object
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("input.xlsx");
//Get the first worksheet
Worksheet sheet1 = workbook.getWorksheets().get(0);
//Get the second worksheet
Worksheet sheet2 = workbook.getWorksheets().get(1);
//Get the used range in the second worksheet
CellRange sourceRange = sheet2.getAllocatedRange();
//Specify the destination range in the first worksheet
CellRange destRange = sheet1.getCellRange(sheet1.getLastRow() + 1, 1);
//Copy the used range of the second worksheet to the destination range in the first worksheet
sourceRange.copy(destRange);
//Remove the second worksheet
sheet2.remove();
//Save the result file
workbook.saveToFile("MergeWorksheets.xlsx", ExcelVersion.Version2013);
}
}
The input Excel worksheets:
The merged Excel worksheets:
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
Document Operation
Tagged under
This article demonstrates how to create a nested group in a worksheet using Spire.XLS for Java.
import com.spire.xls.*;
import java.awt.*;
public class CreateNestedGroup {
public static void main(String[] args) {
//Create a Workbook object
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Create a cell style
CellStyle style = workbook.getStyles().addStyle("style");
style.getFont().setColor(Color.blue);
style.getFont().isBold(true);
//Write data to cells
sheet.get("A1").setValue("Project plan for project X");
sheet.get("A1").setCellStyleName(style.getName());
sheet.get("A3").setValue("Set up");
sheet.get("A3").setCellStyleName(style.getName());
sheet.get("A4").setValue("Task 1");
sheet.get("A5").setValue("Task 2");
sheet.getCellRange("A4:A5").borderAround(LineStyleType.Thin);
sheet.getCellRange("A4:A5").borderInside(LineStyleType.Thin);
sheet.get("A7").setValue("Launch");
sheet.get("A7").setCellStyleName(style.getName());
sheet.get("A8").setValue("Task 1");
sheet.get("A9").setValue("Task 2");
sheet.getCellRange("A8:A9").borderAround(LineStyleType.Thin);
sheet.getCellRange("A8:A9").borderInside(LineStyleType.Thin);
//Pass false to isSummaryRowBelow method , which indicates the summary rows appear above detail rows
sheet.getPageSetup().isSummaryRowBelow(false);
//Group the rows using groupByRows method
sheet.groupByRows(2,9,false);
sheet.groupByRows(4,5,false);
sheet.groupByRows(8,9,false);
//Save to file
workbook.saveToFile("NestedGroup.xlsx", ExcelVersion.Version2016);
}
}
Published in
Cells
Tagged under
