gistfile1.txt

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelTemplateGenerator {

    public static void main(String[] args) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet mainSheet = workbook.createSheet("Template");

        // Define header row
        Row header = mainSheet.createRow(0);
        header.createCell(0).setCellValue("Name");
        header.createCell(1).setCellValue("Age");
        header.createCell(2).setCellValue("Date of Birth");
        header.createCell(3).setCellValue("Status");

        // Generate dropdown options in a single hidden sheet
        Sheet hiddenSheet = workbook.createSheet("DropdownOptions");
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);

        // Add options for "Status" dropdown in column A
        String[] statusOptions = {"Active", "Inactive"};
        createDropdownOptions(hiddenSheet, statusOptions, 0, "StatusOptions");

        // Add options for another dropdown if needed
        String[] departmentOptions = {"HR", "Finance", "IT", "Marketing"};
        createDropdownOptions(hiddenSheet, departmentOptions, 1, "DepartmentOptions");

        // Set dropdown in the main sheet using the named ranges
        addDropdownToColumn(mainSheet, 3, "StatusOptions", 1000);  // Status dropdown in column D
        addDropdownToColumn(mainSheet, 4, "DepartmentOptions", 1000);  // Department dropdown in column E if needed

        // Write to file
        try (FileOutputStream fileOut = new FileOutputStream("template_with_dropdowns.xlsx")) {
            workbook.write(fileOut);
        }
        workbook.close();
        System.out.println("Template with dropdowns generated successfully!");
    }

    // Function to create dropdown options in a hidden sheet and define a named range
    private static void createDropdownOptions(Sheet hiddenSheet, String[] options, int column, String rangeName) {
        for (int i = 0; i < options.length; i++) {
            Row row = hiddenSheet.getRow(i) != null ? hiddenSheet.getRow(i) : hiddenSheet.createRow(i);
            row.createCell(column).setCellValue(options[i]);
        }
        Workbook workbook = hiddenSheet.getWorkbook();
        Name namedRange = workbook.createName();
        namedRange.setNameName(rangeName);
        namedRange.setRefersToFormula("DropdownOptions!" + CellReference.convertNumToColString(column) + "$1:$" + CellReference.convertNumToColString(column) + "$" + options.length);
    }

    // Function to add a dropdown to a specific column in the main sheet using a named range
    private static void addDropdownToColumn(Sheet sheet, int colIndex, String rangeName, int rows) {
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(rangeName);
        CellRangeAddressList addressList = new CellRangeAddressList(1, rows, colIndex, colIndex);
        DataValidation validation = validationHelper.createValidation(constraint, addressList);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }
}