[PHP] Excel, Word, PowerPoint Libraries

Posted by cikul | Posted in PHP, Programming | Posted on 20-07-2012-05-2008

1

Need export your data in XLS Format? maybe DOC format or PPT format. It’s can be frustrating to PHP programmer converting data to these formats.

No worry, there are several libraries/classes available for PHP to simplify your process to export data. let’s discuss one by one :

1. WriteExcel Class

This Class based on John McNamara’s Spreadsheet::WriteExcel. It supports all the basic values for cells (strings, numbers), and has support for fomulas, images (bmp) and URL’s. But tt doesn’t support charts.

Personally i use this class for my project, because the  module is compatible with Excel 97, 2000, 2003 and 2007.  So I never worry what version of Excel in my user side.

For class documentation, you can refer to CPAN documentation, and  the class libraries, you can download from phpclasses.

Spoiler for Sample Code:


<?php
//require_once('OLEwriter.php');
//require_once('BIFFwriter.php');
require_once('Worksheet.php');
require_once('Workbook.php');

function HeaderingExcel($filename) {
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$filename" );
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Pragma: public");
}

// HTTP headers
HeaderingExcel('test.xls');

// Creating a workbook
$workbook = new Workbook("-");
// Creating the first worksheet
$worksheet1 =& $workbook->add_worksheet('First One');
$worksheet1->set_column(1, 1, 40);
$worksheet1->set_row(1, 20);
$worksheet1->write_string(1, 1, "This worksheet's name is ".$worksheet1->get_name());
$worksheet1->write(2,1,"http://www.phpclasses.org/browse.html/package/767.html");
$worksheet1->write_number(3, 0, 11);
$worksheet1->write_number(3, 1, 1);
$worksheet1->write_string(3, 2, "by four is");
$worksheet1->write_formula(3, 3, "=A4 * (2 + 2)");

$workbook->close();

2. Excel Writer for PHP (XML)

This class will allow Excel XML files to be generated dynamically. Nearly a full implementation of the MS XML architecture. This Class Requires PHP 5+ and does not require any XML libraries.

Never tested this Class, but seems more easy and simple than WriteExcel Class.

Download

Spoiler for Sample Code:
<?php
$xml = new ExcelWriterXML;

$format = $xml->addStyle('StyleHeader');
$format->fontBold();
$format->alignRotate(45);
$sheet = $xml->addSheet('My Sheet');

$sheet->writeString(1,1,'Header','StyleHeader');
$sheet->writeString(2,1,'Test String','StyleHeader');

$xml->sendHeaders();
$xml->writeData();
?>


3. PHP-Excel

If you no need complex output for your excel data, you can try this class.  php-excel aims to be the most simple and lightweight approach to convert a matrix-like, two-dimensional array from PHP to Microsoft Excel.

However, this library does not support many features, which would be possible: Creating functions, styling cells etc.

Download

Spoiler for Sample Code:

require (dirname (__FILE__) . "/class-excel-xml.inc.php");
$myarray =  array (
1 => array ("Oliver", "Peter", "Paul"),
array ("Marlene", "Mica", "Lina")
);

$xls = new Excel_XML;
$xls->addArray ( $myarray );
$xls->generateXML ( "testfile" );

?>


4. SimpleExcel PHP

PHP library with simplistic approach for parsing/writing data from/to Microsoft Excel XML/CSV format. It accepts getting all the cells in a row or only a single cell (the same goes for the column).

Download

Spoiler for Sample Code:

<?php

/**
 * Warning: note that there must be no data sent to browser
 * (be it an echo, HTML element, or even a whitespace)
 * before the writer->saveFile() method get called
 */
require_once('../src/SimpleExcel/SimpleExcel.php'); // load the main class file

$excel = new SimpleExcel('xml'); // instantiate new object (will automatically construct the writer)

$excel->writer->setData(
 array
 (
 array('ID', 'Nama Kota', 'ID Wilayah'),
 array('1', 'Kab Bogor', '1' ),
 array('2', 'Kab Sukabumi', '1' )
 )
); // add some data to the writer
$excel->writer->saveFile('example'); // force browser to save the file with specified name (example.xml)

?>


5. PHP Export Data

The library is based on the PHP-Excel mentioned above and can export data to Excel-friendly XML, CSV, or TSV.

There is support for streaming the data to a file or as a download.

Download

Spoiler for Sample Code:
</pre>
<?php

require "../php-export-data.class.php";

$excel = new ExportDataExcel('browser');
$excel->filename = "test.xml";

$data = array(
 array(1,2,3),
 array("asdf","jkl","semi"),
 array("1273623874628374634876","=asdf","10-10"),
);

$excel->initialize();
foreach($data as $row) {
 $excel->addRow($row);
}
$excel->finalize();
<pre>


6. PHPExcel

This class can write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML.

This class has rich features compared than other class, and support various spreadsheets formats.

Download

Spoiler for Sample Code:
<?php
/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
ini_set('include_path', ini_get('include_path').';../Classes/');

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';

// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

// Add some data
echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('D2', 'world!');

// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Simple');

// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

// Echo done
echo date('H:i:s') . " Done writing file.\r\n";


7. PHPWord

above we examine about excel libraries, now we examine a class to export word document.

PHPWord is a powerful PHP library for creating Word files. Actually, .docx files (Office Open XML) which can be opened by many office programs.

Using the library, we can insert text, text breaks, page breaks, headers/footers, tables, list elements, hyperlinks and format all of them. The cons is the libraries only support word > 2007, so if you have older version, you must install “Microsoft Office Compatibility Pack”

Download

Spoiler for Sample Code:

// Include the PHPWord.php, all other classes were loaded by an autoloader
require_once 'PHPWord.php';

// Create a new PHPWord Object
$PHPWord = new PHPWord();

// Every element you want to append to the word document is placed in a section. So you need a section:
$section = $PHPWord->createSection();

// After creating a section, you can append elements:
$section->addText('Hello world!');

// You can directly style your text by giving the addText function an array:
$section->addText('Hello world! I am formatted.', array('name'=>'Tahoma', 'size'=>16, 'bold'=>true));

// If you often need the same style again you can create a user defined style to the word document
// and give the addText function the name of the style:
$PHPWord->addFontStyle('myOwnStyle', array('name'=>'Verdana', 'size'=>14, 'color'=>'1B2232'));
$section->addText('Hello world! I am formatted by a user defined style', 'myOwnStyle');

// You can also putthe appended element to local object an call functions like this:
$myTextElement = $section->addText('Hello World!');
$myTextElement->setBold();
$myTextElement->setName('Verdana');
$myTextElement->setSize(22);

// At least write the document to webspace:
$objWriter = PHPWord_IOFactory::createWriter($PHPWord, 'Word2007');
$objWriter->save('helloWorld.docx');


8. PHPPowerPoint

PHPPowerPoint is a PHP library that makes it possible to create a presentation, add slides to it (including test and images), format them and more.

The presentation can be exported as a PowerPoint 2007 (presentationML) or PHPPowerPoint Serialized Spreadsheet file.

Download

Spoiler for Sample Code:


<?php

/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');

/** PHPPowerPoint */
include 'PHPPowerPoint.php';

/** PHPPowerPoint_IOFactory */
include 'PHPPowerPoint/IOFactory.php';

// Create new PHPPowerPoint object
echo date('H:i:s') . " Create new PHPPowerPoint object\n";
$objPHPPowerPoint = new PHPPowerPoint();

// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPPowerPoint->getProperties()->setCreator("Maarten Balliauw");
$objPHPPowerPoint->getProperties()->setLastModifiedBy("Maarten Balliauw");
$objPHPPowerPoint->getProperties()->setTitle("Office 2007 PPTX Test Document");
$objPHPPowerPoint->getProperties()->setSubject("Office 2007 PPTX Test Document");
$objPHPPowerPoint->getProperties()->setDescription("Test document for Office 2007 PPTX, generated using PHP classes.");
$objPHPPowerPoint->getProperties()->setKeywords("office 2007 openxml php");
$objPHPPowerPoint->getProperties()->setCategory("Test result file");

// Create slide
echo date('H:i:s') . " Create slide\n";
$currentSlide = $objPHPPowerPoint->getActiveSlide();

// Create a shape (drawing)
echo date('H:i:s') . " Create a shape (drawing)\n";
$shape = $currentSlide->createDrawingShape();
$shape->setName('PHPPowerPoint logo');
$shape->setDescription('PHPPowerPoint logo');
$shape->setPath('./images/phppowerpoint_logo.gif');
$shape->setHeight(36);
$shape->setOffsetX(10);
$shape->setOffsetY(10);
//$shape->setRotation(25);
$shape->getShadow()->setVisible(true);
$shape->getShadow()->setDirection(45);
$shape->getShadow()->setDistance(10);

// Create a shape (text)
echo date('H:i:s') . " Create a shape (rich text)\n";
$shape = $currentSlide->createRichTextShape();
$shape->setHeight(300);
$shape->setWidth(600);
$shape->setOffsetX(170);
$shape->setOffsetY(180);
$shape->getAlignment()->setHorizontal( PHPPowerPoint_Style_Alignment::HORIZONTAL_CENTER );
$textRun = $shape->createTextRun('Thank you for using PHPPowerPoint!');
$textRun->getFont()->setBold(true);
$textRun->getFont()->setSize(60);
$textRun->getFont()->setColor( new PHPPowerPoint_Style_Color( 'FFC00000' ) );

// Save PowerPoint 2007 file
echo date('H:i:s') . " Write to PowerPoint2007 format\n";
$objWriter = PHPPowerPoint_IOFactory::createWriter($objPHPPowerPoint, 'PowerPoint2007');
$objWriter->save(str_replace('.php', '.pptx', __FILE__));

// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

// Echo done
echo date('H:i:s') . " Done writing file.\r\n";

Share and Enjoy

Comments posted (1)

aku jadi lebih memahami deh nih

Write a comment

*