Solved

using javascript to find string in excel

Posted on 2007-11-26
17
678 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 2
17 Comments
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 20353175
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
ID: 20353232
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
ID: 20353245
PS - I can use VB on the server, I'm just having some problems maiking sure the VB code is being executed.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 10

Accepted Solution

by:
Dxpert earned 500 total points
ID: 20353260
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
ID: 20353895
Tried the examples above and they were either incomplete or had errs.
0
 
LVL 1

Author Comment

by:evault
ID: 20354755
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
ID: 20355327
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
ID: 20357534
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
 
LVL 10

Expert Comment

by:Dxpert
ID: 20357546
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
ID: 20363912
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
ID: 20368556
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
ID: 20368676
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
ID: 20368692
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
ID: 20368750
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
ID: 20368796
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
ID: 20369123
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
ID: 31411067
Not the exact solution I was looknig for but good enough. Thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

737 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