1 Topic by carmelr (edited by carmelr 01/20/2015 01:51:48 pm)

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Topic: Problem with Excel Reports

Whenever I output an excel report and try to open it in Excel I get the following message:

The file you are trying to open, 'PriceListing.xls', is in a different format than specified by the file extension.

When I force the file to open in excel, the data is not there.

I then compared the first few bytes of the above file with those of a properly formatted file and I found that there are 4 spaces as the first characters of the file. When I remove the spaces, the file opens properly in Excel.

Here is a hex dump of a file produced by FA:
file name: PriceListing.xls
mime type: 

0000-0010:  20 20 20 20-d0 cf 11 e0-a1 b1 1a e1-00 00 00 00  ........ ........
0000-0020:  00 00 00 00-00 00 00 00-00 00 00 00-3e 00 03 00  ........ ....>...
0000-0030:  fe ff 09 00-06 00 00 00-00 00 00 00-00 00 00 00  ........ ........
0000-0040:  01 00 00 00-08 00 00 00-00 00 00 00-00 10 00 00  ........ ........

and here is a hex dump of another excel file:
file name: UK_UBL_feed_template_v1.11.xls
mime type: 

0000-0010:  d0 cf 11 e0-a1 b1 1a e1-00 00 00 00-00 00 00 00  ........ ........
0000-0020:  00 00 00 00-00 00 00 00-3e 00 03 00-fe ff 09 00  ........ >.......
0000-0030:  06 00 00 00-00 00 00 00-00 00 00 00-01 00 00 00  ........ ........
0000-0040:  01 00 00 00-00 00 00 00-00 10 00 00-58 00 00 00  ........ ....X...

What is placing the spaces in the file? Is there anything I can change to get the excel function working?

TIA
Carmelo

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Problem with Excel Reports

Which version of FA are you using. What are your client and server platforms and versions of php/mysql used? What collation is your database on? You might be getting utf-8 and latin1 mixed up.

apmuthu's Website

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

apmuthu wrote:

Which version of FA are you using. What are your client and server platforms and versions of php/mysql used? What collation is your database on? You might be getting utf-8 and latin1 mixed up.

FA version: 2.3.22
Server: Ubuntu 14.4LTS
MySQL: 5.5.40
PHP: 5.5.9-1
Client: Firefox, Chrome and IE

Collation ... hmmm I have some tables utf8 and some latin1. I wonder how did this happen.

Is there an easy way to convert everything to utf8?

TIA
Carmelo

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Problem with Excel Reports

Look at one of the code recent fixes in FA v2.4 - all tables were converted elegantly to InnoDB. Otherwise export the data alone out, drop the database and create a new one, import the schema with the appropriate collation and import the data back.

apmuthu's Website

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

Thanks apmuthu for your help.
I converted all the tables and data but the problem remained.

I don't understand why the extra four spaces at the beginning of the xls file.
Do you think these are generated from the database? The first characters tell the Excel program that it is an excel sheet. At this point the database is not playing any part yet.

Which php file is responsible for writing the file?

TIA
Carmelo

6 Reply by apmuthu (edited by apmuthu 01/21/2015 08:29:13 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Problem with Excel Reports

The method End() in class FrontReport in includes/excel_report.inc file is the one that writes the excel sheet and then it is sent to the browser with headers for download to a unique filename in lines 30 to 42 of reporting/prn_redirect.php:

if (isset($_GET['xls']))
{
    $filename = $_GET['filename'];
    $unique_name = preg_replace('/[^0-9a-z.]/i', '', $_GET['unique']);
    $path =  company_path(). '/pdf_files/';
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=$filename" );
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
    header("Pragma: public");
    echo file_get_contents($path.$unique_name);
    exit();
}

Try it under PHP 5.3.x first - possibly in xampp 1.7.3 or so and see if we can locate and php version specific issue. Works okay from my end though.

Attached is the screenshot from XAMPP v1.7.3 / PHP 5.3.1 / MySQL 5.1.41.

Post's attachments

excel.png 33.4 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

apmuthu's Website

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

I'm still trying to find a solution for this problem.
I started again check what is actually written in the files at various stages.
If I pick the file as written in /company/x/pdf_files and transfer it via ftp or sftp to my local PC, the file opens in Excel straightaway.
However, if I use the file that is downloaded through FA, then I find the dreaded 4 spaces at the beginning of the file. No other changes occur, just these 4 spaces.

Any idea what could be adding these spaces? I don't think it is a locale problem as there are no changes in the rest of the file.

TIA
Carmelo

8 Reply by apmuthu (edited by apmuthu 01/22/2015 06:04:50 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Problem with Excel Reports

This is a browser encoding issue. Try to set the browser encoding to Western and then to Unicode and see the difference. Also what platforms and browsers have you tried? What about a browser in Linux? The major ones - IE 8, FF24+, Chrome (I haven't tesed it), etc should have their own quirks for encoding settings. Also see if there are any line endings that may be dos instead of unix style in the scripts.

Also try to upload a good xls file to the server and download it back to see if there are any differences.

apmuthu's Website

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

Here's an update ....

So I created the following file on the server:

<?php
    header('Content-type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="test.xls"');
    readfile('pdf_files/test.xls');
?>

called the file and it works. It sends the file and opens in Excel

So I changed prn_redirect.php to be like the above and the spaces where still being added at the beginning of the file.
This means that there is some setting within FA that is causing this.

I did a lot of research online trying to find what is causing this problem, but I cannot find a solution.
Any assistance would be greatly appreciated.

TIA
Carmelo

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Problem with Excel Reports

The only difference I find is that your $filename is quoted but the one in the code I posted is not!

Replace line 36 in reporting/prn_redirect.php

    header("Content-Disposition: attachment; filename=$filename" );

with

    header("Content-Disposition: attachment; filename='$filename'");

Or try swapping the single and double quotes in the replacement as well.

apmuthu's Website

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

apmuthu wrote:

This is a browser encoding issue. Try to set the browser encoding to Western and then to Unicode and see the difference. Also what platforms and browsers have you tried? What about a browser in Linux? The major ones - IE 8, FF24+, Chrome (I haven't tesed it), etc should have their own quirks for encoding settings. Also see if there are any line endings that may be dos instead of unix style in the scripts.

Also try to upload a good xls file to the server and download it back to see if there are any differences.

Thanks apmuthu. I know that the file generated by FA on the Ubuntu server is good as when I downloaded it to my PC using Filezilla the file opened in Excel.
Also, when I created the few lines of php code in a separate file to download the file generated by FA through my browser, the file opened in Excel.
Then when I changed the code in FA to use the readfile function (like in the php file I created) the spaces appeared again.

For this reason I don't think that it is a browser problem or an encoding problem. I do think, though, that FA is setting something internally and is causing the 4 spaces to be prepended to the file.

Carmelo

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Problem with Excel Reports

See my post of 4 seconds earlier than yours.

apmuthu's Website

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

Tried the quotes. Exactly the same results.

sad

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Problem with Excel Reports

Replace the line with:

$abc = "Content-Disposition: attachment; filename='$filename'";
header($abc);

apmuthu's Website

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

apmuthu wrote:

Replace the line with:

$abc = "Content-Disposition: attachment; filename='$filename'";
header($abc);

Same results sad

I have done more tests as follows:

I inserted an echo 'Testing'; right after
If (isset(G_GET['xls']))
{

and tried again a report.
Now the file contains 4 spaces and Testing.
Then I added the following commands after the echo line and or before the first header line:

flush();
ob_clean();
ob_end_clean();

For all of the above, only the four spaces are prepended to the file.

I believe that somehow, these four spaces are being sent to the browser from a different part of the code and the flush or the ob_clean is not clearing these spaces from the buffers.

I come to this conclusion because the spaces were added even before the word 'Testing' sent before the filename was created.

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

P R O B L E M   S O L V E D

I created another file that handles just the download of the excel file and passed the necessary parameters to it.

Now file downloads as it should and opens into Excel without a hitch.

I can share the code with anyone who is having similar problems.

Carmelo

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,089

Re: Problem with Excel Reports

Ok, carmelr,

I made a test here in my Environment and I got NO 4 spaces (0x20) in front of the file.

But if any other have the same problems as Carmelr, please Contact him.

/Joe

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

Thanks Joe.
My fix is simply moving out that part of the file that downloads the .xls file.
According to my research there is something that is generating the spaces and for some reason they remain in the buffer.

Moving it out was the only way to get rid of them.

Carmelo

19 Reply by apmuthu (edited by apmuthu 01/23/2015 05:59:59 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Problem with Excel Reports

I too am unable to duplicate this error in a test environment in Debian Squeeze, Debian Wheezy and XAMPP v1.7.3 in WinXP SP3 / Win7 / LXDE on Debian 7 Desktop and IE 8 and FF33.

@carmelr can post the code and location of file include here and/or in the wiki for anyone encountering this problem.

apmuthu's Website

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

Here is what I did:

I created a file in fa/reporting called xlssend.php containing the following code:

<?php
$filename = htmlspecialchars($_GET['fn']);
$unique = htmlspecialchars($_GET['un']);
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename='$filename'" );
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
    header("Pragma: public");
    echo file_get_contents($unique);
?>

Then I modified the file FA/reporting/prn_redirect.php as follows:

if (isset($_GET['xls']))
{   
    $filename = $_GET['filename'];
    $unique_name = preg_replace('/[^0-9a-z.]/i', '', $_GET['unique']);
    $path =  company_path(). '/pdf_files/';
//      header("Content-type: application/vnd.ms-excel");
//    header("Content-Disposition: attachment; filename='$filename'" );
//    header("Expires: 0");
//    header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
//    header("Pragma: public");
//     echo file_get_contents($path.$unique_name);

    header("Location: xlssend.php?fn=$filename&un=$path$unique_name"); /* Redirect browser */ 
    exit();
}
elseif (isset($_GET['xml']))

That's it.
Hope this will be useful for others.

Carmelo

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Problem with Excel Reports

Thanks @carmelr: way to go! Now look at the security implications of anyone having a go at the excel file maker script xlssend.php! Have a check for something to indicate that the user is logged in.

apmuthu's Website

  • From: Malta
  • Registered: 04/06/2012
  • Posts: 123

Re: Problem with Excel Reports

Hi apmuthu, that was the reason of my hesitation to share publicly the mod smile
Although there is a check for legal characters and one has to know the filename in order to be able to download it.
But obviously, I have to work more on that issue and will be updating the file to include more security.

carmelo

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Problem with Excel Reports

Just choose a define('XXXX','yyyy') in the calling script or one that already exists in FA and exit in the called script if it does not exist.

Sharing it in the public domain will help get rid of such vulnerabilities rather than live with "security thru obscurity".

apmuthu's Website