asked on

using javascript to find string in excel

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?

b0lsc0tt

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

evault

ASKER

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.

evault

ASKER

PS - I can use VB on the server, I'm just having some problems maiking sure the VB code is being executed.

ASKER CERTIFIED SOLUTION

Dxpert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

evault

ASKER

Tried the examples above and they were either incomplete or had errs.

evault

ASKER

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?

b0lsc0tt

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

Dxpert

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

Dxpert

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....

evault

ASKER

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.

evault

ASKER

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

Dxpert

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

Dxpert

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

Dxpert

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?

evault

ASKER

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.

evault

ASKER

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

evault

ASKER

Not the exact solution I was looknig for but good enough. Thanks.