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 http://www.php.net/downloads.php 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 http://phpexcel.codeplex.com   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

 PHP |  copy code |? 
01
<?php
02
 
03
/**
04
 * FireExport PHP script 
05
 *
06
 * It is script for generating excel files from firebird database.
07
 * Many thanks to frank1973 (http://phpexcel.codeplex.com/discussions/392942) for posting script for exporting from MySQL.
08
 * It is modification of this script for FireBird.
09
 *
10
 * This library is free software; you can redistribute it and/or
11
 * modify it under the terms of the Creative Commons Attribution 3.0 License. 
12
 * http://creativecommons.org/licenses/by/3.0/
13
 * So if you want to reuse it just inform about my work in it.
14
 *
15
 * This library is distributed in the hope that it will be useful,
16
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
18
 *
19
 * @copyright  Maciej Egermeier  http://www.egermeier.com
20
 * @license    http://creativecommons.org/licenses/by/3.0/
21
 * @version    1.0, 2013-03-18
22
 */
23
 
24
$select=$_GET["select"]; //selected query
25
$date = (date ("Y-m-d")); //today
26
$time = (date ("H-i")); //current time
27
set_time_limit(0); //set time limit - reports may take long time
28
 
29
$host = 'localhost:C:/BAZALEFTHEND/LH_MULTI.GDB';  //!!!!!here you need to specify your database path.
30
$username='SYSDBA';   //!!!!!username for database
31
$password='masterkey';   //!!!!!password for database user
32
 
33
//!!!! change this
34
//It will wirk if you run this script like this:  http://localhost/export.php?select=adresy
35
if ($select=="adresy"){ //checking if specific query is needed and change query text
36
 $stmt="Select * from MAC_ADRESY"; //here you need to define your query to database. It can be also view.
37
}
38
 
39
//if you need another query
40
//If you run your script like this: http://localhost/export.php?select=contacts it will run your query
41
//uncomment this:
42
//
43
//if ($select=="contacts"){
44
// $stmt="Select * from contacts"; //here you need to define your query to database
45
//}
46
 
47
 
48
/** require the PHPExcel file 1.0 */
49
    require 'Classes/PHPExcel.php';
50
 
51
/** Set Memory Limit 1.0 */
52
    ini_set("memory_limit","500M"); // set your memory limit in the case of memory problem
53
 
54
/** Caching to discISAM 1.0*/
55
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_discISAM;
56
$cacheSettings = array( 'dir'  => '/usr/local/tmp' // If you have a large file you can cache it optional
57
                      );
58
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
59
 
60
/** connection with the database 1.0 */
61
 
62
//here is connection to your Firebird database.
63
$dbh = ibase_connect ( $host, $username, $password ) or die ("error in db connect");
64
$query = ibase_prepare($stmt);
65
$result=ibase_execute($query);
66
 
67
 
68
/** Create a new PHPExcel object 1.0 */
69
   $objPHPExcel = new PHPExcel();
70
   $objPHPExcel->getActiveSheet()->setTitle('Data');
71
 
72
 
73
/** Loop through the result set 1.0 */
74
    $rowNumber = 1; //start in cell 1
75
    while ($row = ibase_fetch_row($result)) {
76
       $col = 'A'; // start at column A
77
       foreach($row as $cell) {
78
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
79
          $col++;
80
       }
81
       $rowNumber++;
82
}
83
 
84
/** Create Excel 2007 file with writer 1.0 */
85
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
86
    header('Content-Disposition: attachment;filename="'.$select.$date."x".$time.'.xlsx"'); //using query name, current date and time as file name
87
    header('Cache-Control: max-age=0');
88
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
89
    $objWriter->save('php://output');
90
exit;
91
 
92
?>

Dodaj komentarz

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

Możesz użyć następujących tagów oraz atrybutów HTML-a: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">