You are here: Home » Uncategorized » Export data from firebird database to Excel using PHP

Export data from firebird database to Excel using PHP

If you want to create simple reporting (rather exporting) tool in PHP for use with Firebird database this post is for you. I assume you have Firebird on Windows 7 machine (in Windows 8 it should be similar) with IIS. With some basic changes this will work on Apache in Windows.

First step – create your view in firebird

Take your time to create proper SQL query as new view in database. I think it is more comfortable. In your reporting tool you will just run select * from view_name.

2 step – enable IIS on machine

In Windows run Programs and Features and then click Turn Windows features on or off

Enable World Wide Web services and than enable CGI in Appllication Development Features.

3 step – get PHP

Go to web page and than „Windows binaries and source”. Download x86 Non Thread Safe ZIP file

Unpack ZIP file and place its content in c:\PHP

Go to c:\PHP and make copy of file php.ini-development     Name this file php.ini

4 step – Configure your environment

  1. Open php.ini file in text editor.
  2. Find ;cgi.force_redirect = 1and change it to:cgi.force_redirect = 0
  3. Find ;error_log = php_errors.logand change it to:error_log = php_errors.logThat way you will be able to find all PHP errors in file c:\PHP\php_errors.log

    Just in case.

  4. Find ;extension=php_interbase.dlland change it to:extension=php_interbase.dllThis will enable support for our database.
  5. Find :extension_dir = „ext” and change it to:extension_dir = „ext”By default ext directory make no effect and also your interbase extension in it.
  6. Go to C:\Program Files (x86)\Firebird\Firebird_1_5\binSelect file fbclient.dll and copy it to C:\PHPCopy fbclient.dll also to c:\windows\system32
  7. Go to C:\PHP\extCopy file: php_interbase.dll to c:\windows\system32
  8. Go to C:\PHPCopy files: libeay32.dll and ssleay32.dll to c:\windows\system32

 6 step – IIS configuration

  1. Run IIS manager (windows key + R    inetmgr)
  2. You can enable PHP for whole server or just one site. Go to Handler Mappings and new module mapping.Request path: *.phpModule: FastCGIModule

    Executable: C:\php\php-cgi.exe

  3. Now go to sites (list of sites).Click on Default Web SiteChoose option „Limits…” on the right menubar.

    Type in 600 seconds. Reports may take long time, so you don’t want timeouts before the end of report generation.

  4. Make shure that IIS is working properly. Type in localhost in your web browser. There should be some default webpage.

7 step – PHP Script for excel file generation

  1. Download recent version of PHPExcel from   Extract it and place Classes directory in your Default Web Page directory (C:\inetpub\wwwroot).
  2. Create file export.php and place it in your Default Web Page directory. (C:\inetpub\wwwroot)  Paste my script to it.
  3. Now you need to edit the script. You need path to your database file, username and password. You need to define name for your query. Script will generate excel file if you use this script like http://localhost/export.php?select=contacts it will run query defined in script with that name. This way you can generate files for many different queries using single php file.
  4. If you go to http://localhost/export.php?select=contacts it will create Excel file with name constructed using query name, current date and time like contacts2013-03-18×17:24.xlsx

The script

'/usr/local/tmp' // If you have a large file you can cache it optional
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

/** connection with the database 1.0 */

//here is connection to your Firebird database.
$dbh = ibase_connect ( $host, $username, $password ) or die ("error in db connect");
$query = ibase_prepare($stmt);

/** Create a new PHPExcel object 1.0 */
$objPHPExcel = new PHPExcel();

/** Loop through the result set 1.0 */
$rowNumber = 1; //start in cell 1
while ($row = ibase_fetch_row($result)) {
$col = 'A'; // start at column A
foreach($row as $cell) {

/** Create Excel 2007 file with writer 1.0 */
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$select.$date."x".$time.'.xlsx"'); //using query name, current date and time as file name
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');


Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *