Not getting any values in downloadable excel file generated using PHPexcel plugin

Question

I am trying to use PHPexcel plugin to generate downloadable excel file from mysql data. Unfortunately, I am only getting only the column headers and not their values in the downloaded excel file

Where am I going wrong ? Also, I would like unicode characters to show properly. Following is my code of the downloadexcel.php file -

<?php
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/Writer/Excel2007.php';

$objPHPExcel = new PHPExcel();

$data=array();

$sql="SELECT s.t_id,s.t_text,p.user_name,p.description,s.time,p.place from t 
AS s INNER JOIN users AS p ON s.user_name=p.user_name order by s.time desc";
 $result = mysqli_query($con,$sql);

while($row = mysqli_fetch_array($result))   {
    $array=array("T Link" => $row[0],"T"=>$row[1] , "User Name" => $row[2] , 
"User Profile" => $row[3], "Time" => $row[4] , "Place" => $row[5]);
     array_push($data,$array);
  }

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'T Link');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'T');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'User Name');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'User Profile');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Time');
$objPHPExcel->getActiveSheet()->setCellValue('F1', 'Place');

$row=2;
foreach($data as $row->$value) {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row,$value->t_id);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$row,$value->t_text);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$row,$value->user_name);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$row,$value->description);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$row,$value->time);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$row,$value->place);
    $row++;
}
header('Content-Type: application/vnd.openxmlformats-
officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="helloworld.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
?>

Based on comments of Mark Baker, edited the code but still not getting any result. This is my edited code -

<?php
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/Writer/Excel2007.php';

$objPHPExcel = new PHPExcel();

$data=array();

$sql="SELECT s.t_id,s.t_text,p.user_name,p.description,s.time,p.place from t 
AS s INNER JOIN users AS p ON s.user_name=p.user_name order by s.time desc";
 $result = mysqli_query($con,$sql);

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'T Link');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'T');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'User Name');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'User Profile');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Time');
$objPHPExcel->getActiveSheet()->setCellValue('F1', 'Place');

while ($row = mysqli_fetch_array($result))
      {
$n=2;
foreach($data as $row) {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$n,$row['t_id']);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$n,$row['t_text']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$n,$row['user_name']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$n,$row['description']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$n,$row['time']);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$n,$row['place']);
    $n++;
   }
}
header('Content-Type: application/vnd.openxmlformats-
officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="helloworld.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
?>

The __array__ that you're pushing into $data bears no resemblance to the __model__ that you're accessing in `foreach($data as $row->$value)` — Mark Baker, Apr 10 '17 at 15:59
$row is being used for the row number; but also as some form of model holder: `$row=2; foreach($data as $row->$value) {` — Mark Baker, Apr 10 '17 at 15:59
That's 2 problems with your code; both of which will prevent it from working correctly — Mark Baker, Apr 10 '17 at 15:59
It's also highly inefficient building an array by looping through the data, then looping through that array to build the spreadsheet data — Mark Baker, Apr 10 '17 at 16:01
If I write `$row=2; foreach($data as $value)` still getting same result — user20152015, Apr 10 '17 at 16:45
@MarkBaker, Can you please suggest right code in the answer ? — user20152015, Apr 10 '17 at 16:48

score 0 · Answer 1 · edited May 23 '17 at 12:17

0

Change this line:

foreach($data as $row->$value) {

to

foreach($data as $row => $value) {

"->" operator is used with objects. The difference between the two operators is explained here: https://stackoverflow.com/a/14037376/577778

Updated Code

<?php
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/Writer/Excel2007.php';

$objPHPExcel = new PHPExcel();

$data=array();

$sql="SELECT s.t_id,s.t_text,p.user_name,p.description,s.time,p.place from t 
AS s INNER JOIN users AS p ON s.user_name=p.user_name order by s.time desc";
 $result = mysqli_query($con,$sql);

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'T Link');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'T');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'User Name');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'User Profile');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Time');
$objPHPExcel->getActiveSheet()->setCellValue('F1', 'Place');

$n=2;

while ($row = mysqli_fetch_array($result))
      {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$n,$row['t_id']);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$n,$row['t_text']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$n,$row['user_name']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$n,$row['description']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$n,$row['time']);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$n,$row['place']);
    $n++;
}
header('Content-Type: application/vnd.openxmlformats-
officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="helloworld.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
?>

edited May 23 '17 at 12:17

Community

1
1

answered Apr 10 '17 at 17:30

Mohammed Q. Hussain

46
3

Q Hussain Please see my edited code. That however is not working – user20152015 Apr 10 '17 at 17:51
In the modified code, I don't think you need "foreach" anymore. Try running the code after removing it. Furthermore, inside "while" block try to print the value of $row['t_id']. Are you sure your query returns some result? – Mohammed Q. Hussain Apr 10 '17 at 17:58
If I remove foreach , then how can I fetch subsequent rows ? – user20152015 Apr 10 '17 at 18:45
As I can see from the modified code "$data" is an empty array and there is no elements in it. The rows will be fetched by "mysqli_fetch_array($result)" through the variable "$row" (which is an array of columns) and "foreach" in this situation overwrites the variable "$row" which has the actual data. – Mohammed Q. Hussain Apr 10 '17 at 19:03
Can You write the correct code in your opinion @Mohammed Q Hussain in your answer ? – user20152015 Apr 10 '17 at 19:28
Thanks. I updated your code for the connection line and added it. It gave me an error - "Excel cannot open the file 'helloworld.xlsx' because the file format or file extension is not valid. Verify that the file as not been corrupted and that the file extension matches the format of the file" Opening the file with a text editor, I found an empty line at the beginning (did not find the reason for it). I removed that line and saved the file and opened it in excel. It is showing only 2 rows - first row with the titles and the second row with the content of one row of mysql database – user20152015 Apr 11 '17 at 02:53
I added `ob_end_clean();` to the code and it removed the error while opening file. But now only issue is that I get only two rows in the excel sheet - one of the header and another a row from the mysql database. Please see. – user20152015 Apr 11 '17 at 04:12
Are you sure that your query should return more than one row? You can check that easily using phpMyAdmin or by printing some values under "while" block. – Mohammed Q. Hussain Apr 11 '17 at 14:04
I ran the query in phpMyAdmin and it returned more than 23,000 rows. So, there is no issue with query. – user20152015 Apr 12 '17 at 00:21
I haven't notice that. The line which initializes $n with 2 must be outside the loop. I updated the code in the answer. Please try it, it must work fine now. – Mohammed Q. Hussain Apr 12 '17 at 08:40
When I did that, it is giving only the row with titles – user20152015 Apr 12 '17 at 09:09
Can you please make a sqlfiddle and see if it works ? – user20152015 Apr 12 '17 at 15:22
Hi. I have tried a the code in my machine (with different database) and it worked fine. Can you create a new PHP page and run the same query and print the result instead of sending them to PHPExcel. See if there is more than one row will be printted. – Mohammed Q. Hussain Apr 13 '17 at 17:34
Sorry for late reply. I echoed this and it is giving all the results on the page from mysql database (I removed the phpexcel sending code) echo ""; echo "".$n.""; echo "".$row['t_id'].""; echo "".$row['t_text'].""; echo "".$row['user_name'].""; echo "".$row['description'].""; echo "".$row['time'].""; echo "".$row['place'].""; echo'"; – user20152015 Apr 19 '17 at 14:09
There was an issue with memory it seems and adding some code on it solved the problem. Please update it in your answer and let me know and I will choose your answer as right one `set_time_limit(0); ini_set('memory_limit', '1024M');` `$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp; $cacheSettings = array( 'memoryCacheSize' => '100MB'); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);` – user20152015 Apr 23 '17 at 13:35

Not getting any values in downloadable excel file generated using PHPexcel plugin

1 Answers1