We are currently researching excel .net objects to see if we can find one that fits our needs. It’s seems yours has mostly everything except range/ranges validation. We must have this feature in order to consider purchasing Aspose Excel. Your USA tech informed me that you might be willing to add this feature. I noticed somewhere on your web site that you we’re considering adding range validation in April of 2004, but it didn’t happen. Anyway, we need to know right away as we are short on time. Thanks for your efforts.
Wayne R. Worthen
Senior Software Engineer
Washington Group Int.
208-386-7090
Dear Wayne,
Do you mean the “Data Validation” feature in MS Excel? Now currently Aspose.Excel supports it in designer speadsheets. We did plan to make the APIs to support it at run time in April of 2004. However few users ask for this feature so we postponed it to meet other more urgent feature request.
Could you tell me your expected date for this feature? I have to make some investigation to see if I can meet your need. Thanks.
The expected date is right away, meaning as soon as possible. We have to deliver our software product in 2 weeks.
I will try my best to provide this feature within 2 weeks but cannot shorten the schedule any more.
We are in the process of evaluating your component and Validation ranges are a requirement for our use case as well. Can you give an update on this? Our timing is before 10/25.
Thanks!
Kris
Hi Kris,
I am still working on this issue. I will publish a new release before 10/25. Now thanks for your patience.
Now data validation APIs are available in Aspose.Excel. Please download it and check http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Validation.html.
However, Date,Time and Custom validation type are not supported yet. They will be available in the future release.
Laurence,
I’m trying to follow the API for validation ranges and having several issues, I’m unable to follow. All of my validations use the “List” option of Excel. I’m trying to perform the simplest of my validation tasks first. The first task is to create a column where each row has the same validation list which is a pre-defined range name.
My first attempt was to locate the existing validation in the designer file and then add the new range to the list of areas, here is what the code looked like:
private void DefineDataValidation(Worksheet w, int rowCount, byte columnNumber)
{
Validations validations = w.Validations;
Validation MfrValidation = null ;
foreach (Validation val in validations)
{
if (val.Formula1 == “=MfrNames” )
{
MfrValidation = val ;
break ;
}
}
if (MfrValidation != null)
{
CellArea area ;
area.StartRow = 1 ;
area.EndRow = rowCount ;
area.StartColumn = columnNumber ;
area.EndColumn = columnNumber ;
MfrValidation.AreaList.Add(area) ;
}
}
When attempting to save after this attempt I got the inner exception of:
Destination array was not long enough. Check destIndex and length, and the array’s lower bounds.
at System.Array.Copy(Array sourceArray, Int32 sourceIndex, Array destinationArray, Int32 destinationIndex, Int32 length)
at Aspose.Excel.Record.aj.a(Validation A_0)
at Aspose.Excel.Worksheet.c(bn A_0)
at Aspose.Excel.Worksheets.d(bn A_0)
at Aspose.Excel.Worksheets.a(UCOMIStream A_0)
at Aspose.Excel.Record.c5.a(c3 A_0, t A_1, Worksheets A_2)
at Aspose.Excel.Worksheets.a(String A_0, SaveType A_1, FileFormatType A_2, HttpResponse A_3)
So then I went with an approach of defining a new validation object from scratch. Here is the code for it:
private void DefineManufacturerDataValidation(Worksheet w, int rowCount, byte columnNumber)
{
Validations validations = w.Validations;
Validation MfrValidation = null ;
Validation validation = validations[validations.Add()];
validation.Type = Aspose.Excel.ValidationType.List;
validation.Operator = OperatorType.None;
validation.ShowInput = false ;
validation.IgnoreBlank = true ;
validation.InCellDropDown = true ;
validation.AlertStyle = ValidationAlertType.Warning ;
validation.ErrorMessage = “Value provided is not a valid value per the current KSD.” ;
validation.Formula1 = “=MfrNames”;
CellArea area ;
area.StartRow = 1 ;
area.EndRow = rowCount ;
area.StartColumn = columnNumber ;
area.EndColumn = columnNumber ;
validation.AreaList.Add(area) ;
}
This code appears to work without error, but when I open the spreadsheet after saving, the validation appears on the entire column, but the formula (Source in Excel) is set to a different value than this code defines (the other options defined here seem to be properly set). This is the only code I have that does any
validation definition, so not sure where it is picking up that formula from??
I have another question, but will post it in a separate thread.
Kris
Laurence,
The next task I have for Data validation requires many individual cells to have different validation lists. Something like each cell in column “F” will need a validation list, but row 1, 7, 14, 21 use list “A” and 2, 50, 70, use list “B” and so on in a seemingly random fashion.
I have already defined range names for each of the value lists needed for all validations (these will be the Source property of each cell’s validation). The question I have, is how would I setup the validations using the API. There seems to be a couple of approaches that might work, but not sure if I’m on the right track.
1) Loop through the list of excel.Worksheets[0].Validations and compare the formula value to the range required for the current cell. If found, then create a new CellArea for the single cell and add it to the validation.AreaList.Add. If not found, then create a new validation and add the single cell area list.
2) Bypass the loop checking for existence and just create a new validation and add a single cell AreaList to the validation.AreaList.
I will probably have a few hundred different validation list settings (all the same except for the range used in the Source property of the validation. Some guidance on how to do this would be appreciated!
Kris
Hi Kris,
The reported validation bugs are fixed in the latest hotfix. Please download it at
Since the bugs are fixed, now your code can work to meet your two requirements.
Laurence,
I sent you an email to the support email address with a test case. Seems the new hotfix is close, but still having an issue. The validation appears to be doing the right thing, but the list of values inside Excel doesn’t render unless I open the validation dialog and click OK (without making any changes to a setting).
Kris
Laurence,
I emailed a second more complex test case, that generates to completion, but results in a corrupt Excel file. Hope you can help once again!
Kris
Hi Kris,
It’s fixed. Please download hotfix at
And I also find in your test case, some undefined named range are assigned to data validations. Please check it.
Laurence,
Thanks once again for the quick turnaround!
Kris
kris_k wrote:
Laurence,
I sent you an email to the support email address with a test case. Seems the new hotfix is close, but still having an issue. The validation appears to be doing the right thing, but the list of values inside Excel doesn't render unless I open the validation dialog and click OK (without making any changes to a setting).
Kris
I am having a similar problem (except that the Data Validations are not being set up by Aspose.Excel). The cells with Data Validation are set up as dropdown lists beforehand and Aspose.Excel is simply copying the workbook from one location to another and inserting some text in a cell on the first worksheet. The result is when I open up the workbook in its new location the drop downs will not function unless I select the cell with Data Validation and then select Data > Validation from the menu and click on OK without making any changes.
Any idea what I should do?
Thanks,
Theo
Hi Theo,
Do you use the latest v3.5.3? Could you please post your sample code and template file here? I will check it ASAP.
I downloaded the latest version (3.5.3) and it worked.
Thanks,
Theo