CODEWITHSUNDEEP
phpexcelcsv
Sujan Shrestha
Sujan Shrestha

Reputation: 614

How to speed up excel conversion in php with large data and multiple function calls

My sample php script looks like below. I want to save my database data into Excel format. Here i have table which has 35 columns. I need to display 16 to 18 thousands of data in this table. There are multiple functions to get data for different columns.

This script is working but it takes 8-10 minutes to get the excel file. The size of this output excel file is 18mb.

I think these multiple function calls for each row and the large file size is the reason of this slowness. 

    include('../include/functions.php'); //includes functions
    $sql_query=$sql; //here $sql is some sql query
    $result_details=mysql_query($sql_query);

     $data=
    '<table border="1">
        <tr style="font-weight:bold">
            <td>Title 1</td>
            <td>Title 2</td>
            .
            .
            .
            <td>Title n</td>

        </tr>';

    while(($row_details=mysql_fetch_array($result_details))){           
     $data.=
        '<tr>
            <td>'.function_1($row_details['col-1']).'</td> //some function function_1 to get first column
            <td>'.function_2($row_details['col-2']).'</td> //some function function_2 to get second column
            .
            .
            .
            <td>'.function_n($row_details['col-n']).'</td>  //some function function_n to get nth column                                
        </tr>';
            }
    $data.='</table>';  

    $file_type = "vnd.ms-excel";
    $file_name= "details.xls";
    header("Content-Type: application/$file_type");
    header("Content-Disposition: attachment; filename=$file_name");
    header("Pragma: no-cache");
    header("Expires: 0");
    print $data;

I would appreciate any help where i can speed up my excel file conversion. thank you.

Upvotes: 0

Views: 754

Answers (3)

kuldeep.kamboj
kuldeep.kamboj

Reputation: 2606

As you are taking lot of data into memory by appending it into $data variable. You need to change approach little bit.

First in loop write every row directly into file instead of appending to $data. That will keep ram usage less. After writing all rows, directly use readfile function to read file with excel headers.

That should speed up the excel build/download process. 

$file_path= "/tmp/details.xls";
$file_name= "details.xls";
$fp = fopen($file_path, "a");

$data =
    '<table border="1">
        <tr style="font-weight:bold">
            <td>Title 1</td>
            <td>Title 2</td>
            .
            .
            .
            <td>Title n</td>

        </tr>';
fwrite($fp, $data);
while(($row_details=mysql_fetch_array($result_details))) {
    $data =
        '<tr>
            <td>'.function_1($row_details['col-1']).'</td> //some function function_1 to get first column
            <td>'.function_2($row_details['col-2']).'</td> //some function function_2 to get second column
            .
            .
            .
            <td>'.function_n($row_details['col-n']).'</td>  //some function function_n to get nth column                                
        </tr>';
            } 
     fwrite($fp, $data);        
}
$data = '</table>';
fwrite($fp, $data); 
fclose($fp); 

$file_type = "vnd.ms-excel";

header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=$file_name");
header("Pragma: no-cache");
header("Expires: 0");
readfile($file_path);

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212522

Generate a real csv file rather than confusing csv/html/xls

include('../include/functions.php'); //includes functions
$sql_query=$sql; //here $sql is some sql query
$result_details=mysql_query($sql_query);

$file_type = "text/csv";
$file_name= "details.csv";
header("Content-Type: $file_type");
header("Content-Disposition: attachment; filename=$file_name");
... other headers here

$fh = fopen('php://output', 'w');
while($row_details = mysql_fetch_array($result_details)){
    fputcsv($fh, $row_details);
}
fclose($fh);

Upvotes: 3

Mr_Smile
Mr_Smile

Reputation: 29

Simple way to speed up this work:

  • add to your query this => " ... INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

  • show this temporary file from disk with you headers

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=file.csv");

Upvotes: 1

Related Questions