Sometimes you have to generate some MS Office document for the users of your website using dynamic data. For example, few days ago, I had to generate some Excel reports using data from some tables in MySQL. I knew about the COM but it has a lot of disadvantages, including Windows-only platform and not very high speed. CSV is much easier and faster but you can't format the cells to make them better-looking. The method I've never thought about before was using HTML. So here came the inspiration and after some research and experiments I wrote this tutorial for all of you who are looking how to create Office documents on the fly using PHP. Of course, I included the COM methods and some methods to create other MS Office documents like DOC and PPT, to make this tutorial as full and as useful as possible.
How to generate Microsoft Office Documents (DOC, XLS, PPT) on the fly using PHP
There are two main approaches on building documents in MS Word, MS Excel and MS PowerPoint using PHP. The first is by using the COM library (only if you are running your PHP script on a Windows server) and the other is by "tricking" the Microsoft Office programs by using simple HTML or CSV (for Excel).
1. How to create a document with COM:
As I said above, COM is available only in Windows (for example if you are running Apache on Windows XP). By using COM you can launch Microsoft Word or any other available component (or custom component made by you and registered with regsvr32), fill in a document template and save the result as a Word document (as .doc, .rtf or other available formats) and send it to the users of your website.
It's very easy to use it. The code below shows how to create a Word .doc file:
$word = new COM("word.application");
//To see the version of Microsoft Word, just use $word->Version
echo "I'm using MS Word {$word->Version}";
//It's better to keep Word invisible
$word->Visible = 0;
//Creating new document
$word->Documents->Add();
//Setting 2 inches margin on the both sides
$word->Selection->PageSetup->LeftMargin = '2"';
$word->Selection->PageSetup->RightMargin = '2"';
//Setup the font
$word->Selection->Font->Name = 'Verdana';
$word->Selection->Font->Size = 8;
//Write some text
$word->Selection->TypeText("Hello, universe!");
//Save the document as DOC file
$word->Documents[1]->SaveAs("C:\htdocs\hello2.doc");
// or use: $word->Documents[1]->SaveAs("C:htdocshello2.rtf",6); to save as RTF file
// or use: $word->Documents[1]->SaveAs("C:htdocshello2.htm",8); to save as HTML file
//And of course, quit Word
$word->quit();
$word->Release();
$word = null;
//Give the user a download link
echo '<a href="hello2.doc">Download file as .doc</a>';
?>
Here are screenshots of the MS Word document after downloaded and opened:
Here comes one of the most important questions - how to find the names and the parameters of these functions like "SaveAs". Just open Microsoft Word, press Alt+F11 to start the Visual Basic Editor and then press F2 (or View -> Object Browser). Find "ThisDocument" on the left. In the right frame you'll see the available variables and functions. Here is a screenshot showing the function SaveAs:
As you can see, the second parameter is the file format. Above, in the sample code, if you write 6, your document will be saved in RTF format. However, Microsoft Word allows many formats. To see their numbers find "wdSaveFormat" in the left frame, click on the desired format in the right frame and you'll see its number. This procedure is similar in the other Office programs (however, for example, the Save Formats are displayed in PpSaveFileFormat in Power Point).
Creating Excel .xls file is similar. The code below creates an Excel file and fills one of the cells. If you get your data from a SQL server you can use "for" but don't forget to "Activate" a cell before writing in it.
$excel = new COM("excel.application");
//Keep Excel invisible
$excel->Visible = 0;
//Create a new workbook
$wkb = $excel->Workbooks->Add();
$sheet = $wkb->Worksheets(1);
//This code adds the text 'Test' on row 2, column 4
$sheet->activate;
$cell = $sheet->Cells(2,4);
$cell->Activate;
$cell->value = 'Test';
//Save the file just like the Word file above
$wkb->SaveAs("C:\htdocs\excel123.xls");
//Quit MS Excel
$wkb->Close(false);
$excel->Workbooks->Close();
$excel->Quit();
unset($sheet);
unset($excel);
?>
And here is the file opened with Microsoft Excel:
If you need to create a presentation using some dynamic data, here is how to create a PowerPoint .ppt file. You can create as many slides as you want and then add elements inside by using "Slides[number_of_slide]":
$powerpnt = new COM("powerpoint.application");
//Creating a new presentation
$pres=$powerpnt->Presentations->Add();
//Adds the first slide. "12" means blank slide
$pres->Slides->Add(1,12);
//Adds another slide. "10" means a slide with a clipart and text
$pres->Slides->Add(2,10);
//Adds a textbox (1=horizontal, 20=left margin, 50=top margin, 300=width, 40=height)
$pres->Slides[1]->Shapes->AddTextbox(1,20,50,300,40);
//Adds a 16-point star (94=16 point star, 100=left margin, 200=top margin, 300=width, 300=height)
$pres->Slides[1]->Shapes->AddShape(94,100,200,300,300);
//Save the document as PPT file
$powerpnt->Presentations[1]->SaveAs("C:\htdocs\byeworld.ppt");
//And of course, quit Power Point
$powerpnt->quit();
//Give the user a download link
echo '<a href="byeworld.ppt">Download file as .ppt</a>';
?>
To understand the meaning of each function, just open the Object Browser. It's on the same place as in Microsoft Word (Alt+F11 to open Visual Basic Editor, F2 to open Object Browser). It's really interesting to experiment with these functions.
2. Using HTML (or CSV for Excel):
Of course, if you are running your PHP script on a UNIX server or you don't want to use COM, there is an alternative (I recommend using these alternative methods unless you really need to do something special in your documents). It's much faster but you won't have the control over the document that COM provides.
Probably you know that you can create and read HTML files with MS Word. Here comes the idea to format the content just like any other HTML file and write it in a file with ".doc" extension.
$filepnt = fopen("hellohtml.doc", 'w+');
$content = "<html><head></head><body><b>Say hello</b> to my http server</body></html>";
fwrite($filepnt, $content);
fclose($filepnt);
echo '<a href="hellohtml.doc">Download as .doc</a>';
?>
Here is a screenshot of the file opened with Microsoft Word:
For Microsoft Excel, you need to create a file in CSV format (tab-delimited). You can save it in both .csv and .xls. The PHP code is much like the previous one:
$filepnt = fopen("newfile.xls", 'w+');
//This is an example of a table about some users of an example website
//Of course, if you are getting the data from MySQL (or somewhere else) the code should be formatted better
$content = "Id\tUsername\tE-mail\tLevel\n"; //"\t" means a TAB and "\n" is a New line
$content .= "1\tWebmaster\twebmaster@example.com\t1\n"; //every new line is a new row in the table
$content .= "2\tmysql_fan\tmysql@php.be\t3\n";
$content .= "3\tfedora\tfedora@redhat.com\t3\n";
fwrite($filepnt, $content);
fclose($filepnt);
echo '<a href="newfile.xls">Download as .xls</a>';
?>
Here is a screenshot of the file opened with Microsoft Excel:
Excel also can open HTML files. It allows you to make the table look better. Here is an example:
$filepnt = fopen("newfilehtml.xls", 'w+');
//This is an example of a table about some users of an example website
//Of course, if you are getting the data from MySQL (or somewhere else) the code should be formatted better
$content = "<table border="1"><tr><td bgcolor="#CCCCCC"><b>Id</b></td><td bgcolor="#CCCCCC"><b>Username</b></td></tr> <tr><td>1</td><td>Webmaster</td></tr> <tr><td>2</td><td>mysql_fan</td></tr> <tr><td>3</td><td>slackware</td></tr></table>";
fwrite($filepnt, $content);
fclose($filepnt);
echo '<a href="newfilehtml.xls">Download as .xls</a>';
?>
Here is how it looks like:
Microsoft PowerPoint opens HTML files, too, so if you just replace the XLS extension with PPT in the code above, here is how PowerPoint renders the table:
It's all about writing plain text in a file, so you can create these Office documents using Python, Ruby or any other language as well. These PHP examples are just to show how it works.
Creating ZIP archives using PHP
It's a good idea to archive the files to save some hosting bandwidth. Especially the Microsoft Office documents can be really large sometimes (if they are created with COM or manually). I've been searching a lot and it seems that the most easy and simple way to create a ZIP file is to use the ziplib class. Here is how it can be implemented:
include("ziplib.php");
$zipfile = new Ziplib;
$zipfile->zl_add_file("Here is the content of File No.1. For example, this could be your just generated XLS file using the methods above.","thenameofthefile.xls","g9");
$zipfile->zl_add_file("And this could be your DOC file's content.","letter.doc","g9");
//You can stream the ZIP file or write it in a file on your server
header("Content-type: application/zip");
header("Content-Disposition: attachment; filename="file.zip"");
echo $zipfile->zl_pack("You can leave a comment in the ZIP file");
?>
You can download the Ziplib class here (.zip, 7.14 KB), (.tar.gz, 5.15 KB). Put the 3 php files in some directory and include the "ziplib.php" file.
Streaming the file directly
If you prefer, instead of creating and saving the document on the server and giving the user a download link, you can stream the file directly. Just add this code in the beginning of the php file:
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=myexcelreport.xls");
header("Pragma: no-cache");
header("Expires: 0");
...
//And here echo the document's content instead of writing it into a file on your server
?>
Alternatives of the Content-Type above are "application/vnd.ms-excel" for MS Excel files and "application/msword" for MS Word files.
I hope that this tutorial was useful for you. If you have any comments or if you've seen an error, feel free to post your comment below. Thank you.
Tags: technology, php, programming, web, development, software
