Getting VALID range of Excel Cells in javascript

  • Comp Sci
  • Thread starter iamjon.smith
  • Start date
  • #1
117
3
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:

Answers and Replies

  • #2
35,224
7,042
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.
 
  • #3
117
3
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)
 
  • #4
117
3
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:
  • #5
35,224
7,042
There isn't a way to do it, but I added a note in the first post.
 

Related Threads on Getting VALID range of Excel Cells in javascript

  • Last Post
Replies
2
Views
2K
  • Last Post
Replies
3
Views
3K
  • Last Post
Replies
13
Views
4K
  • Last Post
Replies
2
Views
8K
  • Last Post
Replies
3
Views
770
  • Last Post
Replies
0
Views
8K
Replies
11
Views
1K
Replies
12
Views
841
Replies
8
Views
2K
  • Last Post
Replies
3
Views
1K
Top