?
Solved

quick question. Can i import records in Excel using PHP

Posted on 2005-04-12
34
Medium Priority
?
348 Views
Last Modified: 2008-02-01
I have reservation table with a number of fields. Could I somehow show those fields in an excel spreadsheet. How would i if it can be done
0
Comment
Question by:acslater
  • 11
  • 9
  • 5
  • +3
34 Comments
 
LVL 6

Expert Comment

by:alextr2003fr
ID: 13763519
yes you can, try to look here : http://www.zend.com/codex.php?id=542&single=1
hope it helps
0
 
LVL 32

Accepted Solution

by:
ldbkutty earned 800 total points
ID: 13763531
For writing to a simple CSV format file:

<?php

// DB Connection here

$select = "SELECT * FROM table_name";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

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

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

$data = str_replace( "\r" , "" , $data );

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

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

?>

Make sure you dont output any HTML or echo tags before "header" function is called. (ie) header function should be called before any output is sent to the browser!

For exporting the results into Excel with formatted display and/or additional Excel related tasks, try one of these:

http://www.web-aware.com/biff/
http://sourceforge.net/projects/php-doc-xls-gen/
http://www.apg88.com/apgForm/
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13763629
Ldbkutty is spot on, but I'd suggest you use the real MIME type for the download rather than the generic octet-stream, which would be:

header("Content-type: application/vnd.ms-excel");

Although strictly speaking, the format could also be correctly specified as text/plain or text/csv as it is really plain text and not a binary Excel file.

http://www.shaftek.org/publications/drafts/mime-csv/draft-shafranovich-mime-csv-03.html
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:Promethyl
ID: 13764037
My advice is to output the data as a CSV,  using the header specified.
0
 

Author Comment

by:acslater
ID: 13765281
That code work perfectly in Mozillia Guys but in IE 6 it comes up with an error....

Internet Explorer was not able to open this internet Site...


Is there a work around for this.

It would be greatly appreciated
0
 
LVL 1

Expert Comment

by:djs120
ID: 13766150
I get the same error
0
 
LVL 32

Expert Comment

by:ldbkutty
ID: 13766295
1) If the Excel document is opened, close it and try again.

2) From my googling: Remove the "no-cache" header statement - try just for the sake if it works or not!
0
 

Author Comment

by:acslater
ID: 13771990
Well i tried taking out that line but it never worked. Is it something with Internet Explorer. Can post code i have
0
 
LVL 7

Assisted Solution

by:Promethyl
Promethyl earned 600 total points
ID: 13772052
HEre' s an example I'm told works. I can't find my copy of when I made it work cross-browser.

My web application allows user to select certain part of my MySQL table for export and the route I want to offer is either XLS file or CSV.

Exporting and saving works just fine in both formats, but ... Excel does not want to show data correctly: instead of the value "441234567890" in one column it displays "4.41234E+11". The routes that I've already explored are:
1. Adding " ' " in front of the numbers to tell excel to treat them as text -- Excel treats info as text BUT displays the '-symbol and confuses users
2. Putting brackets around the value -- Excel ignores them and treats info as numbers and does the E+11 trick again

Is there a way to pass to excel column formats with the data -- number formats, column width, etc?

Or has someone found a way to tell Excel that values should be treated as text without changing information displayed in Excel?

Thanks for your help,
Oleg
PS ... here is the current code:
<?php
// Here we tell the browser that this is an excel file.
header("Content-type: application/octet-stream");
header("Content-disposition: attachment; filename=SMSRealty-Sales-Leads-".date("Y-m-d").".csv");
header("Pragma: no-cache");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Expires: 0");
//Variables to connect to MySQL database
error_reporting (E_ALL);
$user = "user"; //MySQL Username
$password = "password"; // MySQL Username's Password
$dbname= "dbname"; //Database Name
$tablename ="tablename"; // DB Table Name
$server="server"; //MySQL server name

$global_dbh = mysql_connect("localhost", $user, $password); // This establishes the database connection

mysql_select_db($dbname, $global_dbh); // Select what database to use

// You do not need to change the function
echo "Col1,Col2,Col3,Col4,Col5\n";
$query = mysql_query("SELECT col1, col2, col3, col4, col5, col6 FROM tablename WHERE col7='$variable' AND col8='Y' AND col8 BETWEEN '$startingtime' AND '$endtime'");
while($row = mysql_fetch_array($query)){
echo "$row[col1],$row[col2],$row[col3],$row[col4],$row[col5],$row[col6]\n";
}
?>
0
 

Author Comment

by:acslater
ID: 13772417
well i tried that code but still didnt download: here is what i tried:

<?php
// Here we tell the browser that this is an excel file.
header("Content-type: application/octet-stream");
header("Content-disposition: attachment; filename=SMSRealty-Sales-Leads-".date("Y-m-d").".csv");
header("Pragma: no-cache");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Expires: 0");
//Variables to connect to MySQL database
error_reporting (E_ALL);
include "db_connection.php";

$connect = db_connect();
mysql_select_db($hbc, $connect); // Select what database to use

// You do not need to change the function
echo "Col1,Col2,Col3,Col4,Col5\n";
$query = mysql_query("SELECT col1, col2, col3, col4, col5, col6 FROM tablename WHERE col7='$variable' AND col8='Y' AND col8 BETWEEN '$startingtime' AND '$endtime'");
while($row = mysql_fetch_array($query)){
echo "$row[col1],$row[col2],$row[col3],$row[col4],$row[col5],$row[col6]\n";


}
?>

0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13772520
Here's the one which worked for me in Firefox and IE. It's been tested and verified.


<?php
// Here we tell the browser that this is an excel file.
header("Content-type: application/octet-stream");
header("Content-disposition: attachment; filename=MyData-".date("Y-m-d").".csv");
header("Pragma: no-cache");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Expires: 0");
//Variables to connect to MySQL database
error_reporting (E_ALL);
include ('../db.php');

// You do not need to change the function
$rs=sql_query("select author,post_text from hk_posts limit 10");

while (NULL!=$r=mysql_fetch_assoc($rs)) {
    echo implode('","',$r)."\n";
}
?>
0
 
LVL 1

Expert Comment

by:djs120
ID: 13772800
Are you using SSL?  I'm having the same problem using SSL, but similar code works fine on the same server without SSL.
0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13772859
No. Straight through.
0
 
LVL 1

Assisted Solution

by:djs120
djs120 earned 600 total points
ID: 13772936
I was asking acslater actually, I think we're both having this problem for the same reason.  I just logged into my bank website and I can't right click->Save target as... on any pages in IE, I get that same error.  But it works in Firefox.  It has something to do with IE and SSL I think.
0
 

Author Comment

by:acslater
ID: 13773008
yes we are using ssl so you dont think there is any way about it?
0
 

Author Comment

by:acslater
ID: 13773018
ill post it in another open question to see if anyone knows anything bout it? thanks lads
0
 

Author Comment

by:acslater
ID: 13773060
actually promethyl i didnt see your post for it working in IE ill try it now

Maybe it has something to do with SSL that wont work
0
 
LVL 1

Expert Comment

by:djs120
ID: 13773075
"yes we are using ssl so you dont think there is any way about it?"

I certainly hope there is a way to do it with SSL, I just don't know what it is. :)  We've found a common thread though, and hopefully someone will know how to fix it.  I've tested the exact same code on a non SSL server, and it works fine.
0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13773111
I think so. I believe there's some trick to doing it in SSL. If the program works, it's some workaround in the SSL. Get it working in regular HTTP and then go to the secure http.
0
 
LVL 1

Expert Comment

by:djs120
ID: 13773129
Try this in Internet Explorer Options, Advanced Tab, under Security, make sure "Do not save encrypted pages to disk" is unchecked.
0
 

Author Comment

by:acslater
ID: 13773198
yes checked that and it wasnt checked
0
 

Author Comment

by:acslater
ID: 13773230
// You do not need to change the function
$rs=sql_query("select author,post_text from hk_posts limit 10");

while (NULL!=$r=mysql_fetch_assoc($rs)) {
    echo implode('","',$r)."\n";
}
?>
 prmethyl i havnt tried the second one yet but see the echo line should it be:

echo implode('","',$rs)."\n"; and not


echo implode('","',$r)."\n";
0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13773259
Negative.

while (NULL!=$r=mysql_fetch_assoc($rs)) {
    echo implode('","',$r)."\n";
}

In English pseduocode, for each record for the database, convert the array into a string seperated by quote comma quote, and add a newline break at the end.

You line would implode the recordset resource, which wouldn't work, since it's not an array, it's a stringed resource result.
0
 

Author Comment

by:acslater
ID: 13773412
well lads got it working without SSL so it was SSL. ill look into it more and post again to see if anyone can help and let ye know if get it working
0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13773459
Do other sites work in SSL, besides this program?
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13773697
This is horrible syntax:

while (NULL!=$r=mysql_fetch_assoc($rs)) {

This will do the same thing because the while terminates on a non-true return value for the loop condition:

while ($r = mysql_fetch_assoc($rs)) {
0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13774003
You have your way, I have mine. Both work./
 
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13776002
Yours works because the operator precedence happens to work out (it would break if you reversed the order) and you have picked a value to compare with that's not a legal return value for the function (great idea), but by a side-effect of the implicit coercion, happens to work. Used with expressions that can legitimately give rise to NULL values, this approach could fail. And this is meant to be guidance for someone less able than yourself?

while (false!=$r=mysql_fetch_assoc($rs)) {

would be marginally more acceptable, but it's just wasteful, obfuscating code as that precise comparison is implicit in the definition of while. I mean, would you write garbage like this:

if(true==$a=1)

Comparing to NULL is almost never a good idea - that's why is_null() exists. It's right up there with using isset() to detect the presence of array keys; it's just not reliable.

Whether your syntax works is a secondary consideration - we're meant to be setting a good example here, not creating Zend certification puzzles.
0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13776142
>And this is meant to be guidance for someone less able than yourself?

Yes. Indeed.  Mine works because the program works the way I wrote the code.

It is more than reliable in my usage.

This is not a flame forum, take your comments elsewhere.

 
0
 
LVL 32

Expert Comment

by:ldbkutty
ID: 13776455
This is neither a flame forum nor an yet-another-forum, its *experts exchange*. If something is unusual or not required and if an expert points out, please accept the fact or try to explain why it is not unusual. Squinky is been in EE for a long time setting up a good example for all PHP experts.


0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13776703
Roger.
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13777164
I'm not flaming anyone. I've merely pointed out some legitimate technical issues with the way you've chosen to write something, and you have not provided any justification for your choices. It's a purely academic exercise, nothing personal. The code you've been submitting on here has been generally excellent and you're clearly very capable - I was surprised to see a construct like that from you.

> Mine works because the program works the way I wrote the code.

You're relying on a side effect and implicit execution order, directly against the advice of (to cite just one source) http://www.eecs.harvard.edu/~ellard/CS50-95/programming-style.html item 15. It's just not a good way to do it, and especially not a good thing to advise someone else to do.

> It is more than reliable in my usage.

But it's not for you. You're meant to be showing someone else the right way to do stuff.
0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13777993
I concede all your points. Last think I want to do is get into an argument with the PHP Wizard.

There are a couple things I want to say for clarity and giving credit where due.

I don't reinvent the wheel. That is, if I see a topic that can be answered by searching the web, I do so. Most of the code I posted here in this thread isn't mine. I defend it on the basis of sheer arrogance on my part.
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13779777
That's very gracious of you Promethyl. I look forward to working with you in future.

> please accept the fact or try to explain why

I'd say don't necessarily accept the 'fact' - it's perfectly reasonable to question what I, or any other expert says. I try to code in a logical way (at least most of the time), so that if asked, I can justify my choices from first principles. On the other hand, it's reasonable to take a proposition at face value if you don't have grounds to object to it. Also, asking why something is so is not the same as objecting to it. I'm also quite happy to concede a point in the face of logical counter-argument - I certainly don't know everything (I mean, this is just embarrassing - I clearly know nothing about WSDL: http://www.experts-exchange.com/Web/Web_Languages/PHP/Q_21372808.html), but I'm always trying to get there...
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

807 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