Saturday, March 13, 2010

12:51 AM
1

Browsing the World Wide Web you can find out various methods of creating files with PHP. In this article we demonstrate several ways to create Microsoft Word and Excel documents, and also CSV files using PHP.

  • MS Word document
    • Using HTTP headers
    • Using COM objects
    • Using OpenOffice templates
    • Using Zend Framework component phpLiveDocx
  • MS Excel document
    • Using HTTP headers
    • Using COM objects
  • CSV file
    • Using HTTP headers
    • Using fputcsv()
How to create MS Word document
Method 1 - Using HTTP headers
In this method you need to format the HTML/PHP page using Word-friendly CSS and add header information to your PHP script. Make sure you don't use external style sheets since everything should be in the same file.
As a result user will be prompted to download a file. This file will not be 100% "original" Word document, but it certainly will open in MS Word application. You can use this method both for Unix and Windows environments.
<?php
header("Content-type: application/vnd.ms-word");
header("Content-Disposition: attachment;Filename=document_name.doc");

echo "<html>";
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=Windows-1252\">";
echo "<body>";
echo "<b>My first document</b>";
echo "</body>";
echo "</html>";
?>
As you may note, the formatting capabilities are limited here.

Method 2 - Using COM objects
Note that the server running the code stated below must have MS Word installed. COM will work on Windows only.
Word document is saved to the temporary directory and then sent to the browser via readfile() function.
...
// Create new COM object – word.application
$word = new COM("word.application");

// Hide MS Word application window
$word->Visible = 0;

//Create new document
$word->Documents->Add();

// Define page margins 
$word->Selection->PageSetup->LeftMargin = '2';
$word->Selection->PageSetup->RightMargin = '2';

// Define font settings
$word->Selection->Font->Name = 'Arial';
$word->Selection->Font->Size = 10;

// Add text
$word->Selection->TypeText("TEXT!");

// Save document
$filename = tempnam(sys_get_temp_dir(), "word");
$word->Documents[1]->SaveAs($filename);

// Close and quit
$word->quit();
unset($word);

header("Content-type: application/vnd.ms-word");
header("Content-Disposition: attachment;Filename=document_name.doc");

// Send file to browser
readfile($filename);
unlink($filename);
...

Method 3 - Using OpenOffice templates
  1. Create manually an ODT template with placeholders, like [%value-to-replace%].
  2. When instantiating the template with real data in PHP, unzip the template ODT (it's a zipped XML), and run against the XML the textual replace of the placeholders with the actual values.
  3. Zip the ODT back.
  4. Run the conversion ODT -> DOC via OpenOffice command line interface.
There are tools and libraries available to ease each of those steps.
Method 4 - Using Zend Framework component phpLiveDocx
One of the ways to create DOC files in Linux using PHP is to use the Zend Framework component phpLiveDocx. It allows developers to generate documents by combining structured data from PHP with a template, created in a word processor. The resulting document can be saved as a PDF, DOCX, DOC or RTF file. The concept is the same as with mail-merge.
PhpLiveDocx is completely free to download and use. For more information, please take a look athttp://www.phplivedocx.org/articles/brief-introduction-to-phplivedocx/.


How to create MS Excel document

Method 1 - Using HTTP headers
As described for the MS Word, you need to format the HTML/PHP page using Excel-friendly CSS and add header information to your PHP script.
<?php
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment;Filename=document_name.xls");

echo "<html>";
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=Windows-1252\">";
echo "<body>";
echo "<b>testdata1</b> \t <u>testdata2</u> \t \n ";
echo "</body>";
echo "</html>";
?>

Method 2 - Using COM objects
Note that the server running the code stated below must have MS Excel installed.
We use the same approach as for MS Word with saving a file to the temporary directory first.
...
//Create new COM object – excel.application
$xl = new COM("excel.application");

//Hide MS Excel application window
$xl->Visible = 0;

//Create new document
$xlBook = $xl->Workbooks->Add();

//Create Sheet 1
$xlBook->Worksheets(1)->Name = "Worksheet 1";
$xlBook->Worksheets(1)->Select;

//Set Width & Height
$xl->ActiveSheet->Range("A1:A1")->ColumnWidth = 10.0;
$xl->ActiveSheet->Range("B1:B1")->ColumnWidth = 13.0;

//Add text
$xl->ActiveSheet->Cells(1,1)->Value = "TEXT";
$xl->ActiveSheet->Cells(1,1)->Font->Bold = True;

//Save document
$filename = tempnam(sys_get_temp_dir(), "excel");
$xlBook->SaveAs($filename);

//Close and quit
unset( $xlBook);
$xl->ActiveWorkBook->Close();
$xl->Quit();
unset( $xl );

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment;Filename=document_name.xls");

// Send file to browser
readfile($filename);
unlink($filename);
...


How to create a CSV file

Method 1 - Using HTTP headers
As in the examples for the Word and Excel, you need to add header information to your PHP script.
The code snippet below creates a CSV file of the specified table including its column names. Then user will be prompted to download this file.
<?php
$table = 'table_name';
$outstr = NULL;

header("Content-Type: application/csv");
header("Content-Disposition: attachment;Filename=cars-models.csv");

$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("db",$conn);

// Query database to get column names  
$result = mysql_query("show columns from $table",$conn);
// Write column names
while($row = mysql_fetch_array($result)){
    $outstr.= $row['Field'].',';
}  
$outstr = substr($outstr, 0, -1)."\n";

// Query database to get data
$result = mysql_query("select * from $table",$conn);
// Write data rows
while ($row = mysql_fetch_assoc($result)) {
    $outstr.= join(',', $row)."\n";
}

echo $outstr;
mysql_close($conn);
?>

Method 2 - Using fputcsv()
The fputcsv() function formats a line as CSV and writes it to an open file. For more information, take a look at http://php.net/manual/en/function.fputcsv.php.
The code snippet below creates a CSV file of the specified table including its column names and sends it to the browser.
<?php 
$table = 'table_name';
$filename = tempnam(sys_get_temp_dir(), "csv");

$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("db",$conn);

$file = fopen($filename,"w");

// Write column names
$result = mysql_query("show columns from $table",$conn);
for ($i = 0; $i < mysql_num_rows($result); $i++) {
    $colArray[$i] = mysql_fetch_assoc($result);
    $fieldArray[$i] = $colArray[$i]['Field'];
}
fputcsv($file,$fieldArray);

// Write data rows
$result = mysql_query("select * from $table",$conn);
for ($i = 0; $i < mysql_num_rows($result); $i++) {
    $dataArray[$i] = mysql_fetch_assoc($result);
}
foreach ($dataArray as $line) {
    fputcsv($file,$line);
}

fclose($file);

header("Content-Type: application/csv");
header("Content-Disposition: attachment;Filename=cars-models.csv");

// send file to browser
readfile($filename);
unlink($filename);
?>

1 comments:

Alex said...

Seven days ago I was at the Inet and noticed there a tool. I downloaded it and surprisingly it settled all my troubles for a minute. It's not the all the software helped me very effectively and I hope will help in distinct composite conditions also - how to Excel file repair.