Add validation not working on cell with existing validation

Hi,

Following method is not working on a cell which has a validation already defined. In the sample attached sheet; I've added a list type of validation (have tested with other validation types also). When I remove the existing validations from the cell; it starts working! Am I missing something here?

Workbook workbook = new Workbook();

workbook.Open(@"C:\test\Book1.xls", FileFormatType.Excel97To2003);

Validation ValCell = sheet.Validations[sheet.Validations.Add()];

ValCell.InputTitle = "Heading";

ValCell.InputMessage = "Test1";

ValCell.ShowInput = true;

CellArea area;

area.StartColumn = sheet.Cells["E8"].Column;

area.EndColumn = sheet.Cells["E8"].Column;

area.StartRow = sheet.Cells["E8"].Row;

area.EndRow = sheet.Cells["E8"].Row;

ValCell.AreaList.Add(area);

workbook.Save(@"C:\test\Book2.xls");

Hi,

I have found the issue after an initial test using your sample code and template file.

We will figure it out soon.

Your issue has been logged into our issue tracking system with an issue id: CELLSNET-16664.

Thank you.

Hi Amjad,

We need a quick fix on that please. We are in UAT stage, and need a work around if your team is going to take some time to fix this issue.

Regards, Vinay

Hi,

Please try the attached latest version/fix v4.9.1.1. You need to remove the old validation settings on the cell with a single line of code (before adding the area list), see the following sample code here that works absolutely fine.

Sample code:

Workbook workbook = new Workbook();
workbook.Open(@“e:\test\Book1.xls”, FileFormatType.Excel97To2003);
Worksheet sheet = workbook.Worksheets[0];
Validation ValCell = sheet.Validations[sheet.Validations.Add()];
ValCell.InputTitle = “Heading”;
ValCell.InputMessage = “Test1”;
ValCell.ShowInput = true;
CellArea area;
area.StartColumn = sheet.Cells[“E8”].Column;
area.EndColumn = sheet.Cells[“E8”].Column;
area.StartRow = sheet.Cells[“E8”].Row;
area.EndRow = sheet.Cells[“E8”].Row;
sheet.Validations.RemoveArea(area);
ValCell.AreaList.Add(area);
workbook.Save(@“e:\test\Book2.xls”);


Thank you.

Hi Amjad,

I don't want the old validation to be removed! The validation added there is of different type(List Type). If the validation type was of input message then the approach mentioned above is alright.

But above mentioned approach will not work, if the cell already has a cell drop down added!

Regards, Vinay

Hi Vinay,

We are not sure about your actual need. Do you want to modify an existing validation on a cell? please confirm us here. Also, give us more details about your issue.


Thank you.

Hi Amjad,

Requirement is very simple. There is a cell which has a validation already added to it. I want to add an input message to it and it's is not working. Also, I had mentioned in the first post that when I remove the existing validation; it works fine.

Following is the summary of an requirement.

1. We are able to add a input validation text to a cell which doesn't have any validations added to it.

2. We are not able to add Input Validation text to a cell which has a validation already added to it. So for example, in the sample I provided earlier : There is a "Validation" to allow List of values "A" and "B" already added to cell "E8" at design time. Now we want to add a input message to that cell dynamically. This scenario is not working!

PS: May be you are right (This would be a case of validation edit), when the validation exists on a cell - Then I would like to edit it to add/update it to show Input Text else I would like to add a new Input Text to it.

Regards, Vinay

Hi,

Well, for your need, you need to modify an existing validation settings. So, you should get the existing validation settings and then directly modify it for your need, see the following sample code:

Workbook workbook = new Workbook();
workbook.Open(@“e:\test\Book1.xls”, FileFormatType.Excel97To2003);
Worksheet sheet = workbook.Worksheets[0];
Validation ValCell = sheet.Validations[0];
ValCell.InputTitle = “Heading”;
ValCell.InputMessage = “Test1”;
ValCell.ShowInput = true;


workbook.Save(@“e:\test\Book2.xls”);


Thank you.

One quick question here. Is there a way to find out the validations added to a current cell only?

As per my understanding Sheet.Validation collection will return all the validations added to the sheet. i.e. Cell A1 can have different validations, A2 can have different validations. Hence using Sheet.Validations[0] may not refer to the validation added to cell "E8". Please corrent me, if I am wrong.

Thanks, Vinay

Hi,

Well, you need to to compare the AreaList by yourself for your validation settings.

e.g
Validation ValCell = sheet.Validations[0];

ArrayList al = new ArrayList();

al = ValCell.AreaList;

CellArea area;
//Define some variables
int frow, fcol, erow, ecol;
//Loop through the arraylist and get each cellarea
for (int i = 0; i < al.Count; i++)
{
area = new CellArea();
area= (CellArea)al[i];
frow = area.StartRow;
fcol = area.StartColumn;
erow = area.EndRow;
ecol = area.EndColumn;

}


Thank you.

Hi,

Please see the following sample code for your reference:


e.g
Validation ValCell =
sheet.Validations[0];

ArrayList al = new ArrayList();


al = ValCell.AreaList;

CellArea area;

//Define some variables
int frow, fcol, erow, ecol;

//Loop through the arraylist and get each cellarea
for
(int i = 0; i < al.Count; i++)
{

area = new CellArea();
area= (CellArea)al[i];

frow = area.StartRow;
fcol = area.StartColumn;

erow = area.EndRow;
ecol = area.EndColumn;


}


Thank you.

You mean,

1. Loop through each validation in the sheet.

2. Gets a list of areas attached to this validation and loop.

3. Check, if its area is same as current area then update the validation else add a new validation?

Can't we get the validations attached to a Cell object?

Thanks, Vinay

Hi,

Please see the code segment:
public static Validation AddValidation(Worksheet sheet,int row,int column)
{
for (int i = 0; i < sheet.Validations.Count; i++)
{
Validation v = sheet.Validations[i];
for (int j = 0; j < v.AreaList.Count; j++)
{

          CellArea ca = (CellArea)v.AreaList[j];
if (row >= ca.StartRow && row <= ca.EndRow
&& column >= ca.StartColumn && column <= ca.EndColumn)
{
return v;
}
}
}
int index = sheet.Validations.Add();
Validation validation = sheet.Validations[index];
CellArea area = new CellArea();
area.StartRow = row;
area.StartColumn = column;
area.EndRow = row;
area.EndColumn = column;
validation.AreaList.Add(area);
return validation;
}


Well, I am afraid, there’s no simple way to get the validation setting that applies to a cell object. We have to check all validations in the worksheet to get/implement it too.

Thanks for your understanding!


Alright, Thanks.