Translate

Saturday, July 30, 2011

Membuat file word, excel dan csv dalam PHP

 

Creating Word, Excel and CSV files with PHP

 Print Bookmark and Share

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.
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.
header("Content-type: application/vnd.ms-word");
header("Content-Disposition: attachment;Filename=document_name.doc");

echo "";
echo "";
echo "";
echo "My first document";
echo "";
echo "";
?>
As you may note, the formatting capabilities are limited here.
Back to top
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);
...
Back to top
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 at http://www.phplivedocx.org/articles/brief-introduction-to-phplivedocx/.
Back to top
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.
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment;Filename=document_name.xls");

echo "";
echo "";
echo "";
echo "testdata1 \t testdata2 \t \n ";
echo "";
echo "";
?>
Back to top
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);
...
Back to top
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.
$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);
?>
Back to top
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.
$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);
?>
at