?
Solved

using javascript to find string in excel

Posted on 2007-11-26
17
Medium Priority
?
699 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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 10

Accepted Solution

by:
Dxpert earned 1500 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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 how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

831 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