Dynamic Range - Used as list for data validation

Aspose.Cells Product Family
1

Hi,

Sample file attached.

2 worksheets, 'Departments' and 'Employees'

Values column A of the 'Departments' worksheet are used as the source for a data validation list in the 'Employees' worksheet. I'm using a drop down list in the Employees worksheet so the user doesn't have to re-type the department name, which could lead to typo errors.

I can create a named range to cover the department values I know about when the spreadsheet is created, but I'd like to allow the user to add new departments, and for that new department to automatically appear in the drop down list on the employees tab.

Formula used to create the dynamic range on the Departments worksheet is as follows

=OFFSET($A$2, 0, 0, COUNTA($A:$A), 1)

Is this possible using Aspose.Cells?

Mat

2

Hi,


Well, I think you may use List data validation, Aspose.Cells does support all the validation types that MS Excel supports. Please see the following sample code, I have dynamically created data list validation, I used your Excel file as the input source and applied the validation on A3:A6 cells in the second sheet.

Sample code:
Workbook workbook = new Workbook(“e:\test2\DynamicRangeExample.xlsx”);

// Get the first worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];

//Get the second worksheet
Worksheet worksheet2 = workbook.Worksheets[1];

// Get the validations collection.
ValidationCollection validations = worksheet2.Validations;

// Create a new validation to the validations list.
Validation validation = validations[validations.Add()];

// Set the validation type.
validation.Type = Aspose.Cells.ValidationType.List;

// Set the operator.
validation.Operator = OperatorType.None;

// Set the in cell drop down.
validation.InCellDropDown = true;

// Set the formula1.
validation.Formula1 = “=DepartmentNames”;

// Enable it to show error.
validation.ShowError = true;

// Set the alert type severity level.
validation.AlertStyle = ValidationAlertType.Stop;

// Set the error title.
validation.ErrorTitle = “Error”;

// Set the error message.
validation.ErrorMessage = “Please select a valid department”;

// Specify the validation area A3:A6.
CellArea area;
area.StartRow = 2;
area.EndRow = 5;
area.StartColumn = 0;
area.EndColumn = 0;

// Add the validation area.
validation.AreaList.Add(area);

// Save the Excel file.
workbook.Save(“e:\test2\outvalidationtypelist1.xlsx”);

And, please see the document for your complete reference on Data Validation and how you can use them using Aspose.Cells APIs:
http://www.aspose.com/docs/display/cellsnet/Data+Filtering+and+Validation

Thank you.

3

Hi Amjad - Thanks for the reply.

Unfortunatley, I don't think I've correcty explained what I was trying to do.

It's the code for creating the range that I need. How do I create a range based on a formula rather than a fixed size?

The CreateRange method has 4 overloads

  • (string address)
  • (string upperLeftCell, string lowerRightCell)
  • (int firstIndex, int number, bool isVertical)
  • (int firstRow, int firstColumn, int totalRows, int totalColumns)

None of these seem to accept the formula "=OFFSET($A$2, 0, 0, COUNTA($A:$A), 1)"

The range needs to be of a dynamic length, rather than being fixed to A3:A6, so that when the user adds a new value into the range (into cell A7, A8 etc), it is automatically available in the list.

Mat

4

Hi,


Thanks for providing us more details.

Well, you may use Name object to define the named ranges that refers to specific formulas/range etc. See the sample code for your reference:

Sample code:


Workbook workbook = new Workbook();
NameCollection names = workbook.Worksheets.Names;
Name name = names[names.Add(“DepartmentNames”)];
name.RefersTo = stringFormula;
workbook.Save(@“e:\test2\out1.xlsx”);


Thank you.
5

Hi Amjad,

Thanks for the reply and sample code - it was exactly what I was looking for.

I now have one more question. On the Employees tab, I'm using a data validation list to ensure a valid department is selected.

The Validation class has an 'AddArea' method that accepts a CellArea class to determine which cells will be validated. The cellArea needs to be hard coded to a particular range of cells (e.g. A2:A1000), however, I don't know how many Employees will be added to the spreadsheet.

I could set the cellArea to a fixed size of A2:A1000, but then what happens if I have 1001 employees?

Is there a way to pass a range to the Validation class?

Mat

6

Hi,


Well, Aspose.Cells follows MS Excel standards. In MS Excel, you have to select or specify the celllarea range where you need to apply data validation, therefore you have to do the same for Aspose.Cells, i.e. create the cell area based on your cells range accordingly.

I think you may try to do the following:

1) If your Excel file is XLS (Excel 97-2003), your maximum row would be 65536 (this is the last row in the sheet).

2) If your Excel file is in the XLSX file format (Excel 2007/2010 or newer versions), then you may set it more i.e. upto 1048576.

Hope, it helps you a bit.

Thank you.