You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Pratik Thaker <Pr...@smartstream-stp.com> on 2015/04/17 16:44:42 UTC
Typable dropdown in excel file (XLSX)
Hi,
Is there a way to create typable dropdown in the excel file (XLSX) using POI, where values in the list will get filtered as user types ?
I am using below code to show the dropdown,
public class sd {
/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
DataValidation dataValidation = null;
DataValidationConstraint constraint = null;
DataValidationHelper validationHelper = null;
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1=(XSSFSheet) wb.createSheet("sheet1");
validationHelper=new XSSFDataValidationHelper(sheet1);
CellRangeAddressList addressList = new CellRangeAddressList(0,5,0,0);
constraint =validationHelper.createExplicitListConstraint(new String[]{"SELECT","10", "20", "30"});
dataValidation = validationHelper.createValidation(constraint, addressList);
dataValidation.setSuppressDropDownArrow(true);
sheet1.addValidationData(dataValidation);
FileOutputStream fileOut = new FileOutputStream("c:\\temp\\vineet.xlsx");
wb.write(fileOut);
fileOut.close();
}
}
Thanks.
________________________________
The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.
RE: Typable dropdown in excel file (XLSX)
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Then I am sorry to say I do not believe that is possible in Excel; I am
saying that I suspect it is a limitation of the Excel GUI not of POI. What I
do know you can do is create lists that depend upon one another - for
example the selection you make in a list in cell A1 for example will
determine the values you can see in a list in cell A2 but there is not a
way, at least as far as I am aware, to create the sort of lists you require.
The best advice I can offer is to find an Excel forum or list similar to
this one and ask the question there. If someone can offer a solution, then
it might be possible to replicate it using POI. If there is a solution, I
suspect it will involve macros and I am not certain how POI's support for
macro programming has developed recently. If you do have to use macros to
accomplish this task - and that is 'if' as an Excel guru may know a
straightforward way to achieve this task - then it might be best to create
the macro in a workbook and then use POI to open and populate that workbook.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Typable-dropdown-in-excel-file-XLSX-tp5718511p5718585.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: Typable dropdown in excel file (XLSX)
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Thanks Mahesh. I assumed the solution would have to be something along those
lines.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Typable-dropdown-in-excel-file-XLSX-tp5718511p5718599.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: Typable dropdown in excel file (XLSX)
Posted by Mahesh Ganapathy <mg...@gmail.com>.
This type ahead functionality is not available in excel. I tried to this about 2 years back and was stuck in the same situation as you are. The only workaround is to have a search cell with a macro that can change the drop downs for another cell. The superset of values can be placed in a hidden column or column in a hidden sheet. But this will also be cumbersome to the users.
Sent from my iPhone
> On Apr 25, 2015, at 7:00 AM, Pratik Thaker <Pr...@smartstream-stp.com> wrote:
>
> Hi Mark,
>
> User will select the value from list only. It's just that user should be able to type in the cell and the list should be narrowed down as per user's input (like usual comboboxes of web UI)
>
> Right now user has to select the value by scrolling through whole list.
>
> Regards,
> Pratik Thaker
>
> -----Original Message-----
> From: Mark Beardsley [mailto:markbrdsly@tiscali.co.uk]
> Sent: Saturday, April 25, 2015 4:18 PM
> To: user@poi.apache.org
> Subject: RE: Typable dropdown in excel file (XLSX)
>
> Can I make sure that I am clear what you want?
>
> Are you asking whether it is possible to create a drop down list that will change dynamically as the user enters something into a cell? If so, then I do not believe that this is possible; according to Microsoft, a data validation can only be used for the following -
>
> / * Make a list of the entries that restricts the values allowed in a
> cell.
> * Create a prompt message explaining the kind of data allowed in a cell.
> * Create messages that appear when incorrect data has been entered.
> * Check for incorrect entries by using the Auditing toolbar.
> * Set a range of numeric values that can be entered in a cell.
> * Determine if an entry is valid based on calculation in another cell.
> /
> (https://support.microsoft.com/en-us/kb/211485)
>
> If you do know of a way this can be accomplished using Excel directly, can I ask you to post the instructions here please? That way, someone can have a go at re-creating these steps with POI and help you that way.
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Typable-dropdown-in-excel-file-XLSX-tp5718511p5718583.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
>
> ________________________________
> The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Typable dropdown in excel file (XLSX)
Posted by Pratik Thaker <Pr...@smartstream-stp.com>.
Hi Mark,
User will select the value from list only. It's just that user should be able to type in the cell and the list should be narrowed down as per user's input (like usual comboboxes of web UI)
Right now user has to select the value by scrolling through whole list.
Regards,
Pratik Thaker
-----Original Message-----
From: Mark Beardsley [mailto:markbrdsly@tiscali.co.uk]
Sent: Saturday, April 25, 2015 4:18 PM
To: user@poi.apache.org
Subject: RE: Typable dropdown in excel file (XLSX)
Can I make sure that I am clear what you want?
Are you asking whether it is possible to create a drop down list that will change dynamically as the user enters something into a cell? If so, then I do not believe that this is possible; according to Microsoft, a data validation can only be used for the following -
/ * Make a list of the entries that restricts the values allowed in a
cell.
* Create a prompt message explaining the kind of data allowed in a cell.
* Create messages that appear when incorrect data has been entered.
* Check for incorrect entries by using the Auditing toolbar.
* Set a range of numeric values that can be entered in a cell.
* Determine if an entry is valid based on calculation in another cell.
/
(https://support.microsoft.com/en-us/kb/211485)
If you do know of a way this can be accomplished using Excel directly, can I ask you to post the instructions here please? That way, someone can have a go at re-creating these steps with POI and help you that way.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Typable-dropdown-in-excel-file-XLSX-tp5718511p5718583.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
________________________________
The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Typable dropdown in excel file (XLSX)
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Can I make sure that I am clear what you want?
Are you asking whether it is possible to create a drop down list that will
change dynamically as the user enters something into a cell? If so, then I
do not believe that this is possible; according to Microsoft, a data
validation can only be used for the following -
/ * Make a list of the entries that restricts the values allowed in a
cell.
* Create a prompt message explaining the kind of data allowed in a cell.
* Create messages that appear when incorrect data has been entered.
* Check for incorrect entries by using the Auditing toolbar.
* Set a range of numeric values that can be entered in a cell.
* Determine if an entry is valid based on calculation in another cell.
/
(https://support.microsoft.com/en-us/kb/211485)
If you do know of a way this can be accomplished using Excel directly, can I
ask you to post the instructions here please? That way, someone can have a
go at re-creating these steps with POI and help you that way.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Typable-dropdown-in-excel-file-XLSX-tp5718511p5718583.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