Java addition excel data validation

In Excel, you can use data validation to impose certain restrictions on data entry. For example, the data validation preferences allow cells to enter only integers, decimals, hours, dates, and so on. You can also create pull-down menu options. This statement introduces data validation using Spire.XLS for Java.


import com.spire.xls.*;

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

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

        //Set up digital authentication in cell B2. 3-You can enter up to 6
        sheet.getCellRange("B1").setText("Input Number(3-6):");
        CellRange rangeNumber = sheet.getCellRange("B2");
        rangeNumber.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeNumber.getDataValidation().setFormula1("3");
        rangeNumber.getDataValidation().setFormula2("6");
        rangeNumber.getDataValidation().setAllowType(CellDataType.Decimal);
        rangeNumber.getDataValidation().setErrorMessage("Please input correct number!");
        rangeNumber.getDataValidation().setShowError(true);
        rangeNumber.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Set up date authentication in cell B 5. 1/1/2020 to 3/1/Just enter the date between 2020
        sheet.getCellRange("B4").setText("Input Date:(1/1/2020 to 3/1/2020)");
        CellRange rangeDate = sheet.getCellRange("B5");
        rangeDate.getDataValidation().setAllowType(CellDataType.Date);
        rangeDate.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeDate.getDataValidation().setFormula1("1/1/2020");
        rangeDate.getDataValidation().setFormula2("3/1/2020");
        rangeDate.getDataValidation().setErrorMessage("Please input correct date!");
        rangeDate.getDataValidation().setShowError(true);
        rangeDate.getDataValidation().setAlertStyle(AlertStyleType.Warning);
        rangeDate.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Set up character length validation in cell B 8. You can only enter text of up to 5 characters
        sheet.getCellRange("B7").setText("Input Text:");
        CellRange rangeTextLength = sheet.getCellRange("B8");
        rangeTextLength.getDataValidation().setAllowType(CellDataType.TextLength);        rangeTextLength.getDataValidation().setCompareOperator(ValidationComparisonOperator.LessOrEqual);
        rangeTextLength.getDataValidation().setFormula1("5");
        rangeTextLength.getDataValidation().setErrorMessage("Enter a Valid String!");
        rangeTextLength.getDataValidation().setShowError(true);
        rangeTextLength.getDataValidation().setAlertStyle(AlertStyleType.Stop);
        rangeTextLength.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        sheet.autoFitColumn(2);

        workbook.saveToFile("output/DataValidation.xlsx", ExcelVersion.Version2010);
    }
}

Effect diagram: DataValidation.png

Recommended Posts

Java addition excel data validation
Importing Excel data in Java 2
Import Excel data in Java
Importing Excel data in Java 3
[Java] Data type ①-Basic type
Print Java Excel Worksheet
[Java] Main data types
Java basic data types
Java learning memo (data type)
Java merge & unmerge Excel cell
Java programming (variables and data)
Zunda's 1-digit addition problem Java 11
Java turns Excel into PDF
Java creates an Excel document
Java for beginners, data hiding
Java sets Excel document properties
List data structure [Java / Scala]
Java Learning 1 (learning various data types)
Java Excel Insertion and Image Extraction
Display Firestore data in RecyclerView [Java]
Basic data types and reference types (Java)
[Java] Data type / matrix product (AOJ ⑧ Matrix product)
Add Bean Validation with Micronaut (Java)
[Java] Runtime Data Areas of JVM
EXCEL file update sample with JAVA
Java basic data types and reference types
Java adds and reads Excel formulas