Data validations are cell based; by this, I mean that you effectively
'insert' the validation into a single cell or into a range of cells.
Therefore, you will have to set a validation for each cell, there is no
other solution that I know of I am sorry to say unless you choose to use a
VBA macro which may - and I repeat may because I am no expert on this matter
- be able to solve the problem for you by inserting data validations into
all of the cells in a column when the sheet is opened. Be aware however that
POI does not support the creation or manipulation of macros but it will copy
them if you open a workbook that contains macros, modify it using POI and
then re-save it again. Furthermore, macros included in the older binary
format workbooks provoke the display of a message that gives the user the
option to prevent the macro from running; that would, of course, prevent the
data validations from being created.
Yours
Mark B
hima bindu-2 wrote:
>
> Hi Mark,
>
> I need this feature for xls i.e, Binary format files only.
>
> In Excel, if we select a column and apply data validation using the
> following custom formula =COUNTIF(A:A,A1)=1, it gets applied to all the
> columns. That means for the cell A13, the formula gets changed to
> =COUNTIF(A:A,A13)=1.
>
> So I am trying to put this feature using Apace POI.
>
> The code being used is as follows:
>
> * private void setUniqueColumnValidation(HSSFCell pCell)
> {
> DVConstraint dvConstraint =
> DVConstraint.createCustomFormulaConstraint(
> "COUNTIF(A:A,A1)=1");
> CellRangeAddressList constraintCellRange = new
> CellRangeAddressList(0,
> 65535, 0, 0);
> HSSFDataValidation dataValidation = new
> HSSFDataValidation(constraintCellRange,
> dvConstraint);
> dataValidation.createErrorBox("Error", "Unique value need to
> entered
> in Id Column");
> pCell.getSheet().addValidationData(dataValidation);
> }*
>
> But the problem Iam facing is for all the columns it gets set as
> *COUNTIF(A:A,A1)=1.
> *So iam net getting desired behaviour.
>
> The only solution I feel is need to iterate over all the rows and set the
> formula according to that cell number. Is there any other solution?
>
> Regards,
> Bindu.
>
> On Mon, Feb 1, 2010 at 10:55 PM, MSB <[email protected]> wrote:
>
>>
>> With regard to the xml based files, I have had a very cursory dig around
>> and
>> cannot see a function that you could substitue for the data validation
>> but
>> I
>> have thought of a possible alternative. Use Excel itself to create a
>> workbook that contains the data validation and save it away as a
>> template.
>> Now, use POI to open the template file, populate it with data and then
>> save
>> it away again.
>>
>> Yours
>>
>> Mark B
>>
>>
>> hima bindu-2 wrote:
>> >
>> > Hi,
>> >
>> > I have the following requirement:
>> >
>> > Suppose there is a column called Id in Excel Sheet. I need to provide
>> some
>> > data validation for that column so that only unique values can be
>> entered.
>> >
>> > Id
>> > 1
>> > 2
>> > 3
>> > 4
>> > .
>> > .
>> > .
>> >
>> > Can some body suggest the possible approaches?
>> >
>> > --
>> > With regards,
>> > B.R.Hima Bindu.
>> >
>> > Stephen
>> >
>> Leacock<http://www.brainyquote.com/quotes/authors/s/stephen_leacock.html
>> >
>> > - "I detest life-insurance agents: they always argue that I shall some
>> > day
>> > die, which is not so."
>> >
>> >
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Unique-value-for-a-column-in-a-Excel-tp27402140p27407896.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]
>>
>>
>
>
> --
> With regards,
> B.R.Hima Bindu.
>
> Mike Ditka <http://www.brainyquote.com/quotes/authors/m/mike_ditka.html>
> -
> "If God had wanted man to play soccer, he wouldn't have given us arms."
>
>
--
View this message in context:
http://old.nabble.com/Unique-value-for-a-column-in-a-Excel-tp27402140p27416487.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]
