Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    JPP
    JPP is offline
    New Coder
    Join Date
    Jan 2012
    Posts
    28
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Using JS to compare numbers against a predefined list in Excel.

    Is it even possible to use JS to check an external Excel file, compare, and return a value based on if the number has been used already?

    If the number has been used, return FALSE.

    If the number has not been used, proceed.


    Any help would be greatly appreciated!

  • #2
    Regular Coder
    Join Date
    May 2012
    Location
    France
    Posts
    216
    Thanks
    0
    Thanked 29 Times in 27 Posts
    It's possible to save an Excel file as text file (comma separated value file) and to paste It's content in a textarea to work on it's values.
    With a server side language, It's too possible to upload this file. Then this file can be display on HTML pages...

  • #3
    Senior Coder rnd me's Avatar
    Join Date
    Jun 2007
    Location
    Urbana
    Posts
    4,188
    Thanks
    10
    Thanked 569 Times in 550 Posts
    Quote Originally Posted by JPP View Post
    Is it even possible to use JS to check an external Excel file, compare, and return a value based on if the number has been used already?
    absolutely, you may have heard of ajax, which is what you describe. there are several methods of doing this, depending on your setup and needs. I'd recommend making it easier by saving the excel as a CSV file, or uploading it to the cloud to live as a google docs url. This will open the door to non-ie browsers and people without Office on their device to use your app.

    a few questions:
    who has to use the app?
    where does the data live and come from?
    does it have to be secure (passworded)?
    my site (updated 13/9/26)
    BROWSER STATS [% share] (2014/1/19) IE7:0.2, IE8:6.7, IE11:7.4, IE9:3.8, IE10:4.4, FF:18.3, CH:43.6, SF:7.8, MOBILE:27.5

  • #4
    Senior Coder rnd me's Avatar
    Join Date
    Jun 2007
    Location
    Urbana
    Posts
    4,188
    Thanks
    10
    Thanked 569 Times in 550 Posts
    Quote Originally Posted by 007julien View Post
    It's possible to save an Excel file as text file (comma separated value file) and to paste It's content in a textarea to work on it's values.
    With a server side language, It's too possible to upload this file. Then this file can be display on HTML pages...
    you can cut and pasted a copied range directly from excel to a textarea, no need to use a file if the mouse will be involved anyway...
    my site (updated 13/9/26)
    BROWSER STATS [% share] (2014/1/19) IE7:0.2, IE8:6.7, IE11:7.4, IE9:3.8, IE10:4.4, FF:18.3, CH:43.6, SF:7.8, MOBILE:27.5

  • #5
    JPP
    JPP is offline
    New Coder
    Join Date
    Jan 2012
    Posts
    28
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Licensed Insurance Agents use the program.
    It HAS to be secure.

    Also, we use 2 different sites to house our information.

    Enrollments are done through inside sales. Which they would pull the number from.


    Our electronic information is house at the APL site. They would have to input the number given to them, in the APL site.


    That is where this gets tricky. And AJAX is not something I am famliar with. JS is testing it. Lol.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Think about this problem for a moment:

    Say you have an Excel sheet in Zurich, Switzerland.

    Suppose that one agent in Oslo, Norway, asks the Excel sheet: "What is the next number available?" And the Excel sheet says "773".

    And now a different agent in London, England, askst he Excel sheet: "What is the next number available?" And the Excel sheet says "773".

    And then the user in Oslo says, "Okay, I'm going to use 773". And changes the Excel sheet to say "773 is in use".

    And then the user in London says, "Okay, I'm going to use 773". And changes the Excel sheet to say "773 is in use".

    *** OOPS !!! ***

    First of all, Excel is *NOT* a good thing to use for something like this. Excel is *NOT* a true database, and so it provides no protection against the scenario I just described.

    You truly should be moving to a *REAL* database.

    But if absolutely cannot do that, then you need to install some software on the same machine where the Excel sheet lives that will support atomic actions. That is, you need some server-side code that says "the next number is 773, and I just reserved that number for you, Oslo". So that, when London comes along, that same server-side code will say "the next Number is 774 and I just reserved that number for you, London".

    That is, the "find the next number" and the "reserve that number" code *MUST* be done in such a way that it acts as a SINGLE ACTION. And, as I said, Excel alone is not capable of that.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Senior Coder rnd me's Avatar
    Join Date
    Jun 2007
    Location
    Urbana
    Posts
    4,188
    Thanks
    10
    Thanked 569 Times in 550 Posts
    Quote Originally Posted by Old Pedant View Post
    But if absolutely cannot do that, then you need to install some software on the same machine where the Excel sheet lives that will support atomic actions. That is, you need some server-side code that says "the next number is 773, and I just reserved that number for you, Oslo". So that, when London comes along, that same server-side code will say "the next Number is 774 and I just reserved that number for you, London".
    or just save it to skydrive (which is actually the default in excel now), share it with your workers, and call it an early afternoon...
    my site (updated 13/9/26)
    BROWSER STATS [% share] (2014/1/19) IE7:0.2, IE8:6.7, IE11:7.4, IE9:3.8, IE10:4.4, FF:18.3, CH:43.6, SF:7.8, MOBILE:27.5

  • #8
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,472
    Thanks
    0
    Thanked 634 Times in 624 Posts
    Quote Originally Posted by rnd me View Post
    or just save it to skydrive (which is actually the default in excel now), share it with your workers, and call it an early afternoon...
    That still doesn't prevent two or more people looking up the next available number and all seeing the same one before any of them actually update it to indicate that they have reserved it. Also if one person has it open for update they are then locking everyone else out from being able to do their update.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #9
    Senior Coder rnd me's Avatar
    Join Date
    Jun 2007
    Location
    Urbana
    Posts
    4,188
    Thanks
    10
    Thanked 569 Times in 550 Posts
    Quote Originally Posted by felgall View Post
    That still doesn't prevent two or more people looking up the next available number and all seeing the same one before any of them actually update it to indicate that they have reserved it. Also if one person has it open for update they are then locking everyone else out from being able to do their update.
    that's not the case with the diff algos that google docs and M$ use these days. i looked into it, and the newer gen of operational transforms are pretty bad-***. they can handle wrong clocks, different users being connected or disconnected, same-time updates, don't require locking, and can easily merge what turns out to be non-conflicting changes.

    to pull all that off yourself, you'd probably have to be the kind of guy who wrote a phd on it, like the one google hired...


    edit: to a point, what you're saying is correct, there may be lag on machines and as users are visually checking a number to decide on how to proceed, but i'm saying it doesn't have to be that rickety at all in the first place...
    Last edited by rnd me; 07-04-2013 at 01:29 AM.
    my site (updated 13/9/26)
    BROWSER STATS [% share] (2014/1/19) IE7:0.2, IE8:6.7, IE11:7.4, IE9:3.8, IE10:4.4, FF:18.3, CH:43.6, SF:7.8, MOBILE:27.5

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Well, clearly if you could pull an operation in the spreadsheet the equivalent of JS/Java/C/C++ return currentNumber++; that would be adequate for these purposes. So, yes, if the online sheet supports that, you are home free.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •