PHP Excel Implementation in XAMPP Localhost

If you are planning to use PHP to create and interact with a Microsoft Excel spreadsheet application, then you can use the PHP Excel class. It can be a little tricky to use, however, especially for beginners. This tutorial will walk you through the basics and show you how to customize it for your own Excel implementation.

The class is complex, and sometimes getting started on the basics is hard, because PHPExcel is a complete class that interacts not only with MS Excel but with other formats such as PDF, CSV and the OpenDocument Spreadsheet.

The documentation included by the developers is broad, and does not cover the essential beginner steps of the implementation, nor the limitations. In this tutorial, emphasis is given to older versions of Excel because about half of those using Excel use MS Excel 2002/2003 (the older Excel version); this might be a little complex for a beginner, since the phpexcel class will create spreadsheets by default using the MS Excel 2007 version.

Lots of incompatibility issues and basic implementation problems are also discussed thoroughly in this tutorial, including the PHP- elated requirements and customizing the PHP script to output different Excel versions.

I assume you are developing web applications with MS Excel and PHP in the local host environment; this tutorial uses XAMPP local host servers in a Windows environment. If you are not familiar with XAMPP in a Windows environment, it is suggested that you read this tutorial. The completed application can then be deployed on the actual production web server (such as the one at your web hosting company, provided it meets the essential requirements).

The following are the objectives of this tutorial:

  • Learn how to install the PHPExcel class correctly in your Windows XAMPP local host environment.
  • If your target version is the latest version (MS Excel 2007), you will learn how to immediately create files and view them using Excel 2007.
  • If your target version is MS Excel 2002/2003, you will learn how to adjust the default PHP Excel parameters so that you can view the created files.

{mospagebreak title=Downloading and Installing PHP Excel Class in Windows XAMPP}

This section will guide you through downloading and installing the PHP Excel class in Windows XAMPP.

Step 1. Go to the official page: http://phpexcel.codeplex.com/ and then click the Download button.

Step 2. Go to the folder where the application has been downloaded. (In Windows, this is most likely found in the My Documents à Downloads folder)

Step 3. As of the time this tutorial was written (July 2010), the latest version is PHPExcel 1.7.3c. You should see the ZIP file of the application (for example 1.7.3c.zip) in the download folder.

You will need to right click on the zip archive and then “Extract to 1.7.3c”. The zip application will then extract the entire contents to a folder named 1.7.3c.

Step 4. Rename the folder 1.7.3c to phpexcel  

The folder should contain the contents shown in the screen shot below: 

Step 5. Copy the phpexcel folder to your XAMPP htdocs directory.

If your XAMPP directory is installed in the root directory of your C: drive, the path to the phpexcel directory will be: C:xampphtdocsphpexcel

To see if you have correctly installed XAMPP in htdocs, try entering this path in your web browser: http://localhost/phpexcel/install.txt. You should see the contents of the install.txt file without a 404 or file not found error.

{mospagebreak title=Configuration of PHP Excel Class}

According to the developers of this class, the following requirements must be met for this application to work properly:

  1. At least PHP Version 5.2.0
  2. php_zip enabled
  3. php_xml enabled
  4. php_gd2 enabled

If you are still new to these functions (which most new developers are), then you might have some difficulty in checking whether these requirements are met by your existing XAMPP local web server.

So let’s go through the procedures one by one for how to check for the minimum requirements.

First, let’s check the PHP version of your XAMPP local host web server.

Step 1. Using your browser, go to http://localhost/

Step 2. Log in using your XAMPP administrator credentials.

Step 3. In the sidebar navigation menu, click phpinfo()

The PHP version can be clearly seen as the H1 header element (for example, PHP Version 5.2.8) at the top portion of the page.

Next, let’s check to see if php_zip is enabled.

Using the result/output of the phpinfo (), you need to find out if you have this entry:

 

If you do not have that entry, then php_zip has been disabled. You need enable it by following the steps below:

1. Using the output of your phpinfo() provided above, find the path of your php.ini file by looking at the value of Loaded Configuration File

An example path could be: C:xamppapachebinphp.ini

2. Navigate through that directory where your php.ini resides; double click on it, and it will open in notepad.

3. Since there are a lot of entries in php.ini, using your notepad, go to Edit à Find, and then type:

php_zip.dll

4. If php_zip has been disabled, you should see a semicolon before the file name, for example:

;extension=php_zip.dll

5. To enable php_zip, simply remove the semicolon and then save your php.ini file (File à Save 

extension=php_zip.dll

6. In Windows, go to Start à All programs à Apache friends à XAMPP à XAMPP Control Panel.

7. In the control panel, stop the Apache module by clicking “Stop.” Wait for around 10 seconds and then click “Start” again.

This process will restart your local Apache server to implement the changes you have made to your php.ini file.

8. Try viewing your phpinfo again and you should see the php zip entry in your phpinfo result (refer to screen shot shown previously).

Now, let’s check to see if php_xml has been enabled.

Using your phpinfo() result, you should see the results shown in the screen shot below:

 

These functions are enabled by default during the PHP installation along with XAMPP.  

Finally, we’ll check to see if php_gd2 has been enabled.

You should check your php.ini to see if php_gd2.dll has been uncommented (semi-colon removed):

extension=php_gd2.dll

{mospagebreak title=Testing Scripts to Create MS Excel Files}

If your target application is MS Excel 2007, phpexcel’s default PHP scripts will create MS Excel 2007 documents by default. To do this:

Step 1. In the browser, execute this URL: http://localhost/phpexcel/Tests/01simple.php

Step 2. The created files are placed in the Tests folder /phpexcel/Tests  

It is named 01simple.xlsx 

Step 3. Open the files using MS Excel 2007.

However, problems will arise if you have MS Excel 2002 (XP)/2003 installed and not Excel 2007. You cannot view the created MS Excel 2007 files with an MS Excel 2002/XP installation, even if you have compatibility programs and patches installed.

The error generated is “The Converter Failed to Open the File.”

Therefore, this phpexcel class will work best if you have an MS Excel installed that matches the version of your phpexcel-created files.

If your target application is MS Excel 2002(XP) and the later version, like MS Excel 2003 (but not MS Excel 2007), then you need to tweak the script as follows:

Step 1. Go to C:xampphtdocsphpexcelTests1simple.php and open the PHP script with your favorite editor.

Step 2. You need to edit the following lines so that it will output for MS Excel 2002/2003 and not Excel 2007.

Look for these lines:

// Save Excel 2007 file

echo date(‘H:i:s’) . " Write to Excel2007 formatn";

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007′);

$objWriter->save(str_replace(‘.php’, ‘.xlsx’, __FILE__));

 

And change them to:

 

// Save Excel 2002 file

echo date(‘H:i:s’) . " Write to Excel2002 formatn";

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5′);

$objWriter->save(str_replace(‘.php’, ‘.xls’, __FILE__));

You can also change the document properties defined by $objPHPExcel->getProperties() to relevant information (creator, subject, etc.).

The options to make changes so that the created spreadsheet will be compatible with older versions of Excel are the createwriter($objPHPExcel)…

Other supported file formats of the writer can be found here: C:xampphtdocsphpexcelClassesPHPExcelWriter

Based on that information, it is possible for you to create HTML, CSV, Serialized and PDF output, etc. using PHPExcel.  

Step 3. Save the test script 01simple.php

Step 4. Execute it in the browser:  http://localhost/phpexcel/Tests/01simple.php 

Step 5. Open the newly created spreadsheet file, named 01simple.xls.

And then you should see output similar to what is shown below (using the default contents):

To add data to the spreadsheet, you can modify the setCellValue parameter such as what is shown below:

$objPHPExcel->setActiveSheetIndex(0)

            ->setCellValue(‘A1′, ‘Hello’)

            ->setCellValue(‘B2′, ‘world!’)

            ->setCellValue(‘C1′, ‘Hello’)

            ->setCellValue(‘D2′, ‘world!’);

 

For example, if you would like to write the text (coming from a web form or another application, for example) “The quick brown fox jumps over the lazy dog” in cell D5, then the syntax would be:

// Add some data

echo date(‘H:i:s’) . " Add some datan";

$objPHPExcel->setActiveSheetIndex(0)

            ->setCellValue(‘D5′, ‘The quick brown fox jumps over the lazy dog.’);

 

You can comment “Miscellaneous glyphs” as shown below, for example, so that the output text contains only the intended data:

 

// Miscellaneous glyphs, UTF-8

//$objPHPExcel->setActiveSheetIndex(0)

//->setCellValue(‘A4′, ‘Miscellaneous glyphs’)

//->setCellValue(‘A5′, ‘éàèùâêîôûëïüÿäöüç’);

 

In the next tutorials on PHP Excel, you will learn details of how to work with Excel formulas in PHP. Formulas are fairly common applications in Excel.

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort