You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by yashpal <ya...@gmail.com> on 2012/02/17 11:57:02 UTC
Re: how to create dependent drop down list in excel
If there is anybody who has done this before please help me to get out of
this task.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/how-to-create-dependent-drop-down-list-in-excel-tp5482135p5492143.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: how to create dependent drop down list in excel
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Fell prey to temptation this afternoon and put together a little piece of
code that demonstrates how to use the INDIRECT() function to create
dependent drop down lists. The code is below and I do expect it to run
successfully but I have not been able to test it out using Excel. We
switched to OpenOffice some time ago and I do know that the files produced
can be opened using Calc and that they do perform as expected. The same
should be true of Excel but I do not know this for a fact.
To run the example just call the classes constrictor and pass a String
encapsulating the name of and path to the file that should be created along
with one of two constants ValdationsTest.BINARY_WORKBOOK to create a .xls
file or ValdationsTest.OPENXML_WORKBOOK to create a .xlsx.
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package poiembeddeddoctest;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import java.io.*;
/**
*
* @author Mark Beardsley
*/
public class ValidationsTest {
public static final int BINARY_WORKBOOK = 0;
public static final int OPENXML_WORKBOOK = 1;
public ValidationsTest(String filename, int bookType) throws IOException
{
Workbook workbook = null;
Sheet sheet = null;
FileOutputStream fos = null;
BufferedOutputStream bos = null;
StringBuilder refersToFormula = null;
try {
switch(bookType) {
case BINARY_WORKBOOK:
workbook = new HSSFWorkbook();
break;
case OPENXML_WORKBOOK:
workbook = new XSSFWorkbook();
break;
}
sheet = workbook.createSheet("Validations");
buildDataSheet(sheet);
buildValidationsSheet(sheet);
fos = new FileOutputStream(filename);
bos = new BufferedOutputStream(fos);
workbook.write(bos);
}
finally {
if(bos != null) {
bos.close();
}
}
}
private static final void buildValidationsSheet(Sheet sheet) {
DataValidationHelper helper = null;
DataValidationConstraint constraint = null;
DataValidation validation = null;
// Set up the first data validation. This will create a drop down
list
// whose elementsd will be recovered from the named area called
GENRE. Note
// that this drop down list will be in cell A1 and I have hard coded
this
// into the CellRangeAddressList as 0, 0, 0, 0.
helper = sheet.getDataValidationHelper();
constraint = helper.createFormulaListConstraint("GENRE");
validation = helper.createValidation(constraint, new
CellRangeAddressList(0,0,0,0));
sheet.addValidationData(validation);
// Now, set up the second validation. This drop down list will
display
// value which depend upon the selection made in the previous
dropdown
// list. Note that this drop down list will appear in cell B1 as the
// CellRangeAddressList has been hard coded at 0, 0, 1, 1. Also,
look
// at the String passed to the createFormulaListConatrsint() method
// call. In effect, it says convert the contents of cell A1 in
uppercase
// and the treat this as the name of an area on the sheet. Get the
list
// of values for the dopr down from this named range.
constraint =
helper.createFormulaListConstraint("INDIRECT(UPPER($A$1))");
validation = helper.createValidation(constraint, new
CellRangeAddressList(0,0,1,1));
sheet.addValidationData(validation);
}
private static final void buildDataSheet(Sheet sheet) {
Row row = null;
Cell cell = null;
Name name = null;
// First, build the named area that will hold the data for the first
// validation. Later, the elements of this drop down list will be
used
// to determine which values appear in a dependent drop down list.
The key
// here is to ensure that the labels used in the drop down match the
names
// of the areas which will contain their data - with the exception
that
// the latter are capitalised.
row = sheet.createRow(9);
cell = row.createCell(0);
cell.setCellValue("Folk");
cell = row.createCell(1);
cell.setCellValue("Rock");
cell = row.createCell(2);
cell.setCellValue("Indie");
// Now, build the named reagion
name = sheet.getWorkbook().createName();
name.setNameName("GENRE");
name.setRefersToFormula("$A$10:$C$10");
// Next build rows for the data that will populate the dependent
drop
// down list. There will be three named areas; one providing the
data
// for each different Genre.
//
// This first area will provide the contents of the dependent drop
down
// if the user selects Folk in the first list. Note the call to the
// setNameName() method.
row = sheet.createRow(10);
cell = row.createCell(0);
cell.setCellValue("Fairport Convention");
cell = row.createCell(1);
cell.setCellValue("The Strawbs");
cell = row.createCell(2);
cell.setCellValue("The Oyster Band");
cell = row.createCell(3);
cell.setCellValue("The Albion Band");
cell = row.createCell(4);
cell.setCellValue("Morris On");
name = sheet.getWorkbook().createName();
name.setNameName("FOLK");
name.setRefersToFormula("$A$11:$E$11");
// ..and this if the user selects Rock
row = sheet.createRow(11);
cell = row.createCell(0);
cell.setCellValue("Cream");
cell = row.createCell(1);
cell.setCellValue("Free");
cell = row.createCell(2);
cell.setCellValue("Deep Purple");
cell = row.createCell(3);
cell.setCellValue("Frank Zappa");
name = sheet.getWorkbook().createName();
name.setNameName("ROCK");
name.setRefersToFormula("$A$12:$D$12");
// ...and this if they select Indie.
row = sheet.createRow(12);
cell = row.createCell(0);
cell.setCellValue("The Cure");
cell = row.createCell(1);
cell.setCellValue("Echo and The Bunnymen");
cell = row.createCell(2);
cell.setCellValue("Elvis Costello");
name = sheet.getWorkbook().createName();
name.setNameName("INDIE");
name.setRefersToFormula("$A$13:$C$13");
}
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/how-to-create-dependent-drop-down-list-in-excel-tp5482135p5497273.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: how to create dependent drop down list in excel
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Yep, did this some time ago and posted an example to the list; have a good
search through and you ought to find it. Sadly, I do not think I have the
code on my PC anymore and I do have to admit that it was a little
agricultural - using nested IF statements for example - but it did work.
Will dig around at home this evening and if I do manage to find the code,
then I will post it here.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/how-to-create-dependent-drop-down-list-in-excel-tp5482135p5492422.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: how to create dependent drop down list in excel
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Have not been able to find an example of this but you can also implement
dependent drop down lists using the INDIRECT() function. Basically, you do
something like the following;
1. Create a number of named areas on the worksheet to hold the data for the
dependent drop down lists. Make sure the names of these area match the
labels that will appear in the first drop down list of the series.
2. In cell A1 - just as an example, you can use any cell of course - create
a drop down list using a comma separated list of values. Each value will be
the name of one of the named areas..
2. in Cell B1 - again this can be any cell but it should be the drop down
list whose contents should depend on the value the user chooses in the
preceding drop down list - create another drop down list using the
INDIRECT() function something like this - INDIRECT(A1). This should
determine that the value chosen in the first drop down will identify the
named area from which the values should be chosen to populate the second
drop down.
Sadly, I cannot test this technique myself as I now use OpenOffice
exclusively and INDIRECT() does not work with the name of an area as it
ought to do with Excel. It should be very easy to test the technique however
for yourself.
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/how-to-create-dependent-drop-down-list-in-excel-tp5482135p5494874.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: how to create dependent drop down list in excel
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Found what I was looking for and have pasted the code into this message
below. You will see that it is quite old but I do expect it still to compile
and run against the latest version of the api. Also, you will see that it
targets HSSF - to produce an older, binary, file format workbook. If you
need to target either XSSF or the unifying SS usermodel then you will have
to make some changes to the code but I do still expect the basic approach to
hold true.
The class will produce a workbook that contains a sheet on which there are
dependent drop down lists. You will need to change this line in the main
method;
new Main().dependentDropDownLists("C:/temp/Dependent Data
Validations.xls");
to include the path to and name the workbook you wish to see generated. All
of the data for the drop down lists is contained within various named areas
on a second or data sheet. You do not have to adopt this approach - you can
hard code the data into lists - but I used it in response to a specific
request; there was a requirement to populate the drop down lists with data
read from an external source and to allow the user to change that data
whilst working with the workbook.
Yours
Mark B
PS I am not saying that this is the best or only approach as I have seen the
INDIRECT() function used with named areas but I now this one works.
/* ====================================================================
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;
HSSFName fordProdOrigin = null;
HSSFName fordProdRange = null;
HSSFName fordDesignOrigin = null;
HSSFName fordDesignRange = null;
HSSFName fordMktOrigin = null;
HSSFName fordMktRange = 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\"))))))))))");
//
// Using names will allow the user to modify the data validation
// through Excel. That does mean that two names must be declared
// to replace a single cell range statement - the first name
// indicates where on the data sheet the range begins and the
second
// name identifies the start and end points of the data range -
// because the added complexity of the OFFSET and COUNTA
functions
// has also been included. Using these functions together makes
it
// possible for the user to add new items to the data sheet.
fordProdOrigin = workbook.createName();
fordProdOrigin.setNameName("fordProdOrigin");
fordProdOrigin.setRefersToFormula("'Data Sheet'!$B$3");
fordProdRange = workbook.createName();
fordProdRange.setNameName("fordProdRange");
fordProdRange.setRefersToFormula("'Data Sheet'!$B$3:$Z$3");
employeeConstraint = DVConstraint.createFormulaListConstraint(
"IF(A2=\"Ford\", " +
"IF(B2=\"Production\", OFFSET(fordProdOrigin, 0, 0,
1, COUNTA(fordProdRange)), " +
"IF(B2=\"Design\", 'Data Sheet'!$B$4:$G$4, " +
"IF(B2=\"Marketing\", 'Data Sheet'!$B$5:$D$5,
\"#Value\")))," +
"IF(A2=\"Toyota\", " +
"IF(B2=\"Planning\", 'Data Sheet'!$B$12:$E$12, " +
"IF(B2=\"Design\", 'Data Sheet'!$B$7:$E$7, " +
"IF(B2=\"Marketing-Europe\", 'Data
Sheet'!$B$10:$E$10, " +
"IF(B2=\"Marketing-Americas\", 'Data
Sheet'!$B$8:$F$8, " +
"IF(B2=\"Marketing-Australasia\", 'Data
Sheet'!$B$9:$D$9, " +
"IF(B2=\"Marketing-Rest Of The World\", 'Data
Sheet'!$B$11:$E$11, " +
"IF(B2=\"Production\", 'Data Sheet'!$B$13:$G$13,
\"#Value\"))))))), \"#Value\"))");
employeeValidation = new HSSFDataValidation(
employeeCellAddressList, employeeConstraint);
employeeValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(employeeValidation);
// Adjust the columne widths so that the validations can be
// correctly seen
sheet.setColumnWidth(0, 3000);
sheet.setColumnWidth(1, 6500);
sheet.setColumnWidth(2, 4500);
// 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);
}
/**
* Demonstartes 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");
}
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/how-to-create-dependent-drop-down-list-in-excel-tp5482135p5493121.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org