Managed to make better than expected progress last night and so I was able to tidy a few things up this morning before work. Sadly, I have neglected to adjust the widths of the columns on the first sheet - the sheet that contains the data validations - but thought you would rather see the code now and that change is after all very easy to put in later and will not affect the process.
Never having tried upoloading a file using Nabble beofre, I hope this works but if not, I have included the code below. http://old.nabble.com/file/p26441578/Main.java Main.java Yours Mark B /* ==================================================================== Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. ==================================================================== */ package moredatavalidations; import java.io.File; import java.io.FileOutputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.hssf.usermodel.HSSFName; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.hssf.usermodel.DVConstraint; /** * An instance of this class creates a workbook containing two worksheets. The * first contains three data validations (drop down lists) that are interlinked; * the selection made in the first determines the data that appears in the * secondwhilst the selection made in the first two determines the data that * appears in the third. * * @author Mark B [msb at apache.org] * @version 1.00 20th November 2009 */ public class Main { private static final String[] organisations = {"Ford", "Toyota"}; private static final String[] fordDepts = {"Production", "Design", "Marketing"}; private static final String[] fordProdEmps = {"Peter Prentis", "Simon Nicol", "David Swarbrick", "Eliza Carthy", "Sandy Denny",}; private static final String[] fordDesignEmps = {"Judy Dyble", "Joni Mitchel", "Barbara Thompson", "Martin Carthy", "Dave Pegg", "Trevor Lucas"}; private static final String[] fordMarketingEmps = {"Alistair Anderson", "Gil Yaroon", "Martin Barre"}; private static final String[] toyotaDepts = {"Planning", "Design", "Marketing-Europe", "Marketing-Americas", "Marketing-Australasia", "Marketing-Rest Of The World", "Production"}; private static final String[] toyotaPlanEmps = {"David Fisher", "Nick Burch", "Tony Blair", "Harriet Harmon"}; private static final String[] toyotaDesignEmps = {"Eric Gill", "Hikari Hino", "Dennis Skinner", "Edward Heath"}; private static final String[] toyotaMktEurEmps = {"Yegor Kozlov", "Angela Merkel", "Dill Katz", "Colin Dudman"}; private static final String[] toyotaMktAmrEmps = {"Frank Zappa", "Ruth Underwood", "Don Van-Vliet", "Scott Thunes", "Chad Wackerman",}; private static final String[] toyotaMktAusEmps = {"Mark Webber", "Alan Jones", "Bruce McClaren"}; private static final String[] toyotaMktROWEmps = {"Jag Rao", "Azuma Harusaki", "Toshiro Mifune", "Brian Habana"}; private static final String[] toyotaProdEmps = {"Brian Moore", "Martin Johnson", "Wade Dooley", "Rob Andrew", "Dean Richards", "Serge Blanco"}; /** * Creates a workbook that consists of two worksheets. The first holds * three interrelated dropd down lists, the second the data for those * drop down lists. * * Note that POI allows us to create Data Validation using formulae that * contain cell range 'addresses' in the following format - * Sheet Name!Cell Range something like this Data Sheet!$A$1:$A$10. Excel * will quite willingly process these to create working Data Validations but * it will complain if you try to modify that validation through Excel * itself. Excel prefers that names be created and used to refer to ranges * of cells in formulae and this method contains an example of that with the * creation of the organisationValidation - the full details are commented * out of the code below. * * @param filename An instance of the String class that encapsulates the * name of and path to the workbook. */ public void dependentDropDownLists(String filename) { HSSFWorkbook workbook = null; HSSFSheet sheet = null; HSSFSheet dataSheet = null; HSSFDataValidation organisationValidation = null; HSSFDataValidation departmentValidation = null; HSSFDataValidation employeeValidation = null; HSSFName orgStartingPoint = null; HSSFName orgDataRange = null; HSSFRow row = null; HSSFCell cell = null; CellRangeAddressList organisationCellAddressList = null; CellRangeAddressList departmentCellAddressList = null; CellRangeAddressList employeeCellAddressList = null; DVConstraint organisationConstraint = null; DVConstraint departmentConstraint = null; DVConstraint employeeConstraint = null; File outputFile = null; FileOutputStream fos = null; try { // New Workbook. outputFile = new File(filename); fos = new FileOutputStream(outputFile); workbook = new HSSFWorkbook(); sheet = workbook.createSheet("List Validation"); dataSheet = workbook.createSheet("Data Sheet"); // Populate the data sheet. this.populateDataSheet(dataSheet); // Firstly, add the column headings to the main sheet. row = sheet.createRow(0); cell = row.createCell(0); cell.setCellValue("Organisation."); cell = row.createCell(1); cell.setCellValue("Departments."); cell = row.createCell(2); cell.setCellValue("Employees."); // Next, create the CellRangeAddressList objects for each // data validation (drop down list). Respectively these will go into // cell A2, B2 and C2. organisationCellAddressList = new CellRangeAddressList(1, 1, 0, 0); departmentCellAddressList = new CellRangeAddressList(1, 1, 1, 1); employeeCellAddressList = new CellRangeAddressList(1, 1, 2, 2); // The first data validation ought to be quite straightforward. It // will be the validation for the organisations names and will contain // data recovered from cells B1 and C1 on the data sheet. // // Note the way that the constraint has been built; it will include all // cells in the range B1 to Z1 of the Data Sheet that contain values. // Any empty cells within that range will be excluded but it is important // that there be no gaps; for example this will not work if cells B1 // and C1 contain data, D1 is empty and then E1 contains data. // // Note also that I have used the name of the sheet and the range of // cells directly in the constraint and Excel will allow POI to do this. // If you try to modify the Data Validation through Excel however, // then an error will be thrown as this is not permissible. To circumvent // this error, simply replace the "'Data Sheet'!$B$1" and // "'Data Sheet'!$B$1:$Z$1" String literals with references to named // ranges. organisationConstraint = DVConstraint.createFormulaListConstraint( "OFFSET('Data Sheet'!$B$1, 0, 0, 1, COUNTA('Data Sheet'!$B$1:$Z$1))"); // Which is just what these lines do. //orgStartingPoint = workbook.createName(); //orgStartingPoint.setNameName("orgStartingPoint"); //orgStartingPoint.setRefersToFormula("'Data Sheet'!$B$1"); //orgDataRange = workbook.createName(); //orgDataRange.setNameName("orgDataRange"); //orgDataRange.setRefersToFormula("'Data Sheet'!$B$1:$Z$1"); //organisationConstraint = DVConstraint.createFormulaListConstraint( // "OFFSET(orgStartingPoint, 0, 0, 1, COUNTA(orgDataRange))"); organisationValidation = new HSSFDataValidation( organisationCellAddressList, organisationConstraint); organisationValidation.setSuppressDropDownArrow(false); sheet.addValidationData(organisationValidation); // Next, build the Data Validation for the Department drop down list. // This validation will use a forumula that says - in effect - if the // user has selected Ford in the first drop down list then only show // me Ford's departments. // // Note the use of the simpler absolute addressing of data ranges // in the formula - "'Data Sheet'!$B$2:$D$2" for instance. This was // done for the sake of prototyping and there is nothing to prevent // it being replaced with the OFFSET() method. Note also that named // ranges are not used in this formula but, of course, there is no // reason why it should not be changed to accomodate them, just make // similar additions and changes as identified above in the commented // out section of code that applies to the organisationConstraint. // departmentConstraint = DVConstraint.createFormulaListConstraint( "IF(A2=\"Ford\",'Data Sheet'!$B$2:$D$2, IF(A2=\"Toyota\", " + "'Data Sheet'!$B$6:$H$6, \"Unknown\"))"); departmentValidation = new HSSFDataValidation( departmentCellAddressList, departmentConstraint); departmentValidation.setSuppressDropDownArrow(false); sheet.addValidationData(departmentValidation); // Finally, the employee validation which uses a slightly more complex // formula that must take into account BOTH the organisation and // department. // // Note that all of the comments made about the departmentConstraint // can be applied to the employeeConstaint. The OFFSET and COUNTA // functions can be used to replace the absolute range references // and names can be created to ensure that the user can modify the // data validation through Excel should that prove necessary. employeeConstraint = DVConstraint.createFormulaListConstraint( "IF(AND(A2=\"Ford\", B2=\"Production\"), 'Data Sheet'!$B$3:$F$3, " + "IF(AND(A2=\"Ford\", B2=\"Design\"), 'Data Sheet'!$B$4:$G$4, " + "IF(AND(A2=\"Ford\", B2=\"Marketing\"), 'Data Sheet'!$B$5:$D$5, " + "IF(AND(A2=\"Toyota\", B2=\"Planning\"), 'Data Sheet'!$B$12:$E$12, " + "IF(AND(A2=\"Toyota\", B2=\"Design\"), 'Data Sheet'!$B$7:$E$7, " + "IF(AND(A2=\"Toyota\", B2=\"Marketing-Europe\"), 'Data Sheet'!$B$10:$E$10, " + "IF(AND(A2=\"Toyota\", B2=\"Marketing-Americas\"), 'Data Sheet'!$B$8:$F$8, " + "IF(AND(A2=\"Toyota\", B2=\"Marketing-Australasia\"), 'Data Sheet'!$B$9:$D$9, " + "IF(AND(A2=\"Toyota\", B2=\"Marketing-Rest Of The World\"), 'Data Sheet'!$B$11:$E$11, " + "IF(AND(A2=\"Toyota\", B2=\"Production\"), 'Data Sheet'!$B$13:$G$13, \"#Value\"))))))))))"); employeeValidation = new HSSFDataValidation( employeeCellAddressList, employeeConstraint); employeeValidation.setSuppressDropDownArrow(false); sheet.addValidationData(employeeValidation); // Write the workbook away. workbook.write(fos); } catch (Exception pEx) { System.out.println("Caught an: " + pEx.getClass().getName()); System.out.println("Message : " + pEx.getMessage()); System.out.println("Stacktrace foillows: "); pEx.printStackTrace(System.out); } finally { if (fos != null) { try { fos.flush(); fos.close(); } catch (Exception ex) { // IGNORE // } } } } /** * Populate the Data Sheet with the data that will appear in the drop down * lists (Data Validations). * * Note this construct that appears regularly; * * if (result > lastColIndex) { * lastColIndex = result; * } * * Purely for the sake of ensuring the Data Sheet is readable, the sheets * columns are automatically adjusted to size after all of the data has * been written onto the sheet. In order to know how many columns to address, * it is important to keep track of the index of the right most cell * populated by the populateRow() method and that piece of code dhecks the * value the latter method returns and records it. A neater method may be * to create an instance variable that can be used by both methods - * populateDataSheet() and populateRow() - balance the problem of data * coupling against removing repeated lines of code. * * @param worksheet An instance of the HSSFSheet class that encapsulates * a reference to the worksheet that is to be populated * with data. */ private void populateDataSheet(HSSFSheet worksheet) { HSSFRow row = null; int rowIndex = 0; int lastColIndex = 0; int result = 0; // Firstly, add the organisations result = this.populateRow(worksheet.createRow(rowIndex++), "Organisations.", Main.organisations); if (result > lastColIndex) { lastColIndex = result; } // Now add the the list of departments for the first organisation, // Ford in this case. result = this.populateRow(worksheet.createRow(rowIndex++), "Ford's Departments.", Main.fordDepts); if (result > lastColIndex) { lastColIndex = result; } // Now add the names of those employed in Ford's Production, Design and // Marketing departments in turn result = this.populateRow(worksheet.createRow(rowIndex++), "Ford's Production Department.", Main.fordProdEmps); if (result > lastColIndex) { lastColIndex = result; } result = this.populateRow(worksheet.createRow(rowIndex++), "Ford's Design Department.", Main.fordDesignEmps); if (result > lastColIndex) { lastColIndex = result; } result = this.populateRow(worksheet.createRow(rowIndex++), "Ford's Marketing Department.", Main.fordMarketingEmps); if (result > lastColIndex) { lastColIndex = result; } // Now, add the list of departments for the second organisation followed // by the lists of employees for each department. result = this.populateRow(worksheet.createRow(rowIndex++), "Toyota's Departments.", Main.toyotaDepts); if (result > lastColIndex) { lastColIndex = result; } result = this.populateRow(worksheet.createRow(rowIndex++), "Toyota's Design Department.", Main.toyotaDesignEmps); if (result > lastColIndex) { lastColIndex = result; } result = this.populateRow(worksheet.createRow(rowIndex++), "Toyota's Marketing Department - Americas.", Main.toyotaMktAmrEmps); if (result > lastColIndex) { lastColIndex = result; } result = this.populateRow(worksheet.createRow(rowIndex++), "Toyota's Marketing Department - Australasia.", Main.toyotaMktAusEmps); if (result > lastColIndex) { lastColIndex = result; } result = this.populateRow(worksheet.createRow(rowIndex++), "Toyota's Marketing Department - Eurpoe.", Main.toyotaMktEurEmps); if (result > lastColIndex) { lastColIndex = result; } result = this.populateRow(worksheet.createRow(rowIndex++), "Toyota's Marketing Department - ROW.", Main.toyotaMktROWEmps); if (result > lastColIndex) { lastColIndex = result; } result = this.populateRow(worksheet.createRow(rowIndex++), "Toyota's Planning Department.", Main.toyotaPlanEmps); if (result > lastColIndex) { lastColIndex = result; } result = this.populateRow(worksheet.createRow(rowIndex++), "Toyota's Production Department.", Main.toyotaProdEmps); if (result > lastColIndex) { lastColIndex = result; } // Finally, size the columns appropriately. There is no need to do // this in the final version of the workbook UNLESS you expect the users // to add/delete data elements. I have done this just to enhance // readability at this stage of the process. for (int i = 0; i < lastColIndex; i++) { worksheet.autoSizeColumn(i); } } /** * Write data to cells within a row. * * @param row An instance of the HSSFRow class encapsulating a reference * to the row that is to be populated with data. * @param label An instance of the String class encapsulating the * label that shoule be placed alongside the data. Currently * the method assumes that the label will be written into the * first cell on the row. * @param data An array of String(s) where each element of the array * encapsulates the data that will be written into a single * cell. * @return A primitive int whose value represents the index of the right * most cell created and populated by this method's code. */ public int populateRow(HSSFRow row, String label, String[] data) { HSSFCell cell = null; int columnIndex = 0; cell = row.createCell(columnIndex++); cell.setCellValue(label); for (String item : data) { cell = row.createCell(columnIndex++); cell.setCellValue(item); } return (columnIndex); } /** * Demonstrates how to call the dependetDropDownLists(String) method. * @param args the command line arguments */ public static void main(String[] args) { new Main().dependentDropDownLists("C:/temp/Dependent Data Validations.xls"); } } nagineni wrote: > > Thanks Mark for the given idea of using dynamic drop downs.Yes,This way is > one solution to the problem. > But other issue is I've various levels of drop downs depends on each > other.Here is an example as you said ,three drop downs of > orginfo,branchinfo and empinfo.If I change first drop down ,the second > should populate values,If I change second one ,third should populate it's > values...like that I've many levels. > > Is this can be achieved in POI ?Sample code could be more helpful.Great > thanks for the help. > > Regards, > Naga. > > MSB wrote: >> >> Forgive me answering a question with a question please but is this even >> possible using Excel itself? If it is, and you can find out how to >> accomplish the same using Excel, then it may be possible to reproduce >> this behaviour with POI. >> >> Just as an aside, rather than disabling items in the list, why not simply >> change the list of items the user has to select from based upon certain >> criteria. For example, it is possible to use formulae to determine the >> items that appear in a drop down list. This formulae could make that >> decision based upon some sort of criteria; the most regularly used being >> the contents of another cell on the worksheet; as an example imagine that >> you have a drop down list containing the names of al of the departmeents >> within an organisation and another that you want to show the employees >> but you only want to see those employed within the department selected in >> the other dropdown list. One limitation is that drop down lists tend not >> to be dynamic; by this I mean that you could select a dpeartment, then >> select an employee then go back and select another department only to >> find that the same employee was displayed whether or not they worked >> within the newly selected department. >> >> Yours >> >> Mark B >> >> >> nagineni wrote: >>> >>> Hi, >>> >>> Can we disable list box value in XLS using POI API ?I've created list >>> box using HSSFDataValidation object and want to disable some of the >>> items in the list box.Also it it possible to apply font/colors to the >>> list box items ? >>> >>> Could any one let me know if we can achieve these features using POI >>> API,sample code is really helpful. >>> >>> Thanks in advance. >>> >> >> > > -- View this message in context: http://old.nabble.com/How-to-disable-list-box-values-in-XLS-using-POI-tp26357726p26441578.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
