Understanding: Client side and grabbing information

Topics: Developer Forum, Project Management Forum, User Forum
Jan 9, 2013 at 9:35 PM

Hi,

I have been trying to find a guide a long the lines of making a client side code so when the PHPoutput is running I can get an update on the browser. I have see a few scenarios on here with a slight push in the right direction, unfortunately I'm not skilled enough to figure it out.

Heres some questions:

How does the logic work?

Do I have a seperate php page that includes the PHPexcel file? or do I insert the code into the PHPexcel file?

I've read ideas to get cookies etc, but at which point do I insert, is it whilst its looping through rows which goes back to the loader?

Whats the best coding to use? Javascript / Ajax?

Are there any simple tutorials online phpexcel specifically that can help me out, even if its a simple show something on the browser its a start.

E.g. I follow my link to the PHPoutput file and the page simple says 'downloading' at least it will put me in the right direction.

What is the basic workflow for this process???

 

 

Thank you.

Jan 10, 2013 at 12:28 PM
For a basic "Please wait, I try to think", the mechanism may be:
* The user expresses his desire to get the Excel file (by clicking on a button, link, etc.)
* Catch action from the user, in JavaScript, to show the waiting message, add a "token" (which the server will return in the form of a cookie) to the request, set up a monitoring loop, in JavaScript, to check cookies (all seconds, for example, check if the "token" is part of the cookies)
* Server-side, in the requested script, add the "token" to the cookies, make the Excel file, send it to the user.
* When the "token" is detected, hide the waiting message, disable the monitoring loop. At this time (or a fraction of a second before) the user sees the file save dialog box.

For something more elaborate, the monitoring loop will not just wait for a cookie, but will make a query (in ajax) to the server to know the status of the construction of the file. The target page will be different: you don't need to force php to consider a substantial page with a tiny part will be used. This page should read something that contains the completion status and return it to the browser. The something can be a field in a table (memory type, it does not matter if you lose the information), device cache (memcached, redis, xcache,...) or a file.
The main page will be in charge of feed the something above with the progress, knowing that the last part is likely to jump sharply: the save() method often take  time (if you want a progress bar, it's that the file does not have three miserable cells).

For the basic part, the work is in JS (ok, and a line in php to remember).

Note: When in "insert code in the PHPExcel file", you mean put the code in the product xls/xlsx file... It is a great way to ensure that the user not have what he wants... The response body must contain only the result data (a clean xls/xlsx, ...), you can cheat that by putting something in the headers, and you can retrieve client-side: cookies.
Jan 10, 2013 at 12:51 PM

so in my PHP Excel file, i can use javascript, and within that javascript ,write something along the lines of:

<script>

var mytext = "Downloading...";

 document.write(mytext);

</script>

 

This is put at the top of the code.

Se when my PHPexcel.php is excecuted the text will show?

 

without PHPexcel i have managed to get the database showing a progress bar, i just need to learn how to effect phpexcel, then I can start toying!

 

Any help with the above is greatly appreciated LWOL

 

Jan 13, 2013 at 2:03 PM

Hum... No, i think...
For me, the PHExcel file is something like :

<?php
require_once 'Excel/PHPExcel.php';
require_once 'Excel/PHPExcel/Writer/Excel2007.php';
$workbook = new PHPExcel;
// bunch of code to fill cells, add styles, etc.
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:inline;filename=filename.xlsx');
$writer = new PHPExcel_Writer_Excel2007($workbook);
$writer->save('php://output');
exit();
?>

First character is '<' (php open tag) and last, '>' (php close tag). Between, no echo with plain text, html, javascript,...
Sure, you can have a multi purpose script, driven with parameters : if (condition) display (send) html (css, JS, ...) else make Excel file. But you can't doing both in same time. So, for simplicity, say your 'phpexcel file' do only one thing : making the excel file.
You need an another script with html and JS for display normal text and "please wait".
Something like (add basic tags, attributes, to have a clean page) :

<form onSubmit="return ShowWait();" action="MakeExcel.php">
Dear user, click the button to have your file : 
<input ID="SubmitButton" type="submit" value="click me !">
<!-- others fields needed for customize the excel file -->
<input ID="TokenField" type="hidden" name="token" value="">
</form>
"PleaseWait" class="NotShowAtStart">Please wait
<script> var TheToken; var TheTimer; function ShowWait(){ //disable submit button, change class of div PleaseWait to display it //fill token field with a random value and save this in the var TheToken //initiate the monitoring loop TheTimer=window.setInterval("CheckToken()", 1000);//call CheckToken every second, save the timer id to stop the loop at completion return true;//ok to send the form ] function CheckToken(){ //check if TheToken is in cookies. // Yes ? Clear the interval, change class of div PleaseWait to initial value, delete the cookie, clear TheToken and associated field, TheTimer, enable submit button,... // No ? Do nothing }

Of course, this is a skeleton, it is necessary to write the actions, and this is not the only way to proceed. An element does not change however: the script responsible for the realization of the Excel file can not at the same time create this file and return information directly to the user.
Jan 13, 2013 at 2:18 PM

Okay your reply is starting to clear things up for me now.

So the phpexcel file, is just purely for making the spreadsheet, no extra coding whatsoever, its purely to compile information and export the file.

Its the file that excecutes phpexcel where all notification information needs to go so on a flow:

 

Submit form:

Contains token checking, changes the submit form to a please wait bar and then starts the phpexcel:

I think the key thing here ive missed is this: 

onSubmit="return ShowWait();" action="MakeExcel.php"
I have been having the onSubmit as the phpexcel file, instead of it being an action, will this mean the page stays as is, but starts processing?
If so Im going to work on this immediately!

Thank you Lwol appreciate this a lot!
Jan 15, 2013 at 12:40 PM
(I just saw the snipet insertion tool has devoured a part: "PleaseWait" class="NotShowAtStart">Please wait,  should be read:
Please wait
)

In fact, we play on the usual behavior for a browser: when you click on a link or submit a form, the current page not disappears, its elements are still active. My little ShowWait function always returns true, accordingly, the form is submitted to the server and in the meantime it deign respond, loop checking client-side can turn quietly.
When the browser receives the headers who informed him that he will receive is not a page (something that it should display) but a file that it should show a save dialog to the user, it remains on the original page, update the cookies, and displays dialog, meanwhile, scripts (JS) on the initial page can work.
If you need to make sure of correct behavior if javascript is not available, the script performing the Excel file must be a little smarter. But let's not muddle things!
[quote]
So the phpexcel file, is just purely for making the spreadsheet, no extra coding whatsoever, its purely to compile information and export the file.[/quote]
Yes. A space, a linefeed (or more text, of course) in the output and you got a corrupt file. On a file produced on a dev' box, if Excel complains, open the file in a text editor : often, a notice or a warning is in the file.
Jan 16, 2013 at 7:58 AM
Edited Jan 16, 2013 at 7:59 AM

Hi Lwol,

Starting to make some real progress now here is my code: 

 

<form action="R31Wexport.php">
Dear user, click the button to have your file : 
<input id="SubmitButton" type="submit" value="Export" />
<!-- others fields needed for customize the excel file -->
<input id="TokenField" name="token" type="hidden" />
</form>

<script>var TheToken; var TheTimer;function ShowWait(){//disable submit button, change class of div PleaseWait to display itdocument.getElementById("PleaseWait").innerHTML="<div style=\"visibility:visible;\">Please Wait while we process the download</div>";document.getElementById('SubmitButton').disabled = 1;//fill token field with a random value and save this in the var TheToken //initiate the monitoring loopTheTimer=window.setInterval("CheckToken()", 1000);//call CheckToken every second, save the timer id to stop the loop at completionreturn true;//ok to send the form}

function CheckToken(){//check if TheToken is in cookies.// Yes ? Clear the interval, change class of div PleaseWait to initial value, delete the cookie, clear TheToken and associated field, TheTimer, enable submit button,...// No ? Do nothing}
</script>

 

Which works brilliantly, now my next task is understanding the Token section. 

How does this work, for example my PHPExcel file uses ForEach, so would I add an instance such as this:

 

$i = 1;

Forloop {          
$i
$I++;}

So that each row increments, would I put the token in here? so that every loop will send information back to the clientside? 

- What is a token?
- What is the functionality of a token?

 

Thank you 

Jan 17, 2013 at 5:50 AM
One thing at the time;-)
The skeleton I posted is able to show the user that the work has begun and that the work is finished, not the progress of the work.
Finalize his writing is not lost time if later you want to implement a progress bar, I would return to this aspect.
But first, the Token. This element is a marker, a pass.
What should contain? What do you want to avoid the risk of collision (if the user requests after or at the same time several files, for example).
Side JS, you'll get something like:
var x = new Date();
TheToken = 'ExportXYZ' + x.getTime ();
document.getElementById('TokenField').value = TheToken;
The script making the Excel file will in turn present the token as "proof" it returns the file you expect (the correct answer to the form transmitted to the server by the browser and, also, the only part of the answer that is accessible to the JS code).
Whatever the number of times that the script will loop to produce the file, the token will be present in a single copy, you add it to the answer (almost) when you want as long as it is before the body of the response is sent, that is, before the call to the save('php://output') method.
How to add? by a setcookie:
If(array_key_exists('Token', $_POST) && $_POST['Token']!=") setcookie($_POST['Token'],'Ready');
Why use this technique? Because, as I have already written, you can't put anything in the body of the response, otherwise the file will be corrupt, in addition, it must be accessible in JavaScript. On the other hand, you stand as well because it is the only way to know that the file is available for the user.
Jan 17, 2013 at 2:58 PM

Okay so, 

In my lets call it 'index.php' file it contains:

The message information saying 'please download file here' with the excecutable button to 'PHPExcel.php'

 

Index.php contains:

var x = new Date();
TheToken = 'ExportXYZ' + x.getTime ();
document.getElementById('TokenField').value = TheToken;

this piece of code, Is the 'tokenfield' the div on index.php which will show the token information?

PHPExcel.php contains:

If(array_key_exists('Token', $_POST) && $_POST['Token']!=") setcookie($_POST['Token'],'Ready');

When I put this into my PHP I have a syntax error here: 

array_key_exists('Token', $_POST) && $_POST['Token']!=")

I tried to do some revision into setcookie from php.net but still can't understand it. Is this the information that makes the token, or is it the javascript makes the token?

 

Jan 18, 2013 at 7:30 AM

 

array_key_exists('Token', $_POST) && $_POST['Token']!=")

After the not equal operator, it's two single quote, not a double quote ;-)

[quote]Is the 'tokenfield' the div on index.php which will show the token information?[/quote]
TokenField is the ID of the hidden form field Token

[quote]Is this the information that makes the token, or is it the javascript makes the token?[/quote]
Here, it's the JS who make the token. The Token is just a piece of text, in my example, something like "ExportXYZ123456" (the number is a Unix Timestamp).
This text go to the server within the form data, php catch it, and make a cookie with this text and send it to the browser.

Jan 20, 2013 at 6:43 PM
Edited Jan 20, 2013 at 6:44 PM

Hi Lwol,

 

Getting closer,

I understand after playing with the code, that like you said the Javascript makes a Value for the ID 'TOKENFIELD' from there this information is then posted to my PHPEXCEL FILE.

The code below:

If(array_key_exists('Token', $_POST) && $_POST['Token']!='') setcookie($_POST['Token'],'Ready');

Uses the value information and makes a cookie in my browser.
Now I need to return it to the javascript file? I have noticed the Token is made in the hidden field, but I would like to publicly see it, eventually I will replace the token ID with the rows of the PHPExcel file that are being made. Then will get new tokens/row ids to be shown on the front end.

What would be the process there, because I am looping, new tokens constantly need to be being made.

Code so far:

<script>
var TheToken; 
var TheTimer;

function ShowWait()
{
document.getElementById("SubmitButton").disabled=true;
//disable submit button, change class of div PleaseWait to display it
var x = new Date();
//fill token field with a random value and save this in the var TheToken 
TheToken = 'TOKEN' + x.getTime ();
//initiate the monitoring loop
TheTimer=window.setInterval("CheckToken()", 2000);
//call CheckToken every second, save the timer id to stop the loop at completion
return true;//ok to send the form
}

function CheckToken(){
document.getElementById('TokenField').value = TheToken;

//check if TheToken is in cookies.
// Yes ? Clear the interval, change class of div PleaseWait to initial value, delete the cookie, clear TheToken and associated field, TheTimer, enable submit button,...
// No ? Do nothing
}

</script>
Jan 21, 2013 at 8:29 AM

"Now I need to return it to the javascript file?"
By setcookie, you give the necessary impetus. Then, it is the server that does the job and the browser. JavaScript just must watch for the appearance of this particular cookie.
The mechanism described is a reliable way to know that the script that performs the Excel file has completed its work, nothing more, but this is useful.

Note: In the suite, I propose a solution for a bar of progression, but IMHO, it should be the first part works before proceeding with the next.
If this works correctly, implementing a progress bar will not rely on this exchange, except to bring it to 100% (you can have a synchronization trouble by other calls, but when you have the cookie, you are certain that the file is here).
But as I wrote, it has not been in vain: you'll keep this part and add things.
In the monitoring loop, client-side you'll add an Ajax call to a small script (*), passing the token, but this script will not return it in the form of a cookie.
In the main script, you'll feed "something" based on the advance, at each step (not whenever you fill a cell, it would be too much... And the assignment of values to cells is only a step... not necessarily the longest).
The "Something" must be accessible to the small script in real-time (or close). You have a number of possibilities... But preferably, choose a possibility that adds the least possible load.
If you have access to a server that knows to store in memory key/value pairs (memcache, redis,...), it is the ideal, if not you can fall back on a SQL table (in MySQL, engine = memory), in which you write  the token and the value that represents the progress.
The small script will read this value and return it in response to the Ajax request (in plain text, XML, JSON, as you wish). Then, the JS will translate this to a visual element to the user.
(*), Yes, an another script. If you use your Excel script, you need to add a test for this case, it's easy, of course, but php must parse the entire script, lost time and resource for nothing.
Jan 21, 2013 at 11:29 AM
Edited Jan 21, 2013 at 11:43 AM

Okay, so this javascript code was to test that it is correctly sending the information to the PHPexcel file and back to the client file?

I have full access to our servers and databases.

 

Next Step:
Learn how to use Ajax to read server processing?

Question:
Will it be possible with ajax to show the % of the process being done? catering for X number of rows?

Am I on the right track here?

Testing Link: http://www.room31wholesale.co.uk/stocklist/developing/testload.php

Jan 22, 2013 at 9:23 AM
[quote]Okay, so this javascript code was to test that it is correctly sending the information to the PHPexcel file back to the client and file?[/ quote]
In a sense. This gives you the starting point and the point of arrival in a reliable manner.

[quote]I have full access to our servers and databases../ quote]
OK, so you can check if a memcache or Redis server is present, if the php extensions are present. Or if not, to consider the question of implementation. This type of server is not used only for the use I mentioned, so to see.
In the meantime, you can create a little function or object that your script will call for the information on the progress. You code the function using the means at your disposal today, you can still adapt it then.

[quote]Next Step:
Learn how to use Ajax to read server processing?[/ quote]
Yes. If you have not had the opportunity to play with this technique still... Either you code everything yourself, or you use a framework that takes care of everything.
Set for the query isn't from the browser cache...

[quote]Question:
Will it be possible with ajax to show the % of the process being done? catering for X number of rows?[/ quote]
Yes. All you want. As you write on both sides (JS, client-side, php server-side), you can ensure you have what it takes.
A true percentage asks you to find out the total number of items and the number of realized items. The response to the Ajax request can be a single number from 0 to 100. the work of the JS will be to display something in function: the number, a nice progress bar...
The server can also return more information, simply, be well formatted so that the JS interprets it easily.
For the management of the value... In the light of the example, you can certainly easily determine the number of lines, from the beginning, and therefore, each processed row can calculate the percentage of progress and call the function that stores data for the other script. However, finalization can also take time, as a result, you can keep a few percent for that part, to avoid having the bar set at 100% and no file.

I followed the link, there is still work to manage the token, but the concept is there;-) In the output file, three images can't be displayed (sku: x 30750, o59725, o42583).
Jan 24, 2013 at 8:02 AM

Hi LWOL,


I think im getting the hang of this now, i've been looking at alternative routes instead of PHPOUTPUT

One of the methods im looking at is saving to the server then allowing users to download, then delete the file.
At the moment ive been working on file reduction and I've been able to minimize a 80MB file down to around 1.2MB 

Testing file:
http://room31wholesale.co.uk/stocklist/developing/test.php

Using Gzip and memcache seems to help a lot, I've literally put the echo on each row loop for now so I can monitor, what im thinking though is to have the javascript in there with the file just so I can start and work on a progress bar using JQUERY

what are the options there?

Jan 24, 2013 at 12:56 PM
Yes, "Save" to php://output is not always the best solution, especially if the file can be reused... This means not to remove it as soon as a user has retrieved it.
This may be interesting to develop a technique to check if the file needs to be rebuilt: currently, your file needs of ~ 6 seconds to be created, it is likely that an audit (the data have changed?) will take much less time, server resources.

Your build script would start by checking the absence of need to (re) build. In this case, either redirect the browser to the file (If verification of access rights do not require php) or send the headers and a readfile of file.
But all this does not solve your problem. Save in a file you would get out html (JS..) in your script - should also provide a redirect so that the user actually retrieves the file - but it may not be awesome: the server does not rushes to send each issued byte, so php, so the display...
As a result, work on this aspect is positive (as well as other efforts that you have undertaken) but not change thing to the problem "progress bar".

And after what you write, you have a memcache server, so you have a very quick way to write (from your builder script) and read (from the "checker" script) a single value.
Client-side jQuery (and certainly jQuery-UI for the widget progress bar )... Easy to make a jQuery.ajax({url: 'StatusReaderScript',data:{'Token':Token},cache:_false}).done (function (data) {$("#progressbar").progressbar('value', Number(data));}); in the above-mentioned monitoring loop.
Jan 24, 2013 at 1:31 PM

Okay so my plan so far:

File 1 is my client side file lets name it index.php for simplicity, this contains:
- Statement, checking if the file has been made in the past hour (needs to be an hour in our case with our website updating), if it hasn't just download the current one.
- Javascript to generate a token when gets sent over to PHPEXCEL.php
- AJAX Call to check if there is a cache token , if there is get the value of token and insert it into progress bar and update.
- When finished process, download file to server with button linking to said file to download 

PHPEXCEL .php
Generates the information for the spreadsheet, using memcache (what does memcache do?) & GZIP
Send new cookie information (value is recieved from the javascript above)? 
End script and write to server

Does this sound about right? 

 

Jan 24, 2013 at 1:40 PM

Also with the exporting of file, depending on what drop down is selected can I have an if statement along the lines of:

If dropdown A is selected save file as A.xlsx

if dropdown B is selected save file as B.xlsx

Can that be done through the PHPExcel file

Jan 26, 2013 at 2:20 PM
Yes, quite a lot;-)
But I do not know if I would do the verification at the opening of the homepage: If the user opens the page... and going to lunch. The information is no longer updated.
In addition, in your message, you write that there may be several files. As long as there have expressed their choice you do not know which file is to be checked, in addition all files were not made at the same time (a first user request resulted in the creation of A file, there is a half hour: file A is therefore considered to date, but the second user does want the file B : It is not up to date).

[quote] what does memcache do?[/ quote]
PHPExcel can use it for his own work and thus reduce the memory used by php (some research in the discussions will give information), but to what interests us, it is to use a few bytes to share information between the script that created the excel file and the script - Ajax requests target - in real time with a very small impact on performance, so that it returns to the client the progress information.
[quote]Send new cookie information (value is recieved from the javascript above)? [/ quote]
Yes, in the monitoring loop. In pseudo-code (don't copy-paste ;-), you have something like:
If (TokenInCookies) {
ProgressBar.value(100%) //just for aesthetics
HideProgressBar
DisableMonitoringLoop
EnableSubmitButton
}else{
AjaxCall // retrieval and display of the progress
} //end if
[quote]End script and write to server [/ quote]
And read the file, send it to the user.

In the form of the index page, you can create all the fields needed to allow the user to refine the product file. It's a standard form by many ways.
The script performing the Excel file will receive them and implemented logic process settings accordingly.
Since you considered to offer choices, the cache logic must be able to determine the correct file check, rebuild as appropriate and send.
Jan 26, 2013 at 10:00 PM

Okay so where I am currently at:

Room31wholesale.co.uk/stocklist/V2

you will have to log in to view the process though.

 

I have done pretty much everything, I have file verification for 30 minues, one file that can export 4 variations of file. And with the progress bar at the moment im just using the OnState method to change info.

things left to do:

1. Understand the progress bar a little more

2. See how I can reduce the phpexcel file and reduce MB request (at the moment im near 300).

Can you fill me in on these LWOL?

 

Once I have everything done I think I'll release a simple version so everyone can use a progress bar. Its not been easy but I've managed to progress in PHP AJAX and Javascript over the past 2 weeks.

Jan 27, 2013 at 3:37 PM

I have not tested the latest version, but, if I rely on the previous:
-You have many images in the file, this takes up space in file.
-You have cells that are SUM. You can earn a little if PHPExcel don't make the calculation ($objWriter->setPreCalculateFormulas(false), from the user, the calculation is instant) and a bit more if you do the calculation in php (you put the result directly into affected cells and you don't fill "source" cells).
Then, you have all the cache methods PHPExcel knows to handle which can save much, at the expense of the speed of realization (not miracle). You will find the information in section 4.2.1 of developer documentation and the results of testing (not all methods) conducted by MarkBaker here: https://phpexcel.codeplex.com/discussions/234150?ProjectName=phpexcel at the end of the topic.
Apr 19, 2013 at 11:11 PM
Hi,

I am facing a similar challenge, trying to get the PHP script (that manipulates and outputs a rather large Excel file directly to the browser) to report it's progress in realtime, so that the user has at least some idea about the amount of waiting time to still expect.

I have followed Lwol's suggestion regarding using a cookie, together with directing an output to an IFRAME and some additional javascript to show a general "hourglass" type of symbol, to show that the script is running. I'm really grateful for the explanations which helped a lot. This however just shows an hourglass for, say, 12 minutes, which is better than nothing, but not very informative

I was inspired by Lwol's remark about using memcache as means to communicate in real time from within the PHP code. Installed memcached and appropriate PHP extension, and am able to write information from PHP to a memcache "variable". Unfortunately, I was unsuccessful in finding a simple browser side memcache client for javascript in order to be able to fetch this value during periodic checks done by the setInterval'ed javascript function...

Does anyone know of such a browser-based memcache client?
Or perhaps there's some other method of transferring a real-time piece of information (for progress purposes it can be as simple as an integer value) from a running PHP script to the browser?

Kind regards
Stan
Apr 20, 2013 at 9:45 AM
Edited Apr 20, 2013 at 9:48 AM
I'm answering myself... :)

It was a mental block on my part apparently...

I made the requesting page a .php one instead of .html, and used some PHP code on it to retrieve the memcache value.
Then I made a tiny php file that does the same and invoke it periodically via AJAX.

I must've been really tired the last couple of days :)

Kind regards
Stan