Searching an Excel Sheet with JavaScript

Author Posted by on October 10, 2011

Comments 25 Comments

This took some significant amount of time to put together when it really shouldn’t have. Sharing the code here to save anyone else the trouble.

Objective

We will be trying to set up an HTML page that allows users to input a search phrase and trigger a search through an Excel sheet with data.

Approach

  1. Set up the excel file
  2. Set up the HTML page with the search field
  3. Set up the Javascript code

The Excel File

Create an Excel file and fill in column A with values of your choice. Save the file as “example.xls” in your local C:\ folder.

Setting up the HTML Page

Create an html file and open it in your favourite text editor; e.g. “index.html”.

In your text editor create an input field:

<input type="text" name="searchPhrase" />

<a href="javascript: searchExcel();">Search</a>

<div id="results"></div>

The first line creates the input box with the name “searchPhrase”, and the second creates a link that triggers our JavasScript with the function searchExcel. Results will be displayed in the division labelled “results”.

The JavaScript Function

function searchExcel()
{
	var searchPhrase = document.getElementById('searchPhrase').value;
	var Worksheet = 'C:\\example.xls';
	var Excel = new ActiveXObject('Excel.Application');

	Excel.Visible = false;
	var Excel_file = Excel.Workbooks.Open(Worksheet, null, true, null,
        "abc", null, true, null, null, false, false, null, null, null);

	var range = Excel_file.ActiveSheet.Range('A:A');
	var jsRangeArray = new VBArray(range.Value).toArray();

	var found = false;
	for(cells in jsRangeArray)
	{
		if(jsRangeArray[cells] == searchPhrase)
		{
		   document.getElementById("results").innerHTML = "Found";
		   found = true;
		}
	}

	if(found == false)
	{
	    document.getElementById("results").innerHTML = "Not Found";
	}

	Excel.ActiveWorkbook.Close(true);
	Excel.Application.Quit();
	Excel = null;
}

The code is pretty simple really. We’ll go line by line:

  • First the value in the input field labelled “searchPhrase” is retrieved.
  • The worksheet path is defined as “C:\\example.xls” – the double slash is crucial.
  • A new ActiveX object is created – the Excel Application.
  • The Excel object is marked as not visible.
  • The Excel workbook is opened into the variable Excel_file – you can look up the function definition here on MSDN.
  • The range is defined as all cells in column “A” – denoted by Range(‘A:A’);
  • The results of the cells extracted are converted into an Arraya that we will use as variable jsRangeArray.
  • A new variable “found” is set up to trigger a true or false result for the search.
  • We then loop through the Array matching values in the Arraya with the search term.
  • If the searchPhrase value matches a value in the Array, the found variable is marked as true and the “results” div is transformed to show “Found”.
  • If the loop ends and nothing is found, the “results” div is transformed to show “Not Found”.
  • The excel book and the ActiveX object are then closed.

Category Category: Code

Tag Tags: , ,

25 Comments on “Searching an Excel Sheet with JavaScript”

  1.  by  AnxiousNut

    Why .xls, why not .csv?! And why is example.xls password protected?!

    Anyways, I deleted the password protected file and created “example.xls” of my own. The unexpected thing is that it didn’t work!

    … I opened the .html and noticed the dummy windows path “C:\\example.xls”; changed it to I changed to “example.xls” then gave it another try. It still didn’t work!

    I’m no js expert so my only guess would be compatibility; is the code cross-platform? Not talking about js, but rather the ‘Excel.Application’.

    Reply
    •  by  Salem

      Whoops!

      Thanks for pointing that out! I’ll fix that as soon as I’m home and remove the pass. I pass protected it because the function has an option to access pass protected files without prompt.

      Yes it’s platform dependent: Internet Explorer 🙁 because it runs an ActiveX Control which is platform dependent.

      It works I promise :] tried and tested!

      Reply
    •  by  astha gaur

      I run in Internet Explorer still it’snot working

      Reply
  2.  by  AnxiousNut

    I pass protected it because the function has an option to access pass protected files without prompt.

    Wow, are you saying that JS+ActiveX can open any password-protected excel file without the need of the password?!

    Yes it’s platform dependent: Internet Explorer 🙁 because it runs an ActiveX Control which is platform dependent.

    Uh, hmmm, well, this is sad, this means I can’t try it out; I don’t run windows. … Unless if MS decides to make an IE for Linux, of course! Though that would be stupid and funny! XD

    a .csv parser is easy to write, would you like to see it?

    The reason why I said .cvs is because it’s the simplest format that is compatible and supported in most spread-sheet applications. Meaning that whichever spreadsheet format a user is using (.xls, .xlsx, .ods, .sdc, … etc), he/she can export it to .cvs and use a single code instead writing one for each format. And since yours only searches, I didn’t think it required any excel-specific features.

    Reply
    •  by  Salem

      It requires a password, you pass it to the function (you can encrypt it).

      I agree about the platform dependency but the requirement was for people who only use IE, who only know how to use Excel 2003 and at a basic level, and require a search without enabling access to the excel file. Asking them to export to CSV is complicated :]

      Putting the user first ;]

      Reply
  3.  by  AnxiousNut

    Not sure about MS office, but in libre/open office, all one has to do in order to have a .csv file is selecting “save as” from the file menu then change the extension to .csv; That isn’t complicated!

    But sure, whatever makes them happy.

    Reply
  4.  by  SevesSeas

    Hi that works, you just need to write the path in a different way:

    file:///C:/Users/SevenSeas.SevenSeas/Desktop/example/example/example.xls

    However is there anyway to make the html bring a row starting with the value? Like I am gonna seach and the row starting with 1 in excel (1,a,b,c) will be displayed in html like 1 a b c.

    Thanks
    7Seas

    Reply
  5.  by  Deviprasanna

    I want to use an excel sheet on a shared drive. Do I need to give the file path in any specied format. I tried to use the above script in my own html but I get an “error on page”. I have no idea how to debug it. Please help!

    Reply
  6.  by  Jax Paradize

    You might want to write out the whole path if its not within the same folder or shared drive. Like so, H:drive1/drive2/drive3/filename.cvs (or whatever extension you have) Whenever you build a site, make sure you put your information that you’re feeding the site within a folder near by, that way you don’t have to write out the entire path, lets say it was a link, it would look something like this, I dont know if you know this but the “../” tells the system you want to go back one folder. so doing it twice will go back twice. The error on page, double click on that and see what the error is about and what line its on. I would need more info to find out what’s wrong, if its your js or not.

    Reply
  7.  by  Jaxx

    Your code above works fine. I was wondering if you had anything that took it a step further and allowed you not only to search for data, but also allowed you to change the cells. Not only the cell the information was in, but the sells in that same row? I have a database in excel (I know its not ideal but its what I have to work with, not my call) and right now its login entries in from a webform. I use a search that sends data to a cell in excel and then excels finds the info. once it finds it and displays it in specific cells, javascript then pulls the info to display on the webform. I know its jimmyrigged but its the best I can do for the time frame I had and the code I had on me. any idea’s on how I can make it so the webform will search and display information that is not in a specific cell, AND allow me to change the data on that row, save it and close the wb while all being hidden. (I know how to do the little things, but its the editing the cells that exist in excel that is the issue. Any thoughts?

    Reply
  8.  by  Mike

    Hi Salem, Thank you for the script it is very helpful and I have seen many people re post this script on various forums. I know that this thread may be dead, but I was wondering if there was a way to display the results instead of just “Found”

    Thank you very much

    Reply
    •  by  Salem

      Hi!

      I am surprised that this snippet is still being used, it was written for an older version of Excel. But Yes, in principle you can just output the result by changing the for loop to print:

      document.getElementById(“results”).innerHTML

      Reply
      •  by  Mike

        Hi Salem,

        Thank you for the fast response! You have been a very valuable resource. I am sorry, but I am new to HTML and JS. I am an intern at UCCS and have been asked to develop a web page that can search the excel document that houses the Universities’ energy and water data.
        Currently for the line:

        “document.getElementById(“results”).innerHTML”

        I have: document.getElementById(“results”).innerHTML = “found”; found = true;
        from your original code.
        I have searched many blogs and help forums and I cannot find the way to relate the displayed information to the actual search results found on the excel document. I can only return a message or simple equation that I fill in for “found” when the search matches the excel document.
        What is the code I need to add and what do I need to replace/ amend to have all the search results found in the excel document displayed on screen? I have found the ‘for’ line and do not understand how I need to change it.
        I am sorry for my lack of knowledge, I have only taken one entry level internet programing class and we focused on Google Sites and Fusion Tables to make online maps. So my boss thought I was qualified for this job, lol.

        Thank you so much for your help,

        Mike

        Reply
  9.  by  Eva

    Hello admin do you need unlimited content for your site ?
    What if you could copy article from other websites,
    make it unique and publish on your website – i know the right
    tool for you, just search in google:
    kisamtai’s article tool

    Reply
  10.  by  Nikhil Kumar

    Your code is working effortlessly. Thanks for that.
    Anyway to search across different sheets in a single excel file?
    Please reply.

    Reply
  11.  by  karthik

    I have downloaded the attached pack file and executed the example.html and search the value “1” but it not working kindly suggest

    Reply
  12.  by  Jhon

    Sir I Am Working On A Project Of Scouts In Which I Can Search Name Of Students In They Show Their Points But Its Not Working Plz You Can Send Me Orignal Code

    Reply
  13.  by  Benedetto

    First let me say i haven’t been able to make this work in anyway i tried. Let’s say i figured it out, i do have a question, you say “C:\\example.xls” – the double slash is crucial ” what if i want to read the xls file from the web, how would i write the address?:

    https://www.website.com/example/\\example.xls

    Reply
  15.  by  suheal

    Hello thanks for the code, I wanted to search on a different sheet of same workbook how to do it? ?

    Reply
  16.  by  Max

    I’ve tried your script and it does display found, but I’d like to display the results of the search. Could you please show me how? I tried the example above, document.getElementById(“results”).innerHTML and that didn’t return anything.

    Thank you,
    Max

    Reply
  17.  by  JP

    I’m unable to get this code to work. I tried both upload the xls file to my host site and to Google Drive. Enter the info and nothing happen

    Reply
  18.  by  Manish

    @ All….. Nothing Is Working Here… Tried All Suggested Paths And Browsers But No Outpoyt. Please Help Me Whats Im Doing Wrong.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *