Taking too long to export excel using spout library with php & mysqli?

Question

I am facing a problem at the time of generating excel using spout library. It is taking forever to complete. I have a large amount of data to export. This is going to generate at least 600 rows and 98 columns in excel.

require(APPPATH .'libraries/spout/src/Spout/Autoloader/autoload.php');  
$this->load->model('Report_model','report');
$pay_ele = $this->report->pay_elements_data();// iT has 70+ codes like 
hra,Da etc.
$pay_code = '';
$array = [];
array_push($array,'E- 
ID','Name','Month','Year','Department','Grade','Accno','Bank 
Name','IFSC','Aadhar','PFNo','ESI','PAN','Working 
Days','Holidays','Weekoffs','Presents','Leaves','Absents');
foreach( $pay_ele as $pay) {
array_push($array,$pay->code);
}
array_push($array, 'Gross Pay');
array_push($array, 'Net Pay');
$writer->addRowWithStyle($array, $style);
if(empty($employee_id)) {
$employees  = $this->report->find_all_employee_ids();
}
foreach ( $employees as $emp) {
$employee_id    = $emp->employee_id;
$name           = $emp->full_name;
$department     = $emp->company_department;
$grades         = $emp->category_code;
$bank_details       = $this->report->find_bank_details($employee_id);
$p_det              = $this->report->personal_detail($employee_id);
$mon_dat  = $this->report->find_all_monhtlydata($employee_id,$month,$year);
if(!empty($mon_dat)) {
foreach ( $mon_dat as $mondata) {
$absent_days    = $mondata->absent_days; 
$present_days       = $mondata->present_days;
$working_days   = $mondata->working_days;
$holidays       = $mondata->holidays;
$week_off       = $mondata->week_off;
$leave_days     = $mondata->leave_days;
}
}
foreach( $bank_details as $bank) {
$acc_number = $bank->account_number;
$bank_name = $bank->bank_name;
$ifsc_code = $bank->ifsc_code;
}
$data1 = [$employee_id,$name,$month,$year,$department,$grades,$acc_number,
          $bank_name,$ifsc_code,$addhar,$pf,$esi,$pan,$working_days];
$gtotAmt = 0;
$totAmt = 0;
foreach( $pay_ele as $pay) {
$n_data = $this->report->find_pay_ledger_data_withparametrs
          ($employee_id,$month,$year,$department,$pay->code);
$amt = 0;
foreach( $n_data as $dat) {
$amt = round($dat->amount,2);
$totAmt += round($dat->amount,2);
if($pay->type == 'ADDITION') {
$gtotAmt += round($dat->amount,2);
} }
array_push($data1,$amt); 
}
array_push($data1,$gtotAmt);
array_push($data1,$totAmt);
$writer->addRow($data1);
}
$writer->close();
if (file_exists($filePath)
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; 
filename="'.basename($filePath).'"');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($filePath));
readfile($filePath);
exit;
}

I want to export excel in very fast mode but actually it is taking more than 2 hours. For few employees it is generating excel very fast but not for 500+ employees.

Answer 1

600 rows, 100 columns = 60,000 cells. According to the doc, it should not take more than a few seconds to generate your spreadsheet.

So I'm pretty sure the long time it takes has something to do with the code around Spout (maybe double check how data is fetched?). You can add logs to see where time is spent in your program.

Also, instead of using $writer->openToFile($filePath) and read the file to send it to the browser, you can use $writer->openToBrowser($fileName) directly. No need to set extra headers.

Answer 2

You should start looking for bottlenecks.600 rows is not to match. Are you sure if the problem is in the excel library? You will try to measure the time in any part of the application.