How to Use INDIRECT() Function In EasyXLS
Page 1 of 1

Author:  smruti_patra [ Thu Dec 04, 2008 10:59 am ]
Post subject:  How to Use INDIRECT() Function In EasyXLS

I am creating Three dropdown list like CustomerType,AOO ,COO, DOO in Sheet First tab A,B, C, D column.

I am displaying the customerType dropdown in the E column up to 10 record.

I have one requirement like:
When user select AOO in from dropdown in E1 , In the F1 need to display dropdown with values of AOO dropdown creted in B column.

I am atatching the java code for reference.

This code should be working without any manual changes.
I am being forced to select DATA -> VALIDATION -> OK button on F1 cell, to get it to work.

Please let me know how we need to resolve this issue.

Thanks in advance.


package tutorial;

import EasyXLS.ExcelDocument;
import EasyXLS.ExcelTable;
import EasyXLS.ExcelWorksheet;
import EasyXLS.Constants.DataValidator;

public class JeffCode2 {

    public static void main(String[] args) {
        try {
            System.out.println("---Smruti Champ---");

            ExcelDocument xls = new ExcelDocument(1);

            ExcelWorksheet mainXlsSheet = (ExcelWorksheet) xls.easy_getSheetAt(0);
            mainXlsSheet.setSheetName("First tab");
            ExcelTable xlsFirstTable = mainXlsSheet.easy_getExcelTable();

            xlsFirstTable.easy_getCell(0, 0).setValue("AOO");
            xlsFirstTable.easy_getCell(1, 0).setValue("COO");
            xlsFirstTable.easy_getCell(2, 0).setValue("DOO");

            xlsFirstTable.easy_getCell(0, 1).setValue("AOO1");
            xlsFirstTable.easy_getCell(1, 1).setValue("AOO2");
            xlsFirstTable.easy_getCell(2, 1).setValue("AOO3");
            xlsFirstTable.easy_getCell(3, 1).setValue("AOO4");

            xlsFirstTable.easy_getCell(0, 2).setValue("COO1");
            xlsFirstTable.easy_getCell(1, 2).setValue("COO2");
            xlsFirstTable.easy_getCell(2, 2).setValue("COO3");
            xlsFirstTable.easy_getCell(3, 2).setValue("COO4");

            xlsFirstTable.easy_getCell(0, 3).setValue("DOO1");
            xlsFirstTable.easy_getCell(1, 3).setValue("DOO2");
            xlsFirstTable.easy_getCell(2, 3).setValue("DOO3");
            xlsFirstTable.easy_getCell(3, 3).setValue("DOO4");

            mainXlsSheet.easy_addName("CustomerType", "=" + mainXlsSheet.getSheetName() + "!$A$1:$A$4");
            mainXlsSheet.easy_addName("AOO", "=" + mainXlsSheet.getSheetName() + "!$B$1:$B$5");
            mainXlsSheet.easy_addName("COO", "=" + mainXlsSheet.getSheetName() + "!$C$1:$C$5");
            mainXlsSheet.easy_addName("DOO", "=" + mainXlsSheet.getSheetName() + "!$D$1:$D$5");
           //String validate = E
            mainXlsSheet.easy_addDataValidator("E1:E5", DataValidator.VALIDATE_LIST, DataValidator.OPERATOR_EQUAL_TO, "=CustomerType", "");
            for (int i = 1; i < 10; i++) {
                mainXlsSheet.easy_addDataValidator("F"+i, DataValidator.VALIDATE_LIST, DataValidator.OPERATOR_EQUAL_TO, "=INDIRECT(E"+i+")", "");
            // Generate the file

            if (xls.easy_getError().equals("")) {
                System.out.println("File successfully created.");
            } else {
                System.out.println("Error encountered: " + xls.easy_getError());

        } catch (Exception ex) {


Page 1 of 1 All times are UTC - 4 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group