Warning: fopen(/wp-content/plugins/devformatter/geshi/geshi/html4strict.php) [function.fopen]: failed to open stream: No such file or directory in /home/captatio/egermeier/wp-content/plugins/devformatter/devgeshi.php on line 103

Warning: fopen(/wp-content/plugins/devformatter/geshi/geshi/html.php) [function.fopen]: failed to open stream: No such file or directory in /home/captatio/egermeier/wp-content/plugins/devformatter/devgeshi.php on line 103

Warning: fopen(/wp-content/plugins/devformatter/geshi/geshi/php.php) [function.fopen]: failed to open stream: No such file or directory in /home/captatio/egermeier/wp-content/plugins/devformatter/devgeshi.php on line 103

Warning: fopen(/wp-content/plugins/devformatter/geshi/geshi/php.php) [function.fopen]: failed to open stream: No such file or directory in /home/captatio/egermeier/wp-content/plugins/devformatter/devgeshi.php on line 103

Warning: fopen(/wp-content/plugins/devformatter/geshi/geshi/php.php) [function.fopen]: failed to open stream: No such file or directory in /home/captatio/egermeier/wp-content/plugins/devformatter/devgeshi.php on line 103
Home > Uncategorized > Export data from firebird database to Excel using PHP

Export data from firebird database to Excel using PHP


Warning: fopen(/wp-content/plugins/devformatter/geshi/geshi/html4strict.php) [function.fopen]: failed to open stream: No such file or directory in /home/captatio/egermeier/wp-content/plugins/devformatter/devgeshi.php on line 103

Warning: fopen(/wp-content/plugins/devformatter/geshi/geshi/html.php) [function.fopen]: failed to open stream: No such file or directory in /home/captatio/egermeier/wp-content/plugins/devformatter/devgeshi.php on line 103

Warning: fopen(/wp-content/plugins/devformatter/geshi/geshi/php.php) [function.fopen]: failed to open stream: No such file or directory in /home/captatio/egermeier/wp-content/plugins/devformatter/devgeshi.php on line 103

Warning: fopen(/wp-content/plugins/devformatter/geshi/geshi/php.php) [function.fopen]: failed to open stream: No such file or directory in /home/captatio/egermeier/wp-content/plugins/devformatter/devgeshi.php on line 103

Warning: fopen(/wp-content/plugins/devformatter/geshi/geshi/php.php) [function.fopen]: failed to open stream: No such file or directory in /home/captatio/egermeier/wp-content/plugins/devformatter/devgeshi.php on line 103

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
?>

Categories: Uncategorized Tags:
  1. July 22nd, 2013 at 10:34 | #1

    Aw, this was a really good post. Finding the time and actual
    effort to generate a good article… but what can I say… I hesitate a whole lot and never seem to get anything done.

  2. July 26th, 2013 at 13:00 | #2

    Just how to increase pressure from all aspects, such as for instance customer needs,
    ongoing management, reduce confidence, rising total wellness, the growing
    needs of the corporation, strengthening the regulatory framework and increase technological habits, it is actually time
    for finance institutions to take into account big and deliver robust solutions
    dedicated to technologies that lessen operating costs and increase their
    business efficiency. Among the agonizing challenges of finance institutions could be the allocation of resources,
    assets, technology, and especially the following dominant vibration community.
    This is why, they must focus their aspects of banking technology to provide products which makes it because of their business needs.

    They’ve a totally organized framework, in order to attenuate risk and develop customers. There exists a sought after for technology platforms to earnestly be engaged in the development of the crop. To help you improve the total value of financial products and services offer finance institutions have a fantastic technique for business consulting, business analysis, project implementation, project support, and hardware and software. This may help them to eradicate complex planning and efficient economic infrastructure and their needs. This may solve them for more support and networking. Today, banking and finance institutions to go from the original principles of technology-based banking. Indeed, banks have changed the way and pacified risk. They weaken the medial side ramifications of global competition and economic slowdown. It’s triggered the
    growth of commercial explosives under real pressure.

    They enhance innovation, technological skills and creativity.
    They’ve been solving long-term difficulties with on line payments, online transactions, banking, business, and technology policy. Even though they’ve been managing their data along with other
    information resources enable the transparency of the method and
    acquire a handle on. Banks and finance institutions also put their programs and
    dilemmas in the banking and financial conference.

    One of the key success factors of continuous banking
    and finance institutions is constant innovation, technology adoption and stimulate
    business services. They are a part of strategic planning, management and comprehensive method of the study, evaluation, communication, integration
    and collaboration of IT, information technology and business development.
    This course of action can give attention to adaptation to enhance the complex catering to the growing needs of corporate clients.
    Specifies fine baking, mission and objectives of the
    enterprise projected increase in the longer term. Actually several challenges in the banking sector, banks are floundering towards just
    one more ripple of growth, development and innovation.
    Regularly encourage investors, businesses, to systematically define their criteria and the logic of business processes, to ensure that banking authorities can perhaps work
    with them to complete higher income, customer service and compliance with
    regulations. Actually, banking conference to share
    with you and represent their banking and tax issues.

  3. January 16th, 2014 at 18:39 | #3

    You ought to be a part of a contest for one of the greatest
    websites on the net. I’m going to highly recommend this website!

  4. June 24th, 2014 at 08:16 | #4

    Fabuła zbliżona każdemu, polecam publikację

  1. No trackbacks yet.
*