Export to Excel - A little help required Im almost there

[notonurnelly]

Hi All,

 

I have copied the export mysql to excel tutorial from this website and it works fine.

 

I am trying to ammend the program so that the open,save or cancel box does not popup and the file is just saved to a specific location with a certain name.

 

ie

 

C:\temp\export.xls

 

The reason I need the file to be specific is becuase I want to run macros on the data to create charts and tables etc. I am then dragging the data in the named spreadsheet into a sheet that contains all of the macros. Therefore it is essential that the file is saved in the exact location and not named by the user which could lead to errors.

 

Here is the code for the export

 

<?php
define (db_host,"mysql.xcalibre.co.uk");
define (db_user,"######");
define (db_pass,"######");
define (db_link,mysql_connect(db_host,db_user,db_pass));
define (db_name,"######");
mysql_select_db(db_name);



$select = "select * from tblResultsDesc";
$export= mysql_query($select);
$fields = @mysql_num_fields($export);

for ($i = 0;$i<$fields;$i++){
$header .= mysql_field_name($export,$i) . "\t";
}
while ($row = @mysql_fetch_row($export)){
$line = '';
foreach($row as $value){
if ((!isset($value))or($value=="")){
$value = "t";
}
else
{
$value = str_replace('"','""',$value);
//echo $value;
$value = '"' . $value . '"' . "\t";
}
$line .=$value;
}
$data .=trim($line)."\n";
//echo $data;
}
$data = str_replace("\r","",$data);


if ($data ==""){
$data = "n(0) Records Found!n";
}

header("Content-Type: application/vnd.ms-excel");
header ("Content-Disposition: attachment;fielname=extraction.xls");
header("pragma:no-cache");
header("Expires:0");
print "$header$data";
ob_end_flush();
?>

 

Many Thanks in Advance

Jamie

[notonurnelly]

Can anyone help on this one,

[pascaru_val]

I am also interested in that

[ignace]

Replace this:

 

header("Content-Type: application/vnd.ms-excel");
header ("Content-Disposition: attachment;fielname=extraction.xls");
header("pragma:no-cache");
header("Expires:0");
print "$header$data";

 

With:

 

file_put_contents('/path/to/file.xsl', $header . $data);

[Barand]

you also need to write the contents of the output buffer

 

file_put_contents('/path/to/file.xsl', $header . $data . ob_get_contents());
ob_end_clean();

[pascaru_val]

It works!

Thank you.

I need now to make a file.bat for a user just to click on executable.

For the moment  I  am running  this example integrated in an php file from web (browser)

 

My export file it is an xls.

When I open that file with Excel , a pop up message apear and said that the file is not a correct format of  excel, can do something to stop this message appearing ?

[ignace]

/full/path/to/php.exe /full/path/to/php/file.php

[pascaru_val]

@ignace I do not understand.

 

I made a bat file with this content

ECHO off

 

ECHO file is loaded

 

start /d "C:\Program Files\Internet Explorer" IEXPLORE.EXE http://_my_ip/formular/export.php"

and open internet explorer with the link "http://my_ip/formular/export.php%22"

the corect wanted link is:"http://my_ip/formular/export.php" without %22

 

and give me an eror:" you do not have the permission" I guess it is a problem from xampp(apache)

 

[pascaru_val]

 

Now I understand what @ignace said.

I create .bat file with this content and it worked:

"ECHO off

 

ECHO bla bla

 

E:\xampp\php\php.exe -f  "E:\xampp\htdocs\formular\export.php""

 

just that  I receive 2 similar PHP NOTICE "ob_end_clean():failed to delete buffer. No buffer to delete in E:\..."  in the command line, after the work is done:

 

So I delete the line: "ob_end_clean(); "

and no problem, but I do not know exactly what this line does.

Thank you @ignace

 

[Barand]

I assumed output was buffered as original code ended with a flush command. Looks like it isn't.

 

Go with ignace's code

 

file_put_contents('/path/to/file.xsl', $header . $data);