Page 1 of 1

writing to .csv file to be opened by Excel

Posted: Mon Dec 08, 2008 9:51 am
by itp
I want to write to a CSV file and then let user click on link to load Excel.
Is there something special that I have to add to file header to prompt the browser to use Excel to open file.
Now it opens with browser as a text file.

Code: Select all

    
    $string = " SELECT * FROM "  .  $InvoiceHeader ;                 
    $result = fnConnectSQL($string);                            
    
    $csvLine  = "";
 
    // make a unqiue directory name for each customer 
    $directoryToPutFile = 'D' . $User_id ."_". (md5($User_id)) ;
    
    // if directory does not exist then create it
    if (!is_dir($directoryToPutFile)) 
    {
        mkdir($directoryToPutFile, 0700);
    }
    
    // directory + file name
    $myFile = $directoryToPutFile . "/" . $User_id .".csv";
    
    //write to file over-ride existing file
    $fh = fopen($myFile, 'w') or die("can't open file"); 
    
    // create some headers & write to file with fputcsv     
    $csvLine = array("header1","header2","header3","header4");  
    fputcsv($fh,$csvLine);
 
    while($row = fnFetchArraySQL($result)) 
    {       
        $csvLine[0] = $row[0] ;
        $csvLine[1] = $row[1] ;
        $csvLine[2] = $row[2] ;
        $csvLine[3] = $row[3] ;
        fputcsv($fh, $csvLine);
    }       
    fclose($fh);
 
    // show icon with link to file
    echo "&nbsp; &nbsp; <a href=" .$dq . $myFile . $dq."><img src='excel_icon.jpg' alt='download CSV file'></a>";   
?>
  
  
 

Re: writing to .csv file to be opened by Excel

Posted: Mon Dec 08, 2008 10:42 am
by mintedjo
http://uk2.php.net/header
As far as i know, you can force the file to be a download but you can't specify how to open the file. The browser will use the default program that is set up to handle that file type on the users computer. You can tell the browser that its a .xls file but you would be lying and then excel would probably complain about unexpected format or something.

Re: writing to .csv file to be opened by Excel

Posted: Mon Dec 08, 2008 11:27 am
by Mark Baker
mintedjo wrote:As far as i know, you can force the file to be a download but you can't specify how to open the file. The browser will use the default program that is set up to handle that file type on the users computer. You can tell the browser that its a .xls file but you would be lying and then excel would probably complain about unexpected format or something.
Quite correct. There is no way that you can force the opening of a particular application on a client PC, other than another instance of their browser.

Typically, Windows is configured to open files with an extension of CSV using MS Excel when you first install Excel. This can be changed manually at any time by editing the "File Types" settings under "Tools/Folder Options" in Windows Explorer, so it is not universal. It can alos be overridden by other software installations, such as OpenOffice, which allows you to change the default program to OO Calc.

Re: writing to .csv file to be opened by Excel

Posted: Mon Dec 08, 2008 3:28 pm
by itp
I had to resort to using a intermediary php file which brings together header() information and data.
Let me know if there was simpler way to do it.

Code: Select all

 
// if excel user requests Excel file 
    if($toCsv == 'on')
    {   
        // define output buffer file
        $obFile =  $directoryToPutFile . "/saveFile.php " ;     
        //start the PHP output buffer - write data to a new file, including csv data
        ob_start(); 
            echo "<?php\n";     
            echo "header(" .$dq. "Content-type: application/octet-stream" .$dq. ");\n";     
            echo "header(" .$dq. "Content-Disposition: attachment; filename=".$User_id.".csv" .$dq. ");\n"; 
            echo "header(" .$dq. "Pragma: no-cache" .$dq. ");\n"; 
            echo "header(" .$dq. "Expires: 0" .$dq. ");\n";         
            echo "include('". $User_id . ".csv');\n";
            echo "?>";  
        $data = ob_get_contents(); //puts the content of the output buffer into the $data variable
        //write output buffer to file saveFile.php
        $fh2 = fopen ("$obFile", "w"); //see the PHP manual for options
        fwrite($fh2, $data);
        fclose($fh2);
        ob_end_clean();                         
        // if excel file - show icon on screen
        echo "&nbsp; &nbsp; <a href=" .$dq . $directoryToPutFile. "/saveFile.php". $dq."><img src='excel_icon.jpg' alt='download CSV file'></a>";   
    }
 
 

Re: writing to .csv file to be opened by Excel

Posted: Mon Dec 08, 2008 5:09 pm
by itp
oops, just one problem...
when I put code on UNIX type server, the generated code has type of

text/x-generic

instead of

application/x-httpd-php

can I change this programatically? All other php scripts work properly