How to use PHP EXcel functionality.

Topics: Developer Forum, User Forum
Feb 28, 2013 at 11:16 AM
Please let me know the process to use PHP Excel functionality as i required to export a recordset created with multiple tables to excel file in core php.

But i dont know the process to download and use of PHP Excel.

Any help will be highly appreciated.

Thanks,
Pooja
Coordinator
Feb 28, 2013 at 12:30 PM
to download, go to the Home page (by clicking on the "Home" tab); then click on the download button.

To use, read some of the documentation, and look at some of the examples in the /Tests directory of the download
Feb 28, 2013 at 1:48 PM
i have installed PHP excel in my wamp server . But when i run the tests page it shows the below error:

Fatal error: require_once(): Failed opening required '../Build/PHPExcel.phar' (include_path='.;C:\php\pear') in C:\wamp\www\Tests\01pharSimple.php on line 37
Coordinator
Feb 28, 2013 at 1:58 PM
If you haven't installed the phar file (or haven't installed it in that directory), then the example using the phar won't work.... if you've installed the phar, then modify that test to point to the phar. The other examples in /Tests all work with source files rather than the phar.
Mar 4, 2013 at 7:30 AM
Hi,

i have one code when i run that and try to open a excel file it gives me below error

Excel cannot open the file because file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the formatted file



Below is my code:

<?php

/* require the PHPExcel file 1.0 /
require 'Classes/PHPExcel.php';
/* Set Memory Limit 1.0 /
ini_set("memory_limit","500M"); // set your memory limit in the case of memory problem
/* Caching to discISAM 1.0/
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_discISAM;
$cacheSettings = array( 'dir' => '/usr/local/tmp' // If you have a large file you can cache it optional
                  );
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

/* connection with the database 1.0 /
/* $dbhost = "localhost";
$dbuser = "user";
$dbpass = "pw";
$dbname = "mydb";
mysql_connect($dbhost,$dbuser,$dbpass); 
mysql_select_db($dbname);*/
require_once('Connections/mycon.php');
mysql_select_db($database_mycon,$mycon); //file having my database connections

/* Query 1.0 /
$query = "SELECT * FROM user_details";

if ($result = mysql_query($query) or die(mysql_error())) {
echo $result;
/* Create a new PHPExcel object 1.0 /
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Data');
}

/* Loop through the result set 1.0 /
$rowNumber = 1; //start in cell 1
while ($row = mysql_fetch_row($result)) {
   $col = 'A'; // start at column A 
   foreach($row as $cell) {
      $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
      $col++;
   }
   $rowNumber++;
}

/* Create Excel 2007 file with writer 1.0 /
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Technical.xlsx"');
header('Cache-Control: max-age=0');
$objPHPExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

/ $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');/
$objWriter->save('php://output');
exit;

?>
Coordinator
Mar 4, 2013 at 7:40 AM
Open the file in a text editor.

Look for whitespace characters (spaces, tabs, newlines, etc.) or a BOM marker at the beginning or end of the file, in which case your script is outputting those additional characters; or for any obvious error messages in the content of the file.
Mar 4, 2013 at 7:48 AM
i have tried to open the file in text editor but it is giving me wierd output.


I am only embedding the PHP code in file.

Please help


Thanks
Coordinator
Mar 4, 2013 at 9:17 AM
That "weird output" is the xlsx file stream. It should begin with the characters:
PK
indicating that it is a zipped archive.
If there are any characters before the PK then it's corrupted

Attache the file to http://phpexcel.codeplex.com/workitem/10749?ProjectName=phpexcel and I'll take a look at it
Mar 4, 2013 at 10:13 AM
Hi,

I have attached the file to http://phpexcel.codeplex.com/workitem/10749?ProjectName=phpexcel

File Name is Technical_test.xlsx


Any kind of help will be appreciated


Thanks,
Pooja
Coordinator
Mar 4, 2013 at 11:07 AM
As the first bytes in the file are
Resource id #14
I suspect you're trying to echo a filehandle or database handle to screen. Remove that from your code.
Mar 4, 2013 at 11:28 AM
I have removed the echo statement but it is not exporting any data . The sheet is totally blank.
Coordinator
Mar 4, 2013 at 11:36 AM
Then check that your query is actually returning data
Mar 4, 2013 at 11:39 AM
Yes it is returning the data
Coordinator
Mar 4, 2013 at 11:56 AM
Please run some basic diagnostics.... change the save to a disk file while you're debugging and put some echo statements in your code to show what data is being stored in which cell in your loops
Mar 5, 2013 at 9:14 AM
Hi ,
Instead of producing output in IE i have saved file on disk and it is returning me blan worksheet. But when i try to put any sort of echo statements in the code the excel file gets corrupted.
Mar 8, 2013 at 10:35 AM
i tried below code and my worksheet is opening with blank:

<?php

/* require the PHPExcel file 1.0 /
require 'Classes/PHPExcel.php';
/* Set Memory Limit 1.0 /
ini_set("memory_limit","500M"); // set your memory limit in the case of memory problem
/* Caching to discISAM 1.0/
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_discISAM;
$cacheSettings = array( 'dir' => '/usr/local/tmp' // If you have a large file you can cache it optional
               ); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); 
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Data');
$objPHPExcel->getActiveSheet()->setCellValue('A1','hello');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Technical.xlsx"');
header('Cache-Control: max-age=0');
$objPHPExcel = new PHPExcel(); / $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);/

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output'); exit;
?>

Please help

Thanks