Morning Naga,
I see what you meant now. The shorter of the two lists is artificially
extended because there is another, longer list on the same data sheet.
The good news, well, I suppose it is good news of a sort, is that this is
exactly the sort of behaviour you would see if the same process was
performed using Excel itself. It seems as though the data sheet can be
thought of as a flat, two dimensional arry of cells. Each list simply uses
all of the values in a column as the source of their data and, if there are
empty cells in that column, then it substitutes blank values in the list.
As of this moment, I do not know exactly how to get around the problem
though I can think of two obvious solutions. The first is to create a
separate sheet in the workbook for each validation - messy in the extreme
and not a good idea in my opinion. Secondly, pass an array of values rather
than a range of cells when creating the validation. This has the drawback of
preventing the user - unless they are more technically confident - from
adding values to the validation. If they wanted to add values, they would
need to select tha data tab, select Data Validation, etc. Also, if you want
to retrieve tha validations values at the end to write them away to a
database for future reference, this will not be possible with the API.
I have to leave for work soon but I will be able to look into the problem
later today. The two avenues I am going to look into first are writing the
data into rows rather than columns and seeing if the validations can deal
with ragged - uneven length - rows. Secondly, I am going to see if there is
a forumula that makes it possible for me to specify a exclusive range of
cells; something along the lines of 'select the values from cells A1 to A200
and exclude any that are empty'. If so, this could be used as the source for
the list (this is the one I am banking on if it exists).
Will let you know if I make progress but I cannot promise an answer for a
few hours.
Yours
Mark B
Nagineni wrote:
>
> Hi Mark,
>
> I'm sorry I could not explain the issue in right way.Here is the issue.
> I modified bit of below code ,please run this code you will see the issue.
> Column A is having values from 1 to 100 rows and column B's range from 1
> to 200.
>
> In formula ,specified range is A1to A200,leads empty options in drop
> down.I can not change formula range from A1 to A100 because use can enter
> values up to 200 rows.
>
> I hope I explained the issue now clearly.Please let me know if you are
> clear the issue.
>
> Is there any way to fix this issue ?
>
> HSSFWorkbook workbook = null;
> HSSFSheet sheet = null;
> HSSFSheet dataSheet = null;
> HSSFDataValidation numericDataValidation = null;
> HSSFDataValidation textDataValidation = null;
> HSSFDataValidation dateDataValidation = null;
> HSSFName namedRange = null;
> HSSFRow row = null;
> HSSFCell cell = null;
> CellRangeAddressList numericCellAddressList = null;
> DVConstraint numericDVConstraint = null;
> File outputFile = null;
> FileOutputStream fos = null;
> try {
> outputFile = new File("C:/Documents and
> Settings/nravilla/Desktop/temp.xls");
> fos = new FileOutputStream(outputFile);
> workbook = new HSSFWorkbook();
> sheet = workbook.createSheet("List Validation");
> dataSheet = workbook.createSheet("Data Sheet");
> // Just to populate the cells in column A of the data
> // sheet. Have changed the terminator value without
> // seeing corrupted lists.
> for (int i = 0; i < 100; i++) {
> row = dataSheet.createRow(i);
> cell = row.createCell(0);
> cell.setCellValue(i);
> }
>
> for (int i = 0; i < 200; i++) {
> row = dataSheet.getRow(i);
> if (row == null) {
> row = dataSheet.createRow(i);
> }
> cell = row.createCell(1);
> cell.setCellValue(i);
> }
> namedRange = workbook.createName();
> namedRange.setNameName("dataRange1");
> namedRange.setRefersToFormula("'Data Sheet'!$A$1:$A$200");
> numericCellAddressList = new CellRangeAddressList(0, 0, 0, 0);
> numericDVConstraint =
> DVConstraint.createFormulaListConstraint("dataRange1");
> numericDataValidation = new
> HSSFDataValidation(numericCellAddressList,
> numericDVConstraint);
> // Switching these two made no difference aside from the obvious -
> passing
> // false meant that a drp down arrow was displayed, passing tru
> suppressed
> // the arrow
> // numericDataValidation.setSuppressDropDownArrow(false);
> numericDataValidation.setSuppressDropDownArrow(false);
> sheet.addValidationData(numericDataValidation);
> row = sheet.createRow(0);
> cell = row.createCell(0);
> cell.setCellValue(10);
> workbook.write(fos);
> } catch (Exception e) {
> } finally {
> if (fos != null) {
> try {
> fos.close();
> } catch (Exception ex) {
> // IGNORE //
> }
> }
> }
>
>
> Regards,
> Naga.
>
>
>
> MSB wrote:
>>
>> Sorry to hear that you are having problems and of course, I am more than
>> happy to try and help.
>>
>> After reading your message, I ran some test code of my own and cannot
>> seem to reproduce the problem. The first piece of code I ran is copied
>> below. You can see it uses a named range of 200 cells in column A of a
>> data sheet and has a for loop just to populate those cells. Currently, it
>> populates all 200 but I did set it earlier to populate just 20 and did
>> not see the gaps that you spoke of;
>>
>> HSSFWorkbook workbook = null;
>> HSSFSheet sheet = null;
>> HSSFSheet dataSheet = null;
>> HSSFDataValidation numericDataValidation = null;
>> HSSFDataValidation textDataValidation = null;
>> HSSFDataValidation dateDataValidation = null;
>> HSSFName namedRange = null;
>> HSSFRow row = null;
>> HSSFCell cell = null;
>> CellRangeAddressList numericCellAddressList = null;
>> DVConstraint numericDVConstraint = null;
>> File outputFile = null;
>> FileOutputStream fos = null;
>>
>> try {
>> outputFile = new File("C:/temp/Cell Validation From List.xls");
>> fos = new FileOutputStream(outputFile);
>> workbook = new HSSFWorkbook();
>>
>> sheet = workbook.createSheet("List Validation");
>> dataSheet = workbook.createSheet("Data Sheet");
>>
>> // Just to populate the cells in column A of the data
>> // sheet. Have changed the terminator value without
>> // seeing corrupted lists.
>> for(int i = 0; i < 200; i++) {
>> row = dataSheet.createRow(i);
>> cell = row.createCell(0);
>> cell.setCellValue(i);
>> }
>>
>> namedRange = workbook.createName();
>> namedRange.setNameName("dataRange1");
>> namedRange.setRefersToFormula("'Data Sheet'!$A$1:$A$200");
>> numericCellAddressList = new CellRangeAddressList(0, 0, 0, 0);
>> numericDVConstraint =
>> DVConstraint.createFormulaListConstraint("dataRange1");
>> numericDataValidation = new
>> HSSFDataValidation(numericCellAddressList,
>> numericDVConstraint);
>> // Switching these two made no difference aside from the obvious -
>> passing
>> // false meant that a drp down arrow was displayed, passing tru
>> suppressed
>> // the arrow
>> //numericDataValidation.setSuppressDropDownArrow(false);
>> numericDataValidation.setSuppressDropDownArrow(true);
>> sheet.addValidationData(numericDataValidation);
>>
>> row = sheet.createRow(0);
>> cell = row.createCell(0);
>> cell.setCellValue(10);
>>
>> workbook.write(fos);
>>
>> }
>> finally {
>> if(fos != null) {
>> try {
>> fos.close();
>> }
>> catch(Exception ex) {
>> // IGNORE //
>> }
>> }
>> }
>>
>> and likewise, it was possible to set the formula explicitly and to not
>> use a named range as here where I use just 100 cells in column A. Again,
>> I have played around with the for loop to populate all or just a smaller
>> number of the cells in column A of the data sheet withou seeing the gaps
>> you spoke of.
>>
>> outputFile = new File("C:/temp/Cell Validation From List2.xls");
>> fos = new FileOutputStream(outputFile);
>> workbook = new HSSFWorkbook();
>>
>> sheet = workbook.createSheet("List Validation");
>> dataSheet = workbook.createSheet("Data Sheet");
>>
>> // Just to populate the cells in column A of the data
>> // sheet. Have changed the terminator value without
>> // seeing corrupted lists.
>> for(int i = 0; i < 100; i++) {
>> row = dataSheet.createRow(i);
>> cell = row.createCell(0);
>> cell.setCellValue(i);
>> }
>>
>> numericCellAddressList = new CellRangeAddressList(0, 0, 0, 0);
>> numericDVConstraint = DVConstraint.createFormulaListConstraint(
>> "'Data Sheet'!$A$1:$A$100");
>> numericDataValidation = new HSSFDataValidation(numericCellAddressList,
>> numericDVConstraint);
>> //numericDataValidation.setSuppressDropDownArrow(false);
>> numericDataValidation.setSuppressDropDownArrow(true);
>> sheet.addValidationData(numericDataValidation);
>>
>> row = sheet.createRow(0);
>> cell = row.createCell(0);
>> cell.setCellValue(10);
>>
>> workbook.write(fos);
>>
>> Something I did note though, and I think it will have to be added into
>> the documentation for this feature, if you specify a range of cells that
>> covers from, for example A1 to A200 and then only populate the first 100
>> cells in the range with data, then the validation will not work
>> correctly. As some of the cells are empty, Excel cannot be certain that a
>> value is incorrect and it will hapilly allow you to enter values that are
>> 'illegal'; only if all of the cells in the range are populated is it able
>> to make this determination.
>>
>> Perhaps, you could try running my code just to be confident that it does
>> not reproduce the 'gappy' list - for want of a better term - that you
>> saw. Then, could you let me see the code that is causing you trouble
>> please and could I ask you to explain what you are expecting it to do?
>>
>> Yours
>>
>> Mark B
>>
>>
>> Nagineni wrote:
>>>
>>> Hi Mark,
>>>
>>> I see new issue with data validation dropdownlist.Here is the issue.I'm
>>> creating set of values in one sheet and giving namereferance to these
>>> list of values in other sheet by creating datavalidation.
>>>
>>> Ex :In sheet1 colummn A is having 5 values and column B is having 10
>>> values.Using HSSFRow and HSSFCell I created all these values in this
>>> sheet.
>>>
>>> From other excel sheet say sheet2 I'm reffering column A values uing
>>> formula from A1 to A200 and created datavalidation dropdown.
>>>
>>> If I open sheet2 ,the listbox is showing 10 values outof these 5 are
>>> values and 5 are empty options.
>>> It showd show only 5 values of columnA of sheet1.
>>>
>>> I used dataValidation.setEmptyCellAllowed(false); but i'm help less.
>>>
>>> Any idea on this.please help me on this.
>>>
>>> Regards,
>>> Naga.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Nagineni wrote:
>>>>
>>>> Hey Mark,
>>>>
>>>> No hurry ,with datavalidation approach I implemented and it's working
>>>> fine.Please take your own time to find other approach if any.
>>>>
>>>> Thanks lot for your help !!!!
>>>>
>>>> Regards,
>>>> Naga.
>>>>
>>>>
>>>> MSB wrote:
>>>>>
>>>>> Sorry Naga,
>>>>>
>>>>> It occured to me as I was working in the woods this morning that I had
>>>>> answered a question that you had not really asked. On further
>>>>> reflection, I think that Hima may have had the answer all along, and
>>>>> it does mean turning back to the data validation.
>>>>>
>>>>> I think that if you create a validation with just a single value and
>>>>> 'attach' it to a cell then, within the java code, set the value of
>>>>> that cell correctly, then this could do the trick. When the user sees
>>>>> the sheet, the cell will be displaying the value correctly and if they
>>>>> try to change that value, they will see an error message. To my mind,
>>>>> the only drawback to this technique is that the user, if they are
>>>>> technically capable, can change the validation from within Excel and
>>>>> there is no way, as far as I am aware, to prevent this from happening
>>>>> although, to quote Baldrick, I have a cunning plan that may circumvent
>>>>> this to some extent.
>>>>>
>>>>> We had quite a long day today and I have just got back to the office.
>>>>> Later tonight after I get home, I will try to put together some code
>>>>> to test the idea out and let you know what happens. Just sorry I did
>>>>> not think of this earlier today!!!
>>>>>
>>>>> Yours
>>>>>
>>>>> Mark B
>>>>>
>>>>>
>>>>> Nagineni wrote:
>>>>>>
>>>>>> Hi Hima Bindu,
>>>>>>
>>>>>> This way is also working for me and it's done my job complete.But it
>>>>>> is to validate data not lock the cell.
>>>>>>
>>>>>> Need to find the way when we lock the cell and unlock it as mark
>>>>>> suggested.
>>>>>>
>>>>>> I hope I can come come up with solution using locked mode.
>>>>>>
>>>>>> Regards,
>>>>>> Naga.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Hima Bindu wrote:
>>>>>>>
>>>>>>> Hi Naga,
>>>>>>>
>>>>>>> Even I have same issue like yours. So I implemented the same way as
>>>>>>> u
>>>>>>> suggested. It is showing me the cell value. Might be the cell
>>>>>>> address range
>>>>>>> you are specifying is not correct. This is how its working for me.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> HSSFCell displayNameCell = (This is the cell reference u
>>>>>>> need to
>>>>>>> specify for which u have lock. In your case, its EmpName and EmpNo
>>>>>>> columns)
>>>>>>> String[] displayNameList = new String[]{(Here u need to
>>>>>>> specify
>>>>>>> the column values itself. To provide lock for EmpName column the
>>>>>>> list has to
>>>>>>> be EmpName and for EmpNo column its EmpNo )};
>>>>>>> DVConstraint displayNameConstraint =
>>>>>>> DVConstraint.createExplicitListConstraint(displayNameList);
>>>>>>> CellRangeAddressList displayNameCellRange = new
>>>>>>> CellRangeAddressList(displayNameCell.getRowIndex(),displayNameCell.getRowIndex(),displayNameCell.getColumnIndex(),displayNameCell.getColumnIndex());
>>>>>>> HSSFDataValidation displayNameValidation = new
>>>>>>> HSSFDataValidation(displayNameCellRange,displayNameConstraint);
>>>>>>> displayNameValidation.createErrorBox("Not
>>>>>>> Applicable","Cannot
>>>>>>> change the value");
>>>>>>> displayNameValidation.setSuppressDropDownArrow(true);
>>>>>>>
>>>>>>> displayNameCell.getSheet().addValidationData(displayNameValidation);
>>>>>>>
>>>>>>> --
>>>>>>> With regards,
>>>>>>> B.R.Hima Bindu.
>>>>>>> Calvin
>>>>>>> Trillin<http://www.brainyquote.com/quotes/authors/c/calvin_trillin.html>
>>>>>>> - "Health food makes me sick."
>>>>>>>
>>>>>>> On Wed, Jul 8, 2009 at 9:10 AM, Nagineni <naganirange...@yahoo.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>>
>>>>>>>> Hi Mark,
>>>>>>>>
>>>>>>>> If I lock cell with style and protect sheet with password,It's not
>>>>>>>> allowing
>>>>>>>> me even to add data.If I unprotect the sheet with the password then
>>>>>>>> I can
>>>>>>>> modify cell values.
>>>>>>>>
>>>>>>>> lockedNumericStyle.setLocked(true);
>>>>>>>> sheet.protectSheet("password");
>>>>>>>>
>>>>>>>> My requirement is while creating sheet for some columns I'm writing
>>>>>>>> data
>>>>>>>> and
>>>>>>>> these columns should not be
>>>>>>>> modified by the user.Here is the senario
>>>>>>>>
>>>>>>>> EmpName EmpNo
>>>>>>>>
>>>>>>>> after creating sheet with these columns ,user can open sheet and
>>>>>>>> can add
>>>>>>>> data for these columsns like below
>>>>>>>>
>>>>>>>> EmpName EmpNo
>>>>>>>>
>>>>>>>> A 1
>>>>>>>> B 2
>>>>>>>>
>>>>>>>> like this but user can not modify column names.If I unprotect sheet
>>>>>>>> tehn
>>>>>>>> user canmodify EmpName and EmpNo ,It should not.
>>>>>>>>
>>>>>>>> is it possible to lock specified cells readonly and for reamaining
>>>>>>>> cells
>>>>>>>> can
>>>>>>>> editable.
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>> Naga.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> MSB wrote:
>>>>>>>> >
>>>>>>>> > Your welcome.
>>>>>>>> >
>>>>>>>> > It is possible to have some cells locked and others that the user
>>>>>>>> can
>>>>>>>> > modify. To achieve this you need to create different
>>>>>>>> HSSFCellStyle
>>>>>>>> objects
>>>>>>>> > to apply to the cells; one will be locked the other not. Then
>>>>>>>> when you
>>>>>>>> > protect the sheet only those cells to which the locked style was
>>>>>>>> added
>>>>>>>> > will be un-editable.
>>>>>>>> >
>>>>>>>> > Yours
>>>>>>>> >
>>>>>>>> > Mark B
>>>>>>>> >
>>>>>>>> >
>>>>>>>> > Nagineni wrote:
>>>>>>>> >>
>>>>>>>> >> Hi Mark,
>>>>>>>> >>
>>>>>>>> >> Thanks for the spontaneous response.As I'm not aware of locking
>>>>>>>> feature
>>>>>>>> >> ,I used data validation approach
>>>>>>>> >> Thanks again for letting me know about this feature.
>>>>>>>> >>
>>>>>>>> >> Yes,this works for me.
>>>>>>>> >>
>>>>>>>> >>
>>>>>>>> >> Regards,
>>>>>>>> >> Naga.
>>>>>>>> >>
>>>>>>>> >>
>>>>>>>> >>
>>>>>>>> >>
>>>>>>>> >> MSB wrote:
>>>>>>>> >>>
>>>>>>>> >>> I think that you may be looking at the worng feature of the
>>>>>>>> cell.
>>>>>>>> >>>
>>>>>>>> >>> Are you saying that you want to enter a value into a cell when
>>>>>>>> you are
>>>>>>>> >>> preparing the sheet and to prevent the user from subsequently
>>>>>>>> making
>>>>>>>> any
>>>>>>>> >>> changes to that value?
>>>>>>>> >>>
>>>>>>>> >>> If this is the case then you need to create an HSSFCellStyle
>>>>>>>> object
>>>>>>>> that
>>>>>>>> >>> has it's locked attribute set. This style needs to be applied
>>>>>>>> to the
>>>>>>>> >>> cell that you want to protect and the worksheet itself then
>>>>>>>> needs to be
>>>>>>>> >>> protected.
>>>>>>>> >>>
>>>>>>>> >>> This is the sort of code you will need to use;
>>>>>>>> >>>
>>>>>>>> >>> HSSFWorkbook workbook = new XSSFWorkbook();
>>>>>>>> >>>
>>>>>>>> >>> // Cell styles. Note the setLocked(true) method call.
>>>>>>>> >>> HSSFCellStyle lockedNumericStyle = workbook.createCellStyle();
>>>>>>>> >>> lockedNumericStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
>>>>>>>> >>> lockedNumericStyle.setLocked(true);
>>>>>>>> >>>
>>>>>>>> >>> HSSFSheet sheet = workbook.createSheet("Protection Test");
>>>>>>>> >>> HSSFRow row = sheet.createRow(0);
>>>>>>>> >>> HSSFCell cell = row.createCell(0);
>>>>>>>> >>> cell.setCellValue(100);
>>>>>>>> >>> cell.setCellStyle(lockedNumericStyle);
>>>>>>>> >>>
>>>>>>>> >>> // This line should cause all locked cells to be protected,
>>>>>>>> >>> // the user should not be able to change the cells
>>>>>>>> >>> // contents.
>>>>>>>> >>> sheet.protectSheet("password");
>>>>>>>> >>>
>>>>>>>> >>> The password makes it possible to remove the protection from
>>>>>>>> the sheet
>>>>>>>> >>> and makes it possible then for the locked cells to be modified.
>>>>>>>> >>>
>>>>>>>> >>> Data Vaidations are a different feature; they are used to
>>>>>>>> ensure that
>>>>>>>> >>> the value a user enters into a cell is within a specified range
>>>>>>>> of
>>>>>>>> >>> values.
>>>>>>>> >>>
>>>>>>>> >>> Hope that helps
>>>>>>>> >>>
>>>>>>>> >>> Yours
>>>>>>>> >>>
>>>>>>>> >>> Mark B
>>>>>>>> >>>
>>>>>>>> >>>
>>>>>>>> >>>
>>>>>>>> >>> Nagineni wrote:
>>>>>>>> >>>>
>>>>>>>> >>>>
>>>>>>>> >>>> Hi,
>>>>>>>> >>>>
>>>>>>>> >>>> Can any one help me to make excel cell as read only.After
>>>>>>>> creating
>>>>>>>> >>>> workbook using POI,want make sure some of the columns in a
>>>>>>>> sheet are
>>>>>>>> >>>> read only.
>>>>>>>> >>>>
>>>>>>>> >>>> I'm using following code to achieve this but it not showing
>>>>>>>> the cell
>>>>>>>> >>>> value.But when I try to edit the cell it is prompting error.
>>>>>>>> >>>>
>>>>>>>> >>>>
>>>>>>>> >>>> CellRangeAddressList addressList = new CellRangeAddressList(3,
>>>>>>>> 3, i,
>>>>>>>> >>>> i);
>>>>>>>> >>>> DVConstraint dvConstraint =
>>>>>>>> >>>> DVConstraint.createExplicitListConstraint(new String[]
>>>>>>>> >>>> {"ReadOnlyCell"});
>>>>>>>> >>>> HSSFDataValidation dataValidation = new
>>>>>>>> HSSFDataValidation(addressList,
>>>>>>>> >>>> dvConstraint);
>>>>>>>> >>>> dataValidation.setSuppressDropDownArrow(true);
>>>>>>>> >>>> dataValidation.setEmptyCellAllowed(false);
>>>>>>>> >>>> dataValidation.createErrorBox("Error", "Cell can not be
>>>>>>>> editable");
>>>>>>>> >>>> sheet.addValidationData(dataValidation);
>>>>>>>> >>>>
>>>>>>>> >>>> Please let me know if any other solutions for this.
>>>>>>>> >>>>
>>>>>>>> >>>> Regards,
>>>>>>>> >>>> Naga.
>>>>>>>> >>>>
>>>>>>>> >>>>
>>>>>>>> >>>>
>>>>>>>> >>>>
>>>>>>>> >>>
>>>>>>>> >>>
>>>>>>>> >>
>>>>>>>> >>
>>>>>>>> >
>>>>>>>> >
>>>>>>>>
>>>>>>>>
>>>>>>>> -----
>>>>>>>> Regards,
>>>>>>>> Naga.
>>>>>>>> --
>>>>>>>> View this message in context:
>>>>>>>> http://www.nabble.com/How-to-make-readonly-cell-tp24367500p24384814.html
>>>>>>>> Sent from the POI - User mailing list archive at Nabble.com.
>>>>>>>>
>>>>>>>>
>>>>>>>> ---------------------------------------------------------------------
>>>>>>>> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
>>>>>>>> For additional commands, e-mail: user-h...@poi.apache.org
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
--
View this message in context:
http://www.nabble.com/How-to-make-readonly-cell-tp24367500p24457035.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
