Solved

using javascript to find string in excel

Posted on 2007-11-26
17
661 Views
Last Modified: 2010-05-18
I would like to be able to find a strnig in an excel file, using JavaScript inside a PHP document. This has to run on the server side. Once I find the file that has the string I am looking for, I would like to open said file on the client side. Any suggestions?
0
Comment
Question by:evault
  • 9
  • 6
  • 2
17 Comments
 
LVL 54

Expert Comment

by:b0lsc0tt
Comment Utility
evault,

If it has to happen on the server side then Javascript won't work, unless you are talking about server side Javascript.  Javascript is usually just used by the browser on the clientside.  If PHP is your server language then it is what you would need to use for server operations.

Where is the Excel file?  What type of page is this (i.e. Internet page, intranet/local page, etc)?  There are some contradictions and possible issues in what you said.  For the file to be searched on the server it would need to be there.  However you then said open the file on the clientside.  Did you mean send it from the server to the client so the browser could show (or open) it?  If so that is fine but if the Excel file is on the computer with the browser then the browser usually can't open it (with Javascript, etc), especially if the page/site is an Internet one.  A browser can have an Excel file sent to it and will often "know" how to open it but where the file comes from and the type of "web page" is a factor.  Browser security will prevent (Internet) web pages from opening files on the computer.

One other factor is an Excel file is a proprietary format.  It isn't normal "text" so you will need some "help" reading it.  Javascript doesn't have a way to do it even if it can access the file.  Often server languages can with "components" or objects but do you have one of these?

Let me know if you have any questions or need more information.

b0lsc0tt
0
 
LVL 1

Author Comment

by:evault
Comment Utility
The Excel file is on the server side in a specific folder. I need to loop through all the files on the server side (done) and as I do so I need to check each excel file for a particular string until I find that string or have no more excel files to search. Once I find the particular string I am looking for I need the client to be able to open it with Excel. Fell free to ask for further clarifications f necessary.
0
 
LVL 1

Author Comment

by:evault
Comment Utility
PS - I can use VB on the server, I'm just having some problems maiking sure the VB code is being executed.
0
 
LVL 10

Accepted Solution

by:
Dxpert earned 500 total points
Comment Utility
I guess first of all, we need to clarify one thing here. You can't use JavaScript on the Server. JavaScript is a client language, it runs on the client's computer (the one who's accessing the website) not on the Server.

I think, your best bet will be to use something like this:
http://sourceforge.net/projects/phpexcelreader/ or this: http://paggard.com/projects/xls.reader/ (I found these links through this post: http://www.thescripts.com/forum/thread170118.html)

To open you Excel file, then search for the string, then display it. As far as displaying it, I think all you should have to to is use:

<?php
header("Content-Type: application/vnd.ms-excel");
?>

You might want to look at this article as well: http://www.evolt.org/node/26896

Hope it helps you ;-)



0
 
LVL 1

Author Comment

by:evault
Comment Utility
Tried the examples above and they were either incomplete or had errs.
0
 
LVL 1

Author Comment

by:evault
Comment Utility
Since what I thought I wanted was not what I wanted and what I wanted I didn't know I wanted, can we switch this question to --->>> how to open an excel file with java script from the client side?
0
 
LVL 54

Expert Comment

by:b0lsc0tt
Comment Utility
I don't mind but you will need to provide more details.  Please review my previous comment to get an idea of the info we need.

bol
0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
well, if that's the case then you must know who the "client" is, is this on a Intranet? Can you rely on IE? If yes, then you can use VB Script, something like this:


<HTML>

<HEAD>

<SCRIPT LANGUAGE=VBScript>

Dim objExcel
 

Sub Btn1_onclick()

    call OpenWorkbook("c:\temp\test.xls")

End Sub
 

Sub OpenWorkbook(strLocation)
 

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = true

objExcel.Workbooks.Open strLocation

objExcel.UserControl = true

End Sub
 

</SCRIPT>

<TITLE>Launch Excel</Title>

</HEAD>

<BODY>

<P>Click the button to open the Excel Spreadsheet</P>

<INPUT TYPE=BUTTON NAME=Btn1 VALUE="Open Excel File">

</BODY>

</HTML> 

Open in new window

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
That code works, but only with IE. Also, depending on the Security setting of IE the user will get the security warning message about running ActiveX controls....
0
 
LVL 1

Author Comment

by:evault
Comment Utility
Hey people; I've been busy fighting all kinds of fires and haven't been able to test out any of these proposals. I will try to wrap this up by Thursday noon. Thanks for being patient.
0
 
LVL 1

Author Comment

by:evault
Comment Utility
Dxpert:

Tried the code and it works well as a stand alone, but gives me errs when I try to integrate it into a PHP app. I am new at PHP and so I am having challenges with it. I have attached the code snippet below.
<?php

        //Includes

        require_once("vcl/vcl.inc.php");

        use_unit("forms.inc.php");

        use_unit("extctrls.inc.php");

        use_unit("stdctrls.inc.php");
 

        //Class definition

        class Unit2 extends Page

        {

               public $Btn1 = null;

               function Btn1Click($sender, $params)

               {

               $str = "test";

?>

<HTML>

<HEAD>

<SCRIPT LANGUAGE=VBScript>

Dim objExcel

    call OpenWorkbook("C:\Documents and Settings\Administrator\My Documents\CWS\Cn 301009 config.xls")
 

<?php

               }
 

        }
 

        global $application;
 

        global $Unit2;
 

        //Creates the form

        $Unit2=new Unit2($application);
 

        //Read from resource file

        $Unit2->loadResource(__FILE__);
 

        //Shows the form

        $Unit2->show();

?>

<HTML>

<HEAD>

<SCRIPT LANGUAGE=VBScript>
 

Sub OpenWorkbook(strLocation)
 

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = true

objExcel.Workbooks.Open strLocation

objExcel.UserControl = true

End Sub
 

</SCRIPT>

<TITLE>Launch Excel</Title>

</HEAD>

<BODY>

<P>Click the button to open the Excel Spreadsheet</P>

<INPUT TYPE=BUTTON NAME=Btn1 VALUE="Open Excel File">

</BODY>

</HTML>

Open in new window

0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
What I would do first, is figure out where the error is occurring. For example, eliminate the PHP code, and check if it will work. Also, you forgot a CLOSE script tag on the first block, and also have multiple HTML and HEAD tags.

Use this code:

<HTML>

<HEAD>

	<TITLE>Launch Excel</Title>
 

	<SCRIPT LANGUAGE=VBScript>

		Dim objExcel

		call OpenWorkbook("C:\Documents and Settings\Administrator\My Documents\CWS\Cn 301009 config.xls")
 
 

		Sub OpenWorkbook(strLocation)
 

		Set objExcel = CreateObject("Excel.Application")

		objExcel.Visible = true

		objExcel.Workbooks.Open strLocation

		objExcel.UserControl = true

		End Sub

	</SCRIPT>

</HEAD>
 

<BODY>

<P>Click the button to open the Excel Spreadsheet</P>
 

<INPUT TYPE=BUTTON NAME=Btn1 VALUE="Open Excel File">

</BODY>
 

</HTML>

Open in new window

0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
Then try with the first part of the PHP code:

<?php

        //Includes

        require_once("vcl/vcl.inc.php");

        use_unit("forms.inc.php");

        use_unit("extctrls.inc.php");

        use_unit("stdctrls.inc.php");

 

        //Class definition

        class Unit2 extends Page

        {

               public $Btn1 = null;

               function Btn1Click($sender, $params)

               {

               $str = "test";

?>
 

<HTML>

<HEAD>

	<TITLE>Launch Excel</Title>
 

	<SCRIPT LANGUAGE=VBScript>

		Dim objExcel

		call OpenWorkbook("C:\Documents and Settings\Administrator\My Documents\CWS\Cn 301009 config.xls")
 
 

		Sub OpenWorkbook(strLocation)
 

		Set objExcel = CreateObject("Excel.Application")

		objExcel.Visible = true

		objExcel.Workbooks.Open strLocation

		objExcel.UserControl = true

		End Sub

	</SCRIPT>

</HEAD>
 

<BODY>

<P>Click the button to open the Excel Spreadsheet</P>
 

<INPUT TYPE=BUTTON NAME=Btn1 VALUE="Open Excel File">

</BODY>
 

</HTML>

Open in new window

0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
Well, I hadn't noticed that you had the PHP code wrapping HALF of that VBScript. Why is that???

What are you trying to accomplish? The PHP code runs on the Server and not on the Client. So, there will be no interaction between the two of them.
I think that's why it's not working. Are you by any chance trying to call OpenWorkbook with the PHP code?

0
 
LVL 1

Author Comment

by:evault
Comment Utility
Like I said in the original description, on the server side I need to search for excel files and find a string in side the excel files. Once I find that string I need to open, on the client side, the specific excel file in which the was found. That is why, originally I was wanting to open the excel file with javascript. I could use VB Script, but am unsure of how to implement it on the client side inside a PHP form.
0
 
LVL 1

Author Comment

by:evault
Comment Utility
I copied the code from the phpreader, it works great as a stand alone, but when I incoroporate the code into my PHP I get Done, but with errs on page when I run the PHP form. I have attached the code below and in commenting out the different lines in this subroutine, it appears that the line
                             {  echo "\"".$dataf->sheets[0]['cells'][$i][$j]."\",";
is the one causing the errs, but I have no idea why.

               function SearchBtnJSClick($sender, $params)

               { $FoundWty = FALSE;

                 $dataf = new SpreadSheet_Excel_Reader();

                 if ($handle = opendir('C:\CWS'))

                 {  while (false !== ($file = readdir($handle)))

                    {  $fileext = substr(strrchr($file, "."), 1);

                       if(strtolower($fileext) == "xls")

                       {  // open the file

                          $dataf->read('c:\cws\Cn 301009 config.xls');

                          error_reporting(E_ALL ^ E_NOTICE);

                          for ($i = 1; $i <= $dataf->sheets[0]['numRows']; $i++)

                          {  for ($j = 1; $j <= $dataf->sheets[0]['numCols']; $j++)

                             {  echo "\"".$dataf->sheets[0]['cells'][$i][$j]."\",";

                             }

                             echo "\n";

                          }
 

                          // search for the key string
 

                          // close the file

                         // fclose($fh);

                       }

                    }

                    //closedir($handle);

                 }

               ?>

               //Add your javascript code here

                 if (MT)

                   window.alert("Please enter search criteria.");

               <?php

               }

Open in new window

0
 
LVL 1

Author Closing Comment

by:evault
Comment Utility
Not the exact solution I was looknig for but good enough. Thanks.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now