You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Sakthi Priya <te...@yahoo.co.in> on 2009/04/24 11:02:42 UTC
Drop down list in excel with POI3.5
Hi,
I am using Jakarta POI3.5 to generate an excel spreadsheet. I need to have a drop down list box in a column in the excel sheet to restrict the user in changing the value of that particular cell.
Is it possible to do this with POI 3.5 version?
Thanks,
Sakthi Priya
Re: Drop down list in excel with POI3.5
Posted by MSB <ma...@tiscali.co.uk>.
Curisoty killed the cat so to speak. I had to try out the code that Pierre
and co put together and it does seem to work. I made a couple of changes the
what I originally posted and here it is;
// New Workbook.
outputFile = new File("C:/temp/ddlist book.xls");
fos = new FileOutputStream(outputFile);
workbook = new HSSFWorkbook();
// Add a sheet
sheet = workbook.createSheet("List Sheet");
// Create the cells that will be used to provide the data fro
the
// list and aginst which the value the user enters into the cell
// can be validated.
//
// These cells could be hidden away at the bottom right corner
of
// the sheet. Alternatively, another sheet could be created and
// used to hold the values for the drop down list(s). Have not
tried
// this latter option yet.
row = sheet.createRow(0);
cell = row.createCell((short)0);
cell.setCellValue(10);
row = sheet.createRow(1);
cell = row.createCell((short)0);
cell.setCellValue(20);
row = sheet.createRow(2);
cell = row.createCell((short)0);
cell.setCellValue(30);
row = sheet.createRow(3);
cell = row.createCell((short)0);
cell.setCellValue(40);
// Named range provides the data for the vaildation/list object
namedRange = workbook.createName();
namedRange.setNameName("NAMEDAREA");
namedRange.setReference("Sheet1!$A$1:$A$4");
// Set up tha data validation object. Note the the drop down
// list will appear when the user clicks into cell B1 in this
case.
start_row = (short)0;
String strFormula = "$A$1:$A$4";
dataValidation = new
HSSFDataValidation((short)(start_row),(short)1,(short)(start_row),(short)1);
dataValidation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
dataValidation.setFirstFormula(strFormula);
dataValidation.setSecondFormula(null);
dataValidation.setExplicitListFormula(true);
dataValidation.setSurppressDropDownArrow(false);
dataValidation.setEmptyCellAllowed(true);
dataValidation.setShowPromptBox(false);
dataValidation.createErrorBox("Invalid input !", "Something is
wrong. check condition!");
sheet.addValidationData(dataValidation);
// Write the workbook away.
workbook.write(fos);
One thing you could try - that is if it matters to you - placing the data
for the list onto another sheet. Not too sure what changes would need to be
made but teher could nto be many and that would allow you to keep the data
for the lists away from the main sheet(s) the users would interact with.
Also, note that you are not limited to using just numeric cells - I would
guess that lists could be made out of String, Date, etc cells but do not
think that you could mix the types.
TRSP wrote:
>
> Hi,
>
> I am using Jakarta POI3.5 to generate an excel spreadsheet. I need to have
> a drop down list box in a column in the excel sheet to restrict the user
> in changing the value of that particular cell.
>
> Is it possible to do this with POI 3.5 version?
>
> Thanks,
> Sakthi Priya
>
>
>
>
>
--
View this message in context: http://www.nabble.com/Drop-down-list-in-excel-with-POI3.5-tp23215207p23240260.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: Drop down list in excel with POI3.5
Posted by MSB <ma...@tiscali.co.uk>.
Right at the start I should say that I have never done this myself and that I
got this piece of code from a discussion that was led by Pierre Lavignotte
and contributed to by a few other users so can claim no credit at all if it
works.
Having said all of that, I think that you need to look at the
HSSFDataValidation class. As far as I can see, it uses a named range of
cells to provide the basis for the validation of the input a user makes.
Further, it seems that it is possible to make this validation work in a few
different ways, one of whihc is to use a drop down list.
I have not tried or tested this code myself - it was always something I
intended to do when I got the time - so can make no promises for it.
Nevertheless, here is the code that the group worked on;
HSSFSheet sheet = wb.getSheet("Sheet1");
HSSFName namedRange = wb.createName();
namedRange.setNameName("NAMEDAREA");
namedRange.setReference("Sheet1!$A$1:$A$2");
int start_row = (short)0;
String strFormula = "$A$1:$A$2";
HSSFDataValidation data_validation = new
HSSFDataValidation((short)(start_row),(short)1,(short)(start_row),(short)1);
data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
data_validation.setFirstFormula(strFormula);
data_validation.setSecondFormula(null);
data_validation.setExplicitListFormula(true);
data_validation.setSurppressDropDownArrow(false);
data_validation.setEmptyCellAllowed(true);
data_validation.setShowPromptBox(false);
dta_validation.createErrorBox("Invalid input !", "Something is wrong. check
condition!");
sheet.addValidationData(data_validation);
Good luck and I hope it works for you.
TRSP wrote:
>
> Hi,
>
> I am using Jakarta POI3.5 to generate an excel spreadsheet. I need to have
> a drop down list box in a column in the excel sheet to restrict the user
> in changing the value of that particular cell.
>
> Is it possible to do this with POI 3.5 version?
>
> Thanks,
> Sakthi Priya
>
>
>
>
>
--
View this message in context: http://www.nabble.com/Drop-down-list-in-excel-with-POI3.5-tp23215207p23239951.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: Drop down list in excel with POI3.5
Posted by MSB <ma...@tiscali.co.uk>.
It's raining today confing me to the workshop and office. Anyway, that means
I have some time to play and I found something more about the
HSSFDataValidation class today.
There is no need to set up a named range to create a drop down list and to
set up input validation on a cell. There are two other options.
The first is to enter the values into a contiguous range of cells on a
worksheet and then reference that range bay passing an appropriate value to
the setFirstFormula() method of the HSSFDataValidation object. The second is
to create a comma spearated list of values enclosed withiom double quotes
and to pass this to the setFirstFormula() method of the HSSFDataValidation
object. This code snippet shows both - I have commented out the lines of
code and shown only the latter method.
// New Workbook.
outputFile = new File("C:/temp/ddlist book.xls");
fos = new FileOutputStream(outputFile);
workbook = new HSSFWorkbook();
// Add a sheet
sheet = workbook.createSheet("List Sheet");
// Either of these two lines will supply the list of values for the drop
down
// There is no need to create a named range and reference that and there is
// no need either to include the list's values on the Worksheet, a comma
// separated list can be provided as shown below.
//String strFormula = "$A$1:$A$4";
//String strFormula = "\"100, 200, 300, 400, 500\"";
// Just for the sake of a demo, am using the comma separated list
// of value firstly.
String strFormula = "\"100, 200, 300, 400, 500\"";
dataValidation = new
HSSFDataValidation((short)(start_row),(short)1,(short)(start_row),(short)1);
dataValidation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
dataValidation.setFirstFormula(strFormula);
dataValidation.setSecondFormula(null);
dataValidation.setExplicitListFormula(true);
dataValidation.setSurppressDropDownArrow(false);
dataValidation.setEmptyCellAllowed(true);
dataValidation.setShowPromptBox(false);
dataValidation.createErrorBox("Invalid input !", "Something is wrong. check
condition!");
sheet.addValidationData(dataValidation);
// Write the workbook away.
workbook.write(fos);
Still though I cannot find a way to use values from cells on another
worksheet!!! Maybe though that does not matter here.
TRSP wrote:
>
> Hi,
>
> I am using Jakarta POI3.5 to generate an excel spreadsheet. I need to have
> a drop down list box in a column in the excel sheet to restrict the user
> in changing the value of that particular cell.
>
> Is it possible to do this with POI 3.5 version?
>
> Thanks,
> Sakthi Priya
>
>
>
>
>
--
View this message in context: http://www.nabble.com/Drop-down-list-in-excel-with-POI3.5-tp23215207p23254021.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