You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by nagineni <na...@yahoo.com> on 2009/11/15 10:47:17 UTC
How to disable list box values in XLS using POI
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.
-----
Regards,
Naga.
--
View this message in context: http://old.nabble.com/How-to-disable-list-box-values-in-XLS-using-POI-tp26357726p26357726.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 disable list box values in XLS using POI
Posted by MSB <ma...@tiscali.co.uk>.
Changing the way the IF() function is coded does work;
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);
In that piece of code, I have also shown how to use names and the OFFSET()
function to replace the familiar cell addresses. In short, everywhere you
see something like this - 'Data Sheet'!$B$11:$E$11 - it can be replaced with
the OFFSET() function. Simply define two named areas, one that points to the
first cell in the range, another that defines a contiguous series of cells
that will contain the data for that element and use them to create the
OFFSET() function and replace the cell reange address. Doing this will mean
that the Data Validation can be maintained using Excel rather than just POI.
It does however mean that you will have to declare quite a few names and I
am guessing there is a limit but do not know what this could be.
If you need any more info, just post to the list.
Yours
Mark B
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-tp26357726p26454133.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 disable list box values in XLS using POI
Posted by MSB <ma...@tiscali.co.uk>.
Being English and benefitting from that great institution the 'tea break', I
had the time to realise that I was not using the IF() statement to it's best
advantage.
Excel's IF() function looks like this;
IF(condition, do if true, do if false)
and you will be able to see that I am using the AND() function currently to
say, for example, if the user has selected Ford in the first list and
Production in the second, then display these values in the third list. That
looks something like this;
IF(AND(A2=\"Ford\", B2=\"Production\"), 'Data Sheet'!$B$3:$F$3,
But, I think there may be a better way to proceed that does not require the
AND() function and so cleans thing up alittle.
IF(A2="Ford", IF(B2="Production", 'Data Sheet'!$B$3:$F$3,
IF(B2="Design,....,....")), IF(A2="Toyota", IF(B2="", , ), ))
This assumes that if A2 does contain the word Ford then further checking can
take place down the true channel so to speak. I do not think that really
explains it properly so, when I get home tonight, I will change the code to
use this modified version of the IF function and see what happens. My
motivation is a concern that there could be a limit on the size of the
function but I do not know whether this is really the case.
Yours
Mark B
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-tp26357726p26443547.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 disable list box values in XLS using POI
Posted by MSB <ma...@tiscali.co.uk>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to disable list box values in XLS using POI
Posted by MSB <ma...@tiscali.co.uk>.
Yes, that is entirely possible and somewhere I have the code for it which I
will look out and post for you. The only problem is that the lists cannot
work dynamically. By this, I mean if you select a department from one list
and then an employee from another list, if you then go back and change the
department, the employee that you originally selected will still be showing.
There is no way I know to make a selection in one list affect other cells
wothout resorting to VBA I would guess.
Anyway, I will have a dig around on my PC tonight and try to find that code
I spoke of. If I remember correctly, it only uses two lists, a selection in
the first determining the values you can select in another but it will
certainly give you the idea. One caveat, it exploits a loop-hole in Excel
where using POI we can create data validations using named areas on other
sheets. This is likely only to be a problem if you need to modify the
formula that is used to populate the lists through Excel itself rather than
through POI. Once I have the code, I will try to explain this 'problem' a
little more clearly and also outline the - very easy - solution.
Yours
Mark B
PS I was working on an example for the POI website demonstrating the various
different ways to build data validations and became sidetracked by other
problems. This has convinced me to complete that work, sooner rather than
later.
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-tp26357726p26421399.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 disable list box values in XLS using POI
Posted by MSB <ma...@tiscali.co.uk>.
It looks like this relates to a discussion we had a few months back and it
seems that I never actually wrote the code to create the data validations
then. That means it will take me a couple of days to get this written,
tested and posted out to you unfortunately - autumn is a very busy time of
year for us as all of the construction projects start, we can get into the
woods and many of the sites we maintain are closed to vistors so routine
maintenance needs completing before winter arrives. Nevertheless, I will get
something to you in two days at the outside.
Yours
Mark B
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-tp26357726p26421414.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 disable list box values in XLS using POI
Posted by nagineni <na...@yahoo.com>.
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.
>>
>
>
-----
Regards,
Naga.
--
View this message in context: http://old.nabble.com/How-to-disable-list-box-values-in-XLS-using-POI-tp26357726p26421375.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 disable list box values in XLS using POI
Posted by MSB <ma...@tiscali.co.uk>.
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-tp26357726p26358694.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 disable list box values in XLS using POI
Posted by MSB <ma...@tiscali.co.uk>.
I tried a few things that I could think of - for example basing the data
validation on cells on a worksheet and setting the colours of the different
cells contents but nothing seemd to work. With regard to colour, the best
that I could do - and this was using Excel not POI - was to apply
conditional formatting to the cell so that AFTER you had selected an item
from the list it's colour was changed; the list items all remained black
however.
Recently, I have been digging around on the Excel forums (fora?) and MSDN
and it seems that what you want to do is not possible within Excel. Even if
you use a ComboBox control placed on the form, you are limited just to
manipulating the font and possibly the colour of all of the items in the
validations list; it does not seem to offer you the ability to
enable/disable individual items nor to change the colour of each list item.
Sorry for the bad news.
Of course, I would never claim that my search was exhaustive and if you can
find a way then do please post to the list so that we can try to emulate
this behaviour using POI.
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-tp26357726p26393677.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