You might try making a template with all 50 drop down lists. Then use POI to hide the ones that you don't need. Or have several templates, each with a specific number of drop down lists, and choose the template based on however many are required.
Other than that, you could try building a macro in the template to create the drop downs when the workbook is opened for the first time. In my opinion, this way may not work so well in practice. People don't like to download spreadsheets that activate macros once they are downloaded. Well... at least I don't particularly like to download spreadsheets like that. -----Original Message----- From: Ryan Crumley [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 7:37 PM To: POI Users List Subject: RE: Add listbox to spreadsheet via POI 1. Very smart, thanks for the tip. 2. That would work for me, however the problem is I need a variable number of listboxes. My scenario is this: I have a list of products (for this example lets use car parts). Each row is a different part, and each column is a different attribute of that part. Sometimes there will be 5 parts on the sheet, other times 50. I need to be able to control the placement and number of listboxes on the sheet through POI. Thanks again ryan -----Original Message----- From: Michael Zalewski [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 4:20 PM To: POI Users List Subject: RE: Add listbox to spreadsheet via POI 1. The trick is to give the macro or form property a named range in your template. (The default is a relative range like D1:D3. You must first create a named range by using Insert | Name | Define from the menu bar, then type in this name wherever you would use a range). Then, when you are filling the template, use HSSFName to change the dimensions of the named range if that is required. 2. POI cannot define a combo box or a cell with data validation. But it will copy such a cell from a template. If you need a variable number of drop down boxes, I can't really think of a good way to handle the situation. But if you only need one drop down box which sometimes takes its data from D1:D3 and other times from D4:D8, the named range idea should work for you. Let us know if it does -----Original Message----- From: Ryan Crumley [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 4:44 PM To: POI Users List Subject: RE: Add listbox to spreadsheet via POI Thanks everyone for the input. Case 1 below looks like it will fit my needs very well, hopefully the patch will come together soon. I have a few questions about Case 2(using a template): 1. It looks like when you create a macro in excel and specify a range, the range is part of the form/macro. How would you use POI to change the number of elements in that range? (Although I think the work around for this is to specify a range in the macro that is large enough that you won't need to change it). 2. Using this template idea, are you able to use POI to specify the cell(s) that this list should show up on? Or is that done through the template? Currently the way I am planning on laying out my spreadsheet is that sometimes column D row 1,2, and 3 needs a listbox, and other times (depending on the data) it may be column D rows 4,5,6,7, and 8. Thanks for the feedback, it has been very informative. ryan -----Original Message----- From: Michael Zalewski [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 3:23 PM To: POI Users List Subject: RE: Add listbox to spreadsheet via POI Data Validation is not currently supported. And using Forms and Dialogs will cause macros to be created, which are also not supported. I can think of two possibilities 1. An earlier poster has stated that they have implemented the DV record types necessary for data validation. So I suppose a patch may be coming soon. You can read the post at http://www.mail-archive.com/[EMAIL PROTECTED]/msg07714.html. I don't think the patch has been contributed yet. 2. You can build a template with a dropdown list populated from a named range. (I.e., write a macro to populate the dropdown from a range). Now use POI to copy this template, populate the values in the named range - you can even change the number of elements. Let us know how/if it works. -----Original Message----- From: Ryan Crumley [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 3:47 PM To: [EMAIL PROTECTED] Subject: Add listbox to spreadsheet via POI I am creating a servlet based application that allows the user to export data to excel, edit it, and upload it back to the servlet for processing. Some columns in this spreadsheet contain values that are to be selected from a list of values. For instance column D might accept "circle", "triangle, or "square". It would be very convenient if I could generate listboxes in the cells of column D so the user can use the listbox to select from those 3 allowed values. It looks like excel supports listboxes through validators on the cell and the "forms" toolbar (using VB or ActiveX controls). Does anyone have experience using POI to create these type of controls? Thanks, ryan --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
