Output Excel file as string?

Topics: User Forum
Jul 4, 2008 at 10:14 AM
I am using PHPExcel to generate and store Excel 2007 files as raw (BLOB) data in MySQL database. For this purpose I need the Excel file put inside a PHP string variable before I can save it in the database, but there seems to be no way of doing this without first having to save a temporary file. What I am therefore currently doing is the following:

  1. Generate PHPExcel object
  2. Save Excel file in temporary storage using the save() method of PHPExcel_Writer_Excel2007
  3. Use file_get_contents() function to read the saved file and store it into a string variable, say $str
  4. Save $str in the database
  5. Delete the temporary file

As one can see, I am really not using the file saved to disk for anything.

Therefore I was looking for some mechanism so I can skip first having to save the Excel file in the file system.

For example:

$str = $objWriter->getString(); // would be nice if this gave you the raw Excel file as a string

Is there a way to achieve this without first having to save file to in file system?

Note: I looked through the documentation as well as all discussion topics to see if this problem has been addressed before. Sorry if I have overlooked something.
Jul 4, 2008 at 10:37 AM
You can write to any PHP wrapper (http://nl3.php.net/manual/en/wrappers.php), so writing to an in-memory wrapper is possible without needing intermediate file.
Jul 5, 2008 at 11:24 AM
Edited Jul 6, 2008 at 3:46 AM
Thanks, Maarten, for that link plus quick response. This stuff was new to me so I learned many things from reading that section. While I feel I made some progress, I am still now sure exactly what you had in mind as how to avoid the intermediate file?

First I thought, aha, you are saying that the save method accepts a stream reference so one could do something like this:

$fp = fopen('php://memory', 'r+');
$writer->save($fp); // not working
$str = stream_get_contents($fp);

but that was not the case.

Then I thought perhaps try some crazy thing like this:


but then how would I grab the stream? Anyway, I got error message (using PHPExcel changeset 13825 from SVN):

Fatal error: Uncaught exception 'Exception' with message 'Could not close zip file php://memory.' in /opt/lampp/htdocs/dev/current/www/classes/PHPExcel/Writer/Excel2007.php:383 Stack trace: #0 /opt/lampp/htdocs/dev/current/www/test.php(32): PHPExcel_Writer_Excel2007->save('php://memory') #1 {main} thrown in /opt/lampp/htdocs/dev/current/www/classes/PHPExcel/Writer/Excel2007.php on line 383

I would be glad if you could give me a second hint of what you had in mind.

Perhaps it is just me, but I would find it useful if there was a more direct way of getting the raw Excel file using some method in the writer class. As far as I can see, if you can just get the raw contents you will be in a better position to do whatever you want. Saving the Excel file on disk, in database, or redirecting output to a client browser should all be simple tasks if you have direct access to the raw contents.

Thanks again for your response.

EDIT: Slightly rephased last paragraph for better clarification.
Jul 7, 2008 at 6:08 AM
Then the temporary file will be the solution to choose... Under the hood, output to php://output is doing the same.
Jul 7, 2008 at 10:42 AM
Ok, thanks, I will settle with the original solution then.
Jul 7, 2008 at 8:24 PM
Edited Jul 10, 2008 at 4:56 AM
Got an idea... because I just realized that the output buffer is stackable (http://php.net/manual/en/function.ob-start.php).

I am currently trying out the following function which does not break contents already sent to the output stream.

function PHPExcel_get_contents($objPHPExcel, $writerType) {
    $writer = PHPExcel_IOFactory::createWriter($objPHPExcel, $writerType);
    $contents = ob_get_contents();
    return $contents;

// example: How to put the raw Excel 2007 file in string variable?
$objPHPExcel = ...
$str = PHPExcel_get_contents($objPHPExcel, 'Excel2007'); // contains raw data for Excel 2007 file

Hope I have not overlooked something.

For some reason which I haven't got my head into the above function it is not working as expected anyway. I cannot write to headers after function call. I'll stick with your original suggestion using a temporary file:

function PHPExcel_get_contents($objPHPExcel, $writerType) {
    $writer = PHPExcel_IOFactory::createWriter($objPHPExcel, $writerType);
    $tmpFilename = tempnam('./temp', 'tmp');
    $contents = file_get_contents($tmpFilename);
    return $contents;