# Getting VALID range of Excel Cells in javascript

• Comp Sci
Mod note: The OP has found an answer to his question.
I am validating a text field in javascript in EXTJS 4 framework, based on the selected value of a text box:

Code:
else if (record.get("type") === "CELL_VALUE" && record.get("apply_before") === true)
{
Ext.Msg.alert("Field Validation", "I am Validating the CELL VALUE COMBO VALUE"); //alert message for testing only, this is where validation will occur
}

In this scenario, "type" is a standard combobox and "appy_before" is a checkbox. If type = Cell Value and apply_before is checked, I need to validate a text box (range_from) contains a valid excel cell location, and that a second text box (range_to) is empty and if validation passes, set isValid = true, else isValid = false.

I know part of it should be as follows:

Code:
else if (record.get("type") === "CELL_VALUE" && record.get("apply_before") === true)
{
if (rangeFrom != valid excel cell location**problem here**) || (rangeTo != null)
isValid = false;
}

The only direction I have been given so far is:

ASPOSE function: CellsHelper.cellNameToIndex may be used to get the valid cells

but I have no Idea how to implement this as a solution. Please provide any help that is available.

I am as stuck as I can get at this point, as I have no idea where to go from the code that is posted....

Last edited by a moderator:

Mark44
Mentor
I don't know if this is what you're looking for, but maybe it will be helpful. The Excel Range object has a property named Cells (see http://msdn.microsoft.com/en-us/library/ff196273(v=office.15).aspx). The Cells property represents the cells in a specified range.

The page in the link above has a VBA example that works with the cells in the range A1 to C5.

Code:
Worksheets("Sheet1").Activate
Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True

The first parameter in Cells is the row number, and the second is the column number, with A = 1, B = 2, and so on.

I don't know anything about the EXTJS 4 framework, but if it works with Excel, it ought to expose ranges and cells.

I guess the framework really doesn't affect the question. The issue is that I have a textbox that a user can enter a value into (rangeFrom). When the user hits the save button, I need to check and see if the value entered is a valid excel cell location where the value can be anything from

A1 (first possible valid Cell in a worksheet)

to

XFD1048576 (last possible valid Cell in a worksheet)

Solution

Code:
function validateRange(coordinate) {
// A little input validation
if (typeof coordinate != "string" || !coordinate.length)
return false;
// Find the first occurrence of a digit
var startIndex = coordinate.search(/[\d+]/);
// The column is the part from the beginning up until the first digit
var column = coordinate.substring(0, startIndex).toUpperCase();
// The row is the remainder of the string
var row = parseInt(coordinate.substring(startIndex), 10);
// The column is sortable alphabetically so we can check its range,
// and the row is numeric so we can check it's range as well
return (column >= "A"   && row >= 1) &&
(column <= "XFD" && row <= 1048576);
}

A friend on stack overflow (no link for respect for this site) provided me with this solution that worked perfectly, but I don't know how to mark a question as resolved on this forum.

Last edited:
Mark44
Mentor
There isn't a way to do it, but I added a note in the first post.