1. Limited time only! Sign up for a free 30min personal tutor trial with Chegg Tutors
    Dismiss Notice
Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Getting VALID range of Excel Cells in javascript

  1. Apr 4, 2014 #1
    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 (Text):
    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 (Text):
    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: Apr 4, 2014
  2. jcsd
  3. Apr 4, 2014 #2

    Mark44

    Staff: 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 (Text):
    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.
     
  4. Apr 4, 2014 #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)
     
  5. Apr 4, 2014 #4
    Solution

    Code (Text):
    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: Apr 4, 2014
  6. Apr 4, 2014 #5

    Mark44

    Staff: Mentor

    There isn't a way to do it, but I added a note in the first post.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: Getting VALID range of Excel Cells in javascript
  1. Excel question (Replies: 1)

  2. Valid connections (Replies: 3)

  3. Excel Help (Replies: 3)

Loading...