[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

formatting excel export of PostgreSQL using php

Posted on 2006-06-06
4
Medium Priority
?
4,238 Views
Last Modified: 2013-12-12
Hello All,

I am using the following php that I have ported from MySQL to PostgreSQL and it pulls the data out fine. However it returns each row in a single cell of the .xls file. I got this off another posting on this site.
Please help me to understand the methods in place here.
Is there a way to separate each column of each row to separate cells in the excel file?


####################################################

<?php

pg_connect("dbname=DATABASE user=USER password=PASS") or die("Couldn't Connect ".pg_last_error()); // Connect to the Database

$select = "SELECT * FROM table";
$export = pg_query ( $select ) or die ( "Sql error : " . pg_error( ) );
$fields = pg_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= pg_field_name( $export , $i ) . "\t";
}

while( $row = pg_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n" . "<br />";
}
$data = str_replace( "\r" , "" , $data );



if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=OUTPUT.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

?>


###############################################################

Any help would be greatly appreciated.

Thanks for looking!
0
Comment
Question by:matzofast
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 1000 total points
ID: 16850037
Can you save the output and present it here rather than load it into Excel.

A few pointers though.

As you are using a tab separater, do you need to escape quotes?

And ...

foreach( $row as $value )
    {
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }

could probably be written as ...

$line = implode("\t", $row) . "\n";


Also, the trailing <br /> is also probably not required.

0
 
LVL 2

Assisted Solution

by:M_N_M
M_N_M earned 1000 total points
ID: 17196151
hi,

this works, it's only a bit slow to open the DLL..
<?
            $xl = new COM("excel.application"); // open DLL
//            $xl->workbooks->open("c:\\test.xlt"); // use this if you want to start from a template..
            $xl->workbooks->add();                     // use this if you want to start from a blanc workbook
            for ($i=1;$i<=5;$i++)
                  $xl->activesheet->cells($i,1)->value = "test".$i; // will place test1,2,3,.. in cells A1,2,3,..

            $xl->activesheet->cells(1,1)->font->bold = True; // will place a cell in bold
            $xl->activesheet->saveas("c:\\blabla.xls"); // will save the tekstfile to "c:\blabla.xls" (on the server!)
            $xl->quit; // close excel, otherwise it'll stay in the memory..
            $xl = null; // release the variable
            echo "done!";
?>

Grtz
0
 

Author Comment

by:matzofast
ID: 17197859
thanks for your creative approaches to this topic. I have split the points evenly because I actually went with a simpler solution that merely had the php print out tables that excel inturn reads as cells.

Thanks for all your ideas! great work.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 17198923
Yes. You can use Excel's "Get External Data", Normally you would get data from a database, you can also use Excel to get data from a web page. Add in auto refresh on open, and you get the latest details every time.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month19 days, 13 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question