Creating Excel document from PHP

[johnsmith153]

How is the best way to do it?

 

I only need PHP to populate Excel with one column - the 500 "names" from a database (which changes regularly).

 

The rest I could just do easily in Excel.

 

This is for internal use - the database is really only searched to save someone typing out 500 names.

[.josh]

It's amazing what google comes up with.

[Mark Baker]

How is the best way to do it?

Read the signature

[johnsmith153]

Mark,

 

This looks a little complicated for me.

 

I can actually now create a simple Excel file (without any formatting) - it is very easy with what I have found.

 

However, my method does not do any formatting of the cells.

 

Ideally what I want is to be able to merge 2 files. The plain version without cell formats that I can produce and another Excel file that has all the fancy formats etc (that never changes and that I have already produced).

 

In fact I only need PHP to populate with one column / one database field.

 

But it must be very simple. I.e import the existing document, edit and export. Or, just merge two completed files server side.

 

I can pay money to anyone who can make this easy for me.

[Mark Baker]

I can actually now create a simple Excel file (without any formatting) - it is very easy with what I have found.

 

However, my method does not do any formatting of the cells.

I'm assuming that what you're actually creating is a CSV file, rather than a real Excel file.

 

But it must be very simple. I.e import the existing document, edit and export.

That's exactly what I'm suggesting. Import, edit, re-write.

 

<?php

error_reporting(E_ALL);

set_include_path(get_include_path() . PATH_SEPARATOR . './PHPExcel/Classes/');

include 'PHPExcel/IOFactory.php';

//  IMPORT
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("myExcelFile.xlsx");


//  EDIT
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'World!');


//  RE-WRITE
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("myExcelFile.xlsx");

?>

 

[johnsmith153]

Just a thought, I only have Excel 2003. I am prepared to pay for the upgrade if I need to so I can get this done. Do I need to?

[Mark Baker]

Just a thought, I only have Excel 2003.

Excel 2003 (Excel5) simply uses a different reader and writer in PHPExcel.

 

<?php

error_reporting(E_ALL);

set_include_path(get_include_path() . PATH_SEPARATOR . './PHPExcel/Classes/');

include 'PHPExcel/IOFactory.php';

//  IMPORT
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("myExcelFile.xls");


//  EDIT
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'World!');


//  RE-WRITE
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save("myExcelFile.xls");

?>

 

I am prepared to pay for the upgrade if I need to so I can get this done. Do I need to?

No, it's not necessary for you to pay for an Excel upgrade... although PHPExcel or PHPFreaks can always benefit from a donation if you believe that you can use this solution.

[johnsmith153]

I will make a donation to PHPExcel.

 

That is if I can get it to work, which I am having a look at now. I am sure I will be able to, you have made it very simple.

 

Thanks.

[Mark Baker]

That is if I can get it to work, which I am having a look at now. I am sure I will be able to, you have made it very simple.

In addition to the files under /Documentation, there's plenty of simple examples in the /Tests directory... and you can always post questions in the PHPExcel help forum on codeplex.

[johnsmith153]

I am still part way through doing this, shouldn't be a problem.

 

Just a thought, is there a way I can export out as a PDF also?

 

Ie exactly the same as what this should do (still importing the .xls to start), but to then create/export as a PDF instead of .xls.

[johnsmith153]

Mark, I have trtied using your program to export out at PDF.

 

If I start with a normal Excel file (that would fit on an A4 page) and then export out at PDF, I get about a 7 page PDF document.

 

If I try to convert an Excel file with about 8 cells, I get a full size A4.

 

Basically, I want it not to enlarge everything.

 

Does this usually happen?

[Mark Baker]

Just a thought, is there a way I can export out as a PDF also?

You've already found out that you can, but I don't actually use the PDF exporter myself yet because it still has some problems, though I do use the two Excel formats, HTML and CSV extensively.

 

As you've noticed, it seems to display everything disproportionately large, and if your worksheet has too many columns, then they simply vanish into the aether. It's an exporter that still needs work.

 

What I have done as an interim measure for the professional systems that I develop using PHPExcel is modify it to generate an HTML first, and then convert that to PDF

[johnsmith153]

Mark,

 

I am finding if I import an Excel file with "some" formatiing that PHP's memory just can't handle it.

 

If I import an Excel file with say just a title and a few cells completed, then it is fine.

 

However, I have imported an Excel file that is 300k - not that much - and it just cant cope.

 

It keeps complaining of being out of memory.

 

So I have set ini_set('memory_limit','1000000000M'); - which is crazy, and then it just hits my 300 max execution time - surely this isn't right?

 

In fact if I set memory limit to something like 10000M it still complains of being out of memory.

 

If I uploaded a 5mb Excel file or had a very low memory limit then fair enough, but you can see that it just seems impossible to do anything with my 300k file, regardles of any other settings.

 

I have encoded video on this server - surely encoding video is a bigger job than what this is doing?