Google Answers Logo
View Question
 
Q: Write java script for a webpage that will 1) seach an Excel File 2) return hits ( Answered 5 out of 5 stars,   2 Comments )
Question  
Subject: Write java script for a webpage that will 1) seach an Excel File 2) return hits
Category: Computers > Software
Asked by: ken3141-ga
List Price: $150.00
Posted: 28 Jun 2003 04:10 PDT
Expires: 28 Jul 2003 04:10 PDT
Question ID: 222748
BACKGROUND

I have an Excel file that contains several rows (~360) of data. Each
row has several columns worth of data, such as serial number, name,
and location.

IDEAL ANSWER

Create a webpage with java script that has a "search box."  If I enter
a string (or substring)of numbers, it searches the excel file for a
match or matches.  It then returns a webpage that either notes that
there are no matches, or returns a webpage that contains the entire
row of data for each correct match.  To rephrase, I don't want it to
link to the excel file, I want it to find a match in the rows, then
take that row/those rows of data and present it/them on the returned
page.

HELPFUL NOTES:

-this webpage, the excel file, etc will all be OFFLINE. What I mean
is, it does not need to be able to search an excel file outside of the
same folder that the search page is in.
-there is only ONE Excel file that needs to be searched. 
-the string of numbers to be searched should be able to be a substring
of the serial number, e.g. if I enter 456 it should find the match in
123456789.
-Prefer it to not be case sensitive.
-a "heavily commented" answer is most appreciated, so that if I have
to tweak I'll know what the heck I'm tweaking.
-be aware that this will be used on a PocketPC with Pocket Internet
Explorer.  However, if your answer works on a PC but NOT on a
PocketPC, I'll be OK with that, as I realize that would be asking a
little much. It's a shot in the dark.

CAVEAT NOTE:

If there is some technical reason why this can't be done, such as
"java script can't search an Excel file," please let me know and I'll
consider changing the requirement to searching another
webpage/database and then I'd have to first change the excel file to
that format.

Clarification of Question by ken3141-ga on 28 Jun 2003 06:17 PDT
From some webseraching, the closest I could find was some javascript
code that would search one's site, although it returns links and not a
summary data page.

http://www.htmlgoodies.com/tutors/jsdb.html

Clarification of Question by ken3141-ga on 29 Jun 2003 02:36 PDT
Out of the two comments/options provided, the first on looks like it
would be acceptable if it will work.  The second WOULD work, but since
ultimately it will all be an offline thing on a PocketPC, I don't
think a CGI script will work... correct me if I'm wrong.

I must be an idiot to not think of exporting as csv!  However, I've
found that the excel files we're given have commas in some of the
cells (go figure...)  I can manually remove, but tab separated values
would be preferable if doable, but I doubt it.  CSV will do if need
be, and I can remove the 360 commas...

Anyway, the following changes to the original questions will also be
an acceptable answer:
---------------------
Write a Javascript/HTML page that can do a search of some embedded,
but hidden data on that page and return a page that has the entire
LINE  (or LINES) of data associated with that find (see below for
example) in a table format.  Specifics are:

-data will be coming from an Excel file with multiple cells
(ultimately I'll do that conversion).  Data will be exported as CSVs.
To delimit separate lines, you (meaning you, the answerer of this
question) can state what the delimiting character must be, buit the ~
is preferred. Semicolons and pound signs are not acceptable as they're
all throughout the cells.

---------------------------

EXAMPLE: Say you have the following data hidden on the page, and you
type in 234 as your search string and hit the "enter" button (note:
have an "enter" or "Search Now" button, as a PPC doesn't have a return
key...)  I've used commas as the delimiter in the example, but if tabs
would work that's be better.  Commas will be acceptable for the
answer.

~123456,Bob Metcalf,Seattle, Washington,Blue
~Steve Allen,56 dollars and 35 cents,Car
~xyM2Hz,September,Dallas,Xy234
~George Bush,Jaque Chirac,Starchild,P-Funk Mothership Connection

--------------------------
THE RESULTING PAGE WOULD RETURN A PAGE THAT LOOKS SOMETHING LIKE (I
wrote it in HTML):

----------------------
<HTML>

<BODY>
<H3>Your Search Returned 2 Matches:</H3>

<TABLE BORDER="1" WIDTH="100%" CELLPADDING="0" CELLSPACING="0">
<TR>
	<TD>1<Font COLOR="Red">234</font>56</TD>
	<TD>Bob Metcalf</TD>
	<TD>Seattle</TD>
	<TD>Washington</TD>
	<TD>Blue</TD>
</TR>
<TR>
	<TD>xyM2Hz</TD>
	<TD>September</TD>
	<TD>Dallas</TD>
	<TD>Xy<Font COLOR="Red">234</font></TD>
	<TD></TD>
</TR>
</TABLE>

</BODY>
</HTML>
------------------------

Note that I highlighted the matching part in RED, but if that's too
hard, that's OK, don't worry about it. Maybe I should leave that for
another "question." I know it can be done, and have some Javascript
from a guy in a user group who wrote it up, if you care to see.

Also, if it's too hard to have the script "dynamically" figure out how
many columns to have in the table, it's looking like we've got 5
colums.

Any other questions/clarifications necessary?

Clarification of Question by ken3141-ga on 29 Jun 2003 02:57 PDT
Sorry, one more clarification. Actually a reclarification from the
original question, but it is important: the search should ideally be
case insensitive, meaning 'n123' would find a match in 'XPN123&8j'
Answer  
Subject: Re: Write java script for a webpage that will 1) seach an Excel File 2) return hits
Answered By: j_philipp-ga on 29 Jun 2003 05:35 PDT
Rated:5 out of 5 stars
 
Hi Ken3141,

Happy to hear my suggestion is acceptable to you! Here are the
results. Anything you want to have changed, or which doesn't work for
you, let me know and we'll take it through the clarifications.
Note there is a simple phrase vs keywords differentiation (try
entering _is a_ versus _"is a"_). Also check the comments for things
that can be easily adapted by you (like the highlight style).

------------ "index.html" file follows below ------------

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>CSV Search</title>

<style><!--

body
{
    background-color: white;
    color: black;
    font-family: arial, helvetica, sans-serif;
}

/* The result-found highlight style for
   a matching string, can be changed.
   For example, you could give it a yellow
   background-color (see comment).
*/

.hilite
{
    color: red;
    /* background-color: yellow; */
    font-weight: bold;
}

table
{
    border-collapse: collapse;
    border: 1px solid black;
}

td
{
    text-align: left;
    vertical-align: top;
    padding: 6px;
    border: 1px solid black;
    width: 120px;
}

/* Two row background-colors
   to seperate rows; can be changed:
*/

tr td
{
    background-color: #eee;
}

tr.alternateRow td
{
    background-color: #ccc;
}

#excel
{
    display: none;
}

--></style>

<script type="text/javascript"><!--

// Row and column separator
// characters, can be changed:

var rowSeparator = "~";
var columnSeparator = ";";

var resultsI = 0;

function searchData()
{
    var data = getData();
    var search = document.getElementById("searchString").value;
    var arrRows = data.split(rowSeparator);
    var rowsLength = arrRows.length;

    var s = "";

    search = replaceStr(search, "  ", " ");
    var phraseSearch = ( search.indexOf("\"") >= 0 );
    if (phraseSearch)
    {
        resultsI = 0;
        search = replaceStr(search, "\"", "");
        s += getMatchingRows(arrRows, search);

        if (s != "")
        {
            s = "<p>" + resultsI +
                    " results found for phrase \"" +
                    search + "\":</p> " +
                    "<table>" + s + "</table>";
        }

    }
    else
    {
        var searchWords = search.split(" ");
        var wordsLength = searchWords.length;
        for (var i = 0; i < wordsLength; i++)
        {
            resultsI = 0;
            if (searchWords[i] != "")
            {
                var thisS = getMatchingRows(arrRows,
                        searchWords[i]);
                if (thisS != "")
                {
                    thisS = "<p>" + resultsI +
                            " results found for \"" +
                            searchWords[i] + "\":</p> " +
                            "<table>" + thisS + "</table>";
                    s += thisS;
                }
            }
        }
    }

    if (resultsI < 1)
    {
        // No results output
        s = "<p><em>No results matching \"" +
                search + "\" were found.</em></p>";
    }

    var outputLayer = document.getElementById("dataOutput");
    outputLayer.innerHTML = s;

    document.getElementById("h1data").innerHTML = "Results";
    searchForm.style.display = "none";
    newSearchLink.style.display = "block";
}

function newSearch()
{
    var outputLayer = document.getElementById("dataOutput");
    outputLayer.innerHTML = "";
    searchForm.style.display = "block";
    newSearchLink.style.display = "none";
    document.getElementById("h1data").innerHTML = "Search Data";
}

function getMatchingRows(arrRows, search)
{
    var sRow = "";
    var isAlternate = false;
    var rowsLength = arrRows.length;
    var lowSearch = search.toLowerCase();
    for (var i = 0; i < rowsLength; i++)
    {
        var row = arrRows[i];
        if ( row.toLowerCase().indexOf(lowSearch) >= 0 )
        {
            // Construct a table row with matches
            // to display later:

            var classString = (isAlternate) ?
                    " class=\"alternateRow\"" : "";
            var rowDisplay = replaceStr(row, search,
                    "<span class=\"hilite\">" + search +
                    "</span>");
            sRow += "<tr" + classString + "><td>";
            sRow += replaceStr(rowDisplay, columnSeparator,
                    "</td><td>");
            sRow += "</td></tr>";

            isAlternate = !isAlternate;
            resultsI++;
        }
    }
    return sRow;
}

function getData()
{
    var excelLayer = document.getElementById("excel");
    return excelLayer.firstChild.data;
}

function replaceStr(str, oldStr, newStr)
{
    var strPos = str.indexOf(oldStr);
    return (strPos >= 0) ?
        str.substring(0, strPos) + newStr +
        replaceStr( str.substring(strPos +
        oldStr.length), oldStr, newStr ) : str;
}

// --></script>
</head>
<body>

<h1 id="h1data">Search Data</h1>

<form id="searchForm">
    <p>
    Your query:
    <input type="text" size="20"
        name="searchString" id="searchString" />
    <input type="text" name="suppressReturn"
            style="display: none;" />
    <input type="button" onclick="searchData()"
        value="Search" />
    </p>
</form>

<div id="dataOutput">
&nbsp;
</div>

<div id="newSearchLink" style="display: none;">
<p>[<a href="javascript:newSearch()">New search ...</a>]</p>
</div>

<div id="excel"> 
d;3;40~this;is a;test~this;is a second;test~testing yet;again;...
</div> 

<p>Copyright &copy; 2003 by Ken</p>

</body>
</html>

Request for Answer Clarification by ken3141-ga on 29 Jun 2003 06:59 PDT
OK, we're almost there.  I mean, I suppose technically you're done,
but you're fighting for another star!

OK, here're the one issue, and the two requests, and one relatively
unrelated question:

ISSUE: While the searches work great as the "extra" requested while
being case insensitive (very cool), if you use the 'wrong' case, you
don't get the red highlighting.  Is that possible to fix?

REQESTS (No need to really answer these if you don't want; if it's
quick for you, go for it. Otherwise no worries):

a. I actually like how the cell width of the delivered table is fixed;
is there a way to fix each cell size as a different width?

b. Is there a way to have a fixed content table headings show up at
the top of the table(s) if something is found?


LASTLY:
I tried it on a PocketPC (iPaq 3955, running 2002 OS) and got no love.
Now, I very explicitly said that this answer is NOT dependent upon it
working on a PocketPC, and that's true!  The thing I want to know is:
if I post a second question after this is answered to see if it can be
tweaked to work on a PPC, are you interested?  It's entirely possible
it won't EVER work due to some 'PPC sucks at JAVA' thing. (aside: PPC
DOES run one simple HTML javascript that I could show as an example).

Clarification of Answer by j_philipp-ga on 29 Jun 2003 14:04 PDT
Hello Ken,

Glad you liked it!

The changes I made, as pasted below:
- Highlighting is now case-insensitive.
- The column-width can be set via the style, see comment "Define
column width ...".
- The first data-row provided will now be used as header-text (see
data "Header 1;Header 2 ...").

Now, the highlighting will (case-insensitive) replace matches with the
string as entered by the user (case kept). If you want this to be
shown as entered by your data, I understand and will try to work on
it, as it would certainly require more tweaking. It was more important
to me that you get a speedy reply.

Indeed making this work on a PocketPC would be an entirely different
task. I would suggest to you that you post a new

question asking for anyone's help, referring to the script I prepared
in this question-thread. Then, I might find time to tackle the
problem, but we might have luck and there's a "PocketPC Expert"
amongst the Researchers group!

If there is no such expert, I could do online research on what is
supported on this system. I know different JavaScript implementations
(ECMAScript, JScript, JavaScript, also Java, which is unrelated to
this though, plus I know different the different browser support. E.g.
the "innerHTML" in my script -- Internet Explorer, and Mozilla,
specific --  as well as the new addition of a regular expression,
might cause problems. And e.g. getElementById is also only supported
in IE5+, to the best of my memory. There are possible different
implementations for other browsers.)

However, all that might become very theoretical without me being able
to test anything. So one solution would be that I prepare a set of
test-cases for you. Those would be specific, very small scripts, which
you would need to save as different HTML pages, then telling me "1
works", "2 doesn't work", "3 ...", and so on.
Since I couldn't promise this would be all ending in a working script,
I'm afraid we would have to separate this process by yet another
question, specifically for me to create test cases. That's why maybe
first you should post a question to anyone and see what happens!

Let me know how things work and how you would like to proceed!


--------- New file below ---------


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> 
<head> 
<title>CSV Search</title> 
 
<style><!-- 
 
body 
{ 
    background-color: white; 
    color: black; 
    font-family: arial, helvetica, sans-serif; 
} 
 
/* The result-found highlight style for 
   a matching string, can be changed. 
   For example, you could give it a yellow 
   background-color (see comment). 
*/ 
 
.hilite 
{ 
    color: red; 
    /* background-color: yellow; */ 
    font-weight: bold; 
} 

/* Define column width;
   Leave out specific column
   for auto-sizing depending
   on cell-contents:
*/

.column1
{
    width: 100px;
}

.column2
{
    width: 150px;
}
 
table 
{ 
    border-collapse: collapse; 
    border: 1px solid black; 
} 
 
td 
{ 
    text-align: left; 
    vertical-align: top; 
    padding: 6px; 
    border: 1px solid black; 
} 
 
/* Two row background-colors 
   to seperate rows; can be changed: 
*/ 
 
tr td 
{ 
    background-color: #eee; 
} 
 
tr.alternateRow td 
{ 
    background-color: #ccc; 
} 
 
#excel 
{ 
    display: none; 
} 
 
--></style> 
 
<script type="text/javascript"><!-- 
 
// Row and column separator 
// characters, can be changed: 
 
var rowSeparator = "~"; 
var columnSeparator = ";"; 
 
var resultsI = 0; 
 
function searchData() 
{ 
    var data = getData(); 
    var search = document.getElementById("searchString").value; 
    var arrRows = data.split(rowSeparator); 
    var rowsLength = arrRows.length;
 
    var s = ""; 
 
    search = replaceStr(search, "  ", " "); 
    var phraseSearch = ( search.indexOf("\"") >= 0 ); 

    arrHeader = arrRows[0].split(columnSeparator);
    headerLength = arrHeader.length;
    header = ""; 
    for (var i = 0; i < headerLength; i++)
    {
    	header += "<th>" + arrHeader[i] + "</th>";
    }
    header = "<tr>" + header + "</tr>";

    if (phraseSearch) 
    { 
        resultsI = 0; 
        search = replaceStr(search, "\"", ""); 
        s += getMatchingRows(arrRows, search); 
 
        if (s != "") 
        { 
            s = "<p>" + resultsI + 
                    " results found for phrase \"" + 
                    search + "\":</p> " + 
                    "<table>" + header + s + "</table>"; 
        } 
 
    } 
    else 
    { 
        var searchWords = search.split(" "); 
        var wordsLength = searchWords.length; 
        for (var i = 0; i < wordsLength; i++) 
        { 
            resultsI = 0; 
            if (searchWords[i] != "") 
            { 
                var thisS = getMatchingRows(arrRows, 
                        searchWords[i]); 
                if (thisS != "") 
                { 
                    thisS = "<p>" + resultsI + 
                            " results found for \"" + 
                            searchWords[i] + "\":</p> " + 
                            "<table>" + header + thisS + "</table>"; 
                    s += thisS; 
                } 
            } 
        } 
    } 
 
    if (resultsI < 1) 
    { 
        // No results output 
        s = "<p><em>No results matching \"" + 
                search + "\" were found.</em></p>"; 
    } 
 
    var outputLayer = document.getElementById("dataOutput"); 
    outputLayer.innerHTML = s; 
 
    document.getElementById("h1data").innerHTML = "Results"; 
    searchForm.style.display = "none"; 
    newSearchLink.style.display = "block"; 
} 
 
function newSearch() 
{ 
    var outputLayer = document.getElementById("dataOutput"); 
    outputLayer.innerHTML = ""; 
    searchForm.style.display = "block"; 
    newSearchLink.style.display = "none"; 
    document.getElementById("h1data").innerHTML = "Search Data"; 
} 
 
function getMatchingRows(arrRows, search) 
{ 
    var sRow = ""; 
    var isAlternate = false; 
    var rowsLength = arrRows.length; 
    var lowSearch = search.toLowerCase(); 
    for (var i = 1; i < rowsLength; i++) 
    { 
        var row = arrRows[i]; 
        if ( row.toLowerCase().indexOf(lowSearch) >= 0 ) 
        { 
            // Construct a table row with matches 
            // to display later: 
 
            var classString = (isAlternate) ? 
                    " class=\"alternateRow\"" : ""; 
            sRow += "<tr" + classString + ">";

            arrColumns = row.split(columnSeparator);
            var columnLength = arrColumns.length;
            for (var j = 0; j < columnLength; j++) 
            {
                var column = arrColumns[j];
                sRow += "<td class=\"column" + (j + 1) + "\">";
                var rowDisplay = replaceStr(column,
                    search, 
                    "<span class=\"hilite\">" + search + 
                    "</span>");
                sRow += rowDisplay;
                sRow += "</td>";
            }
            sRow += "</tr>"; 
 
            isAlternate = !isAlternate; 
            resultsI++; 
        } 
    } 
    return sRow; 
} 
 
function getData() 
{ 
    var excelLayer = document.getElementById("excel"); 
    return excelLayer.firstChild.data; 
} 
 
function replaceStr(str, oldStr, newStr) 
{ 
    regExp = new RegExp(oldStr, "gi");
    results = str.replace(regExp, newStr);
    return results;
} 
 
// --></script> 
</head> 
<body> 
 
<h1 id="h1data">Search Data</h1> 
 
<form id="searchForm"> 
    <p> 
    Your query: 
    <input type="text" size="20" 
        name="searchString" id="searchString" /> 
    <input type="text" name="suppressReturn" 
            style="display: none;" /> 
    <input type="button" onclick="searchData()" 
        value="Search" /> 
    </p> 
</form> 
 
<div id="dataOutput"> 
&nbsp; 
</div> 
 
<div id="newSearchLink" style="display: none;"> 
<p>[<a href="javascript:newSearch()">New search ...</a>]</p> 
</div> 
 
<div id="excel">  
Header 1;Header 2;Header 3~d;3;40~this;is a;test~this;is a
second;test~testing yet;again;...
</div>  
 
<p>Version 2, Copyright &copy; 2003 by Ken</p> 
 
</body> 
</html>

Request for Answer Clarification by ken3141-ga on 29 Jun 2003 22:24 PDT
j_phillipp- 

Looks great.  Would prefer that the highlighted answers to the search
are in the original case, but it's really not all that important. 
We're just trying to get the functionality done.

Anyway, if you want to mod it great, otherwise, don't sweat it.  I'm
going to go ahead and rate (and pay!).  I'll repost as a question to
see if anyone can get it to work on a PPC.  If not, oh well, it was
worth the risk.

Too bad Google Answers won't let us give personal specifics, or I
could tell you what we're doing with it.  It's pretty interesting and
novel actually, although it might not seem like it without knowing
more details.

Ken

Request for Answer Clarification by ken3141-ga on 29 Jun 2003 23:31 PDT
I've now posted the follow up question at
http://answers.google.com/answers/main?cmd=threadview&id=223370

Clarification of Answer by j_philipp-ga on 30 Jun 2003 03:32 PDT
Hello Ken!

Thanks for rating and tip!

I'll be working on the improved highlightling and keep you updated
here. Also, I'll be checking out your new question.

Now you made me curious what you're doing with your application!

Philipp

Clarification of Answer by j_philipp-ga on 30 Jun 2003 03:52 PDT
----- New version below: --------------------------
----- Will match and highlight case-insensitive, --
----- keeping old text's case ---------------------


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
"DTD/xhtml1-strict.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">  
<head>  
<title>CSV Search</title>  
  
<style><!--

body
{
    background-color: white;
    color: black;
    font-family: arial, helvetica, sans-serif;
}
 
/* The result-found highlight style for
   a matching string, can be changed.
   For example, you could give it a yellow
   background-color (see comment).
*/

.hilite
{
    color: red;
    /* background-color: yellow; */
    font-weight: bold;
}

/* Define column width;
   Leave out specific column
   for auto-sizing depending
   on cell-contents:
*/

.column1
{
    width: 100px;
}

.column2
{
    width: 150px;
}
 
table 
{
    border-collapse: collapse;
    border: 1px solid black;
}
 
td  
{  
    text-align: left;  
    vertical-align: top;  
    padding: 6px;  
    border: 1px solid black;  
}  
  
/* Two row background-colors  
   to seperate rows; can be changed:  
*/  
  
tr td  
{  
    background-color: #eee;  
}  
  
tr.alternateRow td  
{  
    background-color: #ccc;  
}  
  
#excel  
{  
    display: none;  
}  
  
--></style>  
  
<script type="text/javascript"><!--  
  
// Row and column separator  
// characters, can be changed:  
  
var rowSeparator = "~";  
var columnSeparator = ";";  
  
var resultsI = 0;  
  
function searchData()  
{  
    var data = getData();
    var search = document.getElementById("searchString").value;
    var arrRows = data.split(rowSeparator);
    var rowsLength = arrRows.length;

    var s = "";

    search = replaceStr(search, "  ", " ");
    var phraseSearch = ( search.indexOf("\"") >= 0 );

    arrHeader = arrRows[0].split(columnSeparator);
    headerLength = arrHeader.length;
    header = "";
    for (var i = 0; i < headerLength; i++)
    {
        header += "<th>" + arrHeader[i] + "</th>";
    }
    header = "<tr>" + header + "</tr>";

    if (phraseSearch)
    {
        resultsI = 0;
        search = replaceStr(search, "\"", "");
        s += getMatchingRows(arrRows, search);

        if (s != "")
        {
            s = "<p>" + resultsI +
                    " results found for phrase \"" +
                    search + "\":</p> " +
                    "<table>" + header + s + "</table>";
        }

    }
    else
    {
        var searchWords = search.split(" ");
        var wordsLength = searchWords.length;
        for (var i = 0; i < wordsLength; i++)
        {  
            resultsI = 0;
            if (searchWords[i] != "")
            {
                var thisS = getMatchingRows(arrRows,
                        searchWords[i]);
                if (thisS != "")
                {
                    thisS = "<p>" + resultsI +
                            " results found for \"" +
                            searchWords[i] + "\":</p> " +
                            "<table>" + header + thisS + "</table>";
                    s += thisS;
                }
            }
        }
    }
  
    if (resultsI < 1)  
    {  
        // No results output
        s = "<p><em>No results matching \"" +
                search + "\" were found.</em></p>";
    }  
  
    var outputLayer = document.getElementById("dataOutput");
    outputLayer.innerHTML = s;
  
    document.getElementById("h1data").innerHTML = "Results";
    searchForm.style.display = "none";
    newSearchLink.style.display = "block";  
}  
  
function newSearch()  
{  
    var outputLayer = document.getElementById("dataOutput");
    outputLayer.innerHTML = "";  
    searchForm.style.display = "block";  
    newSearchLink.style.display = "none";  
    document.getElementById("h1data").innerHTML = "Search Data";
}  
  
function getMatchingRows(arrRows, search)  
{  
    var sRow = "";  
    var isAlternate = false;  
    var rowsLength = arrRows.length;  
    var lowSearch = search.toLowerCase();  
    for (var i = 1; i < rowsLength; i++)  
    {  
        var row = arrRows[i];  
        if ( row.toLowerCase().indexOf(lowSearch) >= 0 )
        {  
            // Construct a table row with matches
            // to display later:  
  
            var classString = (isAlternate) ?  
                    " class=\"alternateRow\"" : "";  
            sRow += "<tr" + classString + ">"; 
 
            arrColumns = row.split(columnSeparator);
            var columnLength = arrColumns.length; 
            for (var j = 0; j < columnLength; j++)  
            { 
                var column = arrColumns[j]; 
                sRow += "<td class=\"column" + (j + 1) + "\">";
                var rowDisplay = replaceStr(column,
                    search,
                    "<span class=\"hilite\">" + search +
                    "</span>");
                var rowDisplay = wrapString(column, 
                    search,  
                    "<span class=\"hilite\">",  
                    "</span>"); 

                sRow += rowDisplay; 
                sRow += "</td>"; 
            } 
            sRow += "</tr>";  
  
            isAlternate = !isAlternate;  
            resultsI++;  
        }  
    }  
    return sRow;  
}  
  
function getData()  
{  
    var excelLayer = document.getElementById("excel");
    return excelLayer.firstChild.data;  
}  

function wrapString(sAll, sFind, sStart, sEnd)
{
    var sAllLow = sAll.toLowerCase();
    var sFindLow = sFind.toLowerCase();
    var strPos = sAllLow.indexOf(sFindLow); 

    var isThere = (strPos >= 0);

    var sNew = "";
    if (isThere)
    {
        var sBefore = sAll.substring(0, strPos);
        var sMiddle = sAll.substring(strPos, strPos + sFind.length);
        var sAfter = sAll.substring(strPos + sFind.length);
        sNew = sBefore + sStart + sMiddle + sEnd + sAfter;
    }
    else
    {
        sNew = sAll;
    }
    return sNew;
}
  
function replaceStr(str, oldStr, newStr)  
{  
    regExp = new RegExp(oldStr, "gi"); 
    results = str.replace(regExp, newStr); 
    return results; 
}  
  
// --></script>  
</head>  
<body>  
  
<h1 id="h1data">Search Data</h1>  
  
<form id="searchForm">  
    <p>  
    Your query:  
    <input type="text" size="20"  
        name="searchString" id="searchString" />  
    <input type="text" name="suppressReturn"  
            style="display: none;" />  
    <input type="button" onclick="searchData()"  
        value="Search" />  
    </p>  
</form>  
  
<div id="dataOutput">  
&nbsp;  
</div>  
  
<div id="newSearchLink" style="display: none;">  
<p>[<a href="javascript:newSearch()">New search ...</a>]</p>
</div>  
  
<div id="excel">   
Header 1;Header 2;Header 3~d;3;40~this;is a;test~this;is a
second;test~testing yet;again;...
</div>   
  
<p>Version 2.1, Copyright &copy; 2003 by Ken</p>  
  
</body>  
</html>
ken3141-ga rated this answer:5 out of 5 stars and gave an additional tip of: $22.50
This answer rocked. Not only got what I needed, but got some other
little custom tweaks. Thus, I tipped appropriately (15%, even if I
didn't get table service...).

The concept of what I asked for and the answer I got is interesting. I
think it really went above and beyond what I thought I could get out
of GA. (An no, it wasn't a homework problem or something I'd run off
and sell commercially; it was just something I need for a project). 
Very, very neat.  I love the internet.

Comments  
Subject: Re: Write java script for a webpage that will 1) seach an Excel File 2) return hits
From: j_philipp-ga on 28 Jun 2003 07:01 PDT
 
Hi Ken3141,

Though you can include an Internet Explorer/ JScript ActiveX
file-object (using "GetFile" and "OpenAsTextStream"), that has certain
security and path-finding problems so I wouldn't suggest it.

However If you could export the Excel to a CVS file (text based,
tab-delimited), replace newline-characters with tilde ("~"), and
include the data as hidden layer in the HTML ...

    <div id="excel" style="display: none;">
    a;1;41~b;10;2~c;33;0~d;3;40
    </div>

... I could provide you with a solution. Would that work for you?
Subject: Re: Write java script for a webpage that will 1) seach an Excel File 2) return h
From: highroute-ga on 28 Jun 2003 17:40 PDT
 
Must it be a Javascript file? If instead you can use a CGI script,
written in Perl, that runs on the Web server, there are dozens out
there available for free use. I use one named Rilax, from
  http://risearch.org/
that does pretty much what you're asking. It will not search a
proprietary-format Excel file, but will search a CSV (comma-separated
values) file, and CSV is one of the formats in which Excel will save
files.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy