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: [email protected]
For additional commands, e-mail: [email protected]
